Oracle External Stored Procedure Parameters

Question

I have a stored procedure with the declared parameter OUT OUT as 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, which is also missing?

Answer 1

Your question is not entirely clear. The IN OUT parameter is transmitted in both directions, as its name implies. This means that it needs to pass a variable, not a literal, and for this you need the declare block. 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;

Answer 2

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.