Home » SQL & PL/SQL » SQL & PL/SQL » Problem in Execute Immediate (oracle 10 g)
Problem in Execute Immediate [message #420246] Thu, 27 August 2009 14:46 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi friends,
I didnt figure out why the code is not working. Every thing looks good to me.
create or replace procedure table_proc is

i varchar2(4):=43;
cursor table_creation is
select * from Table_info  where CREATED_FLG='N';

sql_stmt varchar2(400);

Begin
   for r_table_creation in table_creation loop
    sql_stmt:=' create table '||r_table_creation.table_nm 
            ||'(' ||r_table_creation.col1 || '   varchar2( '||i||'),'
                  ||r_table_creation.col2 || '   varchar2( '||i||'),'
                  ||r_table_creation.col3 || '   varchar2( '||i||'),'
                  ||r_table_creation.col4 || '   varchar2( '||i||'),'
                  ||r_table_creation.col5 || '   varchar2( '||i||'),'
                  ||r_table_creation.col6 || '   varchar2( '||i||'),'
                  
                  ||r_table_creation.col7 || '   varchar2( '||i||') )'
            ;  
    

  dbms_output.put_line(sql_stmt); 
Execute immediate 'sql_stmt';

 -- dbms_output.put_line('Table  '||r_table_creation.table_nm || 'is created '); 
end loop;
end;
/
show errors
/
set serveroutput on
/
exec table_proc;

Error file is attached in this thread.
Thanks in advance
Re: Problem in Execute Immediate [message #420247 is a reply to message #420246] Thu, 27 August 2009 14:51 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Error file is attached in this thread.
I don't think so.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Problem in Execute Immediate [message #420248 is a reply to message #420246] Thu, 27 August 2009 14:57 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
sorry ...file is attached
  • Attachment: err.JPG
    (Size: 64.16KB, Downloaded 110 times)
Re: Problem in Execute Immediate [message #420249 is a reply to message #420248] Thu, 27 August 2009 15:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Too bad CUT & PASTE are broken on your system.
I suggest you upgrade immediately.

>i varchar2(4):=43;
strings should be enclosed by single quote marks

>select * from Table_info where CREATED_FLG='N';
It is very poor programming to use "*" in SELECT statements.

It is bad programming to use keywords as column names.

It is very poor programming to make DB objects via EXECUTE IMMEDIATE!

When attempting dynamic SQL it is good practice to test SQL statement in sqlplus before EXECUTE IMMEDIATE.

[Updated on: Thu, 27 August 2009 15:05]

Report message to a moderator

Re: Problem in Execute Immediate [message #420250 is a reply to message #420249] Thu, 27 August 2009 15:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Plus, you can't use reserved words like "date" for column names.
Re: Problem in Execute Immediate [message #420281 is a reply to message #420250] Fri, 28 August 2009 02:07 Go to previous message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

plus
Execute immediate 'sql_stmt';

single quote is not required here .

Regards
Prajakta
Previous Topic: year comparison - pls help (merged)
Next Topic: Send Email With Attachment
Goto Forum:
  


Current Time: Fri Dec 02 20:41:54 CST 2016

Total time taken to generate the page: 0.13395 seconds