Solved

PL/SQL: ORA-00984: column not allowed here

Posted on 2006-07-19
4
1,976 Views
Last Modified: 2009-06-11
Hi,

    i have an insert statement in my procedure  and i m using the cursor value in it ..

eg:

insert into sample(sid,sname) values(cursor1.sid,cursor1.sname);

and when i try to execute the procedure it gives the error

 PL/SQL: ORA-00984: column not allowed here

so what is the cause for this error.i CANNOT POST THE PROC AS IT TOO BIG. Can anyone help me with this.

Thanks
0
Comment
Question by:mahee999
  • 2
4 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17138598
Are you using EXECUTE immediate to do this?

Also post the cursor loop with in which you're doing this insert
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17138612
Probably You want to insert values from record/variable, not cursor itself!

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor cr is select * from tab_test;
  3  begin
  4    for rec in cr loop
  5      insert into tab_test(desc_id) values (cr.desc_id);
  6    end loop;
  7* end;
SQL> /
    insert into tab_test(desc_id) values (cr.desc_id);
                                          *
ERROR at line 5:
ORA-06550: line 5, column 43:
PLS-00225: subprogram or cursor 'CR' reference is out of scope
ORA-06550: line 5, column 46:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 5, column 5:
PL/SQL: SQL Statement ignored



but

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor cr is select * from tab_test;
  3  begin
  4    for rec in cr loop
  5      insert into tab_test(desc_id) values (rec.desc_id);
  6    end loop;
  7* end;
SQL> /

PL/SQL procedure successfully completed.
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17138629
or if You use variables instead of records it would be

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor cr is select * from tab_test;
  3    desc_id varchar2(30);
  4  begin
  5    open cr;
  6    loop
  7      fetch cr into desc_id;
  8      exit when cr%notfound;
  9      insert into tab_test(desc_id) values (desc_id);
 10    end loop;
 11   close cr;
 12* end;
SQL> /

PL/SQL procedure successfully completed.
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 250 total points
ID: 17139470
"column not allowed here" error comes when the values of insert statement has any column name of the table. insert into sample(sid,sname) values(sid,name); --- this will give error

Post the PL/SQL or SQL code
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

867 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

22 Experts available now in Live!

Get 1:1 Help Now