CODEWITHSUNDEEP
oracle-databaseplsql
gpa
gpa

Reputation: 2451

Execute stored procedure with multiple values

I have following stored procedure, how do i execute this with multiple status codes???

PROCEDURE process_orders (p_statuses VARCHAR2)
AS
  CURSOR v_c
  IS
     SELECT *
       FROM orders a
      WHERE status_code IN (p_statuses);
BEGIN
  DBMS_OUTPUT.put_line ('Start');
  FOR v_rec IN v_c
  LOOP
     DBMS_OUTPUT.put_line (v_rec.order_id);
  END LOOP;

  DBMS_OUTPUT.put_line ('End');
EXCEPTION
  WHEN OTHERS
  THEN
     DBMS_OUTPUT.put_line (SQLCODE || '-' || SQLERRM);
END process_orders ;

How do i pass multiple status codes like 'OPEN','INPROCESS','PENDING'

EXEC process_orders('OPEN');

Stored procedure should execute query something like following:

SELECT *
           FROM orders a
          WHERE status_code IN ('OPEN','INPROCESS','PENDING');

Upvotes: 0

Views: 185

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

The simplest approach would be to declare and pass a collection

CREATE TYPE status_tbl
    IS TABLE OF VARCHAR2(50);

CREATE OR REPLACE PROCEDURE process_orders (p_statuses status_tbl)
AS
  CURSOR v_c
  IS
     SELECT *
       FROM order a
      WHERE status_code IN (SELECT column_value
                              FROM TABLE(p_statuses));
BEGIN
  DBMS_OUTPUT.put_line ('Start');
  FOR v_rec IN v_c
  LOOP
     DBMS_OUTPUT.put_line (v_rec.order_id);
  END LOOP;

  DBMS_OUTPUT.put_line ('End');
END;

You'd call that procedure by passing in a collection of status values

BEGIN
  process_orders( status_tbl( 'OPEN', 'INPROCESS', 'PENDING' ));
END;

Note that an exception handler that catches all exceptions (WHEN OTHERS), fails to rethrow them (with a RAISE) and merely writes the error message to dbms_output should be avoided at all costs. In the best case scenario, you only lose the actual line number of the error message. In practice, though, you lose the line number of the error, you lose the stack trace of the error stack, you fail to inform the caller that there was an error so the caller continues on with the data in an invalid state, and you frequently lose the error entirely because the caller may not have enabled the dbms_output buffer, may not have sized it large enough, and may not read the buffer after the call completes.

Upvotes: 2

Related Questions