Hi,
I have few questions in creating dynamic queries in Oracle procedure
using Toad.
I have a table (xyz) where there are 3 fields ( P, Varchar, Q Varchar,
R Varchar). P Column will have the "Table name" of the other tables in
the database, Q column will have the "Field names" for the
corresponding table_name in the P column and R will have a value for
that field. Find below the procedure, which I created to do this task,
I'm very new to Oracle Procedures and Dynamic Query, infact I haven't
doen this before and don't have relavent experience in this. Any help
on this will be highly appreciated very much.

Thanks
Arun


CREATE OR REPLACE procedure DEFAULT_MATCH()
as

DECLARE

Tname In_Params.Table_name%TYPE;
Fname In_Params.Field_name%Type;
Def_val In_Params.Default_Value%Type;
Mcount Default_Result.Match_count%TYPE;

For i in 1..5
Loop

Cursor T1 is Select Table_Name, Field_name, Default_Value from
In_params;

BEGIN

OPEN T1;

FETCH T1 into Tname, Fname, Def_val;

Cursor C1 is select count(*) from Tname where Fname = Def_val;

OPEN C1;
FETCH C1 into Mcount;
Exit when C1%NOTFOUND;
INSERT into Default_values Values (Mcount);

Close T1;
Close C1;

End loop;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
End Default_Match;
End
..
run;