CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Shweta Puchu
Shweta Puchu

Reputation: 13

Calling one procedure from another procedure

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.

Upvotes: 1

Views: 21778

Answers (3)

brenners1302
brenners1302

Reputation: 1478

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

Upvotes: 2

T.S.
T.S.

Reputation: 19330

Your first block is anonymous block in which you declare procedure - you can call procedure Get_manager_details within anonymous block only. You can't call Get_manager_details from Test_Procedure because there is no such procedure. You need to create your procedure Get_manager_details first

Create or replace procedure Get_manager_details ....

Then you can run 

Create or replace procedure Test_Procedure ....

Or it will not compile.

Upvotes: 1

sudip das
sudip das

Reputation: 36

If you are trying to call the procedure get_manager_details inside test_procedure then you first need to create the test procedure. Add create or replace procedure test_procedure . Then after creating the test_procedure you can execute it in an anonymous block which will call the get_manager_details procedure.

Upvotes: -1

Related Questions