oracle in out pl sql package in python procedure call error

Question

This is pl/sql code:

CREATE OR REPLACE PACKAGE SALES_PHARMA_19.PKG_DIL_ABCD
IS
   TYPE DIL10001_REC IS RECORD
   (
      DILCOLM_01   NUMBER,
      DILCOLM_02   NUMBER,
      DILCOLM_03   NUMBER,
      DILCOLM_04   NUMBER
   );

   TYPE RC_DIL_10001 IS REF CURSOR
      RETURN DIL10001_REC;

   TYPE DIL10001_TAB IS TABLE OF DIL10001_REC
      INDEX BY BINARY_INTEGER;

   PROCEDURE PRC_DIL10001 (P_REF_DIL001   IN OUT RC_DIL_10001,
                           P_PERREQ_BY    IN     NUMBER,
                           P_UD_REQ_BY    IN     VARCHAR2,
                           P_DESIGCODE    IN     VARCHAR2--,
                     --      P_SFRMST_DT  IN     DATE,
                     --      P_SENDTO_DT  IN     DATE
                     );
                          
END;
/

This the body of the package:

PROCEDURE PRC_DIL10001 (P_REF_DIL001   IN OUT RC_DIL_10001,
                       P_PERREQ_BY    IN     NUMBER,
                       P_UD_REQ_BY    IN     VARCHAR2,
                       P_DESIGCODE    IN     VARCHAR2--,
                 --      P_SFRMST_DT    IN     DATE,
                 --      P_SENDTO_DT    IN     DATE
                 )
IS
BEGIN
  OPEN P_REF_DIL001 FOR
     SELECT 4 DILCOLM_01,
            2 DILCOLM_02,
            1 DILCOLM_03,
            1 DILCOLM_04
       FROM DUAL;
END;

This is my Python code:

def data(request):
userpwd = "__"
connection = cx_Oracle.connect("user", userpwd, "192.168.0.1:1501/orcl")
curs = connection.cursor()
record = curs.arrayvar(cx_Oracle.NUMBER, [1, 2, 3, 4])
t = [0, '1', '2']

k = curs.callproc("PKG_DIL_ABCD.PRC_DIL10001", [record, t])

for i in record.getvalue():
    print(i)

This is the given error:

NotSupportedError at /data/
element 1 value is not the same type as previous elements
Request Method:   GET
Request URL:  http://127.0.0.1:8000/data/
Django Version:   3.1.3
Exception Type:   NotSupportedError
Exception Value:  
element 1 value is not the same type as previous elements
Exception Location:   D:\s h o w n o k\Practice_Project\oracle_api\o_api\myapp\views.py, line 31, in data
Python Executable:    C:\Users\ATI\AppData\Local\Programs\Python\Python37-32\python.exe
Python Version:   3.7.2

Reviewing: Just for completeness you could pose the question "What is the cause of this error", but on the other hand that is pretty obvious... I think this Q is OK. — H.Hasenack
– H.Hasenack, Commented Dec 27, 2020 at 11:57
"element value is not the same type as previous elements": is there in your code a list where not all of the values have the same type? — Luke Woodward
– Luke Woodward, Commented Dec 27, 2020 at 13:13

Anthony Tuininga · Accepted Answer · 2020-12-30 18:17:01Z

1

In your code you have this:

t = [0, '1', '2']

Note that the first element is an integer and the remaining elements are strings. They must be of the same type.

In addition, records must be handled as shown in this sample.

edited Dec 30, 2020 at 18:17

answered Dec 28, 2020 at 17:11

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shownok Ferdous Over a year ago

after editing to t=[0, 1, 2] it gives me this error: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PRC_DIL10001' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Anthony Tuininga Over a year ago

See additional information in answer -- records are handled differently from arrays.

Collectives™ on Stack Overflow

oracle in out pl sql package in python procedure call error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related