asked on

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;

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Daniel_PL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

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

Daniel_PL

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.

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial