Re: How to execute a procedure with IN OUT parameters

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: Thu, 07 Sep 2000 23:11:59 -0500
Message-ID: <39B8670F.AED44DA0@ntsource.com>

Using your code pieces I was able to insert a record in a table with the sqlplus procedure you provided. (However, I did place a semicolon at the end of line 7 to make it work).

For example, I created the table t2 as follows:

SQL> create table t2 (id varchar2(50),
  2 name varchar2(50),
  3 e_code number,
  4 e_message varchar2(255));
Table created.

Then the procedure was created:

SQL> create or replace procedure ADD_CUST   2 (

  3          id                      in      varchar2,
  4          name                    in      varchar2,
  5          e_code                  in out integer,
  6          e_message               in out varchar2
  7          ) as
  8  begin
  9     insert into t2 values (id, name, e_code, e_message);
 10     e_code := 0;
 11     e_message := 'done';

 12 end;
 13 /
Procedure created.

And finally the sqlplus script with the semicolon at line 7 ran as follows:

SQL> DECLARE

  2      v_e_code number;
  3      v_e_message VARCHAR2(255);
  4      BEGIN
  5      v_e_code := 0;
  6     v_e_message := 'Testing';
  7      ADD_CUST('ABC','ABC User',v_e_code,v_e_message);
  8      END;

  9 /
PL/SQL procedure successfully completed.

And the t2 table had a new record in it:

SQL> select * from t2;

ID

---

NAME                                                   E_CODE
-------------------------------------------------- ----------

E_MESSAGE

---

ABC

ABC User                                                    0

Testing

I was using Oracle 8.1.6 on NT.

Frank Hubeny

colinleung_at_my-deja.com wrote:

> Dear All,
>
> I am using Oracle8i (8.1.5). I have a procedure which consists of 2 IN
> OUT parameters. How can I execute the procedure from SQLPlus?
>
> The procedure is as follow:
>
> procedure ADD_CUST
> (
> id in varchar2,
> name in varchar2,
> e_code in out integer,
> e_message in out varchar2
> ) as .....
>
> I have tried to execute the following commands from SQLPlus, and it
> returns 'PL/SQL procedure successfully completed.'
>
> SQL> DECLARE
> 2 v_e_code number;
> 3 v_e_message VARCHAR2(255);
> 4 BEGIN
> 5 v_e_code := 0;
> 6 v_e_message := 'Testing';
> 7 ADD_CUST('ABC','ABC User',v_e_code,v_e_message)
> 8 END;
> 9 /
> PL/SQL procedure successfully completed.
>
> However I have checked there is no new record inserted to the table.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Sep 07 2000 - 23:11:59 CDT