INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Oracle dynamic PL-SQL and SQL*Plus

Oracle dynamic PL-SQL and SQL*Plus

(OP)
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

RE: Oracle dynamic PL-SQL and SQL*Plus

(OP)
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

RE: Oracle dynamic PL-SQL and SQL*Plus

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:

CODE

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; 

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

RE: Oracle dynamic PL-SQL and SQL*Plus

(OP)
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

RE: Oracle dynamic PL-SQL and SQL*Plus

(OP)
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

RE: Oracle dynamic PL-SQL and SQL*Plus

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Oracle dynamic PL-SQL and SQL*Plus

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close