Home » SQL & PL/SQL » SQL & PL/SQL » Out Parameter
Out Parameter [message #188437] Fri, 18 August 2006 10:06 Go to next message
Messages: 38
Registered: August 2005

when i attempt to execute a stored procedure, I get a message stating that wrong number of parameters have been passed. This is my execution statement and a description of the procedure

var v_sao_pao_desc varchar2(240) ;
var v_pao_street varchar2(240) ;
var v_locality varchar2(240);
var v_town_city varchar2(60) ;
var v_county  varchar2(60);
var v_postcode varchar2(60);
var v_country varchar2(80);
var v_fuzzy_rule_id varchar2;
var o_return_code    VARCHAR2; 
    o_return_code := '  ';
var o_return_status  VARCHAR2;
var o_return_msg     VARCHAR2;
var o_address_list   XMLTYPE;
var v_seach_organisation varchar2;

execute XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('&DESC','&Str','&Loc','&Town','&County','&PCode','&Country','10020', :o_return_code, :o_return_status, :o_return_msg, :o_address_list,'&Search');

below is a description of the procedure

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SAO_PAO_DESC                 VARCHAR2(240)           IN
 P_PAO_STREET                   VARCHAR2(240)           IN
 P_LOCALITY                     VARCHAR2(240)           IN
 P_TOWN_CITY                    VARCHAR2(60)            IN
 P_COUNTY                       VARCHAR2(60)            IN
 P_POSTCODE                     VARCHAR2(60)            IN
 P_COUNTRY                      VARCHAR2(80)            IN
 P_FUZZY_RULE_ID                VARCHAR2                IN     DEFAULT
 X_RETURN_CODE                  VARCHAR2                OUT
 X_RETURN_STATUS                VARCHAR2                OUT
 X_RETURN_MSG                   VARCHAR2                OUT
 X_ADDRESS_LIST                 XMLTYPE                 OUT
 P_SEARCH_ORGANIZATION          VARCHAR2                IN     DEFAULT

thanks for your help

Re: Out Parameter [message #188518 is a reply to message #188437] Sat, 19 August 2006 00:28 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Your statement:
o_return_code := '  ';

is a PL/SQL statement; it should be prefixed with an EXEC, ow wrapped in a BEGIN/END. I don't think that's your problem though.

I have never played around with XMLTYPE and bind variables. If you thought that might be the problem, you could remove the XMLTYPE from the parameter list and try it again. It wouldn't help, but it might redirect your efforts.

Also, it might not be the outer procedure. Are there any procedure calls inside FUZZY_VALIDATE_ADDRESS? It may be one of those. Can FUZZY_VALIDATE_ADDRESS be called successfully from within PL/SQL jobs? ie. Is it only when called from SQL*Plus with bind variables that it fails?

Good Luck.

Ross Leishman
Previous Topic: connect by root oracle 8
Next Topic: Stored Procedure status- Oracle
Goto Forum:

Current Time: Sat Aug 19 00:47:19 CDT 2017

Total time taken to generate the page: 0.06353 seconds