-
"from" clause question
Hi,
How can I make the "FROM" clause variable using a cursor ??
Cursor c_one is
select tables
from all_tables
where ..... ;
r_c_one c_one%rowtype;
Cursor c_two is
select count(*)
from r_c_one.tables;
r_c_two c_two%rowtype;
Or... do I have to use dynamic SQL for this??
Please give me some examples on dynamic SQL ( its been ages ago I last used it ).
mvg GKramer
-
Hi.
If you have a finite number of options you could do something like:
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
c_objects SYS_REFCURSOR;
TYPE t_object IS RECORD (
object_name user_objects.object_name%TYPE,
object_type user_objects.object_type%TYPE
);
l_object t_object;
BEGIN
IF FALSE THEN
OPEN c_objects FOR
SELECT object_name, object_type FROM user_objects;
ELSE
OPEN c_objects FOR
SELECT object_name, object_type FROM all_objects WHERE owner = USER;
END IF;
LOOP
FETCH c_objects
INTO l_object;
EXIT WHEN c_objects%NOTFOUND;
DBMS_OUTPUT.put_line('Object Name:' || l_object.object_name || ' - ' || l_object.object_type);
END LOOP;
CLOSE c_objects;
END;
/
Otherwise use Dynamic SQL.
Cheers
-
TIM,
This didn't work out You defined two tables in te select part.
My chalange is to retrieve data from various tables fetched by a previous cursor ( like the example I gave )
/*********************************************************/
-- Retrieval of tablenames from all_tables
-- based on the user and made earlier in another script
Cursor c_one is
select tables
from all_tables
where ..... ;
r_c_one c_one%rowtype;
/*********************************************************/
-- I want to make a selection on these newmade tables
-- fetched in cursor c_one DYNAMICLY
Cursor c_two is
select data
from r_c_one.tables;
r_c_two c_two%rowtype;
/*********************************************************/
If this can not be done in a cursor please give me some examples of dynamic SQL on this.
Regards,
GKramer
-
Hi to all,
I found some doumetation in the DYNAMIC SQL and assembled a script on this information and it WORKS !!! ( hurray ).
If you have some comment on it please let me know.
Regards,
GKramer
******************************************************************
create or replace procedure XXXXXXX
as
--
STRING_V VARCHAR2(2000);
--
CURSOR C_EEN IS
SELECT OWNER,
TABLE_NAME
FROM ALL_TABLES
WHERE OWNER ='Q1000_SPSS'
AND (TABLE_NAME LIKE ('U0%')
OR TABLE_NAME LIKE ('M0%') )
and TABLE_NAME LIKE ('%MODULE')
AND TABLE_NAME NOT LIKE ('%SESSION%');
--
R_C_EEN C_EEN%ROWTYPE;
--
begin
--
open c_een;
fetch c_een into r_c_een;
while c_een%found loop
--
--DBMS_OUTPUT.PUT_LINE(r_c_een.table_name);
STRING_V := 'select count(*) from Q1000_spss.'||r_c_een.table_name;
--
DECLARE
LOGCUR INTEGER;
REQ_COLUMN VARCHAR2(2000);
LOGCUR_EXEC INTEGER;
STRING_DAT VARCHAR2(3000);
LAST_ROW EXCEPTION;
PRAGMA EXCEPTION_INIT(LAST_ROW,-01002);
BEGIN
LOGCUR:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(LOGCUR,STRING_V,DBMS_SQL.NATIVE);
dbms_sql.define_column(LOGCUR, 1, REQ_COLUMN,2000);
LOGCUR_EXEC := DBMS_SQL.EXECUTE(LOGCUR);
--
loop
IF DBMS_SQL.FETCH_ROWS(LOGCUR) = 1 THEN
DBMS_SQL.COLUMN_VALUE(LOGCUR, 1, REQ_COLUMN);
--
--DBMS_OUTPUT.PUT_LINE('r_c_een.table_name '||r_c_een.table_name);
DBMS_OUTPUT.PUT_LINE(' Number of column present in '||r_c_een.table_name||' -- '||REQ_COLUMN);
--
STRING_DAT := 'INSERT INTO Q1000_spss.aap VALUES ( ''I did it !!'||r_c_een.table_name||' ** '||REQ_COLUMN||''')';
DBMS_OUTPUT.PUT_LINE('INSERT INTO Q1000_spss.aap VALUES ( ''I did it !!'||r_c_een.table_name||' ** '||REQ_COLUMN||''')');
execute immediate(STRING_DAT);
ELSE
RAISE LAST_ROW;
END IF;
end loop;
--
DBMS_SQL.CLOSE_CURSOR(LOGCUR);
--
EXCEPTION
WHEN LAST_ROW THEN
null;
--DBMS_OUTPUT.PUT_LINE('--Last row--');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('What went wrong ;'||SQLERRM);
END;
--
commit;
--
fetch c_een into r_c_een;
end loop;
close c_een;
end;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|