Execute Oracle Stored Procedure with Structure as input parameter

Do you want to execute a stord procedure with Oracle Strucure as an input?. Here is an psuedo code.

Assume below is the api of your stored procedure

PROCEDURE createTable ( p_rottablemap IN MYTABLE, p_retcode OUT NUMBER )

Below is the sample structure details.

create or replace TYPE mytable AS TABLE OF mytablestructure;

create or replace TYPE mytablestructure AS OBJECT ( tablename VARCHAR2(22), columnname VARCHAR2(30), datatype VARCHAR2(35), datalength VARCHAR2(35), isnullable VARCHAR2(35), dataprecision VARCHAR2(35), datadefault VARCHAR2(35))

Here is the code to execute:

DECLARE
l_array MYTABLE;
retcode Number;
BEGIN
retcode := -1;
l_array := MYTABLE();
l_array.extend(2);
l_array(1) := MYTableSTRUCTURE('ORMJOINKEY','ORM_KEY','NUMBER','','N','', '',);
l_array(2) := ROTSTRUCTURE('ORMJOINKEY','ENTITY_ID','NUMBER','','N','', '');
mypkg.createtable(l_array, retcode);
dbms_output.put_line('retcode: ' retcode);
END;

Happy Coding...