CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Stefanos Kargas
Stefanos Kargas

Reputation: 11073

Oracle PL/SQL - Show results of declared table

I am using Toad. I have a declaration of a table in a package as follows:

  TYPE MyRecordType IS RECORD
    (ID                      MyTable.ID%TYPE
    ,FIELD1                  MyTable.FIELD1%TYPE
    ,FIELD2                  MyTable.FIELD2%TYPE
    ,FIELD3                  MyTable.FIELD3%TYPE
    ,ANOTHERFIELD            VARCHAR2(80)
    );
  TYPE MyTableType IS TABLE OF MyRecordType INDEX BY BINARY_INTEGER;

There is a procedure (lets say MyProcedure), that is using an object of this table type as input/output. I want to run the procedure and see the results (how the table is filled). So I am thinking I will select the results from the table:

declare
    IO_table           MyPackage.MyTableType;
begin                      
    MyPackage.MyProcedure (IO_table
                      ,parameter1
                      ,parameter2
                      ,parameter3);
    select * from IO_table;
end;

I get the message: Table or view does not exist (for IO_table). If I remove the select line, the procedure runs successfully, but I cannot see its results. How can I see the contents of IO_table after I call the procedure?

Upvotes: 0

Views: 447

Answers (3)

vishad
vishad

Reputation: 1164

You cannot see the results for a PL/SQL table by using Select * from IO_table

You will need to loop through the collection in the annonymous block. do something like, given in pseudo code below...

       declare
        IO_table           MyPackage.MyTableType;
        l_index        BINARY_INTEGER;
    begin                      
      MyPackage.MyProcedure (IO_table
                  ,parameter1
                  ,parameter2
                  ,parameter3);
       l_index := IO_table.first;

      While  l_index is not null
      loop

      dbms_output.put_line (IO_table(l_index).id);
      .
      .
      .
      .

      l_index :=IO_table.next(l_index_id); 

     end loop;  
     end;

Upvotes: 1

Dmitry Nikiforov
Dmitry Nikiforov

Reputation: 3038

1) You can not use associated arrays in SELECT statement, Just nested tables or varrays declared globally. 2) You should use TABLE() expression in SELECT statement 3) You can't simply use SELECT in PL/SQL code - cursor FOR LOOP or REF CURSOR or BULK COLLECT INTO or INTO must be used. 4) The last but not least - please study the manual: http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjcol.htm#ADOBJ00204

Just an example:

    SQL> create type t_obj as object( id int, name varchar2(10));
  2  /

SQL> create type t_obj_tab as table of t_obj;
  2  /

SQL> var rc refcursor
SQL> declare
  2   t_var t_obj_tab := t_obj_tab();
  3  begin
  4   t_var.extend(2);
  5  t_var(1) := t_obj(1,'A');
  6  t_var(2) := t_obj(2,'B');
  7   open :rc for select * from table(t_var);
  8  end;
  9  /

SQL> print rc

        ID NAME                                                                 
---------- ----------                                                           
         1 A                                                                    
         2 B

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59486

You have to do it like this:

select * from TABLE(IO_table);
  • and, of course you missed the INTO or BULK COLLECT INTO clause

Upvotes: 1

Related Questions