Re: Trouble executing data found on TABLE: exe_sel_dttime_xml (COLUMN: QUERY)

From: <fitzjarrell_at_cox.net>
Date: 31 Jul 2006 07:04:23 -0700
Message-ID: <1154354663.750436.268650@s13g2000cwa.googlegroups.com>

Comments embedded.
nicolas246_at_gmail.com wrote:
> Hello,
>
> I want to execute using EXECUTE IMMEDIATE the data found on a table
> exe_sel_dttime_xml

And it isn't doing what you want, or it isn't doing what you expect? There is a difference.

>
> The following code inside a PLSQL procedure constantly gives me coding
> problems(therefore not working as it should!!):
>

Define 'coding problems'; my crystal ball has been in disrepair for years.

> BEGIN
> FOR r
> IN (SELECT QUERY INTO v_toexecute FROM exe_sel_dttime_xml)
> LOOP
> EXECUTE IMMEDIATE v_toexecute;
> END LOOP;
> END;
>

What, exactly, is r? It appears to be a senselessly declared variable of absolutely no use whatsoever. I should think the following loop would be better suited to this task:

declare

     cursor get_qry is
     select query from exe_sel_dttime_xml;
begin
     for c_toexecute in get_qry loop
          execute immediate c_toexecute;
     end loop;

end;
/

>
> ------------------------------------------------------------------------------
> Basically what this code is supposed to do is execute each of the data
> found inside the COLUMN: [QUERY] found on the TABLE:
> [exe_sel_dttime_xml]
>

Which is what I posted and should do exactly as you expect.

> ie,
>
> SQL> r
> 1 select * from exe_sel_dttime_xml
> 2*
>
> QUERY
> --------------------------------------------------------------------------------
> SELECT COL_TNAME, DEVICE_DTTIME
> INTO table_dttime(COL_TNAME, COL_DTTIME)
> FROM t_xml_01;
>
> SELECT COL_TNAME, DEVICE_DTTIME
> INTO table_dttime(COL_TNAME, COL_DTTIME)
> FROM t_xml_02;
>
> SELECT COL_TNAME, DEVICE_DTTIME
> INTO table_dttime(COL_TNAME, COL_DTTIME)
> FROM t_xml_03;
>
> SELECT COL_TNAME, DEVICE_DTTIME
> INTO table_dttime(COL_TNAME, COL_DTTIME)
> FROM t_xml_04;
>
> SELECT COL_TNAME, DEVICE_DTTIME
> INTO table_dttime(COL_TNAME, COL_DTTIME)
> FROM t_xml_05;
>
> SELECT COL_TNAME, DEVICE_DTTIME
> INTO table_dttime(COL_TNAME, COL_DTTIME)
> FROM t_xml_06;
>
> Remember,
> I need this to work using the following:
> + EXECUTE IMMEDIATE
> + Querying the data from table: exe_sel_dttime_xml
>

Correction: you WANT this to work using that methodology. Use the PL/SQL block I provided instead of the 'interesting block' you've tried to code.

> (ie, SELECT QUERY INTO v_toexecute FROM exe_sel_dttime_xml)
>

WHY do you insist upon using SELECT ... INTO in a LOOP? Learn something from the examples posted.

> All (and any) help is appreciated
> -ng

David Fitzjarrell Received on Mon Jul 31 2006 - 09:04:23 CDT