I'm starting new into SQL and we got an exercise related to Packages and Varrays. To be more specific, I have to create a python script which connects to my BD and inserts a varray of 100 numbers in a table.
I've create the package this way:
create or replace PACKAGE Pack AS
TYPE num_array IS VARRAY(100) OF NUMBER;
PROCEDURE procediment_1 (arr in num_array);
END Pack
and its body
create or replace PACKAGE BODY PACK AS
PROCEDURE procediment_1 (arr in num_array) AS
BEGIN
FOR i IN 1..arr.COUNT LOOP
INSERT INTO B VALUES (arr(i));
END LOOP;
END;
END Pack;
About the python script, here it is:
lista = []
x=0
while x < 100:
RandomNumero = random.randint(0, 1000)
if RandomNumero not in lista:
lista.append(RandomNumero)
x += 1
args = [lista]
cur.callproc("PACK.procediment_1", args)
conn.commit()
Imagine that all imports and the cursor (cur) work perfectly.
The problem comes when this error appears:
cur.callproc("PACK.procediment_1", args)
cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PROCEDIMENT_1'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
I know that the variable "args" might be the problem, but I don't know how to solve it. I have read a lot about python and SQL variables and I think that the way I do it is correct.
In fact, if I try to do it using SQL and not Python, like this:
DECLARE
TYPE v1 IS VARRAY(10) OF NUMBER;
VAR1 V1 := v1(1,2,3,4,5,6,7,8,9,10);
begin
pack.procediment_1(arr=>V1);
end;
but still doesn't work.
I have a bad level of English, so I tried my best explaining myself. If anyone has any recommendation I will listen to it.
Thank you.
