Solved

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

Posted on 2006-11-07
5
961 Views
Last Modified: 2012-05-05
DECLARE CURSOR cur_locmetreading IS
select item.itemnum as a, item.recond AS b, MATUSETRANS.STORELOC AS c, to_char(MATUSETRANS.transdate, 'MM') AS d,
sum(MATUSETRANS.QUANTITY) as f from item, matusetrans
where item.itemnum=matusetrans.itemnum
and matusetrans.transdate > to_date('20050901', 'YYYYMMDD')
and matusetrans.transdate < to_date('20051001','YYYYMMDD')
group by item.itemnum, item.recond, MATUSETRANS.STORELOC, to_char(MATUSETRANS.transdate, 'MM')
order by item.itemnum;


BEGIN

 DBMS_OUTPUT.PUT_LINE ('START!');

 FOR c_locmetreading IN cur_locmetreading LOOP


  BEGIN
  INSERT INTO INV_TEMP
  VALUES(cur_locmeterreading.a,
  cur_locmeterreading.b,
  cur_locmeterreading.c,
  cur_locmeterreading.d,
  cur_locmeterreading.f);
  commit;
 

  EXCEPTION
   WHEN too_many_rows THEN
   DBMS_OUTPUT.PUT_LINE ('Too many ');

   WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE ('Err: '|| SQLERRM);
  END;

 END LOOP;

 DBMS_OUTPUT.PUT_LINE ('END!');

EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE ('Err: '|| SQLERRM);

END;
/

SQL> desc inv_temp;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 STOCKCODE                                                      VARCHAR2(20 CHAR)
 RECOND                                                         NUMBER
 STOREROOM                                                      VARCHAR2(15 CHAR)
 MONTH                                                          VARCHAR2(3 CHAR)
 QUANTITY                                                       NUMBER

I am having ORA-00984: column not allowed here when i run the procedure against inv_temp. I even tried adding to_number cur_locmeterreading.b, cur_locmeterreading.f. And it fails. Anyone can help?? Thank in advance
0
Comment
Question by:baihao
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 7

Accepted Solution

by:
Stephen Lappin earned 50 total points
ID: 17891238
You are specifying the cursor object instead of the record objects: Try:

  INSERT INTO INV_TEMP (stockcode, recond, storeroom, month, quantity)
  VALUES(c_locmeterreading.a,
  c_locmeterreading.b,
  c_locmeterreading.c,
  c_locmeterreading.d,
  c_locmeterreading.f);
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 50 total points
ID: 17891812
cursor says locmetreading
values say locmeterreading
0
 
LVL 32

Expert Comment

by:awking00
ID: 17891834
Also, values should be c_locmetreading, not cur_locmetreading.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20296237
Forced accept.

Computer101
EE Admin
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.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

749 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