6.1.PL/SQL Stored Procedures

TheCursor.callproc() method is used to call PL/SQL procedures.

If a procedure with the following definition exists:

createorreplaceproceduremyproc(a_Value1number,a_Value2outnumber)asbegina_Value2:=a_Value1*2;end;

then the following Python code can be used to call it:

out_val=cursor.var(int)cursor.callproc('myproc',[123,out_val])print(out_val.getvalue())# will print 246

CallingCursor.callproc() internally generates ananonymous PL/SQLblock and executes it. This is equivalent to the application code:

cursor.execute("begin myproc(:1,:2); end;",[123,out_val])

SeeUsing Bind Variables for information on binding.

6.2.PL/SQL Stored Functions

TheCursor.callfunc() method is used to call PL/SQL functions.

The first parameter tocallfunc() is the function name. Thesecond parameter represents the PL/SQL function return value and is expected tobe a Python type, one of theoracledb types or anObjectType. Any following sequence of values or named parameters arepassed as PL/SQL function arguments.

If a PL/SQL function with the following definition exists:

createorreplacefunctionmyfunc(a_StrValvarchar2,a_NumValnumber,a_Dateoutdate)returnnumberasbeginselectsysdateintoa_Datefromdual;returnlength(a_StrVal)+a_NumVal*2;end;

then the following Python code can be used to call it:

d=cursor.var(oracledb.DB_TYPE_DATE)# for the a_Date OUT parameterreturn_val=cursor.callfunc("myfunc",int,["a string",15,d])print(return_val)# prints 38print(d.getvalue())# like 2024-12-04 22:35:23

A more complex example that returns a spatial (SDO) object can be seen below.First, the SQL statements necessary to set up the example:

createtableMyPoints(idnumber(9)notnull,pointsdo_point_typenotnull);insertintoMyPointsvalues(1,sdo_point_type(125,375,0));createorreplacefunctionspatial_queryfn(a_Idnumber)returnsdo_point_typeist_Resultsdo_point_type;beginselectpointintot_ResultfromMyPointswhereId=a_Id;returnt_Result;end;/

The Python code that will call this procedure looks as follows:

obj_type=connection.gettype("SDO_POINT_TYPE")cursor=connection.cursor()return_val=cursor.callfunc("spatial_queryfn",obj_type,[1])print(f"({return_val.X},{return_val.Y},{return_val.Z})")# will print (125, 375, 0)

SeeUsing Bind Variables for information on binding.

6.3.Anonymous PL/SQL Blocks

Ananonymous PL/SQL block can be called asshown:

var=cursor.var(int)cursor.execute("""        begin            :out_val := length(:in_val);        end;""",in_val="A sample string",out_val=var)print(var.getvalue())# will print 15

SeeUsing Bind Variables for information on binding.

6.4.Passing NULL values to PL/SQL

Oracle Database requires a type, even for null values. When you pass the valueNone, then python-oracledb assumes its type is a string. If this is not thedesired type, you can explicitly set it. For example, to pass a NULLOracle Spatial SDO_GEOMETRY object to a PL/SQL storedprocedure with the signature:

proceduremyproc(pinsdo_geometry)

You can use:

type_obj=connection.gettype("SDO_GEOMETRY")var=cursor.var(type_obj)cursor.callproc("myproc",[var])

6.5.Creating Stored Procedures and Packages

To create PL/SQL stored procedures and packages, useCursor.execute()with a CREATE command. For example:

cursor.execute("""        create or replace procedure myprocedure        (p_in in number, p_out out number) as        begin            p_out := p_in * 2;        end;""")

withconnection.cursor()ascursor:cursor.execute("""            create or replace procedure badproc as            begin                WRONG WRONG WRONG            end;""")ifcursor.warningandcursor.warning.full_code=="DPY-7000":print(cursor.warning)# Get detailscursor.execute("""                select line, position, text                from user_errors                where name = 'BADPROC' and type = 'PROCEDURE'                order by line, position""")forinfoincursor:print("Error at line{} position{}:\n{}".format(*info))

DPY-7000:creationsucceededwithcompilationerrorsErroratline3position23:PLS-00103:Encounteredthesymbol"WRONG"whenexpectingoneofthefollowing::=.(@%;

6.6.Using the %ROWTYPE Attribute

In PL/SQL, the%ROWTYPE attributelets you declare a record that represents either a full or partial row of adatabase table or view.

To work with %ROWTYPE in python-oracledb, useConnection.gettype() toget the relevant attribute type information.

Getting a %ROWTYPE value from PL/SQL

Given a PL/SQL function that returns a row of the LOCATIONS table:

createorreplacefunctionTestFuncOUTreturnlocations%rowtypeasplocations%rowtype;beginselect*intopfromlocationswhererownum<2;returnp;end;/

You can usegettype() to get the type of the PL/SQLfunction return value, and specify this as thecallfunc()return type. For example:

rt=connection.gettype("LOCATIONS%ROWTYPE")r=cursor.callfunc("TESTFUNCOUT",rt)

The variabler will contain the return value of the PL/SQL function as anObject Type. You can access its contents using themethods discussed inFetching Oracle Database Objects and Collections. The helper functiondump_object() defined there is aconvenient example:

dump_object(r)

Output will be:

{LOCATION_ID:1000STREET_ADDRESS:'1297 Via Cola di Rie'POSTAL_CODE:'00989'CITY:'Roma'STATE_PROVINCE:NoneCOUNTRY_ID:'IT'}

Constructing a %ROWTYPE value in python-oracledb

You can construct a similar object directly in python-oracledb by usingDbObjectType.newobject() and setting any desired fields. For example:

rt=connection.gettype("LOCATIONS%ROWTYPE")r=rt.newobject()r.CITY='Roma'

Passing a %ROWTYPE value into PL/SQL

Given the PL/SQL procedure:

createorreplaceprocedureTestProcIN(pinlocations%rowtype,cityoutvarchar2)asbegincity:=p.city;end;

you can callcallproc() passing the variabler from thepreviouscallfunc() ornewobject()examples in the appropriate parameter position, for example:

c=cursor.var(oracledb.DB_TYPE_VARCHAR)cursor.callproc("TESTPROCIN",[r,c])print(c.getvalue())

This prints:

Roma

Seeplsql_rowtype.pyfor a runnable example.

6.7.Using DBMS_OUTPUT

The standard way to print output from PL/SQL is with the packageDBMS_OUTPUT. Note, PL/SQL code that usesDBMS_OUTPUT runs to completion before any output is available to the user.Also, other database connections cannot access the buffer.

To use DBMS_OUTPUT:

• Call the PL/SQL procedureDBMS_OUTPUT.ENABLE() to enable output to bebuffered for the connection.

• Execute some PL/SQL that callsDBMS_OUTPUT.PUT_LINE() to put text in thebuffer.

• CallDBMS_OUTPUT.GET_LINE() orDBMS_OUTPUT.GET_LINES() repeatedly tofetch the text from the buffer until there is no more output.

For example:

# enable DBMS_OUTPUTcursor.callproc("dbms_output.enable")# execute some PL/SQL that calls DBMS_OUTPUT.PUT_LINEcursor.execute("""        begin            dbms_output.put_line('This is the python-oracledb manual');            dbms_output.put_line('Demonstrating how to use DBMS_OUTPUT');        end;""")# tune this size for your applicationchunk_size=100# create variables to hold the outputlines_var=cursor.arrayvar(str,chunk_size)num_lines_var=cursor.var(int)num_lines_var.setvalue(0,chunk_size)# fetch the text that was added by PL/SQLwhileTrue:cursor.callproc("dbms_output.get_lines",(lines_var,num_lines_var))num_lines=num_lines_var.getvalue()lines=lines_var.getvalue()[:num_lines]forlineinlines:print(lineor"")ifnum_lines<chunk_size:break

This will produce the following output:

Thisisthepython-oracledbmanualDemonstratinguseofDBMS_OUTPUT

An alternative is to callDBMS_OUTPUT.GET_LINE() once per output line,which may be much slower:

text_var=cursor.var(str)status_var=cursor.var(int)whileTrue:cursor.callproc("dbms_output.get_line",(text_var,status_var))ifstatus_var.getvalue()!=0:breakprint(text_var.getvalue())

6.8.Implicit Results

Implicit results permit a Python program to consume cursors returned by aPL/SQL block without the requirement to use OUTREF CURSORparameters. The methodCursor.getimplicitresults() can be used for thispurpose. It needs Oracle Database 12.1 (or later). For python-oracledbThick mode, Oracle Client 12.1 (or later) isadditionally required.

An example using implicit results is as shown:

cursor.execute("""        declare            cust_cur sys_refcursor;            sales_cur sys_refcursor;        begin            open cust_cur for SELECT * FROM cust_table;            dbms_sql.return_result(cust_cur);            open sales_cur for SELECT * FROM sales_table;            dbms_sql.return_result(sales_cur);        end;""")forimplicit_cursorincursor.getimplicitresults():forrowinimplicit_cursor:print(row)

Data from both the result sets are returned:

(1,'Tom')(2,'Julia')(1000,1,'BOOKS')(2000,2,'FURNITURE')

When using python-oracledb Thick mode, you must leave the parent cursor openuntil all of the implicit result sets have been fetched or until yourapplication no longer requires them. Closing the parent cursor beforefetching all of the implicit result sets will result in the closure of theimplicit result set cursors. If you try to fetch from an implicit result setafter its parent cursor is closed, the following error will be thrown:

DPI-1039:statementwasalreadyclosed

Note that the requirement mentioned above is not applicable forpython-oracledb Thin mode. SeeImplicit Results in Thin and Thick Modes.

6.9.Edition-Based Redefinition (EBR)

Oracle Database’sEdition-Based Redefinition feature enables upgrading ofthe database component of an application while it is in use, thereby minimizingor eliminating down time. This feature allows multiple versions of views,synonyms, PL/SQL objects and SQL Translation profiles to be used concurrently.Different versions of the database objects are associated with an “edition”.

The simplest way to set the edition used by your applications is to pass theedition parameter tooracledb.connect() ororacledb.create_pool():

connection=oracledb.connect(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb",edition="newsales")

The edition can also be set by executing the SQL statement:

altersessionsetedition=<editionname>;

You can also set the environment variableORA_EDITION to your edition name.

Regardless of which method sets the edition, the value that is in use can beseen by examining the attributeConnection.edition. If no value hasbeen set, the value will be None. This corresponds to the database defaulteditionORA$BASE.

Consider an example where one version of a PL/SQL functionDiscount isdefined in the database default editionORA$BASE and the other version ofthe same function is defined in a user created editionDEMO. In your SQLeditor run:

connect<username>/<password>-- create function using the database default editionCREATEORREPLACEFUNCTIONDiscount(priceINNUMBER)RETURNNUMBERISBEGINreturnprice*0.9;END;/

A new edition named ‘DEMO’ is created and the user given permission to useeditions. The use ofFORCE is required if the user already contains one ormore objects whose type is editionable and that also have non-editioneddependent objects.

connectsystem/<password>CREATEEDITIONdemo;ALTERUSER<username>ENABLEEDITIONSFORCE;GRANTUSEONEDITIONdemoto<username>;

TheDiscount function for the demo edition is as follows:

connect<username>/<password>altersessionsetedition=demo;-- Function for the demo editionCREATEORREPLACEFUNCTIONDiscount(priceINNUMBER)RETURNNUMBERISBEGINreturnprice*0.5;END;/

A Python application can then call the required version of the PL/SQL functionas shown:

connection=oracledb.connect(user=user,password=password,dsn="dbhost.example.com/orclpdb")print("Edition is:",repr(connection.edition))cursor=connection.cursor()discounted_price=cursor.callfunc("Discount",int,[100])print("Price after discount is:",discounted_price)# Use the edition parameter for the connectionconnection=oracledb.connect(user=user,password=password,dsn="dbhost.example.com/orclpdb",edition="demo")print("Edition is:",repr(connection.edition))cursor=connection.cursor()discounted_price=cursor.callfunc("Discount",int,[100])print("Price after discount is:",discounted_price)

The output of the function call for the default and demo edition is as shown:

Editionis:NonePriceafterdiscountis:90Editionis:'DEMO'Priceafterdiscountis:50