Cobol Oracle dynamic SQL

[sonofsa]

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

 

[mikrom]

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

 

[fredericofonseca]

mikrom - see thread1662-1717209 for the original discussion on this.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886