1 Reply Latest reply: Jun 8, 2011 8:11 AM by 730428 RSS

    how do you run procedure with declare on sql plus

      Hi i am a newbie in sqlplus so please forgive if my question sounds to0 silly as i was watching a tutorial on sqlplus procedures i know how to run a simple IN procedure like

      create or replace procedure inparameter
      p_name IN VARCHAR2
      DBMS_OUTPUT.PUT_LINE('Hello' || p_name);

      to run
      start inparameter;
      procedure created

      set serveroutput on;

      exec inparameter ('newbie');
      hello newbie
      procedure successfuly completed

      but now i have a procedure with a declare

      now wich from what i see on the video tutorial as two box to create procedure and on the next the declare i dont know if i am meant to put them together and run it as one this how it looks like

      DO I RUN THE FIRST BOX AS MY PREVIOUS PROCEDURE DESCRIBED ABOVE (start procedure, then exec procedure)
      then run the second box which is the declare as the same way or do i put the all code in one single box (start declare then exec declare)

      BOX 1
      Create or replace procedure addition
      ,P_B IN NUMBER
      P_C := P_A +P_B;

      BOX 2
      x number;
      addition (5,5,x);
      dbms_output.put_line('the result is' || x);
      the tutorial can be seen on this youtube link

      fastforward to about 9mins 18 to see the boxes that i mean

      sorry in advance if its a stupid question
        • 1. Re: how do you run procedure with declare on sql plus
          You have to create the first procedure (addition), then run the pl/sql block:
          SQL> Create or replace procedure addition
            2  (
            3  P_A IN NUMBER
            4  ,P_B IN NUMBER
            5  ,P_C OUT NUMBER
            6  )AS
            7  BEGIN
            8  P_C := P_A +P_B;
            9  END ADDITION;
           10  /
          Procedure created.
          SQL> declare
            2  x number;
            3  begin
            4  addition (5,5,x);
            5  dbms_output.put_line('the result is' || x);
            6  end;
            7  /
          the result is10
          PL/SQL procedure successfully completed.
          The procedure is stored in the database, the pl/sql block is anonymous, it's not stored in the db.