' or " or ''' or ''''' ?? who creates this joke ?! [message #8632] |
Tue, 16 September 2003 03:34 |
Gerald
Messages: 54 Registered: January 2001
|
Member |
|
|
Dears,
i'm trying to update one of my tables with some IF THEN ect ... but I got some errors.
So i just try to do a simple update with dbms_sql:
Declare
str varchar2(500);
cur integer;
status integer;
Begin
for i in (Select * from ANALYSE_ADM)
Loop
str:='UPDATE ANALYSE_ADM set '||i.ANALYSE||'=''OK2''' ;
cur:=dbms_sql.open_cursor;
dbms_sql.parse(cur,str,dbms_sql.native);
status:=dbms_sql.execute(cur);
dbms_sql.close_cursor (cur);
end loop;
end;
/
it says : ORA-00904: invalid column name
i know that it comes from the ' or '' because the column is a varchar2 and if i do inline:
UPDATE ANALYSE_ADM set analyse='ok';
it works !!
but if i do inline:
UPDATE ANALYSE_ADM set analyse="ok";
it says : ORA-00904: invalid column name
So how the hell must i write the string str ??
with one ' ? tow ' ? ... ten ' ?
if some one can help , thanx !
gerald
|
|
|
Re: ' or " or ''' or ''''' ?? who creates this joke ?! [message #8636 is a reply to message #8632] |
Tue, 16 September 2003 09:09 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
Gerald,
Your quote syntax looks perfect to me. On the other hand, I am profoundly confused by your intent for this code snippet.
What are you trying to do here?
Does analyse_adm.analyse contain the name of another column in analyse_adm? If not, this code as written will fail. Your issue is that one of your values of i.analyse is not itself a column name in analyse_adm.
If I understand your intent correctly, Gerald, why can't you just forgo the dynamic SQL altogether, and instead say UPDATE analyse_adm SET analyse = 'OK2';?
A.
|
|
|
Re: ' or " or ''' or ''''' ?? who creates this joke ?! [message #8642 is a reply to message #8636] |
Tue, 16 September 2003 10:57 |
Gerald
Messages: 54 Registered: January 2001
|
Member |
|
|
Thank you art,
Well the aim of the program is:
to analayse this table:
Analyse_adm
------------------------
Objet Value Analyse
item#1 3 toto
item#2 3 toto
item#3 1 tata
i will do some IF/THEN/ELSE
If analyse=toto then
if value=3 then "update analyse with the value : GOOD"
else "update analyse with the value : BAD"
If analyse=tata then
if value=1 then "update analyse with the value : GOOD"
else "update analyse with the value : BAD"
do you understand ?
I've checked something
If i change the type of ANALYSE in NUMBER and i change all the values with numbers, and then i try to update not a string but a number in order to overcome the quote trouble ... the result is the same ...
ORA-00904: invalid column name
So you may right ... the i.analyse or do not retrieve the value i expect ... may be i need some WHERE clause ...
tell me
Gerald
|
|
|
Re: ' or " or ''' or ''''' ?? who creates this joke ?! [message #8643 is a reply to message #8642] |
Tue, 16 September 2003 12:26 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
UPDATE analyse_adm SET analyse = 'GOOD' WHERE analyse = 'toto' AND VALUE = 3;
UPDATE analyse_adm SET analyse = 'BAD' WHERE analyse = 'toto' AND VALUE != 3;
UPDATE analyse_adm SET analyse = 'GOOD' WHERE analyse = 'tata' AND VALUE = 1;
UPDATE analyse_adm SET analyse = 'BAD' WHERE analyse = 'tata' AND VALUE != 1;
COMMIT; A.
|
|
|
Re: it works ! [message #8650 is a reply to message #8643] |
Wed, 17 September 2003 03:23 |
Gerald
Messages: 54 Registered: January 2001
|
Member |
|
|
Yes !
Thank you art for this good help ...
Guess, my IF/THEN is the WHERE clause in the UPDATE statement.
I will try to add the WHERE in the dbms_sql clause.
it might works :)
Thank you again!
Gerald
|
|
|