title: Table variable as in parameter to populate a table in oracle Stored procedure
body: <p>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.</p>
  
  <pre><code>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
  /
  </code></pre>
  
  <p>Stored Procedure:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>
  
  <p>Any help please.</p>
  
  <p>Procedure for loading MD_TYPE_TABLE by passing parameters to MD_TYPE</p>
  
  <pre><code>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
    &lt;&lt;vartype&gt;&gt;
           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;
  </code></pre>