Can we use BREAK ON and COMPUTE within an Oracle stored procedure

by Rajesh Ranjan
(Delhi)

Is it possible to create a pl/sql package like the following?


CREATE OR REPLACE PACKAGE CONS_REP1 AS
   TYPE CUR_TEMP IS REF CURSOR;

   PROCEDURE CONS_REPORT1(VRTO_CD IN VARCHAR2,VVH_CLASS IN VARCHAR2,
      VFUEL IN VARCHAR2,FROMDT IN DATE,TODATE IN DATE,RESULT_CUR OUT CUR_TEMP);
END CONS_REP1;

CREATE OR REPLACE PACKAGE BODY CONS_REP1
AS
PROCEDURE CONS_REPORT1
(VRTO_CD VARCHAR2,VVH_CLASS VARCHAR2,VFUEL VARCHAR2,FROMDT DATE,TODATE DATE,RESULT_CUR OUT CUR_TEMP)
AS
BEGIN
   OPEN RESULT_CUR FOR
   BREAK ON "ZONE" ON "VEHICAL TYPE" SKIP 1
   COMPUTE SUM LABEL 'TOTAL BY ZONE' OF COUNT ON "ZONE"
   COMPUTE SUM LABEL 'TOTAL BY VEHICAL TYPE' OF COUNT ON "VEHICAL TYPE"
   SELECT * FROM (
      SELECT Z.ZONE_NAME "ZONE",V.CL_DESC "VEHICAL TYPE",
      F.DESCP "FUEL",COUNT(O.REGN_NO) "COUNT"
      FROM OWNER O
      INNER JOIN ZONE_DETAILS Z ON O.RTO_CD=Z.RTO_CD
      INNER JOIN VHCLASS_CD V ON O.VH_CLASS=V.CLASS_CD AND O.RTO_CD=V.RTO_CD
      INNER JOIN FUEL F ON O.FUEL=F.CODE AND O.RTO_CD=F.RTO_CD
      WHERE O.RTO_CD IN (1,2)
      AND O.VH_CLASS LIKE '%'
      AND O.FUEL LIKE '%'
      GROUP BY Z.ZONE_NAME, V.CL_DESC, F.DESCP, Z.RTO_CD, V.CLASS_CD,F.CODE
      ORDER BY Z.RTO_CD, V.CLASS_CD, F.CODE) A;
END;
END CONS_REP1;

Comments for Can we use BREAK ON and COMPUTE within an Oracle stored procedure

Average Rating starstarstarstarstar

Click here to add your own comments

Nov 07, 2016
Rating
starstarstarstarstar
SQL*Plus commands can't be used in PL/SQL
by: John

The short answer is no. Oracle PL/SQL doesn't understand SQL*Plus commands in stored procedures. It is possible to call PL/SQL procedures and functions from SQL*Plus - as we'll see in a minute. - but not the other way around.

Oracle's SQL*Plus tool provides a command-line interface to the database as well as many data formatting and reporting commands including BREAK ON and COMPUTE.

PL/SQL on the other hand is Oracle's proprietary language providing a procedural extension to SQL. PL/SQL is used to develop applications interacting with an Oracle database either on its own or in conjunction other procedural languages such as C, C#, C++, Java etc.

The advantage of PL/SQL over (some) other languages is that PL/SQL is tightly coupled with the Oracle database and PL/SQL procedures, packages and functions can be stored withing the database. SQL statements (but not SQL*Plus commands) can be embedded in PL/SQL and calls can be made to Java procedures or external procedures but they have to be in separate modules.

So how do you combine SQL*Plus and Oracle PL/SQL?

Well SQL*Plus can be used to run SQL statements which can have calls to PL/SQL functions embedded within them (subject to certain constraints - see
the Oracle Database PL/SQL Language Reference for details). For example if we have a function called my_func then we could write an SQL statement like this to call it:
SELECT my_func(sal) FROM emp;
and embed the report formatting command in the script invoking it. However that SQL*Plus script has to be stored in the file system not the database.

The i/o features in PL/SQL are limited as the language was not designed to be interactive. This makes it a poor choice on its own for producing reports. There are many tools available that are much better suited to providing reports.

A much better solution would be to to choose the right tool for the job and not try to force a tool such as PL/SQL to do a job which it is not designed for. That way your systems will be delivered quicker, cheaper and will require less maintenance in the future.

For details on the SQL*Plus formatting commands see the SQL*Plus User's Guide and Reference available from the Oracle Technology Network along with all the other guides and reference manuals.

You might also find our Oracle SQL tutorials useful.

If you're interested in formal Oracle training (either on-line or in a classroom) then see our Oracle training page for more information.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.