Take the 2-minute tour ×

I have written a PL-SQL block

    SchemaName  VARCHAR2(50) :='REQ_SUNIL_5750';
    userpassword VARCHAR2(50) :='XYZ';  
    stmt VARCHAR2(5000);
   stmt :='INSERT INTO ' || SchemaName || '.USER_CREDS VALUES ('|| SchemaName ||', '|| userpassword ||' )';

When I execute above block I am getting below, ORA-00984: column not allowed here

I have created table with name 'REQ_SUNIL_5750.USER_CREDS and it has username and password columns Please help

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

You have to quote your string values properly:

stmt :='INSERT INTO ' || SchemaName || 
  '.USER_CREDS VALUES ('''|| SchemaName ||''', '''|| userpassword ||''' )';
share|improve this answer
Perfect!! It worked. Thanks –  Sunil Chavan Jul 8 '11 at 10:32
add comment

Frank's answer is great, I would add one point though.

From the perspective of performance and reuseability, your execute immediate statement should use bind variables and the insert syntax should specify the columns that correspond to the values being entered.

share|improve this answer
Appritiate your comment, helps me thanks –  Sunil Chavan Jul 12 '11 at 9:16
add comment

Your Answer


