Python and CX_Oracle Invalid SQL Statement

Question

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;

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — another victim of the mouse
– another victim of the mouse, Commented May 12, 2022 at 18:52
EXEC[UTE] statement is an SQL Plus command, not a SQL statement. SQL Developer processes SQL Plus commands as well — astentx
– astentx, Commented May 12, 2022 at 21:14

Anthony Tuininga · Accepted Answer · 2022-05-12 20:14:35Z

3

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])

answered May 12, 2022 at 20:14

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

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

2 Comments

Fitchad1904 Over a year ago

This helped me a lot! I wasn't aware that the packages used the "callproc" too. I now know what I was doing wrong. I wasn't fetching anything from the cursor after setting the company_id and doing a select statement to get the info from the DB. Thanks!

Anthony Tuininga Over a year ago

Glad you got it figured out!

Collectives™ on Stack Overflow

Python and CX_Oracle Invalid SQL Statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related