Home » SQL & PL/SQL » SQL & PL/SQL » Executing a stored procedure
Executing a stored procedure [message #188865] Tue, 22 August 2006 03:55 Go to next message
aadebayo
Messages: 35
Registered: August 2005
Member
Hello

I am trying to execute a stored procedure. It is claiming that no records are being processed when infact I believe some should. Is there anything wrong with the way that I have passed my parameters?

declare

o_return_code    VARCHAR2(1000); 
o_return_status  VARCHAR2(1000);
o_return_msg     VARCHAR2(1000);
o_address_list   XMLTYPE;
v_value varchar2(200);


begin XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('','Apex','','',null,'',null,'10020', o_return_code, o_return_status, o_return_msg, o_address_list,'1');
begin XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('Apex','','','',null,'',null,'10020', o_return_code, o_return_status, o_return_msg, o_address_list,'1');

begin XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('Apex','11','','','',null,null,'10020', o_return_code, o_return_status, o_return_msg, o_address_list,'1');

begin XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('','','','',null,'lu12rd',null,'10020', o_return_code, o_return_status, o_return_msg, o_address_list,'1');

begin XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('34 apex','','','',null,'lu12rd',null,'10020', o_return_code, o_return_status, o_return_msg, o_address_list,'1');




dbms_output.put_line('code = '|| o_return_code);
dbms_output.put_line('status = '|| o_return_status);
dbms_output.put_line('msg = '|| o_return_msg);


end;


Thanks
Re: Executing a stored procedure [message #188881 is a reply to message #188865] Tue, 22 August 2006 04:47 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
Your syntax have 5 begin statements and 1 end statement?
Make them 5 and set serveroutput on
Re: Executing a stored procedure [message #188891 is a reply to message #188865] Tue, 22 August 2006 05:05 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
I think you should have only one block:

DECLARE

  o_return_code   VARCHAR2(1000);
  o_return_status VARCHAR2(1000);
  o_return_msg    VARCHAR2(1000);
  o_address_list  XMLTYPE;
  v_value         VARCHAR2(200);

BEGIN
    XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('',
                                                  'Apex',
                                                  '',
                                                  '',
                                                  NULL,
                                                  '',
                                                  NULL,
                                                  '10020',
                                                  o_return_code,
                                                  o_return_status,
                                                  o_return_msg,
                                                  o_address_list,
                                                  '1');

    XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('Apex',
                                                  '',
                                                  '',
                                                  '',
                                                  NULL,
                                                  '',
                                                  NULL,
                                                  '10020',
                                                  o_return_code,
                                                  o_return_status,
                                                  o_return_msg,
                                                  o_address_list,
                                                  '1');

    XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('Apex',
                                                  '11',
                                                  '',
                                                  '',
                                                  '',
                                                  NULL,
                                                  NULL,
                                                  '10020',
                                                  o_return_code,
                                                  o_return_status,
                                                  o_return_msg,
                                                  o_address_list,
                                                  '1');

    XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('',
                                                  '',
                                                  '',
                                                  '',
                                                  NULL,
                                                  'lu12rd',
                                                  NULL,
                                                  '10020',
                                                  o_return_code,
                                                  o_return_status,
                                                  o_return_msg,
                                                  o_address_list,
                                                  '1');

    XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('34 apex',
                                                  '',
                                                  '',
                                                  '',
                                                  NULL,
                                                  'lu12rd',
                                                  NULL,
                                                  '10020',
                                                  o_return_code,
                                                  o_return_status,
                                                  o_return_msg,
                                                  o_address_list,
                                                  '1');
  
    dbms_output.put_line('code = ' || o_return_code);
    dbms_output.put_line('status = ' || o_return_status);
    dbms_output.put_line('msg = ' || o_return_msg);

END;


And note that you'll display the return values only for the last procedure call.

Wink

[Updated on: Tue, 22 August 2006 05:09]

Report message to a moderator

Re: Executing a stored procedure [message #188895 is a reply to message #188865] Tue, 22 August 2006 05:16 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
or this

declare

o_return_code VARCHAR2(1000);
o_return_status VARCHAR2(1000);
o_return_msg VARCHAR2(1000);
o_address_list XMLTYPE;
v_value varchar2(200);


begin XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('','Apex','','',null,'',null,'10020', o_return_code, o_return_status, o_return_msg, o_address_list,'1');
begin XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('Apex','','','',null,'',null,'10020', o_return_code, o_return_status, o_return_msg, o_address_list,'1');

begin XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('Apex','11','','','',null,null,'10020', o_return_code, o_return_status, o_return_msg, o_address_list,'1');

begin XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('','','','',null,'lu12rd',null,'10020', o_return_code, o_return_status, o_return_msg, o_address_list,'1');

begin XX_LGS_CDH_ADDRESS_PKG.FUZZY_VALIDATE_ADDRESS('34 apex','','','',null,'lu12rd',null,'10020', o_return_code, o_return_status, o_return_msg, o_address_list,'1');




dbms_output.put_line('code = '|| o_return_code);
dbms_output.put_line('status = '|| o_return_status);
dbms_output.put_line('msg = '|| o_return_msg);


end;
end;
end;
end;
end;

[Updated on: Tue, 22 August 2006 08:04]

Report message to a moderator

Re: Executing a stored procedure [message #188896 is a reply to message #188895] Tue, 22 August 2006 05:18 Go to previous message
aadebayo
Messages: 35
Registered: August 2005
Member
Thanks very much for your help. It is now working.
Previous Topic: horizontal reporting
Next Topic: Regarding business days calculation
Goto Forum:
  


Current Time: Mon Dec 05 04:55:39 CST 2016

Total time taken to generate the page: 0.04909 seconds