How to call a stored procedure in pl/sql developer-CodePudding

I am new at this and have a simple question.

I have created a procedure like so in pl/sql developer

CREATE OR REPLACE PROCEDURE myproc2 AS
BEGIN
  SELECT cd_desc des, cd_value cd FROM v_codes WHERE cd_type='CVS02'
END;

Now I want to call the procedure and see the output however when I run this

BEGIN 
   myproc2; 
  END;

in Pl/sql I am getting an error saying object myproc2 is invalid

How do I call a stored procedure in PL/SQL?

CodePudding user response：

You're calling it right, but the procedure is wrong. If you check its status, it is invalid.

In PL/SQL, a SELECT requires INTO:

CREATE OR REPLACE PROCEDURE myproc2 AS
  l_cd_desc   v_codes.cd_desc%type;
  l_cd_value  v_codes.cd_value%type;
BEGIN
  SELECT v.cd_desc, v.cd_value 
    INTO l_cd_desc, l_cd_value
  FROM v_codes v
  WHERE v.cd_type = 'CVS02';
END;

Beware of possible NO_DATA_FOUND or TOO_MANY_ROWS exception.

Also, although it'll now run OK (I guess), you won't see anything because it is unknown what you'll do next. You could, for example, choose to display values you fetched. In that case, add

  <snip>
  WHERE v.cd_type = 'CVS02';

  dbms_output.put_line(l_cd_desc ||', '|| l_cd_value);
END;

Don't forget to enable serveroutput.

As you commented, you got too_many_rows. How to handle it? It depends on what you want to do. One option is to switch to a cursor FOR loop; now you don't need local variables and - as there's no SELECT statement itself - no INTO clause either:

CREATE OR REPLACE PROCEDURE myproc2
AS
BEGIN
   FOR cur_r IN (SELECT v.cd_desc, v.cd_value
                   FROM v_codes v
                  WHERE v.cd_type = 'CVS02')
   LOOP
      DBMS_OUTPUT.put_line (cur_r.cd_desc || ', ' || cur_r.cd_value);
   END LOOP;
END;

CodePudding user response：

One great thing about Oracle SQL Developer is the GUI and that it does things for you.

You can open a sheet and run it the traditional way:

BEGIN
  PROCEDURENAME(PARAM);
END;

or you can use the GUI, find the object with the (View->) Find DB object, find it, click on it and use the green arrow in the toolbar. It will open a UI for any parameters you used within the procedure.

CodePudding user response：

In SQL Developer, if you want to see the output then you can return a cursor:

CREATE OR REPLACE PROCEDURE myproc2(
  o_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN o_cursor FOR
  SELECT cd_desc AS des,
         cd_value AS cd
  FROM   v_codes
  WHERE cd_type='CVS02'; -- You need a ; statement terminator here.
END;
/

Then you can use:

-- Declare a cursor bind variable
VARIABLE cur SYS_REFCURSOR;

BEGIN
  -- Call the cursor outputting into the bind variable.
  myproc2(:cur);
END;
/

-- Print the cursor
PRINT :cur;

And run it as a script (using F5).