-
Hello! Is there any way to use "execute immediate" with cursors? I would like to make my stored proc somewhat dynamic. Here is a scaled-down version of my procedure:
CREATE OR REPLACE PACKAGE Publications_pkg AS
TYPE char_array IS TABLE OF VARCHAR2(250)
INDEX BY BINARY_INTEGER;
PROCEDURE getPublicationInfo(
o_PubNumber OUT char_array,
o_Locations OUT char_array,
o_Title OUT char_array,
o_Organization OUT char_array,
o_PublicationType OUT char_array,
i_DisplayRows IN INTEGER);
END Publications_pkg;
/
CREATE OR REPLACE PACKAGE BODY Publications_pkg AS
PROCEDURE getPublicationInfo(
o_PubNumber OUT char_array,
o_Locations OUT char_array,
o_Title OUT char_array,
o_Organization OUT char_array,
o_PublicationType OUT char_array,
i_DisplayRows IN INTEGER)
IS
CURSOR crsPub IS
SELECT P.PubNumber,
L.Name AS Locations,
SUBSTR(P.Title, 1, 50),
OrgName(P.OrganizationID) AS Organization,
PubTypeName(P.PublicationTypeID) AS PublicationType
FROM (SELECT PublicationID, Name FROM Location) L, Publications P
WHERE LOWER(P.Title) LIKE LOWER('%test%')
AND L.PublicationID (+) = P.ID
ORDER BY RevDate;
BEGIN
IF NOT crsPub%ISOPEN THEN -- open the cursor if
OPEN crsPub; -- not already open
END IF;
FOR i IN 1..i_DisplayRows LOOP
FETCH crsPub INTO o_PubNumber(i),
o_Locations(i),
o_Title(i),
o_Organization(i),
o_PublicationType(i);
IF crsPub%NOTFOUND THEN
CLOSE crsPub;
END IF;
END LOOP;
END;
/
I have executed statements in the past by concatinating variables that have parts of code in them, and using many if statements, but because the cursor can't go after "BEGIN," (I think this is true of all cases), I haven't been able to make it work.
Have I painted myself into a corner by the way I've structured this procedure, or is there another way to make the proc dynamic?
Thanks in advance for any help!
Shannon
-
well you can use cursor like this
Code:
declare
.............
begin
.............
for i in (select * from dba_users)
loop
execute immediate ...........
end loop;
end;
/
-
i hope this may help u.
this procedure will dynamically create a PL/SQL block.
u try to give dbms_output.put_line() and do the check
DECLARE
lv_table_name VARCHAR(32) := 'emp' ;
lv_dest_table_name VARCHAR(32) := 'testemp' ;
col_name VARCHAR(32) ;
datatype_name VARCHAR(32) ;
stmt_col VARCHAR(4000) ;
stmt_column VARCHAR(4000) ;
sele_stmt VARCHAR(4000) ;
stmt VARCHAR(32767) ;
stmt_1 VARCHAR(10) := 'DECLARE '||CHR(10);
stmt_2 VARCHAR(4000) := '
TYPE c1 IS REF CURSOR;
y1 c1;
PROCEDURE oc (
ct1 IN VARCHAR2,
co1 IN OUT c1
)
IS
BEGIN
OPEN co1 FOR ct1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END;';
BEGIN
FOR r IN (SELECT column_name,data_type,data_length FROM all_tab_columns
WHERE table_name = lv_table_name)
LOOP
stmt_column := stmt_column || ' , ' || r.column_name ;
sele_stmt := SUBSTR(stmt_column,3);
stmt := stmt || 'p_' || r.column_name ||' ' ||lv_table_name||'.'||r.column_name||'%TYPE;'|| CHR(10);
stmt_col := stmt_col || 'p_' || r.column_name || ',';
END LOOP;
stmt := stmt_1 || stmt || stmt_2 ||'
BEGIN
oc( ''SELECT' || sele_stmt || ' FROM ' || lv_table_name || ' '',y1);
LOOP
FETCH y1 INTO ' || SUBSTR(stmt_col,1,LENGTH(stmt_col)-1) || ';
EXIT WHEN y1%notfound;
INSERT INTO '|| lv_dest_table_name || ' VALUES( ' || SUBSTR(stmt_col,1,LENGTH(stmt_col)-1) || ' );
COMMIT;
END LOOP;
END;';
EXECUTE IMMEDIATE stmt ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR'||SQLCODE||SQLERRM);
NULL;
END;
-
Thanks for your help! Is it possible to make the select statement dynamic with what I've got?
e.g.,
IF myVar IS NOT NULL THEN
Statement1 := 'Select myField1 FROM myTable1 WHERE myField2 = myVar';
ELSE
Statement1 := 'Select myField1 FROM myTable1';
END IF; etc...
Thanks again,
Shannon
-
I figured out how to get cursors to work w/ dynamic sql, without using "Execute Immediate." So...for those who are interested:
CREATE OR REPLACE PACKAGE Publications_pkg AS
TYPE PubInfo IS REF CURSOR;
PROCEDURE getPublicationInfo( crsPub OUT PubInfo,
i_DisplayRows IN INTEGER,
i_whr IN VARCHAR2 := NULL);
END Publications_pkg;
/
CREATE OR REPLACE PACKAGE BODY Publications_pkg AS
PROCEDURE getPublicationInfo2( crsPub OUT PubInfo,
i_DisplayRows IN INTEGER,
i_whr IN VARCHAR2 := NULL)
IS
BEGIN
OPEN crsPub FOR
'SELECT P.PubNumber,
P.ATANumber,
P.RevNumber,
P.Volume,
P.RevDate,
P.OrigDate,
L.Name AS Locations,
SUBSTR(P.Title, 1, 50),
OrgName(P.OrganizationID) AS Organization,
PubTypeName(P.PublicationTypeID) AS PublicationType
FROM (SELECT PublicationID, Name FROM Location) L, Publications P
WHERE ' || NVL (i_whr, '1=1') ||
' AND L.PublicationID (+) = P.ID
ORDER BY RevDate';
END;
END;
/
Cheers!
Shannon
