Home » SQL & PL/SQL » SQL & PL/SQL » ' or " or ''' or ''''' ?? who creates this joke ?!
' or " or ''' or ''''' ?? who creates this joke ?! [message #8632] Tue, 16 September 2003 03:34 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: hwo to count no of rows from two tables
Next Topic: DECODE
Goto Forum:
  


Current Time: Thu Mar 28 06:44:16 CDT 2024