PL/SQL Procedure SELECT Input- Error PLS-00306-CodePudding

Hello I'm a beginner at PL/SQL and some help would be appreciated.

So I have this procedure here and my goal is to have it so that when this procedure is executed that I can enter a 5 digit integer (a zipcode) and it will just select those values from the table and display just as if I've done a query like

SELECT * FROM customers WHERE customer_zipcode = "input zipcode".


create or replace PROCEDURE LIST_CUSTOMER_ZIPCODE(
p_zipcode IN customers.customer_zipcode%TYPE,
p_disp OUT SYS_REFCURSOR)
-- User input Variable, Display Variable
IS
BEGIN
    OPEN p_disp for SELECT customer_first_name, customer_zipcode FROM customers 
    WHERE customer_zipcode=p_zipcode;
EXCEPTION
    -- Input Sanitization
    WHEN no_data_found THEN
    dbms_output.put_line('-1');
END;

EXEC LIST_CUSTOMER_ZIPCODE(07080);

When I execute this command I just keep getting this error.

https://i.stack.imgur.com/nCI8T.png

CodePudding user response：

You can't just execute such a procedure as it expects 2 parameters; one is IN, while another is OUT (ref cursor which contains result set).

I don't have your tables so I'll demonstrate it using Scott's sample schema by passing department number and returning list of employees working in that department.

Procedure:

SQL> set serveroutput on
SQL> create or replace procedure p_list
  2    (p_deptno    in dept.deptno%type,
  3     p_disp     out sys_refcursor
  4    )
  5  is
  6  begin
  7    open p_disp for select ename, job from emp
  8                    where deptno = p_deptno;
  9  end;
 10  /

Procedure created.

This is how you use it:

SQL> declare
  2    l_list   sys_refcursor;
  3    l_ename  emp.ename%type;
  4    l_job    emp.job%type;
  5  begin
  6    p_list(10, l_list);       --> calling the procedure; use 2 parameters
  7
  8    loop
  9      fetch l_list into l_ename, l_job;
 10      exit when l_list%notfound;
 11      dbms_output.put_line(l_ename ||' - '|| l_job);
 12    end loop;
 13  end;
 14  /
CLARK - MANAGER
KING - PRESIDENT
MILLER - CLERK

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

TRY EXEC LIST_CUSTOMER_ZIPCODE(:p_Zipcode);

If you put ':' before any string, It will become substitution string and you can type your input.

CodePudding user response：

If you are using SQL*Plus or SQL Developer then you can declare a bind variable and then call the procedure passing the variable and then print it:

SELECT * FROM customers WHERE customer_zipcode = "input zipcode".

create or replace PROCEDURE LIST_CUSTOMER_ZIPCODE(
  p_zipcode IN customers.customer_zipcode%TYPE,
  p_disp OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN p_disp FOR
    SELECT customer_first_name, customer_zipcode
    FROM   customers 
    WHERE  customer_zipcode = p_zipcode;
EXCEPTION
  -- Input Sanitization
  WHEN no_data_found THEN
    dbms_output.put_line('-1');
END;
/

VARIABLE cur SYS_REFCURSOR;

EXEC LIST_CUSTOMER_ZIPCODE('07080', :cur);

PRINT cur;

However, your exception handling block is never going to be called as the cursor can return zero rows without raising that exception so the procedure could be simplified to:

create or replace PROCEDURE LIST_CUSTOMER_ZIPCODE(
  p_zipcode IN customers.customer_zipcode%TYPE,
  p_disp OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN p_disp FOR
    SELECT customer_first_name, customer_zipcode
    FROM   customers 
    WHERE  customer_zipcode = p_zipcode;
END;
/