CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
pistacchio
pistacchio

Reputation: 58953

Oracle stored procedure OUT parameters

I have a stored procedure with an IN OUT parameter declared like follows:

create or replace PROCEDURE RIFATT_SEGN0_INS(pIdRifattSegn0  in  OUT NUMBER,
                           pNumDossier IN VARCHAR2 ,
                           pNumConsegna IN NUMBER,
                           pDtConsegna IN DATE,
[..]

)  AS
[..]

Whenever i call it from another procedure, how do i get the pIdRifattSegn0 parameter that is also out?

Upvotes: 3

Views: 61301

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132710

Your question isn't entirely clear. An IN OUT parameter is passed both ways, as its name implies. This means it has to be passed a variable, not a literal and you need a declare block to do that. For example:

declare
  l_segn number;
begin
  l_segn := 1;
  -- procedure will have received value = 1
  rifatt_segn0_ins(l_segn, 'x', 2, sysdate);
  -- procedure may have changed value of l_segn from 1 to something else
  dbms_output.put_line(l_segn); 
end;

Upvotes: 14

Rob van Wijk
Rob van Wijk

Reputation: 17705

Here is an example:

SQL> create or replace PROCEDURE RIFATT_SEGN0_INS
  2  ( pIdRifattSegn0 IN OUT NUMBER
  3  , pNumDossier    IN     VARCHAR2
  4  , pNumConsegna   IN     NUMBER
  5  , pDtConsegna    IN     DATE
  6  )
  7  as
  8  begin
  9    dbms_output.put_line(pNumDossier);
 10    dbms_output.put_line(to_char(pNumConsegna));
 11    dbms_output.put_line(to_char(pDtConsegna,'yyyy-mm-dd'));
 12    pIdRifattSegn0 := sqrt(pIdRifattSegn0);
 13  end;
 14  /

Procedure is aangemaakt.

SQL> create or replace procedure another_procedure
  2  as
  3    l_IdRifattSegn0 number := 4;
  4  begin
  5    rifatt_segn0_ins
  6    ( pIdRifattSegn0 => l_IdRifattSegn0
  7    , pNumDossier    => '1A'
  8    , pNumConsegna   => 42
  9    , pDtConsegna    => sysdate
 10    );
 11    dbms_output.put_line('from another_procedure: l_IdRifattSegn0 = ' || to_char(l_IdRifattSegn0));
 12  end;
 13  /

Procedure is aangemaakt.

SQL> exec another_procedure
1A
42
2009-05-21
from another_procedure: l_IdRifattSegn0 = 2

PL/SQL-procedure is geslaagd.

Regards, Rob.

Upvotes: 2

Related Questions