[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-22
8
Medium Priority
?
543 Views
Last Modified: 2013-12-07
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.
0
Comment
Question by:cmleung2
8 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35450228
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 35450279
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
 

Expert Comment

by:jm76430
ID: 35450384
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:ajexpert
ID: 35450403
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35450660
>>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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35451052
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
 
LVL 9

Expert Comment

by:anillucky31
ID: 35736548
You can try like this

update yourtable

set yourtable.Name = 'somevalue'
where yourtable.Name = 'something'
0
 
LVL 1

Author Closing Comment

by:cmleung2
ID: 35754298
No I wasn't trying to do the dynamic sql, but I guess I have to now, and it works.  thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month20 days, 1 hour left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question