CODEWITHSUNDEEP
pythonplsql
mona shiri
mona shiri

Reputation: 57

run sql file with input from python

I have a .sql file which gets an input and runs, I should run it from a python code with some inputs, but it doesn't work. What is problem?

sql file:

declare
  --define variables
   v_workspace_id NUMBER;

BEGIN

  select workspace_id into v_workspace_id
  from apex_workspaces
  where workspace = upper('&1');
  DBMS_OUTPUT.PUT_LINE(v_workspace_id);

  apex_application_install.set_workspace_id( v_workspace_id );
  apex_application_install.generate_application_id;
  apex_application_install.generate_offset;

EXCEPTION
    WHEN OTHERS    
    THEN
            RAISE;

END;

part of python file to run this file:

cmd_sql = 'echo quit | sqlplus -S ' + DB_USER + '/' + DB_USER_PWD + '@' + DB_HOST + ' @' + SQL_PATH + '\\' + 'install_apex_apps.sql ' + user_name + ' >> ' + LOG_FILE
os.system(cmd_sql)

user_name is given as an input to sql file.

Upvotes: 0

Views: 195

Answers (1)

davidm
davidm

Reputation: 1760

PL/SQL

Change code to (note the /). Also as @Belayer sugessted in the comment section remove the EXCEPTION section.

SET SERVEROUTPUT ON;
declare
  --define variables
   v_workspace_id NUMBER;

BEGIN

  select workspace_id into v_workspace_id
  from apex_workspaces
  where workspace = upper('&1');
  DBMS_OUTPUT.PUT_LINE(v_workspace_id);

  apex_application_install.set_workspace_id( v_workspace_id );
  apex_application_install.generate_application_id;
  apex_application_install.generate_offset;

END;
/

Python

Linux


import os

DB_USER = 'xxx'
DB_USER_PWD = 'xxx'
DB_HOST = 'xxx'
SQL_PATH = '/home/xxx/Documents/stack/'

LOG_FILE = '/home/xxx/Documents/stack/log.txt'

user_name = 'xxx'

# I override the log file with > for appending use >>
cmd_sql = 'echo quit | sqlplus -S ' + DB_USER + '/' + DB_USER_PWD + '@' + DB_HOST + ' @' + SQL_PATH + 'install_apex_apps.sql ' + user_name + ' > ' + LOG_FILE
os.system(cmd_sql)

Upvotes: 1

Related Questions