confusion about using types instead of gtts in oracle

Question

I am trying to convert queries like below to types so that I won't have to use GTT:

insert into my_gtt_table_1
(house, lname, fname, MI, fullname, dob)
(select house, lname, fname, MI, fullname, dob 
 from (select 'REG' house, mbr_last_name lname, mbr_first_name fname, mbr_mi MI, 
       mbr_first_name || mbr_mi || mbr_last_name fullname, mbr_dob dob from 
       table_1 a, table_b
       where a.head = b.head and mbr_number = '01' and mbr_last_name = v_last_name) c

above is just a sample but complex queries are bigger than this.
the above is inside a stored procedure. So to avoid the gtt (my_gtt_table_1). I did the following:

create or replace type lname_row as object
    (
     house varchar2(30)
     lname varchar2(30),
     fname varchar2(30),
     MI char(1),
     fullname VARCHAR2(63),
     dob DATE
     )

create or replace type lname_exact as table of lname_row 

Now in the SP:

type lname_exact is table of <what_table_should_i_put_here>%rowtype;
tab_a_recs lname_exact;

In the above I am not sure what table to put as my query has nested subqueries.

query in the SP: (I am trying this for sample purpose to see if it works)

        select lname_row('',
                                '',
                                '',
                                '',
                                '',
                                '',
                                sysdate) bulk collect
          into tab_a_recs
        from table_1;

I am getting errors like : ORA-00913: too many values

I am really confused and stuck with this :(

Alfy

Why do you want to remove GTTs? – Vulgus

Answer 1

You defined a type with 6 attributes and you try to instantiate it with 7 values. Try this instead:

    select lname_row(/*'',*/
                     '',
                     '',
                     '',
                     '',
                     '',
                     sysdate) bulk collect
      into tab_a_recs
    from table_1;

Edit There also seems to be a confusion concerning types. In Oracle you can define types in SQL or in PL/SQL. SQL types are accessibles to SQL (!) while PL/SQL ones offer some extra features but are invisible to pure SQL (PL/SQL can also access SQL types).

That being said, it is confusing and unwise to name types the same both in SQL and PL/SQL (you run into shadowing issues). You defined the lname_exact type two times (with your CREATE STATEMENT and in your DECLARE block). Since you are instantiating a lname_exact in a SQL statement, the type chosen in this case is the SQL type (with only 6 attributes).

You should either remove the declaration of lname_exact on the SP or rename it.

Perdomo

Never underestimate the power of simple arithmetic! – Apoplexy

:) arithmetic was a mistake while posting the question. in the actual code I counted more than twice. I had to remove lname_exact and that worked. shadowing issues for the loss. thanks for all the help – Alfy

though, if I do this for a lot of stored procs then I will have scattered oracle objects hanging out. I will look at putting objects related to one stored proc in one package. – Alfy

Answer 2

In addition to Vincent's excellent answer: You don't need to explicitly create an lname_row in your SQL query. BULK COLLECT will automatically match up the columns selected with the fields of the INTO target. So based on what you've shown so far, you don't need to CREATE types at the schema level, you just need to define them in your PL/SQL block.

Here's a simple example that works:

SQL> l
  1  declare
  2    type my_row is record (x number, y date);
  3    type my_tab is table of my_row;
  4    a_table  my_tab;
  5  begin
  6  select 1,sysdate
  7      bulk collect into a_table
  8      from dual;
  9  dbms_output.put_line(a_table(1).y);
 10* end;
SQL> /
14-JUN-10

Lujan

We have to explicitly instantiate the type if we are bulking collecting into a nested table defined in SQL but not if the nested table is declared in PL/SQL. – Apoplexy