Executing and retrieving stored procedure returns in python with oracle db

Question

I am trying to run


 plsql = """
                    DECLARE
                        l_zip BLOB;
                    BEGIN
                        l_zip := apex_export.zip( p_source_files => apex_export.get_workspace(:1),
                                                  p_extra_files => apex_t_export_files( apex_t_export_file( name => 'README.md', contents => 'Merch Read Write Workspace Contents.'),
                                                                                        apex_t_export_file( name => 'LICENSE.txt', contents => 'The Universal Permissive License (UPL), Version 1.0'))
                                                );
                      :2 := l_zip;
                    END;
                """

zip_var = cursor.var(oracledb.DB_TYPE_BLOB)

cursor.execute(plsql, [workspace_id, zip_var])


with open("workspace_export.zip", "wb") as f:
      f.write(zip_data)

I always get the error

oracledb.exceptions.NotSupportedError: DPY-3002: Python value of type "tuple" is not supported

in the line     cursor.execute(plsql, [workspace_id, zip_var])

The second argument to execute is expected to the the values to replace the placeholders. I don't see placeholders in your query. What happens if you remove ", [workspace_id, zip_var]"? — jsf80238, Commented Apr 11 at 13:35
I even tried defining name variable i.e. instead of :1 and :2 cursor.execute(plsql, { "workspace_id": workspace_id, "zip_var": zip_var, }) — Sourabh, Commented Apr 11 at 14:29
always put full error message because there are other useful information. — furas, Commented Apr 11 at 15:40
Maybe first use print() (and print(type(...)), print(len(...)), etc.) to see which part of code is executed and what you really have in variables. It is called "print debugging" and it helps to see what code is really doing. — furas, Commented Apr 11 at 15:42

Christopher Jones · Accepted Answer · 2025-04-13 22:37:45Z

Works for me. Here's an example that uses CLOB (for ease of output):

import getpass
import oracledb

un = 'cj'
cs = 'localhost/orclpdb1'
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        plsql = """
                DECLARE
                  l_zip CLOB;
                BEGIN
                  l_zip := 'My CLOB ' || :1;
                  :2 := l_zip;
                END;
                """
        workspace_id = 1
        zip_var = cursor.var(oracledb.DB_TYPE_CLOB)
        cursor.execute(plsql, [workspace_id, zip_var])
        print(zip_var.getvalue())

There was no issue with bind variable usage.

Note that to access the returned LOB from the python-oracledb "variable" object, I called getvalue()

The output is:

My CLOB 1

Collectives™ on Stack Overflow

Executing and retrieving stored procedure returns in python with oracle db

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related