Passing parameters to SQLPlus script from Python

Question

I am trying to pass variables from python script to PL/SQLscript. But I am getting "SP2-0310: unable to open file" error

Below is the code:

def runSqlQuery():
    connectString = '<username>/"<password>"@//MYHOST:1521/TEST'
    var1=input('Enter VAR1: ')
    var2=input('Enter VAR2: ')
    session = ['sqlplus', '-S', connectString,f'@a.sql {var1} {var2}']
    subprocess.call(session)
runSqlQuery()

SET SERVEROUTPUT ON
 DECLARE
    TYPE usr IS VARRAY(100) OF VARCHAR2(100);
    v_new_rw_user           usr:=usr('&1');
    v_obj_owner             usr:=usr('&2');
 BEGIN
    FOR j IN v_new_rw_user.first..v_new_rw_user.last
    LOOP
        DBMS_OUTPUT.PUT_LINE('Read Only User : ' || UPPER(v_new_rw_user(j)) ||CHR(10));
        FOR i IN v_obj_owner.first..v_obj_owner.last
        LOOP
            DBMS_OUTPUT.PUT_LINE(CHR(10)||'Database Schema : ' || UPPER(v_obj_owner(i)) ||CHR(10));
        END LOOP;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(CHR(10)||'ERROR CODE : ' || SQLCODE ||CHR(10) || 'ERROR MESSAGE: '|| SQLERRM || CHR(10));
END;
/
EXIT;


Appreciate any help. Thanks in advance

a.sql is placed in same location as python script. I able to run the plsql without passing variables from python where variables are provided when plsql script is invoked. But my requirement is to pass variables from python script — Rajesh
– Rajesh, Commented Jul 6, 2021 at 16:51

Marmite Bomber · Accepted Answer · 2021-07-06 17:00:00Z

1

With the help of this tutorial the below code works

TestSQPPlus.py

from subprocess import Popen, PIPE

def runSqlQuery():
    connectString = '<username>/"<password>"@//MYHOST:1521/TEST'
    var1=input('Enter VAR1: ')
    var2=input('Enter VAR2: ')
    sqlCommand = f'@a.sql {var1} {var2}'
    print(sqlCommand)
    session =  Popen(['sqlplus', '-S', connectString], stdin=PIPE, stdout=PIPE, stderr=PIPE)
    session.stdin.write(sqlCommand.encode())
    return session.communicate()
    
queryResult, errorMessage = runSqlQuery()
print(queryResult)
print(errorMessage)

a.sql

SET SERVEROUTPUT ON
 DECLARE
    TYPE usr IS VARRAY(100) OF VARCHAR2(100);
    v_new_rw_user           usr:=usr('&1');
    v_obj_owner             usr:=usr('&2');
 BEGIN
    FOR j IN v_new_rw_user.first..v_new_rw_user.last
    LOOP
        DBMS_OUTPUT.PUT_LINE('Read Only User : ' || UPPER(v_new_rw_user(j)) ||CHR(10));
        FOR i IN v_obj_owner.first..v_obj_owner.last
        LOOP
            DBMS_OUTPUT.PUT_LINE(CHR(10)||'Database Schema : ' || UPPER(v_obj_owner(i)) ||CHR(10));
        END LOOP;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(CHR(10)||'ERROR CODE : ' || SQLCODE ||CHR(10) || 'ERROR MESSAGE: '|| SQLERRM || CHR(10));
END;
/
quit

Example of execution (output cut for brevity and formatted)

python TestSQPPlus.py
Enter VAR1: x
Enter VAR2: y

Read Only User : X
Database Schema : Y
PL/SQL-Prozedur erfolgreich abgeschlossen.

Note - if you want to enter more elements in the VARRAY you must inject as follows (use at own risc)

python TestSQPPlus.py
Enter VAR1: x1','x2
Enter VAR2: y1','y2
@a.sql x1','x2 y1','y2

Read Only User : X1
Database Schema : Y1
Database Schema : Y2
Read Only User : X2
Database Schema : Y1
Database Schema : Y2
PL/SQL-Prozedur erfolgreich abgeschlossen.

answered Jul 6, 2021 at 17:00

Marmite Bomber

21.3k4 gold badges31 silver badges59 bronze badges

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

4 Comments

Rajesh Over a year ago

Thank you, it worked. But curious to what is the risk of using above

Marmite Bomber Over a year ago

@Rajesh, well check unter the title "SQL Injection" you'll find a lot of explanation. You may also accept the answer;)

Rajesh Over a year ago

I did use the answer :). Thanks

Marmite Bomber Over a year ago

If you find the answer usefull, you may also accept it;) @Rajesh

Collectives™ on Stack Overflow

Passing parameters to SQLPlus script from Python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related