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

From: NIk Eichler <nik.eichler_at_oracle.com>
Date: Thu, 03 Aug 2006 11:52:02 +0200
Message-ID: <sZjAg.18$K%1.45@news.oracle.com>

fitzjarrell_at_cox.net wrote:

> 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
> 

Hi Nicolas,

the following workes for me:

REM This is the table that contains the statements to be executed CREATE TABLE t (f1 varchar2(70));

REM Statements to be executed
REM This simple example only allows DDL Statements or updates, delete, inserts, no selects:

INSERT INTO T VALUES('create table t2(f1 date)')
INSERT INTO T VALUES('create table t3(f1 date)')
INSERT INTO T VALUES('create table t4(f1 date)')
INSERT INTO T VALUES('create table t5(f1 date)')

REM procedure to execute the statements in table t

-- 
begin
for c1 in (select f1 from t) loop
execute immediate(c1.f1);
end loop;
end;
/

select table_name from user_tables;   REM Look up whether tables have 
been created

TABLE_NAME
------------------------------
T2
T3
T4
T

best regards,

Nik

Received on Thu Aug 03 2006 - 04:52:02 CDT