| dynamic sql statement [message #443169] |
Sat, 13 February 2010 03:50  |
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 #443178 is a reply to message #443169] |
Sat, 13 February 2010 05:43  |
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 #443194 is a reply to message #443169] |
Sat, 13 February 2010 09:19 |
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 #443213 is a reply to message #443211] |
Sat, 13 February 2010 12:41 |
 |
BlackSwan
Messages: 26766
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 |
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||'''' ;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
) you mentioned i.e.