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

Posted on 2011-04-22
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.
Question by:cmleung2
    LVL 76

    Expert Comment

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

    Expert Comment

    I am not sure, but do you have table called NAME also you have column called NAME?

    Can you check by running this query?


    Expert Comment

    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;
    LVL 14

    Expert Comment

    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

    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    >>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.
    LVL 73

    Accepted Solution

    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)
        execute immediate 'update my_table set ' || my_column || ' = :some_value' using my_value;
    LVL 9

    Expert Comment

    You can try like this

    update yourtable

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

    Author Closing Comment

    No I wasn't trying to do the dynamic sql, but I guess I have to now, and it works.  thanks.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now