Python and CX_Oracle Invalid SQL Statement-CodePudding

I'm writing a Python script to fetch some values from Oracle, but by the middle I have to set an ID to a package so it can create the corresponding view with the data I want.

I'm trying to execute:

ora_query = cursor.execute("EXECUTE VW_WEEKLY_CALL_LOG_PKG.SET_COMPANY_ID(P_COMPANY_ID => '1111111111')")

and it returns me:

cx_Oracle.DatabaseError: ORA-00900: invalid SQL statement

In SQL Developer I can use this execute statement and it gives me the outcome. Am I using the cursor.execute wrong?

This is the package:

create or replace package VW_WEEKLY_CALL_LOG_PKG as
  procedure SET_COMPANY_ID(P_COMPANY_ID VARCHAR2);

  function GET_COMPANY_ID
    return VARCHAR2;
    
end VW_WEEKLY_CALL_LOG_PKG;

And this is the package body:

create or replace package body VW_WEEKLY_CALL_LOG_PKG as
  G_COMPANY_ID   VARCHAR2(255);


  procedure SET_COMPANY_ID(P_COMPANY_ID VARCHAR2) as
  begin
    G_COMPANY_ID := P_COMPANY_ID;
  end;

  function GET_COMPANY_ID
    return VARCHAR2 is
  begin
    return G_COMPANY_ID;
  end;

  
end VW_WEEKLY_CALL_LOG_PKG;

CodePudding user response：

The statement you provided is not a valid SQL statement. It is a SQL*Plus command. You want to do something like this instead:

company_id = '1111111111'
cursor.callproc('VW_WEEKLY_CALL_LOG_PKG.SET_COMPANY_ID', [company_id])

CodePudding user response：

Ok I'm not really good explaining what I want but I'll try to make this as readable as possible.

So I have this table from database with lots of records and every week I want to send this records to certain companys. So what I did was create the packages that I've shown you before and this View :

  CREATE OR REPLACE FORCE VIEW "USER"."VIEW" ("NAME") AS 
  SELECT
'CALL_ASSOCIATION_KEY'
||','||'DATE_TIME'
||','||'CALLING_PARTY'
||','||'CALLING_PARTY_USER_NAME'
||','||'CALLED_PARTY'
||','||'CALLED_PARTY_USER_NAME'
||','||'PRESENTATION_PARTY'
||','||'CALL_TIME'
||','||'CALL_DIRECTION'
||','||'EVENT_FLAGS'
||','||'STATUS'
||','||'CALL_TYPE'  FROM DUAL
union all
SELECT
'K-'||CALL_ASSOCIATION_KEY
||','||DATE_TIME
||','||CALLING_PARTY
||','||CALLING_PARTY_USER_NAME
||','||CALLED_PARTY
||','||CALLED_PARTY_USER_NAME
||','||PRESENTATION_PARTY
||','||CALL_TIME
||','||CALL_DIRECTION
||','||REPLACE(EVENT_FLAGS, ', ', '-' )
||','||STATUS
||','||CALL_TYPE 
FROM
(
SELECT  
CALL_ASSOCIATION_KEY, 
TO_CHAR(CAST((FROM_TZ(CAST(TO_DATE('1970-01-01','YYYY-MM-DD') AS TIMESTAMP)   NUMTODSINTERVAL(DATE_TIME/1000,'SECOND'),'UTC') AT TIME ZONE DBTIMEZONE) AS DATE), 'YYYY-MM-DD HH24:MI:SS') AS DATE_TIME,
DECRYPT(CALLING_PARTY) AS CALLING_PARTY, 
CASE WHEN VW_WEEKLY_CALL_LOG_PKG.GET_COMPANY_ID = CALLING_PARTY_COMPANY_ID THEN DECRYPT(CALLING_PARTY_USER_NAME) ELSE '' END AS CALLING_PARTY_USER_NAME, 
DECRYPT(CALLED_PARTY) AS CALLED_PARTY, 
CASE WHEN VW_WEEKLY_CALL_LOG_PKG.GET_COMPANY_ID = CALLED_PARTY_COMPANY_ID THEN DECRYPT(CALLED_PARTY_USER_NAME) ELSE '' END AS CALLED_PARTY_USER_NAME,
PRESENTATION_PARTY, 
CALL_TIME, 
EVENT_FLAGS, 
CALL_DIRECTION, 
STATUS, 
CALL_TYPE
FROM ONC_CALLOG01.DUMP_CALL_LOG
 WHERE 

trunc(date '1970-01-01'   DATE_TIME/1000/60/60/24) >= trunc(sysdate-8)
AND
trunc(date '1970-01-01'   DATE_TIME/1000/60/60/24) < trunc(sysdate-1)
AND
(
CALLING_PARTY_COMPANY_ID = VW_WEEKLY_CALL_LOG_PKG.GET_COMPANY_ID
OR 
CALLED_PARTY_COMPANY_ID = VW_WEEKLY_CALL_LOG_PKG.GET_COMPANY_ID
));

And this view acts as a cursor for all companies that have enabled this option.

This view will generate all the records from the past 7 days. And I want to get the output from this view and transform it to a CSV in python

When I reproduce the querys on SQL Developer (this is an Oracle DB) I can get all the records, but in my script, even after @Anthony Tuininga input, I can't get any of this records to a variable.

In python I have this:

try:
    with cx_Oracle.connect(
            config.username,
            config.password,
            dsn) as connection:
                with connection.cursor() as cursor:
                    print("DATABASE VERSION: "   connection.version)
                    getMailReport = cursor.execute("SELECT COMPANY_ID, COMPANY_NAME, TO_MAIL_REPORT, TO_MAIL_REPORT2 FROM CALL_LOG_COMPANY WHERE ENABLED='1'")

                    companys=[]
                    for companyID, companyName, toMail, toMail2 in getMailReport:
                        thistuple=(companyID, companyName, toMail, toMail2)
                        companys.append(thistuple)
                        companyID = '1111111111'
                        ora_query = cursor.callproc("VW_WEEKLY_CALL_LOG_PKG.SET_COMPANY_ID",[companyID] )
                        ora2_query = cursor.execute("SELECT * FROM VW_WEEKLY_CALL_LOG_TEST")
                        cursor.execute("commit")
                        aaa = list(ora2_query)

And still I get nothing from this query