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;

Answer 1

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