Home » SQL & PL/SQL » SQL & PL/SQL » simple problem of creating a table inside execute immediate, but can not debug the error (Oracle 10g, Win7)
simple problem of creating a table inside execute immediate, but can not debug the error [message #594318] Tue, 27 August 2013 06:59 Go to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
I am trying to create a table using the following code but getting error, I do not know why,
please can anyone suggest me.
DECLARE

  TEMP VARCHAR2(10) := 'TEMP_';
  I NUMBER :=1; 
  BEGIN
  EXECUTE IMMEDIATE '
  CREATE TABLE DYNAMIC_TBL
  (
     ''TEMP''||I VARCHAR2(50)
  );
  ';
  END; 
/


the error is:
Quote:
ORA-00904: : invalid identifier
ORA-06512: at line 6


Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594319 is a reply to message #594318] Tue, 27 August 2013 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 10903
Registered: September 2008
Location: Rainy Manchester
Senior Member
Debugging dynamic SQL 101:
Assign the dynamic string to a variable.
Output the variable using dbms_output or some equivalent.
Check the displayed string is actually valid sql.
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594320 is a reply to message #594318] Tue, 27 August 2013 07:15 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
Instead of using execute immediate straight away, whenever you are developing code tht is going to use it, user dbms_output.put_line instead, that way, you can check the strings that you have built are correct.

DECLARE

  TEMP VARCHAR2(10) := 'TEMP_';
  I NUMBER :=1; 
  BEGIN
  dbms_output.put_line('
  CREATE TABLE DYNAMIC_TBL
  (
     ''TEMP''||I VARCHAR2(50)
  );
  ');
  END; 

HOWEVER
it is very rarely a good idea to do what you are doing, i.e. buildin a table on the fly. A need to do this, usually means one or two things i.e. a flawed table design or a misunderstanding of the requirements / techniques available to meet the requirements.
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594350 is a reply to message #594320] Tue, 27 August 2013 14:54 Go to previous messageGo to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

I am not aware about the reasons why you guys always say that creating tables on the fly is wrong design.

Can you please refer me a link, what are the consequences if I am creating a table on the fly.

Thanks,
Manu
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594351 is a reply to message #594350] Tue, 27 August 2013 15:06 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
how do you write & test SQL which will access any table built by dynamic SQL before the table actually exists?

What prevent you from creating all tables needed by the application at application installation time from static SQL statements?

Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594352 is a reply to message #594350] Tue, 27 August 2013 15:33 Go to previous messageGo to next message
joy_division
Messages: 4503
Registered: February 2005
Location: East Coast USA
Senior Member
manubatham20 wrote on Tue, 27 August 2013 15:54
I am not aware about the reasons why you guys always say that creating tables on the fly is wrong design.


A bad analogy I just made up:

When I want to go to the store to buy groceries, I use my car. I can use it over and over again.

When you want to go to the store to buy groceries, you purchase a car, go buy your groceries and then sell the car when you are done. Next time you want to buy groceries, you go buy a car, get your groceries and then sell the car.
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594353 is a reply to message #594351] Tue, 27 August 2013 15:41 Go to previous messageGo to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

BlackSwan wrote on Tue, 27 August 2013 20:06
how do you write & test SQL which will access any table built by dynamic SQL before the table actually exists?


Why to write a dynamic sql on the table which actually doesn't exist.
In UI, we provide option for creating intermediate table, and write query on top of them, and will use DBMS_SQL.PARSE verifying DML.
We are database guys, we can code and create table in backend, everyone don't work on database, so we need to relate database with some UI, which will be used by users.


Quote:

What prevent you from creating all tables needed by the application at application installation time from static SQL statements?


What if you don't know the structure of data which will be coming in future. (Don't say me store data in un-pivoted form)

Regards,
Manu

[Updated on: Tue, 27 August 2013 15:42]

Report message to a moderator

Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594354 is a reply to message #594352] Tue, 27 August 2013 16:05 Go to previous messageGo to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

joy_division wrote on Tue, 27 August 2013 20:33
manubatham20 wrote on Tue, 27 August 2013 15:54
I am not aware about the reasons why you guys always say that creating tables on the fly is wrong design.


When you want to go to the store to buy groceries, you purchase a car, go buy your groceries and then sell the car when you are done. Next time you want to buy groceries, you go buy a car, get your groceries and then sell the car.


C'mon rent a car instead. Wink Razz

Seriously bad analogy.

Manu Very Happy
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594359 is a reply to message #594354] Tue, 27 August 2013 17:29 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
Seriously, it is a Bad Thing to CREATE TABLE (ab)using EXECUTE IMMEDIATE & something that you would be wise to avoid doing.
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594449 is a reply to message #594354] Wed, 28 August 2013 09:52 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
You have been advised. You can choose to ignore that advice. I personally will avoid any questions from you that involve the unnecessary creation of objects on the fly, I imagine others will too if you choose to ignore their advice.
If you won't take our advice, maybe a thread from AskTom
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594450 is a reply to message #594449] Wed, 28 August 2013 09:56 Go to previous messageGo to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

I am not ignoring your advise, I know whatever you are saying is 100% fact.

I have read it at many places even, may be I am arguing because I never faced any real-life example.

Regards,
Manu
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594451 is a reply to message #594450] Wed, 28 August 2013 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
I personally do not have to drown in a quick sand pit in order to know that they should ALWAYS be avoided.
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594452 is a reply to message #594451] Wed, 28 August 2013 10:12 Go to previous messageGo to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

Hahaha, thanks. Very Happy

I will remember this line...
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594454 is a reply to message #594452] Wed, 28 August 2013 10:20 Go to previous messageGo to next message
gazzag
Messages: 298
Registered: November 2010
Location: Bristol, UK
Senior Member
Additionally, a DDL statement (CREATE TABLE, for example) implies a COMMIT too so you lose atomicity.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7072180788422
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594456 is a reply to message #594454] Wed, 28 August 2013 10:40 Go to previous messageGo to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

I don't want to talk about this more... but to your reply gazzag, what about a autonomous transaction to execute dynamic ddls.
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594459 is a reply to message #594456] Wed, 28 August 2013 10:48 Go to previous messageGo to next message
gazzag
Messages: 298
Registered: November 2010
Location: Bristol, UK
Senior Member
You're right. Let's not talk about this anymore. I'm no fan of reinventing the wheel badly.
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594731 is a reply to message #594459] Mon, 02 September 2013 05:02 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
hello all:

Thanks for the reply and a healthy discussion on this topic.

I tried to create the table using EXECUTE IMMEDIATE but getting error, please can anyone
suggest me what has happen with this??

DECLARE

  TEMP VARCHAR2(10) := 'TEMP_';
  I NUMBER :=1; 
  vquery VARCHAR2(1000);

  BEGIN  

    vquery:= '
                CREATE TABLE DYNAMIC_TBL
                (
                ''TEMP''||I VARCHAR2(50)
                );
            ';
   EXECUTE IMMEDIATE vquery;

  END;


And the error is:

ORA-00904: : invalid identifier

Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594733 is a reply to message #594731] Mon, 02 September 2013 05:05 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
nischalinn wrote on Mon, 02 September 2013 12:02
hello all:

Thanks for the reply and a healthy discussion on this topic.


Too bad you didn't read it.

You were already told to FIRST display the statement and make sure it is correct. Does this output look correctly to you?
SQL> declare
  2
  3    temp varchar2(10) := 'TEMP_';
  4    i number :=1;
  5    vquery varchar2(1000);
  6
  7    begin
  8
  9      vquery:= '
 10                  CREATE TABLE DYNAMIC_TBL
 11                  (
 12                  ''TEMP''||I VARCHAR2(50)
 13                  );
 14              ';
 15     dbms_output.put_line(vquery);
 16     -- EXECUTE IMMEDIATE vquery;
 17
 18    end;
 19  /

                CREATE TABLE DYNAMIC_TBL
                (
                'TEMP'||I VARCHAR2(50)

);


PL/SQL procedure successfully completed.

SQL>
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594734 is a reply to message #594731] Mon, 02 September 2013 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should better use SQL*Plus to show us:
SQL> DECLARE
  2
  3    TEMP VARCHAR2(10) := 'TEMP_';
  4    I NUMBER :=1;
  5    vquery VARCHAR2(1000);
  6
  7    BEGIN
  8
  9      vquery:= '
 10                  CREATE TABLE DYNAMIC_TBL
 11                  (
 12                  ''TEMP''||I VARCHAR2(50)
 13                  );
 14              ';
 15     EXECUTE IMMEDIATE vquery;
 16
 17    END;
 18  /
DECLARE
*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at line 15

And why don't you do what has been suggested:
SQL> DECLARE
  2
  3    TEMP VARCHAR2(10) := 'TEMP_';
  4    I NUMBER :=1;
  5    vquery VARCHAR2(1000);
  6
  7    BEGIN
  8
  9      vquery:= '
 10                  CREATE TABLE DYNAMIC_TBL
 11                  (
 12                  ''TEMP''||I VARCHAR2(50)
 13                  );
 14              ';
 15    dbms_output.put_line(vquery);
 16
 17    END;
 18  /

                CREATE TABLE DYNAMIC_TBL
                (
                'TEMP'||I VARCHAR2(50)
                );



PL/SQL procedure successfully completed.

Do you see now why it is wrong?

Regards
Michel
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594736 is a reply to message #594733] Mon, 02 September 2013 05:14 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
@ Littlefoot

so how could I create a table via putting variable as column name while creating a table?
can you please explain me.

thank you!!!
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594737 is a reply to message #594736] Mon, 02 September 2013 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fix your code to build a correct CREATE TABLE string.

Regards
Michel
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594738 is a reply to message #594736] Mon, 02 September 2013 05:16 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
Can you explain why you continue to follow what is generally a very poor practice, even after being advised that it is poor practice?
Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594744 is a reply to message #594738] Mon, 02 September 2013 05:55 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
This query gives me correct result.

DECLARE

  TEMP VARCHAR2(10) := 'TEMP_';
  I NUMBER :=1; 
  vquery VARCHAR2(1000);

  BEGIN  

    vquery:= '
                CREATE TABLE DYNAMIC_TBL
                (
                 '||TEMP||''||I||' VARCHAR2(50)
                );
            ';
   dbms_output.put_line(vquery);
   EXECUTE IMMEDIATE vquery; 
  END;
/ 

but when I dump this create table statement in a vairable and do EXECUTE IMMEDIATE I get the error, why??

the o/p is:

CREATE TABLE DYNAMIC_TBL
(
    TEMP_1 VARCHAR2(50)
);
            
ORA-00911: invalid character
ORA-06512: at line 16

Re: simple problem of creating a table inside execute immediate, but can not debug the error [message #594747 is a reply to message #594744] Mon, 02 September 2013 05:59 Go to previous message
gazzag
Messages: 298
Registered: November 2010
Location: Bristol, UK
Senior Member
You don't need the semi-colon.

 vquery:= '
                CREATE TABLE DYNAMIC_TBL
                (
                 '||TEMP||''||I||' VARCHAR2(50)
                );
            ';


Should read:

 vquery:= '
                CREATE TABLE DYNAMIC_TBL
                (
                 '||TEMP||''||I||' VARCHAR2(50)
                )
            ';
Previous Topic: get numbers which is not in the
Next Topic: Advice for procedure
Goto Forum:
  


Current Time: Thu Aug 21 01:44:14 CDT 2014

Total time taken to generate the page: 0.06925 seconds