CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle11g
LivinLife
LivinLife

Reputation: 109

Executing Stored Procedure - Oracle PL SQL

I'm currently trying to execute a stored procedure in Oracle PL SQL. I keep running into the same error for the below with execution.

I've tried both execution with the same error SET SERVEROUTPUT ON; EXEC get_phone(200.00,500.00);

OR

  SET SERVEROUTPUT ON;
   DECLARE
   c_minprice products.price%type;
   c_maxprice products.price%type;
  BEGIN
   c_minprice := get_phone(200);
   c_maxprice := get_phone(500);
  END;


 ERROR from executing the above:
c_minprice := get_phone(200);
              *

ERROR at line 5: 
 ORA-06550: line 5, column 15: 
 PLS-00306: wrong number or types of arguments in call to 'GET_PHONE' 
 ORA-06550: line 5, column 1: 
 PL/SQL: Statement ignored 
 ORA-06550: line 6, column 15: 
 PLS-00306: wrong number or types of arguments in call to 'GET_PHONE' 
 ORA-06550: line 6, column 1: 
 PL/SQL: Statement ignored 

  ****Sample Snip-its form my code:

   CREATE OR REPLACE PROCEDURE get_phone
   (
    c_minprice IN products.price%type,  
    c_maxprice IN products.price%type,  
    i_result OUT VARCHAR2 
  ) AS

--Checking if starting price range is valid or not
IF c_minprice IS NULL THEN
   i_result := 'Starting price range should be valid and cannot be empty';
  RAISE V_MINPRICE; -- Raising exception if starting price is null
END IF;

 --Checking if end price range is valid or not
IF c_maxprice IS NULL THEN
  i_result := 'End price range should be valid and cannot be empty';
  RAISE V_MAXPRICE; -- Raising exception if end price is null
END IF;

Upvotes: 0

Views: 178

Answers (2)

Justin Cave
Justin Cave

Reputation: 231851

Your procedure requires three parameters so you have to pass in three parameters

DECLARE
  l_result varchar2(100);
BEGIN
  get_phone( 200, 500, l_result );
END;
/

should work. Of course, your procedure seems rather pointless. And if the goal is simply to return a result, you really ought to be using a function rather than a procedure with an out parameter.

Upvotes: 1

Gary_W
Gary_W

Reputation: 10360

get_phone expects 3 arguments, c_minprice, c_maxprice and i_result. You are only passing it one number. Pass it the rest of the arguments.

Upvotes: 0

Related Questions