CODEWITHSUNDEEP
djangostored-proceduresplsql
FunCoding
FunCoding

Reputation: 51

Calling a PL/SQL procedure from django with callproc

I need to call a procedure in PL/SQL from an API in Django. I use the callproc and the right values, but get the error:

"PLS-00306: wrong number or types of arguments in call"

In Oracle I have:

PROCEDURE new_payment(pv_id                     IN VARCHAR2,
                        parr_user               IN OWA.vc_arr,
                        parr_date_reg           IN OWA.vc_arr,
                        parr_d_value            IN OWA.vc_arr,
                        parr_descr              IN OWA.vc_arr,
                        parr_type               IN OWA.vc_arr,
                        pi_gerar_ref_mb         IN PLS_INTEGER DEFAULT 0,
                        pv_data_limite_ref_mb   IN VARCHAR2 DEFAULT NULL)

In models.py I have:

class PAYMENT():
    def new_payment(self, id, user, date_reg, value, descr, type):
        cur = connection.cursor()
        ref = cur.callproc("PAYMENT.new_payment", [id, user, date_reg, value, 
    descr, type])
        cursor.close()
        return ref

In views.py:

pay=PAYMENT()
x=pay.new_payment('123', '111', '2019-07-23', '10', 'test1', 'teste2')

At this point, i get the error:

"ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'NEW_PAYMENT'"`

Any tip in what am I doing wrong?

Upvotes: 2

Views: 2856

Answers (1)

hotfix
hotfix

Reputation: 3396

OWA.vc_arr looks like a collection, so you need to pass a collection as variable. in the cx_Oracle documentation you can find the function Cursor.arrayvar(dataType, value\[, size\])

Create an array variable associated with the cursor of the given type and size and return a variable object. The value is either an integer specifying the number of elements to allocate or it is a list and the number of elements allocated is drawn from the size of the list. If the value is a list, the variable is also set with the contents of the list. If the size is not specified and the type is a string or binary, 4000 bytes is allocated. This is needed for passing arrays to PL/SQL (in cases where the list might be empty and the type cannot be determined automatically) or returning arrays from PL/SQL.

so your code could looks like:

class PAYMENT():
    def new_payment(self, id, user, date_reg, value, descr, type):
        cur = connection.cursor()
        u = cur.arrayvar(cx_Oracle.STRING,user)
        ds = cur.arrayvar(cx_Oracle.STRING,date_reg)
        v = cur.arrayvar(cx_Oracle.STRING,value)
        d = cur.arrayvar(cx_Oracle.STRING,descr)
        t = cur.arrayvar(cx_Oracle.STRING,type)
        ref = cur.callproc("PAYMENT.new_payment", [id, u, ds, v, 
    d, t])
        cursor.close()
        return ref

Upvotes: 3

Related Questions