PL SQL Package with Procedure, PLS-00323: subprogram or cursor

Question

Please help me. I am new to PL SQL. I want to Display On Screen all these details

Customer.customerNo, Customer.customerName, Customer.custBalance, customerOrder.orderDate, customerOrder.orderNo

from 2 tables [Customer, CustomerOrder]. Using the only Package with Procedure. Please help me how can I create such a package?

create or replace package my_pkg as
Procedure getAllOrders2(customer_id IN varchar2);
end my_pkg;
/

create or replace package body my_pkg as
Procedure getAllOrders2(customer_id IN varchar2) is
begin
dbms_output.put_line('customer_id is: '||customer_id );
end getAllOrders2;
end my_pkg;
/

Output Should be:

Customer Number, Customer Name, Order Number, Order Date, Customer Balance

Here is the output of all tables

What do you need the CustomerOrder table for? "Customer details" are in Customer table ... Also, saying that you "want to get customer details" - how exactly? What do you want to do with them? Just display then on the screen? Put into variables? Something else? — Littlefoot, May 22 '21 at 19:33

score 0 · Answer 1 · answered May 22 '21 at 20:23

One option is to use a cursor FOR loop (why? Because customer can have more than a single order, and - if you use a single select statement - you might get the too_many_rows error so you'd have to select into a collection or - as in my example - use a loop.

SQL> create or replace procedure getallorders2 (par_customer_id in customer.customerno%type)
  2  is
  3  begin
  4    for cur_r in (select c.customerno,
  5                         c.customername,
  6                         c.custbalance,
  7                         o.orderdate,
  8                         o.orderno
  9                  from customer c join customerorder o
 10                    on c.customerno = o.customerno
 11                  where c.customerno = par_customer_id
 12                 )
 13    loop
 14      dbms_output.put_line
 15        (cur_r.customerno   ||', '||
 16         cur_r.customername ||', '||
 17         cur_r.custbalance  ||', '||
 18         cur_r.orderno      ||', '||
 19         to_char(cur_r.orderdate, 'dd.mm.yyyy')
 20        );
 21    end loop;
 22  end;
 23  /

Procedure created.

Testing:

SQL> set serveroutput on;
SQL> begin
  2    getallorders2('A101');
  3  end;
  4  /
A101, basant, 32000, O101, 04.02.2021

PL/SQL procedure successfully completed.

SQL>

Now that you know how, move that code into a package body.

PL SQL Package with Procedure, PLS-00323: subprogram or cursor

1 Answers1