Link to home
Start Free TrialLog in
Avatar of ncsvietnam
ncsvietnam

asked on

Template table in a function

Hi experts,

Please see my problem bellow and let me know how to make it, thanks!
I want to create a template table in a function, insert data into it and return it by a cursor. Please see my pseudo code:
function report_missing_premium(report_date in date) return ref cursor is
-- define temp table to store data
temp_tbl table(varchar2, number);
data_cursor ref cursor;
loop_counter number;
begin
  -- build table data
  for loop_counter IN 1..10
  loop
     insert into temp_tbl values('loop_counter', loop_counter);
  end loop;
  
  -- insert all result into cursor
  open data_cursor for
    select * from temp_tbl;
return data_cursor;
end report_missing_premium;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Your code also looks correct.
Avatar of ncsvietnam
ncsvietnam

ASKER

@schwertner:
You're must be joking, right?
We cannot define a table like that, it's only my pseudo code.

Error(1368,10): PLS-00314: TABLE declarations are not allowed as PL/SQL local variables
No, I am not joking!
There are some methods to create temporary table "on the fly" and use it.
Example:
http://www.adp-gmbh.ch/ora/admin/read_alert/index.html

But you can precreate an working regular table in the schema (why not! what is bad to do this?)
in advance and to INSERT records there when you need but also DELETE the  records after you consume
them.

Possibly the same method will create TEMPORARY table.

The snippet shows what I use.
It really works and helps me, but it is in a Package.
Create a temporary table IN PACKAGE:
 
/* Create tables and types used in the package */
 
declare
   v_count number;
   V_string varchar2(600);
begin
  select count(*) into v_count from dba_tables 
  where lower(owner)='sys' and lower(table_name)='phr_tbs';
  if v_count = 1 then
      V_string := 'DROP TABLE phr_tbs';
      DBMS_UTILITY.EXEC_DDL_STATEMENT (v_string);
  end if;
end;
/
 
CREATE GLOBAL TEMPORARY TABLE phr_tbs 
       (tablespace_name VARCHAR2(800),
        tables          NUMBER,
        ind             NUMBER);
 
/* End Create tables and types used in the package */
 
CREATE OR REPLACE PACKAGE BODY icwsof
IS
....
 
PROCEDURE analyze_user_tbs
IS
   v_out   VARCHAR2(9000);
   v_sql   VARCHAR2(9000);
   V_parse_string  VARCHAR2(1200);
   v_tbs_name VARCHAR2(300);
   v_tables   NUMBER;
   v_indexes  NUMBER;   
BEGIN
   --CREATE GLOBAL TEMPORARY TABLE phr_tbs (tablespace_name VARCHAR2(800),tables NUMBER,ind NUMBER);
   SELECT count(*) INTO v_tables FROM phr_tbs;
   IF v_tables >0 THEN
      DELETE FROM phr_tbs;
   END IF;
   dbms_output.enable(100000);
   v_out := '' || ' USERNAME, DEFAULT TABLESPACE, DEFAULT TEMPORARY TABLESPACE ' || CHR(10) || 'QUOTA ON EVERY TABLESPACE!:' 
                ; 
   dbms_output.put_line(v_out); 
   FOR i in (select username, DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users 
   WHERE upper(username) like 'BAS%' 
     OR  upper(username) like 'EHF%' 
     OR  upper(username) like 'PHR%'
     OR  upper(username) like 'LSUSER%' 
   ORDER BY username) LOOP
    v_out := '' || RPAD(i.username,20) 
                || '    ' || RPAD(i.DEFAULT_TABLESPACE,20) 
                || '   ' || RPAD(i.TEMPORARY_TABLESPACE,20);
    dbms_output.put_line(v_out);
    FOR k IN (SELECT TABLESPACE_NAME, MAX_BYTES 
              FROM   dba_ts_quotas
              WHERE  UPPER(username) = UPPER (i.username)) LOOP
         v_out := '' || RPAD(k.TABLESPACE_NAME,20) 
                     || '   '  || CASE k.MAX_BYTES 
                                  WHEN -1 THEN RPAD('Unlimited',20)
                                  ELSE  RPAD(k.MAX_BYTES,20)
                                  END;
         dbms_output.put_line(v_out);
    END LOOP;
    END LOOP;
       dbms_output.put_line(CHR(10));
       FOR i in (select tablespace_name  from dba_tablespaces 
                 WHERE upper(tablespace_name) like 'BAS%' 
                   OR  upper(tablespace_name) like 'EHF%' 
                   OR  upper(tablespace_name) like 'PHR%'
                   ORDER BY tablespace_name) LOOP
             v_tables   := 0;
             v_indexes  := 0;
             SELECT count(*) INTO v_tables FROM dba_tables WHERE tablespace_name = i.tablespace_name;
             SELECT count(*) INTO v_indexes FROM dba_indexes WHERE tablespace_name = i.tablespace_name;
             INSERT INTO phr_tbs(tablespace_name,tables,ind)  VALUES  ( i.tablespace_name, v_tables, v_indexes); 
       END LOOP;
       
       v_out := '' || ' Tablespace Name:          Tables:   Indexes:' 
                   || CHR(10); 
       dbms_output.put_line(v_out); 
       FOR i IN (select * from phr_tbs) LOOP
          dbms_output.put_line(RPAD(i.tablespace_name,30) || LPAD(i.tables,4) ||  LPAD(i.ind,4) );
       END LOOP;
          select count(*) into  v_tables
          from phr_tbs
          where tables=0 and ind=0 
            and tablespace_name not in ('PHR7USERS','PHRTEMP');
          IF v_tables <> 0 THEN
             dbms_output.put_line(CHR(10) ||'FOLLOWING TABLESPACES  CURRENTLY ARE NOT USED:' || CHR(10) );
             FOR i IN (select p1.tablespace_name dt, p2.tablespace_name ix
                       from phr_tbs p1, phr_tbs p2
                       where SUBSTR(p1.tablespace_name,5) = SUBSTR(p2.tablespace_name,5)
                         and SUBSTR(p1.tablespace_name,4,1) = 'D'
                         and SUBSTR(p2.tablespace_name,4,1) = 'I'
                         and p1.tables=0 and p1.ind=0 
                         and p2.tables=0 and p2.ind=0
                         and p1.tablespace_name not in ('PHR7USERS','PHRTEMP')
                         and p2.tablespace_name not in ('PHR7USERS','PHRTEMP')
                       order by p1.tablespace_name)  LOOP
             dbms_output.put_line(RPAD(i.dt,30));
             dbms_output.put_line(RPAD(i.ix,30));
             END LOOP;             
             dbms_output.put_line(CHR(10) ||'You can ask to drop these tablespaces.' || CHR(10) );
             dbms_output.put_line('As with any critical operation like dropping datafiles,');
             dbms_output.put_line('ensure you have a full backup of the database.');   
             dbms_output.put_line('You can use the following SQL statements to drop the tablespaces:'|| CHR(10));
             FOR i IN (select p1.tablespace_name dt, p2.tablespace_name ix
                       from phr_tbs p1, phr_tbs p2
                       where SUBSTR(p1.tablespace_name,5) = SUBSTR(p2.tablespace_name,5)
                         and SUBSTR(p1.tablespace_name,4,1) = 'D'
                         and SUBSTR(p2.tablespace_name,4,1) = 'I'
                         and p1.tables=0 and p1.ind=0 
                         and p2.tables=0 and p2.ind=0
                         and p1.tablespace_name not in ('PHR7USERS','PHRTEMP')
                         and p2.tablespace_name not in ('PHR7USERS','PHRTEMP')
                       order by p1.tablespace_name)  LOOP
                 v_sql := '' || 'DROP TABLESPACE '|| i.dt || ' INCLUDING CONTENTS AND DATAFILES;';
                 dbms_output.put_line(v_sql);
                 v_sql := '' || 'DROP TABLESPACE '|| i.ix || ' INCLUDING CONTENTS AND DATAFILES;';
                 dbms_output.put_line(v_sql);
             END LOOP;
          END IF;    
          DELETE FROM phr_tbs;
END analyze_user_tbs;
...
 
END icwsof;
/

Open in new window