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:
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your code also looks correct.
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
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.
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;
/