CODEWITHSUNDEEP
oracle-databaseplsql
Burhan Khalid Butt
Burhan Khalid Butt

Reputation: 275

Table variable as in parameter to populate a table in oracle Stored procedure

Mostly I avoid table variables as input parameters for a stored procedure. Because I do not know how to handle them, but in this case I have no other option. I have a requirement where hundreds of records will be passed on to database from Oracle Agile PLM. What I have to do is to populate a table from the input records/list. For accomplishing this I have developed an object type and then a table type out of that object type.

CREATE OR REPLACE TYPE TEST_USER.MD_TYPE AS OBJECT
                  (QUERY_REF VARCHAR2 (1000 BYTE),
                   COL_NAME VARCHAR2 (100 BYTE),
                   COL_LENGTH VARCHAR2 (50 BYTE),
                   COL_SEQ NUMBER)
/

CREATE OR REPLACE TYPE TEST_USER.MD_TYPE_TABLE  AS TABLE OF  MD_TYPE
/

Stored Procedure:

CREATE OR REPLACE PROCEDURE SP_TEST2
(
  P_MD_TABLE IN MD_TYPE_TABLE,
  p_success OUT number 
) 
IS

BEGIN

  INSERT INTO MDATA_TABLE
  (
QUERY_REF ,
                   COL_NAME ,
                   COL_LENGTH ,
                   COL_SEQ 

  )
    SELECT  ea.*
    FROM  TABLE(P_MD_TABLE) ea;

    p_success :=1;
    EXCEPTION 
    WHEN OTHERS THEN
    p_success := -1;
END SP_TEST2;

The problem is I do not know how to populate, first parameter P_MD_TABLE and then MDATA_TABLE. And the procedure compiles without any errors. I have not tested this procedure.

Any help please.

Procedure for loading MD_TYPE_TABLE by passing parameters to MD_TYPE

CREATE OR REPLACE PROCEDURE SP_UPLOAD_MD_TYPE
(
P_QUERY_REF VARCHAR2,
P_COL_NAME VARCHAR2,
P_COL_LENGTH VARCHAR2,
p_col_seq NUMBER,
p_no_of_rows_to_insert NUMBER,
p_num OUT NUMBER
)
IS

    p_type_tbl  MD_TYPE_TABLE := MD_TYPE_TABLE(); --initialize

BEGIN
  <<vartype>>
         FOR i IN 1..p_no_of_rows_to_insert
         LOOP
    p_type_tbl.extend();
    p_type_tbl(p_type_tbl.last) := MD_TYPE(P_QUERY_REF, P_COL_NAME, P_COL_LENGTH, p_col_seq);
         END LOOP vartype;


    SP_TEST2(p_type_tbl, p_num);


END;

Upvotes: 4

Views: 12533

Answers (1)

Praveen
Praveen

Reputation: 9355

You can populate a table type by using extend/ bulk collect

using extend

p_type_tbl.extend();
p_type_tbl(p_type_tbl.last) := MD_TYPE('QUERY_REF1', 'COL_NAME1', 'COL_LENGTH1', 1);

or using bulk collect

SELECT MD_TYPE(c1, c2... cn)
  BULK COLLECT INTO p_type_tbl
  FROM   some_table;

Demo

DECLARE
    p_type_tbl  MD_TYPE_TABLE := MD_TYPE_TABLE(); --initialize
    p_num NUMBER;
BEGIN
    p_type_tbl.extend();
    p_type_tbl(p_type_tbl.last) := MD_TYPE('QUERY_REF1', 'COL_NAME1', 'COL_LENGTH1', 1);

    p_type_tbl.extend();
    p_type_tbl(p_type_tbl.last) := MD_TYPE('QUERY_REF2', 'COL_NAME2', 'COL_LENGTH2', 2);

    SP_TEST2(p_type_tbl, p_num);

    DBMS_OUTPUT.PUT_LINE(p_num);
END;
/

OutPut
1

SELECT * FROM MDATA_TABLE;

OutPut

QUERY_REF   COL_NAME    COL_LENGTH  COL_SEQ
QUERY_REF1  COL_NAME1   COL_LENGTH1 1
QUERY_REF2  COL_NAME2   COL_LENGTH2 2

Upvotes: 6

Related Questions