PL/SQL: ORA-00942 Error [message #406658] |
Fri, 05 June 2009 04:14 |
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 #406717 is a reply to message #406658] |
Fri, 05 June 2009 08:38 |
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 |
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.
|
|
|