CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
rishabh
rishabh

Reputation: 21

Procedure as an argument in PL/SQL

I want to execute a procedure which takes another procedure as an argument which has some other arguments or parameters. E.g.

ProcA(Proc_B(Name Varchar2, ID Varchar2))

Is this possible? If,so, please suggest me the possible solution.

Upvotes: 0

Views: 1241

Answers (1)

MT0
MT0

Reputation: 167774

This is not possible.

A procedure does not directly return a value. This is different to a function that does return a value.

So you could do:

ProcedureA( FunctionB( name, id ) )

(Note: This is not passing a function as an argument but is passing the result of the function as an argument.)

Like this:

DECLARE
  FUNCTION FunctionB(
    name   IN  VARCHAR2,
    id     IN  NUMBER
  ) RETURN VARCHAR2
  IS
  BEGIN
    RETURN name || id;
  END;

  PROCEDURE ProcedureA(
    value IN VARCHAR2
  )
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( value );
  END;
BEGIN
  ProcedureA(
    FunctionB(
      name   => 'A',
      id     => 1
    )
  );
END;
/

An alternative would be to use an output parameter from ProcedureA and an intermediate variable:

DECLARE
  temp VARCHAR2(50);

  PROCEDURE ProcedureB(
    name   IN  VARCHAR2,
    id     IN  NUMBER,
    result OUT VARCHAR2
  )
  IS
  BEGIN
    result := name || id;
  END;

  PROCEDURE ProcedureA(
    value IN VARCHAR2
  )
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( value );
  END;
BEGIN
  ProcedureB(
    name   => :name,
    id     => :id,
    result => temp
  );
  ProcedureA( temp );
END;
/

But you cannot nest one procedure inside the call invoking another.

Upvotes: 2

Related Questions