Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Oracle Stored Proc - PLS-00306: wrong number or ...
Problem with Oracle Stored Proc - PLS-00306: wrong number or ... [message #200153] Sat, 28 October 2006 15:49 Go to next message
KTZ
Messages: 15
Registered: January 2005
Junior Member
I am trying o run a simple procedure (TOAD) but get teh following errors:

ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'TEST__2

sOffice should contain a string something like XXXXX-XXX.

Any help would be appreciated. Thanks!
Code:
CREATE OR REPLACE PROCEDURE TEST__2 (sMessage out CHAR) 
IS


sOffice varchar2(17);
lOffice number;
sMsg varchar2(100);
bOKToContinue varchar2(1);
BEGIN	 
	select NVL(OFFICE, 'notfound') 
		 into sOffice
		 from dna.eds_view 
		 where (status='N' or status='Y')
		 AND MASTER_KEY = 33898;

	if sOffice = 'notfound' then
		bOKToContinue := 'N';
		sMsg := 'No office symbol provided, or no match for office symbol or office symbol is inactive.\n';
	else
		SELECT NVL(lngOfficeID, 0)
		   INTO lOffice
		   FROM EDS.tblOffice   
		   WHERE txtOfficeCode = sOffice
		   AND blnInactive = 'N';
		sMsg := 'Office is ' || sOffice;
	END IF; 
	sMessage := sMsg;
END TEST__2;
Re: Problem with Oracle Stored Proc - PLS-00306: wrong number or ... [message #200155 is a reply to message #200153] Sat, 28 October 2006 15:59 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you also post the WHOLE code you use to call this procedure? Perhaps everything is fine in procedure 'test_2', but you failed to call it correctly (you do have to specify OUT parameter when calling the procedure!).
Re: Problem with Oracle Stored Proc - PLS-00306: wrong number or ... [message #200157 is a reply to message #200153] Sat, 28 October 2006 16:12 Go to previous messageGo to next message
KTZ
Messages: 15
Registered: January 2005
Junior Member
This will not run in TOAD with the execute statement (EXECUTE TEST__2) called as a script. Generates the errors I posted below with the original message.

Just tried running from ASP with this code:
<%
set objConn = server.CreateObject("ADODB.connection")
objConn.Open session("TRN")

set objCmd= Server.CreateObject("ADODB.Command")
with objCmd
	dim objParm1, objParm2
	.ActiveConnection = objConn
	.CommandType = adCmdStoredProc
	.CommandText = "EDS.TEST__2"
	Set objParm2 = objCmd.CreateParameter("Out1", adVarChar, adParamOutput, 200)
	objCmd.Parameters.Append objParm2
	.Execute
	sMsg=objCmd("Out1")
end with
									
set objCmd = nothing

Response.Write sMsg%>


That generates this ASP error:
Microsoft OLE DB Provider for Oracle error '80040e21'

ORA-01403: no data found ORA-06512: at "EDS.TEST__2", line 20 ORA-06512: at line 1

However I know this record has a valid office.
Re: Problem with Oracle Stored Proc - PLS-00306: wrong number or ... [message #200159 is a reply to message #200157] Sat, 28 October 2006 16:22 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
NO-DATA-FOUND in line 20 suggests that this query returns nothing:
      SELECT NVL (lngofficeid, 0)
        INTO loffice
        FROM eds.tbloffice
       WHERE txtofficecode = soffice AND blninactive = 'N';
To avoid it, either write an exception handler (it *should* be done, but - just to test it - fix this error like this (add the MAX function which will prevent ORA-01503):
      SELECT MAX(NVL (lngofficeid, 0))
        INTO loffice
        FROM eds.tbloffice
       WHERE txtofficecode = soffice AND blninactive = 'N';

Also, the first query will return NO-DATA-FOUND if this WHERE clause isn't OK - fix it the same way as I've suggested previously:
where (status='N' or status='Y')
		 AND MASTER_KEY = 33898;
Re: Problem with Oracle Stored Proc - PLS-00306: wrong number or ... [message #200160 is a reply to message #200153] Sat, 28 October 2006 16:32 Go to previous messageGo to next message
KTZ
Messages: 15
Registered: January 2005
Junior Member
OK I added the max function to the SELECT MAX(NVL(lngOfficeID, 0))INTO lOffice... Same error.

I tried to add Max functions to Where clause in first SQL, but got error - 'ORA-00934 group function not allowed here'. However I do know this is a valid record with a valid office. This returns a row with a valid office.
SELECT NVL(OFFICE, 'notfound') 
from dna.eds_view 
where (status='N' or status='Y')
AND MASTER_KEY = 33898;



However I just refreshed my page and it runs in ASP. Go figure?? The response.write returns 'Office is ....'

Thanks! Any idea why TOAD doesn't like it.

[Updated on: Sat, 28 October 2006 16:39]

Report message to a moderator

Re: Problem with Oracle Stored Proc - PLS-00306: wrong number or ... [message #200162 is a reply to message #200153] Sat, 28 October 2006 16:42 Go to previous messageGo to next message
KTZ
Messages: 15
Registered: January 2005
Junior Member
Could you also tell me what the Max function is doing for me in this instance?

Thanks.
Re: Problem with Oracle Stored Proc - PLS-00306: wrong number or ... [message #200163 is a reply to message #200160] Sat, 28 October 2006 16:42 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No, I don't know why TOAD doesn't like it; in certain situations it is a little bit ... strange (if that's the word). OK, never mind TOAD - more important is that your procedure works now.
Re: Problem with Oracle Stored Proc - PLS-00306: wrong number or ... [message #200164 is a reply to message #200163] Sat, 28 October 2006 16:47 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
MAX (actually, you could have used any other aggregate function as MIN, COUNT, ...) - its purpose is just to avoid the NO-DATA-FOUND error which would force a PL/SQL procedure to end unsuccessfully (i.e. it would raise an unhandled exception error). Here's an example:
SQL> select ename from emp where empno = 1234;

no rows selected    --> this is, actually, NO-DATA-FOUND

SQL> select max(ename) from emp where empno = 1234;

MAX(ENAME)
----------
                    --> this is a NULL value

SQL>

This "trick" is useful for testing purposes, but I'd recommend you to properly handle exceptions in procedures, such as
CREATE OR REPLACE PROCEDURE test AS
DECLARE
  ...
BEGIN
  ...
EXCEPTION
  WHEN NO-DATA-FOUND THEN
    ...
END;
Re: Problem with Oracle Stored Proc - PLS-00306: wrong number or ... [message #200310 is a reply to message #200153] Mon, 30 October 2006 05:47 Go to previous message
ajaybabu.yaleti
Messages: 11
Registered: October 2006
Junior Member
Hi,

try this

First declare the variable

var variablename varchar2(100)

exec procname (:variablename)

print variablename

Regards,
Ajay
Previous Topic: ORA-00600:internal......arguments:[6807].....
Next Topic: constraint type
Goto Forum:
  


Current Time: Wed Dec 07 18:54:15 CST 2016

Total time taken to generate the page: 0.25342 seconds