Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2209
  • Last Modified:

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

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
mahee999
Asked:
mahee999
  • 2
1 Solution
 
sathyagiriCommented:
Are you using EXECUTE immediate to do this?

Also post the cursor loop with in which you're doing this insert
0
 
GGuzdziolCommented:
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
 
GGuzdziolCommented:
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
 
MohanKNairCommented:
"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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now