Search code examples
oracleplsqloracle-sqldeveloperexecute

SQL Developer Oracle, how to call procedure?


I have delcared function like this:

CREATE or replace PROCEDURE proc
(
  P_ID IN INTEGER,
  NAME OUT CHAR,
  SURNAME OUT CHAR,
  TOTAL OUT CHAR
)
 AS
    BEGIN
        SELECT NAME, SURNAME, sum(TOTAL) AS TOT
          INTO NAME,SURNAME,TOTAL
          FROM STATISTICS, PLAYERS, PERSON
          WHERE STATISTICS.SID=P_ID AND PERSON.ID=PLAYERS.SID AND      
          STATISTICS.PLAYERS_SID=PLAYERS.SID
        GROUP BY NAME,SURNAME;
END;

Select statement works corectly, but how to call this procedure in Oracle?

I tried something like

EXEC proc(4);

AND

DECLARE
  NAME OUT CHAR,
  SURNAME OUT CHAR,
  TOTAL OUT CHAR

BEGIN
   P_ID := 12 ;
   proc (
   P_ID => P_ID,
   NAME => NAME,    
   SURNAME => SURNAME,
   TOTAL => TOTAL
);
END;

but without any success.


Solution

  • EXEC proc(4);

    EXECUTE is a SQL*Plus command.

    You have following options:

    • EXECUTE in SQL*Plus
    • Call it in an anonymous PL/SQL block.
    • Run in SQL Developer client tool

    Let's see all the three ways:

    In SQL*Plus:

    SQL> variable v_ename varchar2(20);
    SQL> exec get_emp(7788, :v_ename);
    
    PL/SQL procedure successfully completed.
    
    SQL> print v_ename;
    
    V_ENAME
    --------------------------------
    SCOTT
    

    In an anonymous PL/SQL block:

    SQL> CREATE OR REPLACE PROCEDURE get_emp(
      2      i_empno IN emp.empno%TYPE,
      3      o_ename OUT emp.ename%TYPE)
      4  AS
      5  BEGIN
      6    SELECT ename INTO o_ename FROM emp WHERE empno = i_empno;
      7  END;
      8  /
    
    Procedure created.
    
    SQL> SET serveroutput ON
    SQL> DECLARE
      2    v_ename VARCHAR2(20);
      3  BEGIN
      4    get_emp(7788, v_ename);
      5    dbms_output.put_line('Employee name is '||v_ename);
      6  END;
      7  /
    Employee name is SCOTT
    
    PL/SQL procedure successfully completed.
    

    In SQL Developer client tool:

    1. Go to connections on the left pane.
    2. Expand the Procedures.

    enter image description here

    1. Right click on the procedure and select "Run".

    enter image description here

    1. It will open a new window, provide the Input value and click OK.

    enter image description here

    1. The output will be shown in Output Log at the bottom as "Output Variables".

    enter image description here