Execute Stored Procedure in another Stored Procedure in Oracle

Question

I want to understand how do you call a stored procedure in another stored procedure in Oracle.

For example: In SQL Server stored procedure,

CREATE PROCEDURE P2
.
.
.
DECLARE v_Id INT

EXEC P1 v_Name, v_Id OUTPUT

Both v_Name and v_Id is available in procedure P1.

How do you call the same in Oracle

Answer 1

Something like this is what you're after

CREATE OR REPLACE PROCEDURE p2
AS
  -- Do not use the `DECLARE` keyword
  -- Variables must be defined at the top of the block
  v_id pls_integer; 
BEGIN
  <<do stuff>>

  -- Just call the procedure.  No need for `EXEC`.  
  --  There is also no need to indicate that a parameter is an OUT parameter in the call
  p1( v_name, v_id );
END;

A few things to be aware of

• Variables are defined at the top of the block (between the AS and the BEGIN) rather than being defined inline. You can create nested PL/SQL blocks within your procedure where you declare variables whose scope is just that nested block rather than the procedure but I'm ignoring that possibility here.
• In PL/SQL, you don't need any keyword to indicate a procedure call. Just call it.
• You don't need to indicate that a parameter is an OUT parameter (or IN OUT). That is defined by the procedure you're calling.