Dynamic Query In Oracle Procedure

From: Arun <arunachalam.arcot_at_gmail.com>
Date: 26 Jun 2006 13:17:26 -0700
Message-ID: <1151353046.920690.131250@b68g2000cwa.googlegroups.com>

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; Received on Mon Jun 26 2006 - 15:17:26 CDT