Calling one procedure from another procedure

Question

The code below is saved in a file named proc1.sql

DECLARE
    B VARCHAR2(25);
    C NUMBER;

PROCEDURE Get_manager_detailS(NO IN NUMBER,NAME OUT VARCHAR2,SAL1 OUT NUMBER)
IS
BEGIN
    SELECT ENAME, SAL 
    INTO NAME, SAL1 
    FROM EMP 
    WHERE EMPNO = NO;
END;
BEGIN
Get_manager_detailS(7900,B,C);
DBMS_OUTPUT.PUT_LINE(B);
DBMS_OUTPUT.PUT_LINE(C);
END;
/

This procedure is stored in another file proc3.sql

PROCEDURE Test_Procedure()
IS
BEGIN
b varchar2(25);
c number;
DBMS_OUTPUT.PUT_LINE('CALLING');
 Get_manager_details(7900,b,c);
END;

When I am running it in sqlplus, it is showing an error

SP2-0734 UNKNOWN COMMAND BEGINING PROCEDURE.. REST OF THE LINES IGNORED. SP2-0042 UNKNOWN COMMAND" IS "..REST OF THE LINE IGNORED.

brenners1302 brenners1302 · Accepted Answer · 2015-10-05T02:55:07

Creating a PROCEDURE/FUNCTION vs. ANONYMOUS BLOCK

Stored PROCEDURES/FUNCTIONS always starts with CREATE OR REPLACE ... and ends with END;

CREATE OR REPLACE serves as the implicit declare for stored functions and procedures, thus you dont have to write DECLARE anymore inside the block
An anonymous block starts with DECLARE and ends with END;

As for the code/block of codes saved in proc1.sql.

Your declaration is misplaced.You should place it after the end of the procedure
Start the procedure with CREATE OR REPLACE PROCEDURE

Try This Block:

    -- DECLARE              
    -- B VARCHAR2(25);
    -- C NUMBER;

    CREATE OR REPLACE PROCEDURE Get_manager_detailS(NO IN NUMBER,
                                                     NAME OUT VARCHAR2,
                                                     SAL1 OUT NUMBER)
    IS
    BEGIN
        SELECT ENAME, SAL 
        INTO NAME, SAL1 
        FROM EMP 
            WHERE EMPNO = NO;
    END; -- end of procedure

    /

    DECLARE --  start of anonymous block              
      B VARCHAR2(25);
      C NUMBER;
    BEGIN
      Get_manager_detailS(7900,B,C);
      DBMS_OUTPUT.PUT_LINE(B);
      DBMS_OUTPUT.PUT_LINE(C);
    END;

As for the procedure that will call the get_manager_details procedure.Its will be just the same as the anonymous block, the only difference will be is that it is stored

Base from what have you done already

If you will not declare parameters in your procedure, parenthesis are not necessary so remove it.
If you dont have output parameters that will catch the result of your procedure, you can use dbms_output.put_line as you have used in the anonymous block above
variable declarations should be done after the IS keyword and before BEGIN statements, because as I have noted above CREATE OR REPLACE ... IS is the implicit declare for the stored functions and procedures

TRY THIS:

    CREATE OR REPLACE PROCEDURE Test_Procedure
    IS -- always start with CREATE OR REPLACE
      b varchar2(25);
      c number;

    BEGIN
  --  b varchar2(25); misplaced declarations
  --  c number;
       DBMS_OUTPUT.PUT_LINE('CALLING');
       Get_manager_details(7900,b,c);
       DBMS_OUTPUT.PUT_LINE(B); -- displays the results b
       DBMS_OUTPUT.PUT_LINE(C); -- and c

    END;

Sorry for the long post.

HOPE THIS HELPS.

CHEERS

Calling one procedure from another procedure

3 Answers