Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Trouble executing data found on TABLE: exe_sel_dttime_xml (COLUMN: QUERY)
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;
>
> ------------------------------------------------------------------------------
> 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