CODEWITHSUNDEEP
stored-proceduresplsqloracle11g
wali
wali

Reputation: 239

Output from Oracle stored procedure

I have the following stored procedure:

create or replace PROCEDURE A2CountSkus
(v_count out Number )
AS
 BEGIN 
   SELECT count(*)
      INTO v_count
      FROM a2sku;
   EXCEPTION 
 When Others THEN
    RAISE;
END A2CountSkus;

When I run this script.

SET SERVEROUTPUT ON 
BEGIN 
DBMS_OUTPUT.PUT_LINE('SKU COUNT ' || A2CountSkus); 
END; 
/

Instead of output being printed it gives an error.

Upvotes: 2

Views: 37838

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

First, normally, if you have a named PL/SQL block whose only purpose is to return a value, you would declare a function rather than a procedure. If you had declared A2CountSkus as a function that returned a number, the syntax that you want to use would work.

Second, you do not want that exception clause-- it does nothing useful. It should be eliminated.

Third, if you do want the block to be a procedure, the caller would need to declare a local variable that can be passed in to the procedure and then print the value in the local variable.

If you want to declare a function

create or replace FUNCTION A2CountSkus
  return integer
AS
  l_count integer;
BEGIN 
  SELECT count(*)
    INTO l_count
    FROM a2sku;
  RETURN l_count;
END A2CountSkus;

If you are sure that you want to declare a procedure, the caller would need to be something like

DECLARE
  l_count integer;
BEGIN 
  A2CountSkus( l_count );
  DBMS_OUTPUT.PUT_LINE('SKU COUNT ' || l_count); 
END;

Upvotes: 3

Related Questions