Create a temp table within a procedure and return a ref cursor 2

[Mephansteras]

I need to do something that seems like it'd be easy, but I can't for the life of me figure out how to do this in oracle.

We have a table that contains information about letters that contains (among others) the fields: Letter Id, Column_Nm and Column_Val. A given letter might have anywhere from 2-12 records in this table.

I need to be able to turn those values into a temp table and return that information to an external program (JReports).

For example:

In the Letter table
1 'Addresss' 'Address-info'
1 'Customer id' 101

I need to create a temp table that would look like this
Address Customer id
Address-info 101

I then need to do a select from this temp table into a ref cursor and return that to the external program.

Problem is, I can't figure out how to make Oracle do all three. I can use Execute Immediately to dynamically create the temp table and populate it, but then the procedure won't compile because the temp table doesn't exist yet!

I've tried everything I can think of. Any ideas on how to make this work? Or is this something that would be easy in SQL Server but just can't be done in Oracle?

 

[stefanhei]

Hi.

Creating the table in the stored procedure is a bad idea in my eyes.
When will the table be dropped again? If this is done by JReports what happens if that program dies before it can drop the table.
What happens if a second user calls that procedure before the table is dropped by the first user?
If your application can cope with it you could have a look at transaction specific global temporary tables here.

 

[taupirho]

Please provide a more detailed spec of your letter table and what you want the temp table to look like. I'm not quite getting what you want eg what are the 1's in the first column of your letter table and should they appear in your temp table?

 

[Mephansteras]

Making the table transactional or just session based doesn't bother me. It'll get dropped eventually anyway, and I've got logic in to drop it if it exists anyway.

I'll try to be a little more specific.

The table that holds the letters looks like this

Letter_ID
Letter_Item_Label
Letter_Item_Value
Other attributes...

So, in my earlier example the 1 was just the letter id.

The procedure is passed the letter id and has a ref cursor as an out parameter.

So I'm expecting the external program to call it with something like

Get_Letter_Items(1, refcurs)

The temp table needs to be built with a column for each Letter_Item_Label in the Letter item table.

So if it had 4 records in the Letter Item table, the temp table should have 4 columns.
Col1, Col2, Col3, Col4
with 1 record with the values from the Letter_Item_Value column.
val1, val2, val3, val4

If it had 2 records, the temp table would have 2 columns, and so on.

Is that clearer?

 

[taupirho]

Is there an upper limit to the number of letter_item_label's per leter id?

 

[Mephansteras]

Somewhere around 100.

 

[Beilstwh]

I also wonder why you need a temp table, what is stopping you from build the cursor ref from the base table?

Bill
Oracle DBA/Developer
New York State, USA

 

[Mephansteras]

Well, it may be possible to do it that way, I just don't know what the SQL would be for combining x number of records into a single record without a holding space of some sort.

I need to be able to take some number of records (between 2 and 100) and recombine them into a single record with between 2 and 100 columns.

My current logic is:

CREATE OR REPLACE PROCEDURE IPTS_SD.SP_CANNED_TEXT_ITEM (P_ITEM_ID in integer, p_cursor out sys_refcursor )
AS
  v_tab_create varchar(4000);
  v_insert varchar(4000);
  v_col_len integer;
  v_cnt integer;
  v_temp_nm varchar(40);

  cursor t_cursor is select CORR_ITEM_PARAMETER_NM,CORR_ITEM_PARAMETER_VALUE 
                      from IPTS_SD.CORRESPONDENCE_ITEM_VALUE 
                      where CORRESPONDENCE_ITEM_ID = P_ITEM_ID;
BEGIN
   -- Get all correspondence values for the passed item id 
   
   v_temp_nm := 'ipts_sd.corr_item_temp'; -- || p_item_id;                 
   v_tab_create := 'create global temporary table ' || v_temp_nm || ' (';
   v_insert := 'insert into ' || v_temp_nm || ' values ('; 
   v_cnt := 0;
        
   
   for cnt_var in t_cursor
     loop
        v_col_len := nvl(length(cnt_var.CORR_ITEM_PARAMETER_VALUE ),1);
        if v_col_len < 1 
          then v_col_len := 1; 
        end if;
        v_tab_create := v_tab_create || ' ' || cnt_var.CORR_ITEM_PARAMETER_NM || ' varchar2(' || v_col_len || '),';  
        v_insert := v_insert || '''' || cnt_var.CORR_ITEM_PARAMETER_VALUE || ''',';  
        v_cnt := v_cnt + 1;   
     end loop;
     
   if v_cnt > 0
   then  
    v_tab_create := substr(v_tab_create, 1, length(v_tab_create) -1); 
    v_insert := substr(v_insert, 1, length(v_insert) -1);   
     
    v_tab_create := v_tab_create || ') on commit preserve rows';
    v_insert := v_insert || ')';
   
    --dbms_output.put_line(v_tab_create);
    execute immediate v_tab_create;
    
 
    --dbms_output.put_line(v_insert);
    execute immediate v_insert;
    commit;
                                
    open p_cursor for select * from ipts_sd.corr_item_temp;    
   else
    open p_cursor for select null from dual;
   end if;
      
END;

All of that works, except that I can't reference the temp table from within the procedure since it doesn't exist yet and the procedure won't compile.

 

[taupirho]

It should be perfectly possible to do as you want with the temp table in a stored procedure. See below for a worked example


1 create or replace procedure tomtest1 as
2 type mycur is ref cursor;
3 i_cursor mycur;
4 my_num integer;
5 begin
6 execute immediate 'create global temporary table x(y integer)';
7 execute immediate 'insert into x values(99)';
8 execute immediate 'insert into x values(999)';
9 execute immediate 'insert into x values(9999)';
10 execute immediate 'insert into x values(99999)';
11 open i_cursor for 'select y from x';
12 loop
13 fetch i_cursor into my_num;
14 exit when i_cursor%notfound;
15 dbms_output.put_line( my_num );
16 end loop;
17 close i_cursor;
18* end;
SQL> /

Procedure created.

SQL> show err
No errors.
SQL> set serveroutput on
SQL> execute tomtest1
BEGIN tomtest1; END;

*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "CAPEL.TOMTEST1", line 6
ORA-06512: at line 1


SQL> drop table x
2 /

Table dropped.

SQL> execute tomtest1
99
999
9999
99999

PL/SQL procedure successfully completed.

SQL>

 

[Mephansteras]

Well, that works just fine. Thanks!

I'll have to play with it to see why my code isn't working properly.

Anyone have any ideas on what I'm doing wrong?

 

[Beilstwh]

This will NOT work is more then one user is using it at the same time. If you are going to use a GTT, recreate it and use the same one for every user. This type of program where you build and drop temporary tables is not a good idea.

Bill
Oracle DBA/Developer
New York State, USA

 

[Mephansteras]

Well, that's the trick with this one. I need the temp tables to be different for every letter that is generated. So, I need a system where there can be multiple users with their own version of the temp table that will have different columns (and number of columns).

Really, all I want is a local temp table that I can generate on the fly, use, and drop once the users session ends. Since they are using an external program, each session will end after the program pulls all the data back. Seems strange that Oracle can't handle this easily.

 

[Mephansteras]

Ok. Follow-up on this.

I found the problem with selecting from the temp table. I'd been attempting to do

open p_cursor for select * from ipts_sd.corr_item_temp;

When I should have been doing:

open p_cursor for 'select * from ipts_sd.corr_item_temp';

The single quotes make it so that nothing is checked at compile-time, so no errors show up.

All well and good.

New problem. While I can use this temp table within the procedure, all the records vanish as soon as the procedure is finished. So, JReports is getting nothing back! Hardly productive. I'm surprised at this, since the table is created with "on commit preserve rows"

Anyone got any ideas? How do I create a temp table and return a cursor to an external program?

 

[Beilstwh]

GTT's can only be referenced by the exact same connection (session). Since JReports reconnects internally, it is a separate session and you will see NO rows.

Bill
Oracle DBA/Developer
New York State, USA

 

[Mephansteras]

That's what I was afraid of. Looks like I'll have to find a less elegant solution to this problem.

Thanks!

 

[Beilstwh]

Try the following solution for unique temp tables

declare
my_file_name varchar2(32);
begin
select 'TEMP_'||USERENV('SESSIONID')
into my_file_name
from dual;

execute immediate 'CREATE TABLE '||my_file_name||' (col1 varchar2(10),col2 number(15,2))';
end;

Bill
Oracle DBA/Developer
New York State, USA