Re: Dynamic Query In Oracle Procedure

Arun wrote:
> 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;

Several thoughts. The first is that you are reinventing the wheel. And apparently at considerable expense. What you describe is called all_tab_columns and already exists.

Second, unless you have a Neolithic version of Oracle you should steer away from cursor loops and use array processing (see Bulk Collect and FORALL). Finally, from you code snippet, I can't why or where you would want to use NDS. Can you explain why you can't use the existing resource and why NDS would be of any value as well as providing version info.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org