PLS-00201: identifier must be declared

[urosdigital]

Hello,

I have a the following function:

 FUNCTION myfunction(p_msisdn     IN VARCHAR2,
                                       p_carrierTxt IN VARCHAR2, 
                                       p_username   IN VARCHAR2,
                                       p_reservationDate IN DATE DEFAULT NULL) RETURN INTEGER 

python code goes like this (dont mind pool parameters, i have excluded log lines, decorators and the second connection since the code is failing at this part shown below):

pool_min = 2
pool_max = 2
pool_inc = 0
db_ldap_connection_string = f'{db_user}/{db_password}@{db_network_service_alias}'

def reserve_msisdn(msisdn, carrier, username, reservation_date):
    #result = cursor.callproc('myfunction', int, (msisdn, carrier, username, reservation_date))
    result = cursor.callproc('myfunction', (msisdn, carrier, username, reservation_date))
    return result

if __name__ == '__main__':
    try:
        oracledb.init_oracle_client()
        pp = oracledb.PoolParams(min=pool_min, max=pool_max, increment=pool_inc)
        pool = oracledb.create_pool(rdb_ldap_connection_string, params=pp)  
        try:
            with pool.acquire() as connection:
                with connection.cursor() as cursor:
                    result = reserve_msisdn('123456','mycarrier', 'myusername', 'sysdate+1')
                    #result = reserve_msisdn('123456','mycarrier', 'myusername')
        except oracledb.Error as e:
            if connection.is_healthy():
                connection.rollback()
            else:
                error, = e.args
                error_message = f'code: {error.code}\nfull code: {error.full_code}\nerror message: {error.message}'
                print(f'DATABASE ERROR:\n\n{error_message}\n')
                sys.exit()
        pool.close()
    except Exception as e:
        print((f'ERROR:\n\n{e}\n'))

If i execute function reserve_msisdn() using result = cursor.callproc('myfunction', int, (msisdn, carrier, username, reservation_date)) i get:

DATABASE ERROR:

code: 0
full code: DPY-2012
error message: DPY-2012: arguments must be a list or tuple

If i execute function reserve_msisdn() using result = cursor.callproc('myfunction', (msisdn, carrier, username, reservation_date)) , note i've excluded int parameter for returning value, i get:

DATABASE ERROR:

code: 6550
full code: ORA-06550
error message: ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYFUNCTION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

This error appears even if i execute reserve_msisdn() function without p_reservationDate -> sysdate+1. I thought maybe i made a typo i do not see so i tried to execute the same code in a different way using:

cursor.execute("""begin
                             myfunction(:1, :2, :3, :4);
                             end;""", (msisdn, carrier, username, reservation_date))

The same error appears. What else i should try or check to overcome this issue?

BR,
Uros

[urosdigital]

Hello,

Figured out what was missing. I have omitted user.packagename.functionname when executing sql funtion. Fixed with:

     cursor.execute("""
                        DECLARE
                        output INTEGER;
                        begin
                        output := user.package.myfunction(:1, :2, :3);
                        end;""", (msisdn, carrier, username))
     connection.commit()

It also works for bulk execution using execuemany:

data = [['123456', 'mycarrier', 'myuser'], ['654321', 'mycarrier', 'myuser'], ['456789', 'mycarrier', 'myuser']]
    cursor.executemany("""
                       DECLARE
                       output INTEGER;
                       begin
                       output := user.package.myfunction(:1, :2, :3);
                       end;""", data)
    connection.commit()

Im stuck to figure out how to get/read return value of this function. It is important since in case it returns 0, it means no errors. I need this to be sure when to commit the changes.

If i try the code below, i get None as return value:

cursor_var = cursor.var(int)
     cursor.execute("""
                        DECLARE
                        output INTEGER;
                        begin
                        output := user.package.myfunction(:1, :2, :3);
                        end;""", (msisdn, carrier, username))
     connection.commit()
return cursor_var.getvalue()

If i try this code:

cursor_var = cursor.var(int)
     cursor.execute("""
                        DECLARE
                        output INTEGER;
                        begin
                        output := user.package.myfunction(:1, :2, :3);
                        :output := output;
                        end;""", (msisdn, carrier, username))
     connection.commit()
return cursor_var.getvalue()

I get:

DATABASE ERROR:

code: 1008
full code: ORA-01008
error message: ORA-01008: not all variables bound

BR,
Uros

[anthony-tuininga]

You actually need to bind the output variable! Like this:

cursor_var = cursor.var(int)
cursor.execute("""
    DECLARE
        output INTEGER;
    begin
        output := user.package.myfunction(:1, :2, :3);
        :output := output;
    end;""", (msisdn, carrier, username, cursor_var))
connection.commit()
return cursor_var.getvalue()

You can do the same with a call to a function, but there is a shortcut that is included with the driver:

return_value = cursor.callfunc("myfunction", int, [carrier, username, reservation_date])

[urosdigital]

Hi @anthony-tuininga,

Ahh understood, i see where i've made an error. A BIG thank you for making the community life easier Anthony! This works perfect for single statement. I had some troubles with batch statements. Than i saw this old article https://blogs.oracle.com/opal/post/efficient-and-scalable-batch-statement-execution-in-python-cx_oracle which helped me create something i think i can rely on. Since i know the function will return 0 if everything is OK and some other value in case anything goes wrong i have chosen this solution:

data = [['123456', 'mycarrier', 'myuser'], ['654321', 'mycarrier', 'myuser'], ['987654', 'mycarrier', 'myuser']]
cursor_var = cursor.var(int, arraysize=len(data)) # i do not know how many elements will be in data variable but im sure it will always be 
data[0].append(cursor_var)                                #  a 1000 max, since im taking care of this in some other function not important for this topic
cursor.executemany("""
                    DECLARE
                       output INTEGER;
                    begin
                       output := user.package.function(:msisdn, :carrier, :username);
                       :output := output;
                    end;""", data)
    result = sum(cursor_var.values)
    if result == 0:
        connection.commit()
    else:
        connection.rollback()
    return 

I have made a simple test, where all three msisdns(numbers) are in desired status available for reservation and also the case when one of them was not, test passed OK. Im writting this all, to ask is there a bit elegant solution other than this to rely on output value returned from sql function?

BR,
Uros

[anthony-tuininga]

You should be able to do something a bit simpler, but this is probably as good as you can get!

data = [[None, '123456', 'mycarrier', 'myuser'], [None, '654321', 'mycarrier', 'myuser'], [None, '987654', 'mycarrier', 'myuser']]
cursor_var = cursor.var(int, arraysize=len(data))
cursor.setinputsizes(cursor_var)
cursor.executemany("""
    :output := rdb.rdb_layer5.ReserveBrandedMsisdn(:msisdn, :carrier, :username);
                    end;""", data)
result = sum(cursor_var.values)
if result == 0:
    connection.commit()
else:
    connection.rollback()

[anthony-tuininga]

In theory I could write the equivalent of callfuncmany() and callprocmany() in the driver itself -- but I'm not sure how common that would be!

[urosdigital]

Would be nice but as you wrote, maybe im the only one that needs this currently. Maybe you can put this request on a todo list/wish list :)
Once again, thanks Anthony!
BR,
Uros