can I use a query as a variable?

[SnigRO]

How can I do this?

If I use as following, there is no error;
FOR k IN (SELECT * FROM sys.my_table ) LOOP

however I want to use this FOR LOOP in a variable as following;
query VARCHAR2(100) := 'SELECT * FROM sys.all_objects ';
FOR k IN (query) LOOP

Is there any way to do this?

[willakawill]

How can I do this?

If I use as following, there is no error;
FOR k IN (SELECT * FROM sys.my_table ) LOOP

however I want to use this FOR LOOP in a variable as following;
query VARCHAR2(100) := 'SELECT * FROM sys.all_objects ';
FOR k IN (query) LOOP

Is there any way to do this?

Hi
I assume that you want to do this so that you can alter the SELECT part dynamically. That being the case you could certainly create this string in a calling app such as in vb:

stNewQuery = "SELECT * FROM sys.all_objects"
stLoop = "FOR k IN (" & stNewQuery & ") LOOP"

Or you could hard code several statements for different loops and use an IF condition to select the correct loop

[SnigRO]

I tried as you show but i had again errrors..

I actually want to this:

query VARCHAR2(100) := 'SELECT * FROM sysmy_table;

--instead of
--FOR k IN ( SELECT * FROM sys.my_table)
--this, then i can change the query as a input parameter.
FOR k IN ( query )
LOOP

input_string := k.my_column;

input_string := rpad( input_string, (trunc ( length(input_st ring)/8 ) +1)*8, chr(0) );
raw_input := UTL_RAW.CAST_TO _RAW(input_stri ng);

dbms_obfuscatio n_toolkit.DESEn crypt(
input => raw_input,
key => raw_key,
encrypted_data => encrypted_raw );

utl_file.put_li ne(f, encrypted_raw );

dbms_output.put _line('>' || encrypted_raw);

END LOOP;

[SnigRO]

Actually the tittle has to be : Can I use a variable as query ? :))

[willakawill]

I tried as you show but i had again errrors..

I actually want to this:

query VARCHAR2(100) := 'SELECT * FROM sysmy_table;

--instead of
--FOR k IN ( SELECT * FROM sys.my_table)
--this, then i can change the query as a input parameter.
FOR k IN ( query )
LOOP

input_string := k.my_column;

input_string := rpad( input_string, (trunc ( length(input_st ring)/8 ) +1)*8, chr(0) );
raw_input := UTL_RAW.CAST_TO _RAW(input_stri ng);

dbms_obfuscatio n_toolkit.DESEn crypt(
input => raw_input,
key => raw_key,
encrypted_data => encrypted_raw );

utl_file.put_li ne(f, encrypted_raw );

dbms_output.put _line('>' || encrypted_raw);

END LOOP;

Yes you can do this if you branch through the possible values of the input string with IF and hard code the possible queries. This will multiply the number of lines of code by the number of possible input values.

[SnigRO]

thank you... I found the solution..

TYPE deneme IS REF CURSOR;
query_cv deneme;
k sys.all_objects %ROWTYPE;
query_q VARCHAR2(100) := 'select * from sys.all_objects ';

f := utl_file.fopen( 'ORALOAD', 'something.txt' , 'w');
OPEN query_cv FOR query_q;
LOOP
FETCH query_cv INTO k;
EXIT WHEN query_cv%NOTFOU ND;