CODEWITHSUNDEEP
plsqlinsert
Sunil Chavan
Sunil Chavan

Reputation: 3004

PLSQL error - ORA-00984: column not allowed here

I have written a PL-SQL block 

DECLARE
    SchemaName  VARCHAR2(50) :='REQ_SUNIL_5750';
    userpassword VARCHAR2(50) :='XYZ';  
    stmt VARCHAR2(5000);
BEGIN
   stmt :='INSERT INTO ' || SchemaName || '.USER_CREDS VALUES ('|| SchemaName ||', '|| userpassword ||' )';
   DBMS_OUTPUT.PUT_LINE(stmt) ;
   EXECUTE IMMEDIATE stmt;
   commit;
END;

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

Upvotes: 0

Views: 5976

Answers (2)

Ollie
Ollie

Reputation: 17538

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.

Upvotes: 0

Frank Schmitt
Frank Schmitt

Reputation: 30775

You have to quote your string values properly:

stmt :='INSERT INTO ' || SchemaName || 
  '.USER_CREDS VALUES ('''|| SchemaName ||''', '''|| userpassword ||''' )';

Upvotes: 4

Related Questions