Forum Stats

  • 3,817,098 Users
  • 2,259,276 Discussions
  • 7,893,655 Comments

Discussions

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

User_LM22Y
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
=============================================================


Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,806 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.