Warning while creating procedure [message #349520] |
Mon, 22 September 2008 02:30  |
naikjigar
Messages: 51 Registered: July 2008 Location: India
|
Member |

|
|
Hi,
Bellow is my procedure script, which takes on input parameter of type number.
SQL> CREATE OR REPLACE PROCEDURE PURGE_SMSCONNECT
2 (P_MONTHS NUMBER(3));
3 AS
4 BEGIN
5
6 DELETE FROM SMS_OUTBOUND WHERE ENTRY_TIME < ADD_MONTHS(ENTRY_TIME, P_MONTHS);
7 DELETE FROM REPORTINCOMINGSMS WHERE RECEIVETIME < ADD_MONTHS(RECEIVETIME, P_MONTHS);
8 DELETE FROM REPORTSMSRESPONSE WHERE SENTTIME < ADD_MONTHS(SENT, P_MONTHS);
9
10 END PURGE_SMSCONNECT;
11
12 /
and the warning i am receiving is
Warning: Procedure created with compilation errors.
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/17 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
:= . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.
3/1 PLS-00103: Encountered the symbol "AS"
SQL>
|
|
|
|
|
Re: Warning while creating procedure [message #349534 is a reply to message #349520] |
Mon, 22 September 2008 02:46   |
naikjigar
Messages: 51 Registered: July 2008 Location: India
|
Member |

|
|
Hey thanks,
i removed the length for the variable but then i got the long list of errors.
SQL> CREATE OR REPLACE PROCEDURE PURGE_SMSCONNECT
2 (
3 P_MONTHS NUMBER
4 )
5 AS
6 BEGIN
7 DELETE FROM SMS_OUTBOUND WHERE ENTRY_TIME < ADD_MONTHS(ENTRY_TIME, P_MONTHS);
8 DELETE FROM REPORTINCOMINGSMS WHERE RECEIVETIME < ADD_MONTHS(RECEIVETIME, P_MONTHS);
9 DELETE FROM REPORTSMSRESPONSE WHERE SENTTIME < ADD_MONTHS(SENTTIME, P_MONTHS);
10 END PURGE_SMSCONNECT;
11 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERROR;
Errors for PROCEDURE PURGE_SMSCONNECT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3 PL/SQL: SQL Statement ignored
7/15 PL/SQL: ORA-00942: table or view does not exist
8/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
8/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
8/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
9/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
9/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
9/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
SQL>
|
|
|
Re: Warning while creating procedure [message #349536 is a reply to message #349534] |
Mon, 22 September 2008 02:50   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
ORA-00942: table or view does not exist
Check if table exists, if it is accessible without schema name, if procedure's owner has direct access on it.
In short, execute:
set role none
desc <table>
PLW-07202: bind type would result in conversion away from column type
CAUSE: The column type and the bind type do not exactly match. This
will result in the column being converted to the type of the bind
variable. This type conversion may prevent the SQL optimizer
from using any index the column participates in. This may
adversely affect the execution performance of this statement.
ACTION: To make use of any index for this column, make sure the bind
type is the same type as the column type.
Regards
Michel
[Updated on: Mon, 22 September 2008 02:50] Report message to a moderator
|
|
|
Re: Warning while creating procedure [message #349541 is a reply to message #349520] |
Mon, 22 September 2008 02:56   |
naikjigar
Messages: 51 Registered: July 2008 Location: India
|
Member |

|
|
ok now i am getting only one error of the same type
Message 7202 not found; no message file or product=plsql
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE PROCEDURE PURGE_SMSCONNECT
2 (
3 P_MONTHS NUMBER
4 )
5 AS
6 BEGIN
7 DELETE FROM outbound_sms WHERE entrytime < ADD_MONTHS(entrytime, P_MONTHS);
8 DELETE FROM REPORTINCOMINGSMS WHERE RECEIVETIME < ADD_MONTHS(RECEIVETIME, P_MONTHS)
9 DELETE FROM REPORTSMSRESPONSE WHERE SENTTIME < ADD_MONTHS(SENTTIME, P_MONTHS);
10* END PURGE_SMSCONNECT;
SQL> /
SP2-0804: Procedure created with compilation warnings
SQL> show error;
Errors for PROCEDURE PURGE_SMSCONNECT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
7/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
7/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
7/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
8/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
8/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
8/3 PLW-07202: Message 7202 not found; No message file for
LINE/COL ERROR
-------- -----------------------------------------------------------------
product=plsql, facility=PLW
9/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
9/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
9/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/3 PLW-07202: Message 7202 not found; No message file for
product=plsql, facility=PLW
SQL>
|
|
|
|
|