Generate a report using procedure with cursor ( ORACLE - PL/SQL)

User_LM22Y

Hey Guys, im planning to generate a report using stored procedure with cursor(if possible) to display the completed journey for top most desired to reached location which is (ToDestination) within my table, i just dont know how please help, thank you

Here is my table (Table Name : Booking)

I want to display something like this

SQL> exec summary_report;

=============================================================
Summary Report for top desired to reach location by customer
=============================================================
==================================================================
|Journey Status  |   Desired Location  | Desired Location (COUNT) |
==================================================================
    COMPLETED        Pahang                     3    
    COMPLETED        Perak                      3   
    COMPLETED        Selangor                   1   
    COMPLETED        Perlis                     1  
    COMPLETED        Johor                      1    
    COMPLETED        Kelantan                   1  

=============================================================
             END OF REPORT
=============================================================

Billy Verreynne

PL/SQL is server code and executes inside a db server process. It does not have access to the client's devices (keyboard/display) in order to read and write from/to the client.

Thus your report will need to be created by the server code in server memory - and the client will need to read the report and display it.

3 basic options.

Use DBMS_OUTPUT. This is a server side array of text data. However, other code could be using it to for debug and trace messages, and this will mess up your report.

Use DBMS_LOB. Create a CLOB and write the report to this CLOB. Return this CLOB to the client for it to display the report.

Use a PL/SQL pipeline that returns report lines as rows.