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

Question

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;

"I do not know how to populate the table `MDATA_TABLE`"... `MDATA_TABLE or P_MD_TABLE `??? — Praveen, Mar 16 '16 at 07:12

score 6 · Accepted Answer · edited Mar 16 '16 at 07:42

6

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

edited Mar 16 '16 at 07:42

Maheswaran Ravisankar

17,652
6
47
69

answered Mar 16 '16 at 07:38

Praveen

8,945
4
31
49

As per your answer, I have to first create a procedure that loads MD_TYPE type and then call sp_test2 procedure. The problem is I have to load almost 50000 records per execution not one or two records. However, I have developed a procedure which can pass MD_TYPE values and then execute procedure sp_test2. But it will work for 1 record only. How can I make it work for 50000 records. I have edited my question for this procedure. – Burhan Khalid Butt Mar 16 '16 at 08:15
In my test `SP_UPLOAD_MD_TYPE` inserted 50000 data into the table `MDATA_TABLE`. – Praveen Mar 16 '16 at 08:28
`DECLARE p_num NUMBER; BEGIN SP_UPLOAD_MD_TYPE('QUERY_REF1', 'COL_NAME1', 'COL_LENGTH1', 1, 50000, p_num); END;` – Praveen Mar 16 '16 at 08:29
`SELECT Count(*) FROM MDATA_TABLE` ---> `50000` – Praveen Mar 16 '16 at 08:29
This procedure will insert only one unique record. In other words, this procedure will insert these values 'QUERY_REF1', 'COL_NAME1', 'COL_LENGTH1', 1 50000 times once its called. My input data will have 50000 unique records. But how this procedure can accommodate 50000 unique records. – Burhan Khalid Butt Mar 16 '16 at 09:37
For "unique 50000" records you need to call `p_type_tbl.extend(); p_type_tbl(p_type_tbl.last) := MD_TYPE(.....);` a 50000 times with those "unique" data – Praveen Mar 16 '16 at 09:43
So I have to call this procedure in a loop. Ah! that's a nightmare :) – Burhan Khalid Butt Mar 16 '16 at 09:53
1

Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/106443/discussion-between-praveen-and-burhan-khalid-butt). – Praveen Mar 16 '16 at 09:54

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

1 Answers1