How to get the result of all the select queries executed in oracle procedure as the output of the procedure?
I am having n of tables in my database where the table name starts with "test"(for ex. test1, test2, etc.) and all these table have a primary key column, "id" where the value starts from 1. Each table has 10000s of records. I wanted to get the name of all the table with table name starting with "test" and also all the records in the those table in batch wise as the output of the procedure. I have written a procedure, but it is only returning the result of last select query executed by the procedure since I am using cursor. Is there any other alternative for this
My current Procedure:
create or replace PROCEDURE TEST (tablenamecursor out sys_refcursor,mycursorresult out sys_refcursor
)
As
maxx number;
startindex number := 1;
strcountquery clob;
strquery clob;
strquery1 clob;
countt number;
rowcountt number;
batchsize number :=100;
tablenamequery clob;
test clob;
Begin
select count(*) into countt from user_tables where table_name like 'test_%';
loop
exit when (countt=1);
test:='test_'||countt;
tablenamequery := 'SELECT table_name FROM user_tables where table_name = '''||test||'''';
open tablenamecursor for tablenamequery;
strcountquery := 'select count(*) from "'||test||'"';
execute immediate strcountquery into maxx;
loop
exit when (StartIndex>=maxx);
strquery:='SELECT a.*, Row_number() OVER (ORDER BY "id") AS lookup FROM "'||test||'"
a WHERE "id" >='||StartIndex||' and rownum <='||batchsize;
StartIndex:= StartIndex batchsize;
open mycursorresult for strquery;
End loop;
countt:=countt-1;
End loop;
end;
Suppose, I have 2 tables(say, test1, test2, test3), then I am getting only, "test1" in tablenamecursor cursor and the last set of 100 records of "test1" in mycursorresult cursor. But I need all the result of select queries in line no. 19 & 25 in the procedure as the output of my procedure in the same order.
CodePudding user response：
A CURSOR is effectively a pointer to an area of memory on the database server which stores details of the query and where you are in the results set. It will only point to a single results set; you cannot have multiple results sets in the same cursor and if you open a cursor multiple times then it will close the previous cursor and open the new cursor with the new results set; which is why you only get the last set of results (as all the other ones were opened and then closed as the pointer was replaced by the next pointer).
Your procedure is fundamentally flawed as it cannot return more than the two results sets defined in the procedure's signature.
You may be able to change the signature to return an array of cursors or to return a cursor containing nested cursors.
Alternatively, if all the tables have the same structure then you may be able to use UNION ALL or a use a PIPELINED function. However, that would require much more details on what you are trying to achieve.
CodePudding user response：
Maybe you could create a function that generates a separate SQL Select command for all your ( TEST_% ) tables returning them either all or one by one. The function below creates an array of SQL Select commands and returns one just as an example. For function to work, two varray types are created:
Create Or Replace Type TABS_ARRAY As VARRAY(32) OF VARCHAR2(32);
/
Create Or Replace Type TEST_CURSORS_ARRAY As VARRAY(32) OF VARCHAR2(1024);
/
-- ---------------------------------------------------------------------------------------------
create or replace Function Get_Test_Cursors(P_Cursor_Number NUMBER) RETURN VARCHAR2 AS
BEGIN
Declare
CURSOR cTabs IS
Select col.COLUMN_ID "COL_ID", col.OWNER "SCH_NAME", col.TABLE_NAME "TBL_NAME", col.COLUMN_NAME "COL_NAME",
col.DATA_TYPE "COL_TYPE", col.DATA_LENGTH "COL_LEN", col.DATA_PRECISION "COL_PREC",
col.DATA_SCALE "COL_SCALE", col.NULLABLE "COL_NULL",
Sum(1) OVER(Partition By col.TABLE_NAME Order By col.TABLE_NAME) "NUM_OF_COLS"
From sys.ALL_TAB_COLUMNS col
Inner Join sys.ALL_TABLES t ON(col.OWNER = t.OWNER And col.TABLE_NAME = t.TABLE_NAME)
Where col.OWNER = 'YOUR_OWNER' And col.TABLE_NAME Like('TEST_%')
Order By col.TABLE_NAME, col.COLUMN_ID;
cSet cTabs%ROWTYPE;
--
i NUMBER := 0;
j NUMBER := 0;
tbl VARCHAR2(32) := 'xxx';
clmn VARCHAR2(32) := 'xxx';
varrTabs TABS_ARRAY := TABS_ARRAY();
varrCols TABS_ARRAY := TABS_ARRAY();
varrCurs TEST_CURSORS_ARRAY := TEST_CURSORS_ARRAY();
cmd VARCHAR2(512) := 'Select ';
total_cols NUMBER(3) := 0;
Begin
Open cTabs;
Loop
FETCH cTabs Into cSet;
EXIT WHEN cTabs%NOTFOUND;
total_cols := cSet.NUM_OF_COLS;
If i = 0 OR tbl != cSet.TBL_NAME Then
tbl := cSet.TBL_NAME;
j := 0;
cmd := 'Select ';
Begin
i := To_Number(Replace(cSet.TBL_NAME, 'TEST_', ''));
varrTabs.extend;
varrTabs(i) := cSet.TBL_NAME;
j := j 1;
varrCols.extend;
varrCols(j) := cSet.COL_NAME;
If j = 1 Then
cmd := cmd || cSet.COL_NAME;
Else
cmd := cmd || ', ' ||cSet.COL_NAME;
End If;
Exception
When OTHERS Then Null;
End;
ElsIf i > 0 And tbl = cSet.TBL_NAME Then
j := j 1;
varrCols.extend;
varrCols(j) := cSet.COL_NAME;
If j = 1 Then
cmd := cmd || cSet.COL_NAME;
Else
cmd := cmd || ', ' ||cSet.COL_NAME;
End If;
End If;
If total_cols <= j Then
varrCurs.extend;
varrCurs(i) := cmd || ' From ' || tbl;
End If;
End Loop;
Close cTabs;
RETURN varrCurs(P_Cursor_Number);
End;
END Get_Test_Cursors;
If a few of your TEST tables were like here...
Create Table TEST_1 (ID Number(6), DATE_OF_ID DATE, NAME_OF_ID VARCHAR2(32));
INSERT INTO TEST_1 VALUES(1, SYSDATE - 5, 'Name 1');
INSERT INTO TEST_1 VALUES(2, SYSDATE - 4, 'Name 2');
INSERT INTO TEST_1 VALUES(3, SYSDATE - 3, 'Name 3');
INSERT INTO TEST_1 VALUES(4, SYSDATE - 2, 'Name 4');
INSERT INTO TEST_1 VALUES(5, SYSDATE - 1, 'Name 5');
-- ---------------------------------------------------------------------------------------------
Create Table TEST_2 (ID Number(6), NAME_OF_ID VARCHAR(32), DESCRIPTION_OF_ID VARCHAR2(512));
INSERT INTO TEST_2 VALUES(10, 'Name 10', 'Description Of 10');
INSERT INTO TEST_2 VALUES(20, 'Name 20', 'Description Of 20');
INSERT INTO TEST_2 VALUES(30, 'Name 30', 'Description Of 30');
-- ---------------------------------------------------------------------------------------------
Create Table TEST_3 (ID Number(6), DATE_OF_ID DATE, NAME_OF_ID VARCHAR(32), REF_ID Number(6), DATE_OF_REF_ID DATE);
INSERT INTO TEST_3 VALUES(100, SYSDATE, 'Name 100', 1, SYSDATE - 5);
Commit;
... then the varray would collect following SQL Selects:
--
-- varrCurs containes 3 commands:
--
-- Select ID, DATE_OF_ID, NAME_OF_ID From TEST_1
-- Select ID, NAME_OF_ID, DESCRIPTION_OF_ID From TEST_2
-- Select ID, DATE_OF_ID, NAME_OF_ID, REF_ID, DATE_OF_REF_ID From TEST_3
--
A few options of how to use these commands you could se at (Using Dynamic SQL for Multirow Queries)...
If, by any chance, all your TEST tables have the same columns you could do the UNION, but that probably isn't the case.
Here is a very simple code using the function (table TEST_1) and printing the data out..
SET SERVEROUTPUT ON
Declare
c SYS_REFCURSOR;
cmd VARCHAR2(512);
mId NUMBER(3);
mDt DATE;
mNm VARCHAR2(32);
Begin
cmd := GET_TEST_CURSORS(1);
DBMS_OUTPUT.PUT_LINE(cmd || Chr(10) || Chr(10) ||
LPAD('ID', 8, ' ') || ' ' || RPAD('DATE_OF_ID', 12, ' ') || ' ' || RPAD('NAME_OF_ID', 12, ' ') || Chr(10) ||
LPAD('-', 8, '-') || ' ' || RPAD('-', 12, '-') || ' ' || RPAD('-', 12, '-'));
OPEN c FOR cmd;
LOOP
FETCH c INTO mId, mDt, mNm;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LPAD(mId, 8, ' ') || ' ' || RPAD(mDt, 12, ' ') || ' ' || RPAD(mNm, 12, ' '));
END LOOP;
CLOSE c;
End;
--
-- R e s u l t :
--
-- ID DATE_OF_ID NAME_OF_ID
-- -------- ------------ ------------
-- 1 08-NOV-22 Name 1
-- 2 09-NOV-22 Name 2
-- 3 10-NOV-22 Name 3
-- 4 11-NOV-22 Name 4
-- 5 12-NOV-22 Name 5
Regards...