column not allowed here error message

I am getting an ORA-00984: column not allowed here error message?
I just have a table named "SIMPLE" with one 30 character column named "SM" can someone tell me why I am getting the error message when I run the code below?

declare
lvQuery varchar2(100);
lala varachar2(10);
begin
lala:='Smile';

lvQuery := 'INSERT INTO SIMPLE(SM) VALUES  ('   || lala || ')' ;

END;
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please try like this -

INSERT INTO SIMPLE(SM) VALUES  ('   '|| 'Smile' || '')

You can check the tested output at - http://sqlfiddle.com/#!4/2f111/3
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel_PLDB Expert/ArchitectCommented:
Hi,

declare
lvQuery varchar2(100);
lala varchar2(10);
begin
lala:='Smile';
lvQuery := 'INSERT INTO SIMPLE(SM) VALUES  ('''   || lala || ''')';
END;

Open in new window


Your variable is a literal inside string, so you need to escape it with the apostrophes. Without that it is treated as a column name ;)
0
Pawan KumarDatabase ExpertCommented:
You need like this - Pls try..

declare
lvQuery varchar2(100);
lala varachar2(10);
begin
lala:='Smile';
lvQuery := 'INSERT INTO SIMPLE(SM) VALUES  (''''   || lala || '''')';
END;

Open in new window

0
Daniel_PLDB Expert/ArchitectCommented:
After mine you'll get

SYS@orcltest>
SYS@orcltest>
SYS@orcltest> declare
lvQuery varchar2(100);
lala varchar2(10);
begin
lala:='Smile';
lvQuery := 'INSERT INTO SIMPLE(SM) VALUES  ('''   || lala || ''')';
dbms_output.put_line(lvQuery);
END;
/  2    3    4    5    6    7    8    9
INSERT INTO SIMPLE(SM) VALUES  ('Smile')

PL/SQL procedure successfully completed.

Open in new window


After Pawan's you'll get an error because variable type is wrong, after fixing it you have following. This won't work because you will have an empty string concat lala (not exists inside) concat empty string -> (''   || lala || '')
SYS@orcltest>
SYS@orcltest>
SYS@orcltest>
SYS@orcltest> --Pawan's
declare
lvQuery varchar2(100);
lala varchar2(10);
begin
lala:='Smile';
lvQuery := 'INSERT INTO SIMPLE(SM) VALUES  (''''   || lala || '''')';
dbms_output.put_line(lvQuery);
END;
/SYS@orcltest>   2    3    4    5    6    7    8    9
INSERT INTO SIMPLE(SM) VALUES  (''   || lala || '')

PL/SQL procedure successfully completed.

Open in new window


I assume the exercise here is to build an executable dynamic sql.
0
sdstuberCommented:
while the above posts attempt to answer the question as asked..

it's important to note  building an insert statement like that with concatenation is a terrible sql usage.  - So you really shouldn't do anything shown above in production code.

instead, simply use the bind variable directly on a static statement

DECLARE
    lala   VARCHAR2(10);
BEGIN
    lala := 'Smile';

    INSERT INTO simple(sm)  VALUES lala;
END;

Open in new window


or, if you must use dynamic sql (your example does not have this need though)

Then bind your value, do not concatenate it


DECLARE
    lvquery   VARCHAR2(100);
    lala      VARCHAR2(10);
BEGIN
    lala := 'Smile';

    lvquery := 'INSERT INTO SIMPLE(SM) VALUES (:b1)';

    EXECUTE IMMEDIATE lvquery USING lala;
END;

Open in new window

2
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.