Home » SQL & PL/SQL » SQL & PL/SQL » Warning while creating procedure (Oracle 10g)
Warning while creating procedure [message #349520] Mon, 22 September 2008 02:30 Go to next message
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 #349524 is a reply to message #349520] Mon, 22 September 2008 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Procedure arameter types are unconstrained. Remove "(3)".

Regards
Michel
Re: Warning while creating procedure [message #349526 is a reply to message #349520] Mon, 22 September 2008 02:37 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


This could be used for your reference .

Procedure

Thumbs Up
Rajuvan.
Re: Warning while creating procedure [message #349534 is a reply to message #349520] Mon, 22 September 2008 02:46 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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> 
Re: Warning while creating procedure [message #349542 is a reply to message #349541] Mon, 22 September 2008 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ADD_MONTHS takes an INTEGER as parameter not a NUMBER. Change this.

Regards
Michel
Re: Warning while creating procedure [message #349554 is a reply to message #349542] Mon, 22 September 2008 04:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
See the Documentation here about the Pl/sql compiler warnings, what they do, and how to disable them.
Previous Topic: BULK COLLECT INTO with limit?
Next Topic: varchar to value
Goto Forum:
  


Current Time: Tue Dec 06 11:53:36 CST 2016

Total time taken to generate the page: 0.10900 seconds