<jupyter_start><jupyter_text>![Banner](images/banner.png)  Calling PL/SQLDocumentation reference link: [PL/SQL Execution](https://cx-oracle.readthedocs.io/en/latest/user_guide/plsql_execution.html)PL/SQL is a 'stored' procedural language that is stored and run inside the database itself. PL/SQL lets you capture business logic for reuse across all your applications.  You can call stored procedures and functions easily from cx_Oracle.<jupyter_code>import cx_Oracle
import platform
import os

if platform.system() == 'Darwin':
    cx_Oracle.init_oracle_client(lib_dir = os.environ.get("HOME")+"/instantclient_19_8")
elif platform.system() == 'Windows':
     cx_Oracle.init_oracle_client(lib_dir = r"C:\oracle\instantclient_19_14")
un = "pythondemo"
pw = "<PASSWORD>"
cs = "localhost/orclpdb1"

connection = cx_Oracle.connect(user=un, password=pw, dsn=cs)<jupyter_output><empty_output><jupyter_text>PL/SQL ProceduresThis shows the PL/SQL procedure `MYPROC` used in this demo:<jupyter_code>with connection.cursor() as cursor:
   
    cursor.execute("select dbms_metadata.get_ddl('PROCEDURE', 'MYPROC') from dual")
    ddl, = cursor.fetchone()
    print(ddl.read())<jupyter_output><empty_output><jupyter_text>You can use `callproc()` to call the procedure.  Bind variables are passed by position:<jupyter_code>with connection.cursor() as cursor:

    myinvar  = 22
    myoutvar = cursor.var(int)    # allocate a 'variable' of integer type to hold the OUT bind parameter

    cursor.callproc('myproc', [myinvar, myoutvar])
    print(myoutvar.getvalue())<jupyter_output><empty_output><jupyter_text>You can also call PL/SQL procedures via an 'anonymous' PL/SQL block.  This can be useful if you want to use named bind placeholders:<jupyter_code>with connection.cursor() as cursor:

    myinvar  = 33
    myoutvar = cursor.var(int)

    cursor.execute(' begin myproc(:v1, :v2); end;', {"v1": myinvar, "v2": myoutvar})
    print(myoutvar.getvalue())<jupyter_output><empty_output><jupyter_text>PL/SQL FunctionsThis shows the PL/SQL function `MYFUNC` used in this demo:<jupyter_code>with connection.cursor() as cursor:
    
    cursor.execute("select dbms_metadata.get_ddl('FUNCTION', 'MYFUNC') from dual")
    ddl, = cursor.fetchone()
    print(ddl.read())<jupyter_output><empty_output><jupyter_text>You can use `callfunc()` to call the function. Bind variables are passed by position.  The second argument to `callfun()` is the type of the PL/SQL function return value.  Here it is an integer:<jupyter_code>with connection.cursor() as cursor:

    data = "abc"
    id = 3
    res = cursor.callfunc('myfunc', int, (data, id))
    print(res)<jupyter_output><empty_output><jupyter_text>Similar to calling PL/SQL procedures, you can also invoke PL/SQL procedures via an anonymous block, and optionally used named bind placeholders:<jupyter_code>with connection.cursor() as cursor:

    data  = "def"
    id = 4
    ret = cursor.var(int)

    cursor.execute(' begin :ret := myfunc(:data, :id); end;', {"ret": ret, "data": data, "id": id})
    print(ret.getvalue())<jupyter_output><empty_output><jupyter_text>REF CURSORSREF CURSORS let result sets be returned to cx_Oracle, commonly from PL/SQL.Here is the PL/SQL procedure used in this example:<jupyter_code>with connection.cursor() as cursor:

    cursor.execute("""select text from all_source 
                      where name = 'MYREFCURSORPROC' and type = 'PROCEDURE' 
                      order by line""")
    rows = cursor.fetchall()
    for r, in rows:
        print(r, end="")<jupyter_output><empty_output><jupyter_text>Using `callproc()` as shown before, you can call the PL/SQL procedure. The `ref_cursor` variable needs to be defined as a cursor so it can hold the returned REF CURSOR.  This second cursor is then simply iterated over exactly like a cursor for simple SELECT would be:<jupyter_code>with connection.cursor() as cursor:
    ref_cursor = connection.cursor()

    cursor.callproc("myrefcursorproc", (2, 6, ref_cursor))

    print("Rows between 2 and 6:")
    for row in ref_cursor:
        print(row)<jupyter_output><empty_output><jupyter_text>Implicit CursorsInstead of binding a cursor to get a REF CURSOR, the `dbms_sql.return_result()` procedure can alternatively return a result set back which is fetched in cx_Oracle using `getimplicitresults()`:<jupyter_code>with connection.cursor() as cursor:

    cursor.execute("""
            declare
                c1 sys_refcursor;
                c2 sys_refcursor;
            begin
                open c1 for
                select * from ParentTable;
                dbms_sql.return_result(c1);

                open c2 for
                select * from ChildTable;
                dbms_sql.return_result(c2);
            end;""")

    for resultSet in cursor.getimplicitresults():
        print("Result Set:")
        for row in resultSet:
           print(row)<jupyter_output><empty_output><jupyter_text>Edition Based Redefinition (EBR)Documentation reference link: [Edition-Based Redefinition (EBR)](https://cx-oracle.readthedocs.io/en/latest/user_guide/plsql_execution.htmledition-based-redefinition-ebr)EBR lets you have different implementations of PL/SQL objects with the same signature.  This is useful for upgrading applications. It lets you do advance testing before deciding to drop the old implementations because different users can execute different versions of the objects..  You can create you new objects in a new 'edition'.  At runtime you choose which edition should be used, giving you all the objects created in that new edition,<jupyter_code>user_ed           = "pythoneditions"
password_ed       = "<PASSWORD>"<jupyter_output><empty_output><jupyter_text>The sample setup script already created an edition:```alter user pythoneditions enable editions;create edition python_e1;``` Create functions in the base edition:<jupyter_code>with cx_Oracle.connect(user=user_ed, password=<PASSWORD>, dsn=cs) as connection:
    with connection.cursor() as cursor:

        cursor.execute("""
            create or replace function test_editions return varchar2 as
            begin
                return 'First procedure created in base edition';
            end;""")
        
        cursor.execute("""
            create or replace function second_procedure return varchar2 as
            begin
                return 'Second procedure created in base edition';
            end;""")

print("Done")<jupyter_output><empty_output><jupyter_text>Create a new function with same signature as `test_editions` but in a connection using the 'python_e1' edition:<jupyter_code>with cx_Oracle.connect(user=user_ed, password=<PASSWORD>, dsn=cs, edition="python_e1") as connection_ed1:
    with connection_ed1.cursor() as cursor_ed1:

        cursor_ed1.execute("""
                create or replace function test_editions return varchar2 as
                begin
                    return 'First procedure created in python_e1 edition';
                end;""")

print("Done")<jupyter_output><empty_output><jupyter_text>Both procedures can be called in the base edition:<jupyter_code>with cx_Oracle.connect(user=user_ed, password=<PASSWORD>, dsn=cs) as connection_edb:
    with connection_edb.cursor() as cursor_edb:

        result = cursor_edb.callfunc("test_editions", str)
        print(result)

        result = cursor_edb.callfunc("second_procedure", str)
        print(result)<jupyter_output><empty_output><jupyter_text>After connecting to the 'python_e1' edition, the calling the overloaded function executes the updated code.  The second function was not overloaded so it executes the original code:<jupyter_code>with cx_Oracle.connect(user=user_ed, password=<PASSWORD>, dsn=cs, edition="python_e1") as connection_ed1:
     with connection_ed1.cursor() as cursor_ed1:

        result = cursor_ed1.callfunc("test_editions", str)
        print(result)

        result = cursor_ed1.callfunc("second_procedure", str)
        print(result)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09700838653148079, 'fraction_numerical': 0.005757917136062086, 'mean_word_length': 3.99375, 'pattern_counts': {'":': 5, '<': 67, '<?xml version=': 0, '>': 67, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '434823', 'license': 'Apache-2.0', 'n_tokens_mistral': 2473, 'n_tokens_neox': 2363, 'n_words': 703, 'path': '6-PLSQL.ipynb', 'repo_name': 'subratcall/cx-oracle-notebooks', 'size': '7987'}