How to Retrieve data from oracle database by using cursor?

Question

set serveroutput on;
CREATE OR REPLACE PROCEDURE invoice_report AS


orderDetail Orders%ROWTYPE;

CURSOR c_order IS 
    SELECT Orders.ono AS OrderNo,
        Customer.cno AS CustomerNo,
        Customer.cname AS CustomerName,
        Orders.shipped AS OrderDate,
        Part.pno AS PartNo,
        Part.pname AS PartName,
        OrderItem.qty AS Quantity,
        Part.price AS Price 
    FROM Orders,Customer,Part,OrderItem where Orders.ono='&order_no';


BEGIN
    Open c_order;
Loop
    FETCH c_order INTO orderDetail;
    EXIT WHEN c_order %NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(orderDetail);
END LOOP;
    Close c_order;
ENd;
/

I am new to PL/SQL especially using cursor. I want to EXECUTE invoice_report then display orders detail by a customer after they enter the order number. Then the error message shown: PLS-00394 wrong number of values in the INTO list of a FETCH statement.

Simplest solution, in my opinion, would be to define a separate variable for each column in your cursor, e.g. ORDERS_ONO ORDERS.ONO%TYPE. — Abra
– Abra, Commented May 9, 2019 at 7:24
You have PLS-00394 because of type of orderDetail variable. Now it has a type Orders%ROWTYPE, but it should be c_order%ROWTYPE and should be declared after cursor declaration. — RGruca
– RGruca, Commented May 9, 2019 at 8:07
I changed to orderDetail c_order%ROWTYPE but another error occurred PLS-00306: wrong number of types of arguments in call to 'PUT_LINE' — Simon Teoh
– Simon Teoh, Commented May 9, 2019 at 8:13
Unfortunately dbms_output.put_line can't get CursorType as input parameter. You have to pass each attribute separately. So in your case DBMS_OUTPUT.PUT_LINE(orderDetail.OrderNo ||';'|| oferDetail.CutomerNo .....); — RGruca
– RGruca, Commented May 9, 2019 at 8:50
you mean DBMS_OUTPUT.PUT_LINE (orderDetail.Orders.ono...)? Error message shown: componet 'ORDERS' must be declared, after i key in orderDetail.Orders.ono — Simon Teoh
– Simon Teoh, Commented May 9, 2019 at 9:02

Kaushik Nayak · Accepted Answer · 2019-05-09 08:00:41Z

1

If you're running Oracle 12c and above, simply use DBMS_SQL.RETURN_RESULT with a REF cursor. There's no need of loops or dbms_output.

Note that, a procedure's body should not have a code to receive user's input. It can be passed as an argument.

set serveroutput on
CREATE OR REPLACE PROCEDURE invoice_report(p_orderno Orders.ono%TYPE) AS
c_order SYS_REFCURSOR;
BEGIN
OPEN  c_order FOR 
    SELECT Orders.ono AS OrderNo,
        Customer.cno AS CustomerNo,
        Customer.cname AS CustomerName,
        Orders.shipped AS OrderDate,
        Part.pno AS PartNo,
        Part.pname AS PartName,
        OrderItem.qty AS Quantity,
        Part.price AS Price 
    FROM Orders,Customer,Part,OrderItem where Orders.ono=p_orderno; --The argument
                    --Change it to explicit join syntax
   DBMS_SQL.RETURN_RESULT(c_order);
END;
/

Now, the user can pass the value during execution.

BEGIN
  invoice_report('&order_no');
END;
/

edited May 9, 2019 at 8:00

answered May 9, 2019 at 7:51

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Simon Teoh Over a year ago

It is compulsory for me to use cursor to retrieve data.

Kaushik Nayak Over a year ago

@SimonTeoh : Ok, I didn't realize it was a homework question.

Abdullah Ahsan · Accepted Answer · 2019-05-09 08:12:52Z

-1

Cause: The number of variables in the INTO clause of a FETCH statement does not match the number of columns in the cursor declaration.

Solution: Change the number of variables in the INTO clause or the number of columns in the cursor declaration so that the numbers match.

CREATE OR REPLACE PROCEDURE invoice_report
AS


    CURSOR c_order
    IS
        SELECT   orders.ono AS orderno,
                 customer.cno AS customerno,
                 customer.cname AS customername,
                 orders.shipped AS orderdate,
                 part.pno AS partno,
                 part.pname AS partname,
                 orderitem.qty AS quantity,
                 part.price AS price
          FROM   orders,
                 customer,
                 part,
                 orderitem
         WHERE   orders.ono = '&order_no';
    orderdetail   c_order%ROWTYPE;
BEGIN

    FOR orders IN orderdetail   
    LOOP
        DBMS_OUTPUT.PUT_LINE(orders.orderDetail);
    END LOOP;
END;

answered May 9, 2019 at 8:12

Abdullah Ahsan

10710 bronze badges

2 Comments

hotfix Over a year ago

if you copy a code, make sure that it is also a correct solution :)

hotfix Over a year ago

copy and paste is no help. Especially if the code is wrong

Collectives™ on Stack Overflow

How to Retrieve data from oracle database by using cursor?

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related