Forum Stats

  • 3,836,781 Users
  • 2,262,188 Discussions


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

User_LM22Y Member Posts: 12 Green Ribbon

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
    Billy Verreynne Software Engineer Member Posts: 28,833 Red Diamond

    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.