Re: executing a procedure

In article <8c84ok$ojt$1_at_bob.news.rcn.net>, "Louis" <frolio_at_videoshare.com> wrote:
> Greeting All, I have created a simple stored procedure that takes
 arguments
> and
> then writes them to a new record in my user table. Below is the code
 for
> this procedure:
>
> CREATE OR REPLACE PROCEDURE spAddUser (
> IvcFirstName IN VARCHAR2,
> IvcLastName IN VARCHAR2,
> OvcErrorMessage OUT VARCHAR2
> )
>
> AS
> AppError EXCEPTION;
>
> BEGIN
>
> OvcErrorMessage := 'NO ERROR.';
>
> INSERT INTO VIDEOSHARE.tbUser
> VALUES(
> IvcFirstName,
> IvcLastName
> );
>
> COMMIT;
>
> EXCEPTION
> WHEN OTHERS THEN
> ROLLBACK;
> OvcErrorMessage := 'Error Inserting Record: spAddUser: Pos #1.' ||
> SQLERRM;
> RAISE AppError;
> RETURN;
> END spAddUser;
> /
>
> When I try to execute this procedure:
>
> execute spAddUser ('Pete', 'Rose');
>
> I am prompted with the error message:
>
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'SPADDUSER'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> I think it has to do with my output paramater? Do I need to pass
 something
> to this or
> do I need to default it? Any help would be greatly appreciated.
>
> Louis
>
>

OUT parameters must be supplied by the caller and cannot be defaulted (there is no place to put the OUTPUT to if you, the caller, does not supply a variable).
You can:
variable msg varchar2(255)
exec spAddUser( 'pete', 'rose', :msg );
and if you want to see whats in msg after you are done, you can: print msg
If you want to be able to call this WITH and WITHOUT a third parameter, you could create an overloaded procedure in a package -- eg: create or replace package sp
as
procedure addUser( x in varchar2, y in varchar2 ); procedure addUser( x in varchar2, y in varchar2, z out varchar2 ); end;
/
and in the package body, just implement the first adduser as a call to the second -- supplying a dummy OUT variable for it. Then you could call:
SQL> exec sp.AddUser( 'pete', 'rose' );
SQL> exec sp.AddUser( 'pete', 'rose', :msg );

--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.