run sql file with input from python

Question

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.

I believe @davidm is correct, but I would add one more thing (more correctly remove one thing). Get rid of the EXCEPTION handler. It does nothing that you would not get if it simply does not exist. but it does mislead you. For example as error occurs in the function generate_application_id your exception handler the resulting error message indicate the` raise` statement, not the point where the error occurred. Without the exception handler in would point to the line in generate_application_id — Belayer
– Belayer, Commented Aug 15, 2021 at 17:49
If your organization standard requires the exception handler then at lease look into FORMAT_ERROR_BACKTRACE and FORMAT_ERROR_STACK for your version. — Belayer
– Belayer, Commented Aug 15, 2021 at 17:52

davidm · Accepted Answer · 2021-08-16 08:55:53Z

1

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)

edited Aug 16, 2021 at 8:55

answered Aug 13, 2021 at 13:21

davidm

1,78014 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

mona shiri Over a year ago

thank you for the answer, but my problem is that these 3 commands don't run without any error to recognize the reason.

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

mona shiri Over a year ago

there is not eny error, just dosn't work.

davidm Over a year ago

What should your script do?

davidm Over a year ago

You asked ...I should run it from a python code with some inputs, but it doesn't work...

mona shiri Over a year ago

the goal for whole this script its to install an apex application, when I run all scripts from toad everything is ok, but when I run it from python, it is such that some parts dosnt't work and I get errors in next steps. fro example if 3 command that I just mentioned , run correct, the next step that is installing an apex application should run without error, but i see errors which means these parts didn't run correctly.

|

Collectives™ on Stack Overflow

run sql file with input from python

1 Answer 1

PL/SQL

Python

Linux

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

PL/SQL

Python

Linux

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related