Can I use SQLPlus to execute a .sql file?
12,911
Solution 1
see this tutorial: http://moizmuhammad.wordpress.com/2012/01/31/run-oracle-commands-from-python-via-sql-plus/
i.e.
from subprocess import Popen, PIPE
#function that takes the sqlCommand and connectString and retuns the output and #error string (if any)
def runSqlQuery(sqlCommand, connectString):
session = Popen(['sqlplus', '-S', connectString], stdin=PIPE, stdout=PIPE, stderr=PIPE)
session.stdin.write(sqlCommand)
return session.communicate()
should do it (where sqlCmmand is "@scriptname.sql").
Solution 2
Below is an example of how to do this. You need to read the file and pass the entire string as a command.
(username, password, host) = ("user","password","host")
conn_string = " %s/%s@%s "% (username,password,host)
session = Popen(['sqlplus','-S', conn_string], stdin=PIPE, stdout=PIPE, stderr=PIPE)
logging.info("Starting sqlplus")
sql_file = '%s/%s' % (sql_folder, file)
logging.info("Running " + sql_file)
f= open(sql_file,'r')
cmd = f.read()
session.stdin.write(cmd)
stdout, stderr = session.communicate()
Related videos on Youtube
Author by
Steve83
Updated on September 16, 2022Comments
-
Steve83 over 1 year
I have a .sql file with the following code:
delete from stalist where stalistid=4944 / insert into stalist (stalistid, staid) (select distinct 4944, staid from staref Where staid in( 3797,3798, 3870,4459, 3871,3872, 3876,3877, 0 )) / commit /
I would like to use Python to execute this file from SQLPlus. Is this possible? I do not have any python experience in this type of work and could use some help. Thank you!