pl/sql column name is name, cannot use update statement

Is there a way to wrap the name of the column in pl/sql for Oracle 9i?  
I have this table that has a column name NAME.  The select statement is ok, but the update statement gives me invalid identifer error.  If I put single quote around it, it doesn't compare the condition where name = 'abc' correctly.  What can I do other than alter the name of the column and alter back?  Thanks.
LVL 1
cmleung2Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
are you trying to do dynamic sql?

 that is,  are you trying to "wrap" a column by not specifying the column name in a fixed way within a query?


create or replace procedure my_table_update(my_column in varchar2, my_value in varchar2)
is
begin
    execute immediate 'update my_table set ' || my_column || ' = :some_value' using my_value;
end;
0
 
slightwv (䄆 Netminder) Commented:
I'm not sure I understand the problem here but you can, I don't recommend it, use double quotes.  You need to capitilize the column name:

Select "NAME" from table.
0
 
ajexpertCommented:
I am not sure, but do you have table called NAME also you have column called NAME?

Can you check by running this query?

SELECT * FROM USER_OBJECTS
WHERE OBJECT_NAME = 'NAME'
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
jm76430Commented:
Try this:

create table temp (name varchar2(10));

insert into temp values ('test');

select * from temp;

update temp
   set "NAME" = 'it works'
 where "NAME" = 'test';
 
select * from temp;
0
 
ajexpertCommented:
Well, I do not see use of double quotes here (")

My try:
create table temp1 (name varchar2(10));

insert into temp1 values ('test');

select * from temp1;

update temp1
   set NAME = 'it works'
 where NAME = 'test';

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>Well, I do not see use of double quotes here (")

I don't either but the asker says they want them for some reason.

That said:  jm76430,

I believe double quotes has already been mentioned.
0
 
anillucky31Commented:
You can try like this

update yourtable

set yourtable.Name = 'somevalue'
where yourtable.Name = 'something'
0
 
cmleung2Author Commented:
No I wasn't trying to do the dynamic sql, but I guess I have to now, and it works.  thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.