Home » SQL & PL/SQL » SQL & PL/SQL » dynamic sql statement
dynamic sql statement [message #443169] Sat, 13 February 2010 03:50 Go to next message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
i am using a very basic dynamic SQL statement to create a table but getting error. it is working when i use a column with datatype as varchar2(), but it easily works for a column type number. can anyone please help to identify wrong syntax.

declare
v_city varchar2(14) := 'goa' ;
sql_st varchar2(199);
begin
sql_st := 'create table test4 as select * from employees where city = ' || v_city ;
execute immediate sql_st;
end;

ORA-00904: "GOA": invalid identifier
ORA-06512: at line 6

regards
-navin
Re: dynamic sql statement [message #443171 is a reply to message #443169] Sat, 13 February 2010 03:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
well.

 
where col = 2


works for numbers, but

 
where col = GOA


doesn't work, because there are no quotes around the varchar, so for Oracle GOA is supposed to be a column name.

[Updated on: Sat, 13 February 2010 03:58]

Report message to a moderator

Re: dynamic sql statement [message #443174 is a reply to message #443169] Sat, 13 February 2010 04:08 Go to previous messageGo to next message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
Hi Thomas

so how can we use strings here, any modification to be used ?

regards
-navin
Re: dynamic sql statement [message #443175 is a reply to message #443174] Sat, 13 February 2010 04:21 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When you want to use a string in SQL you have to put quotes around the string.



Re: dynamic sql statement [message #443178 is a reply to message #443169] Sat, 13 February 2010 05:43 Go to previous messageGo to next message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
can you tell me where and how to use that, because column city is of type varchar2, same as variable v_city which i used here.
how it will work in this dynamic sql :: sql_st := 'create table test4 as select * from employees where city = ' || v_city ;

do i have to use some function or so before v_city to covert it into string ?

regards
-navin
Re: dynamic sql statement [message #443190 is a reply to message #443178] Sat, 13 February 2010 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You seem to no see the difference between:
create table test4 as select * from employees where city = v_city ;
and
sql_st := 'create table test4 as select * from employees where city = ' || v_city ;
execute v_sql;

In the second case when you execute sql_st, you NO MORE see v_city as you put its content inside the query text.
So this content should be a constant of the same type of the column. But without quote you indicate oracle not it is a constant but it is a column name.
So put quotes around the value of the variable v_city.

Regards
Michel

Re: dynamic sql statement [message #443194 is a reply to message #443169] Sat, 13 February 2010 09:19 Go to previous messageGo to next message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
do you mean to use it like
sql_st := 'create table test4 as select * from employees where city = ' || 'v_city' ;

this still gives same error, sorry i may be using wrong syntax.

It would be really helpful if you can put the correct syntax /code to be used here.

thanks in advance!!

regards
-navin
Re: dynamic sql statement [message #443196 is a reply to message #443194] Sat, 13 February 2010 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>sql_st := 'create table test4 as select * from employees where city = ' || 'v_city' ;
Not as above but as below

sql_st := 'create table test4 as select * from employees where city = ' || CHR(39) || v_city || CHR(39) ;

post what you understand to be the difference between yours & mine.
Re: dynamic sql statement [message #443197 is a reply to message #443194] Sat, 13 February 2010 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
do you mean to use it like
sql_st := 'create table test4 as select * from employees where city = ' || 'v_city' ;

Not around the variable name but about the value of this variable.
The generated string must not contain:
where city = goa
but
where city = 'goa'

Regards
Michel
Re: dynamic sql statement [message #443198 is a reply to message #443169] Sat, 13 February 2010 10:03 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Just curious nobody here suggested binding the parameter, as it is easier (no need for extra quotes) and avoiding unnecessary hard-parsing when called with different parameters.

It is described e.g. on http://www.oracle.com/oramag/oracle/01-mar/o21sql.html
Re: dynamic sql statement [message #443199 is a reply to message #443198] Sat, 13 February 2010 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is the next step.
Learn how to do it and then how to not do it. Smile

Regards
Michel
Re: dynamic sql statement [message #443200 is a reply to message #443198] Sat, 13 February 2010 10:22 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
First make work, then make it fancy.
Re: dynamic sql statement [message #443211 is a reply to message #443196] Sat, 13 February 2010 12:37 Go to previous messageGo to next message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
Hi Black Swan

i understood that we should use smthing to get city = 'goa' in dynamic sql but never saw/used the concept(m beginner Wink) you mentioned i.e.
city = ' || CHR(39) || v_city || CHR(39) ;

how does concatenating CHR(39) around v_city server this purpose ?

-regards
navin
Re: dynamic sql statement [message #443213 is a reply to message #443211] Sat, 13 February 2010 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>how does concatenating CHR(39) around v_city server this purpose ?

CHR(39) is the ASCII value for single quote mark

For convenience, let us give more compact tables in hex and decimal.

          2 3 4 5 6 7       30 40 50 60 70 80 90 100 110 120
        -------------      ---------------------------------
       0:   0 @ P ` p     0:    (  2  <  F  P  Z  d   n   x
       1: ! 1 A Q a q     1:    )  3  =  G  Q  [  e   o   y
       2: " 2 B R b r     2:    *  4  >  H  R  \  f   p   z
       3: # 3 C S c s     3: !  +  5  ?  I  S  ]  g   q   {
       4: $ 4 D T d t     4: "  ,  6  @  J  T  ^  h   r   |
       5: % 5 E U e u     5: #  -  7  A  K  U  _  i   s   }
       6: & 6 F V f v     6: $  .  8  B  L  V  `  j   t   ~
       7: ´ 7 G W g w     7: %  /  9  C  M  W  a  k   u  DEL
       8: ( 8 H X h x     8: &  0  :  D  N  X  b  l   v
       9: ) 9 I Y i y     9: ´  1  ;  E  O  Y  c  m   w
       A: * : J Z j z
       B: + ; K [ k {
       C: , < L \ l |
       D: - = M ] m }
       E: . > N ^ n ~
       F: / ? O _ o DEL

[Updated on: Sat, 13 February 2010 12:54]

Report message to a moderator

Re: dynamic sql statement [message #443345 is a reply to message #443169] Mon, 15 February 2010 04:00 Go to previous message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Instead of using chr(39). You could have put some extra quotes
as below
sql_st := 'create table test4 as select * from employees where city = '''|| v_city||'''' ;

Previous Topic: Recompile invalid object type
Next Topic: without using trunc how will you write this query excluding time
Goto Forum:
  


Current Time: Thu Dec 08 22:11:48 CST 2016

Total time taken to generate the page: 0.07755 seconds