Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: ORA-00942 Error
PL/SQL: ORA-00942 Error [message #406658] Fri, 05 June 2009 04:14 Go to next message
sowmya_100
Messages: 1
Registered: June 2009
Junior Member
Hi,
I got the Error as PL/SQL: ORA-00942
for the following Stored procedure.
LEt me know what is the actual problem wiht the below code
SQL> create or replace PROCEDURE MEDIUM_3
  2      IS
  3  temp varchar2(1000);
  4  temps varchar2(1000);
  5  o_month VARCHAR2(1000);
  6  Prevmonth VARCHAR2(1000);
  7  CURSOR C1 IS select to_char(sysdate -30,'MON-YY')  into Prevmonth from dual;
  8     BEGIN
  9  FOR I IN C1 LOOP
 10    IF(C1%FOUND) THEN
 11    o_month := Prevmonth;
 12   END IF;
 13   END LOOP;
 14  select count(*) into temp FROM Z_RAID_CONFIG_FSDISKFD_SYS_ID ;
 15   DBMS_OUTPUT.PUT_LINE(' count before  '||temp);
 16  Insert into Z_RAID_CONFIG_FSDISKFD_SYS_ID select sys_id from Z_RAID_CONFIG_FILESYSTEM_D018B where
 17  (evt_gmt_odate ="o_month") GROUP BY sys_id ;
 18  select count(*) into temps FROM Z_RAID_CONFIG_FSDISKFD_SYS_ID ;
 19    DBMS_OUTPUT.PUT_LINE(' count after '||temps);
 20  end MEDIUM_3;
 21  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE MEDIUM_3:

LINE/COL ERROR
-------- -----------------------------------------------------------------
16/1     PL/SQL: SQL Statement ignored
16/62    PL/SQL: ORA-00942: table or view does not exist
SQL>

It would of very great help it some one suggest the cause of this problem.

Regards,
Sowmya

[EDITED by LF: applied [code] tags]

[Updated on: Fri, 05 June 2009 04:56] by Moderator

Report message to a moderator

Re: PL/SQL: ORA-00942 Error [message #406661 is a reply to message #406658] Fri, 05 June 2009 04:23 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Try the statements outwith the procedure and tell us what happens.
Edit: Forgot to mention, please read the forum guide before you post any more questions. Pay particular attention to the section on formatting posts.
Cheers

[Updated on: Fri, 05 June 2009 04:40]

Report message to a moderator

Re: PL/SQL: ORA-00942 Error [message #406662 is a reply to message #406658] Fri, 05 June 2009 04:31 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Are you able to read the following?
Quote:
LINE/COL ERROR
-------- -----------------------------------------------------------------
16/1 PL/SQL: SQL Statement ignored
16/62 PL/SQL: ORA-00942: table or view does not exist
SQL>
Can you locate that row?
Quote:
16 Insert into Z_RAID_CONFIG_FSDISKFD_SYS_ID select sys_id from Z_RAID_CONFIG_FILESYSTEM_D018B where
Are the table Z_RAID_CONFIG_FSDISKFD_SYS_ID and Z_RAID_CONFIG_FILESYSTEM_D018B exists? If so, your schema has access to them?
Is the table Z_RAID_CONFIG_FSDISKFD_SYS_ID contains only one column?

By
Vamsi
Re: PL/SQL: ORA-00942 Error [message #406665 is a reply to message #406658] Fri, 05 June 2009 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ.
Please search before posting.
Search hint: "ORA-00942 in procedure"

Regards
Michel
Re: PL/SQL: ORA-00942 Error [message #406704 is a reply to message #406658] Fri, 05 June 2009 08:02 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Oracle uses single quotes, not double quotes.
Re: PL/SQL: ORA-00942 Error [message #406717 is a reply to message #406658] Fri, 05 June 2009 08:38 Go to previous messageGo to next message
cookiemonster
Messages: 13950
Registered: September 2008
Location: Rainy Manchester
Senior Member
What on earth is the point of this:
  7  CURSOR C1 IS select to_char(sysdate -30,'MON-YY')  into Prevmonth from dual;
  8     BEGIN
  9  FOR I IN C1 LOOP
 10    IF(C1%FOUND) THEN
 11    o_month := Prevmonth;
 12   END IF;
 13   END LOOP;


Just use
to_char(sysdate -30,'MON-YY')

In the where clause of your insert/select directly.

You don't need a loop.
You don't need to check if it's found. If you're in a for loop the cursor has found something by definition and if a select from dual doesn't find a record your database is completely broken.
But then you don't need a select for this at all.

And storing dates as varchars (which is what I assume you're doing) isn't particularly good design.
Re: PL/SQL: ORA-00942 Error [message #406724 is a reply to message #406658] Fri, 05 June 2009 08:55 Go to previous message
cookiemonster
Messages: 13950
Registered: September 2008
Location: Rainy Manchester
Senior Member
And reading that again I notice you've got an into in the cursor definition - that'll never work.

There's a lot of errors here. You need to read up on the basics.
Previous Topic: Retrieve row exclusively - performance
Next Topic: How to check entire table count before delete
Goto Forum:
  


Current Time: Sun Nov 03 01:15:46 CDT 2024