×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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.

Students Click Here

Cobol Oracle dynamic SQL

Cobol Oracle dynamic SQL

Cobol Oracle dynamic SQL

(OP)
I am working on a CICS Cobol project where users enter SQL*Plus (Oracle) statements, the system is expected to return the result sets on-line or Batch. I got the batch part working - the on-line is another matter. I have done a lot research without success. Here's an example of how it's done in SQLDeveloper - I need to replicate the same process using Cobol:

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

Any help would be greatly appreciated!
Thanks,
Rick

RE: Cobol Oracle dynamic SQL

What is the problem? How to use embedded SQL (ESQL) or have you a technical problem that it doesn't work with Oracle DB?

I have no knowledge of Oracle PL/SQL, but with ESQL it must be similar on all systems. You have to do following things in your program:
1. include SQLCA
2. declare data structure for the selected record
3. declare the cursor for your select statement
4. open the cursor
5. read rows using fetch statement and process them - in your case: compute the sums
6. at the end close the cursor

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! Already a Member? Login


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