0

I am trying to parse an sqlscript file based on a delimiter ";" and then subsequently call Cx_Oracle to connect and execute statements on the DB server. I have run into an issue with a cursor related block of code. My call structure is thus:

 ScriptHandle = open(filepath)
 SqlScript = ScriptHandle.read()
 SqlCommands = SqlScript.split(';')
    for sqlcommand in SqlCommands:
        print sqlcommand,'\n'*3
        if sqlcommand:  
            ODBCCon.ExecuteWithCx_Oracle(cursor, sqlcommand)

The problem I have comes with the following sql block:

DECLARE CURSOR date_cur IS (select calendar_date 
        from cg_calendar dates
        where dates.calendar_date between '30-Jun-2014' and '31-Jul-2015'
        and global_business_or_holiday = 'B');

BEGIN
FOR date_rec in date_cur LOOP
insert into fc_pos
SELECT PP.acctid,
        PP.mgrid,
        PP.activitydt,
        PP.secid,
        PP.shrparamt,
        PP.lclmktval,
        PP.usdmktval,
        SM.asset_name_1,
        SM.asset_name_2,
        SM.cg_sym,
        SM.fc_local_crncy_cd,
        SM.fc_local_crncy_id,
        SM.fc_trade_cd,
        substr(SM.asset_name_1, 17,3) as against_crncy_cd
    FROM FC_acct_mgr AM, asset SM, ma_mktval PP
WHERE PP.dw_asset_id = SM.dw_asset_id
AND PP.secid = SM.asset_id
AND PP.activitydt = date_rec.calendar_date
AND AM.acctid = PP.acctid
AND AM.mgrid = PP.mgrid
AND SM.asset_categ_cd = 'FC';
END LOOP;
END;

The above python parse step disassociates the above code based on delimiter ";" where as I need to treat above as one block starting at the DECLARE and ending at the END;

How can I accomplish it from python end. I have been unable to make any headway on this and this is a legacy process flow that I am automating.

Thanks in advance.

Improve this question
4
  • possible duplicate of Parse SQL file with PL/SQL and DML/DDL using cx_Oracle in python Commented Aug 21, 2015 at 2:07
  • Hello I followed the instruction on the above mentioned hyperlink for parsing sql file with PL/SQL and DML/DDL using cx_Oracle. I switched to using SQLPLus using sub_process call from python. I hit a snag though: trying to run the above querry with all the select statement replaced by a NULL never returns where as removing the above PL/SQL block from the script file returns with everything executed as expected and quickly. Commented Aug 26, 2015 at 17:12
  • That's because the select is in the middle of a loop; you're looping infinitely. Commented Aug 26, 2015 at 17:16
  • Thanks Ben. After further research, I figured out that the issue was that I was not ending the PL/SQL blocks with a trailing / which was setting off an infinite loop where SQlPlus was waiting for further input from the script. I think I am in business now. Thanks for pointing me to subprocess way of doing things.I reckon its much cleaner and provides a clear delineation between producer and consumer of the SQL step. Commented Aug 28, 2015 at 18:44
Related questions
1
Type validation for cx_Oracle.OracleCursor
6
Python-Oracle Passing in a Cursor Out Parameter
6
python and cx_Oracle - dynamic cursor.setinputsizes
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.