Oracle dynamic PL-SQL and SQL*Plus

[sonofsa]

I am new to Oracle and would greatly appreciate any help with this.

I am working on a Cobol CICS project where users enter SQL*Plus statements and submit for execution via Batch or On-line mode. My Batch mode works perfectly with control breaks, headers and footers. My problem is accomplishing this in an on-line mode, i.e. execute the SQL*Plus query dynamically and returning the result set for screen display via a cursor (ref cursor). From the research I've done so far, I've come upon some examples using REF CURSOR through a PL-SQL function in conjunction with SQL*Plus (report formatting) - via dynamic SQL. I've seen no Cobol examples using this method - does anyone know how?

Thank you
Rick

 

[fredericofonseca]

http://docs.oracle.com/cd/A57673_01/DOC/api/doc/PCO18/ch1.htm

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[sonofsa]

Frederico,

Thank you for your response. I have been through this link but have not found what I've been looking for. Here's what I want to accomplish using Cobol on the Mainframe dynamically:

VARIABLE rc REFCURSOR
BEGIN
OPEN :rc FOR SELECT DEPARTMENT_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_NAME, LAST_NAME;
END;
/

PL/SQL procedure successfully completed.

SET PAGESIZE 100 FEEDBACK OFF
TTITLE LEFT '*** Departmental Salary Bill ***' SKIP 2
COLUMN SALARY FORMAT $999,990.99 HEADING 'Salary'
COLUMN DEPARTMENT_NAME HEADING 'Department'
COLUMN LAST_NAME HEADING 'Employee'
COMPUTE SUM LABEL 'Subtotal:' OF SALARY ON DEPARTMENT_NAME
COMPUTE SUM LABEL 'Total:' OF SALARY ON REPORT
BREAK ON DEPARTMENT_NAME SKIP 1 ON REPORT SKIP 1
PRINT rc

 

[SantaMufasa]

Sonofsa,

Remember, SQL*Plus is not a query language...it is a formatting language that formats the output of a SQL query. It does not format the results of a PL/SQL block of code. In your code, above, the cursor (and its results) does not survive the execution of your PL/SQL block. So, to test your SQL along with your SQL*Plus, try this code, and let us know the results:

SET PAGESIZE 100 FEEDBACK OFF
 TTITLE LEFT '*** Departmental Salary Bill ***' SKIP 2
 COLUMN SALARY FORMAT $999,990.99 HEADING 'Salary'
 COLUMN DEPARTMENT_NAME HEADING 'Department'
 COLUMN LAST_NAME HEADING 'Employee'
 COMPUTE SUM LABEL 'Subtotal:' OF SALARY ON DEPARTMENT_NAME
 COMPUTE SUM LABEL 'Total:' OF SALARY ON REPORT
 BREAK ON DEPARTMENT_NAME SKIP 1 ON REPORT SKIP 1

SELECT DEPARTMENT_NAME, LAST_NAME, SALARY
 FROM EMP_DETAILS_VIEW
 WHERE SALARY > 12000
 ORDER BY DEPARTMENT_NAME, LAST_NAME;

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[sonofsa]

Dave,

Thank you.

I have already done this in my Batch processing, it works fine. I can do control breaks, report breaks, column heading changes, report titles, formatting etc. The caveat here is that the user has to logon to TSO to check the output - painful!. I want to provide them with a seamless function that displays the results on the screen (in one fluid motion).

My problem is replicating the same on a Mainframe CICS platform using Cobol in an on-line mode. At this point I can bind and size column names using dynamic SQL, I cannot do all the other nice things that SQL*Plus does!

Rick

 

[sonofsa]

One more thing Dave ..

The example I posted in response to Federico's reply - I got this off the net! It's SQL Developer, I'm looking for something similar for my needs.

Thanks,
Rick

 

[fredericofonseca]

Rick,

As Dave mentioned, sqlplus is the application that is doing the formatting.

So unless you can execute and get the output of sqlplus within your online program then you can't use that formatting as it is in no way related to pl/sql.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[sonofsa]

Frederico,

I think you can do that - if you look at the example I posted the PL/SQL proc is being executed from SQL*Plus. The PRINT RC does that (RC is a REF CURSOR). Here's another example below, although this one does not do any special formatting.

QL> create or replace function get_emps(dno in number) return sys_refcursor
2 is
3 return_value sys_refcursor;
4 begin
5 open return_value for
6 select * from emp where deptno = dno;
7 return return_value;
8 end;
9 /

Function created.

SQL> var rc refcursor
SQL> exec :rc := get_emps(30)

PL/SQL procedure successfully completed.

SQL> print rc
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30