How to create an oracle stored procedure with an array parameter

Question

I créated an Oracle stored procedure into a package. I did like this:

CREATE OR REPLACE PACKAGE PACKFACE IS
TYPE LIST_IDS IS TABLE OF INT INDEX BY BINARY_INTEGER;
PROCEDURE P_SELECT_IDBFRIENDS (CONSULTA OUT SYS_REFCURSOR,COD_US IN INT,IDS_NOT IN LIST_IDS);
END;

And the body of the package is:

CREATE OR REPLACE PACKAGE BODY PACKFACE IS
    PROCEDURE P_SELECT_IDBFRIENDS (CONSULTA OUT SYS_REFCURSOR,COD_US IN INT, IDS_NOT IN LIST_IDS) IS
    BEGIN
        OPEN CONSULTA FOR 
        SELECT ID_US1,ID_US2 FROM T_FRIENDSHIP WHERE ID_US1=COD_US AND ID_US2 NOT IN (SELECT COLUMN_VALUE FROM TABLE(IDS_NOT));
    END;
END;
/

These is ok in my Oracle 12c server, but I did the same code in Oracle 11g appeared an error, cannot access rows from a non-nested table ítem. What would be the solution? Thanks in advance After this problem was fixed. Appears other one my Python code was broken. I have this procedure:

def select_ids(self,cod_us,ids_not):
    lista = []
    try:
        cursor = self.__cursor.var(cx_Oracle.CURSOR)
        varray = self.__cursor.arrayvar(cx_Oracle.NUMBER,ids_not)
        l_query = self.__cursor.callproc("PROC_SELECT_IDS_ENT_AMISTADES", [cursor, cod_us, varray])
        lista = l_query[0]
        return lista
    except cx_Oracle.DatabaseError as ex:
        error, = ex.args
        print(error.message)
        return lista

PLS-00306 wrong number or type of arguments in call to a procedure. It was ok using Oracle 12c. Thanks in advance again.

I believe you'd need to define your collection type in SQL. Is there a reason that you need an associative array (which can only be defined in PL/SQL) rather than a nested table (which can be defined in both SQL and PL/SQL)? — Justin Cave
– Justin Cave, Commented Feb 15, 2016 at 21:08
First of all I have to exclude ids stored in a array, After that I do a select using in clause excluding ids (variable size) — lgerras84
– lgerras84, Commented Feb 15, 2016 at 21:50

MT0 · Accepted Answer · 2016-02-15 21:19:05Z

1

You cannot use a collection type defined in PL/SQL in an SQL query in Oracle 11.

If you want to use a collection in both SQL and PL/SQL then you will have to define it in SQL:

CREATE TYPE LIST_IDS IS TABLE OF INT;

Then you can do:

CREATE OR REPLACE PACKAGE PACKFACE IS
  PROCEDURE P_SELECT_IDBFRIENDS (
    CONSULTA OUT SYS_REFCURSOR,
    COD_US IN INT,
    IDS_NOT IN LIST_IDS
  );
END;
/
SHOW ERRORS;

CREATE OR REPLACE PACKAGE BODY PACKFACE IS
  PROCEDURE P_SELECT_IDBFRIENDS (
    CONSULTA OUT SYS_REFCURSOR,
    COD_US IN INT,
    IDS_NOT IN LIST_IDS
  )
  IS
  BEGIN
    OPEN CONSULTA FOR 
    SELECT ID_US1,ID_US2
    FROM   T_FRIENDSHIP
    WHERE  ID_US1=COD_US
    AND    ID_US2 NOT MEMBER OF IDS_NOT;
  END;
END;
/
SHOW ERRORS;

edited Feb 15, 2016 at 21:19

answered Feb 15, 2016 at 21:11

MT0

173k12 gold badges70 silver badges136 bronze badges

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

1 Comment

lgerras84 Over a year ago

Thanks a lot, It was fixed. Now the problema is in my python code. I will modified my question

GSandro_Strongs · Accepted Answer · 2016-02-17 16:32:47Z

First of all you should to create an oracle type like this:

CREATE OR REPLACE TYPE LIST_IDS AS TABLE OF INT;

And after that you should to create the package with the procedure and a nested table in parameter like this:

CREATE OR REPLACE PACKAGE PACKFACE IS
TYPE LISTADO_IDS IS TABLE OF INT INDEX BY PLS_INTEGER;
PROCEDURE P_SELECT_IDBFRIENDS (CONSULTA OUT SYS_REFCURSOR,COD_US IN INT,IDS_NOT IN LISTADO_IDS);
END;

Finally you should create the body. You pass the data to nested table from oracle type like this:

CREATE OR REPLACE PACKAGE BODY PACKFACE IS
    PROCEDURE P_SELECT_IDBFRIENDS (CONSULTA OUT SYS_REFCURSOR,COD_US IN INT, IDS_NOT IN LISTADO_IDS) 
    IS
        num_array FACEBOOK.LIST_IDS;
    BEGIN
        num_array:=LIST_IDS();
        for i in 1 .. IDS_NOT.count
        loop
            num_array.extend(1);
            num_array(i) := IDS_NOT(i);
        end loop; 
        OPEN CONSULTA FOR 
        SELECT ID_US1,ID_US2 FROM T_FRIENDSHIP WHERE ID_US1=COD_US AND ID_US2 NOT IN (SELECT COLUMN_VALUE FROM TABLE(num_array));
    END;
END;

I did that and I got the solution to the python error wrong number or type parameters. Good luck.

Collectives™ on Stack Overflow

How to create an oracle stored procedure with an array parameter

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related