Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Call PL/SQL procedure from MF Cobol? 2
- Thread starter YankeeJim
- Start date
-
1
- #2
PL/SQL is Oracle specific batch script, so I doubt the Microfocus SQL pre-compiler function would support it being directly in the code or especially calling it if it's being stored as text.
as far as "EXEC" goes, I don't know. What error are you seeing? If Microfocus even supports this, you need to make sure whatever Oracle produces is a callable module so it has code to execute and not a batch script.
In my experience, getting a callable procedure ("EXEC") requires writing a program.
Hope this helps, or at least gives you the right answers to give so someone can help you better.
as far as "EXEC" goes, I don't know. What error are you seeing? If Microfocus even supports this, you need to make sure whatever Oracle produces is a callable module so it has code to execute and not a batch script.
In my experience, getting a callable procedure ("EXEC") requires writing a program.
Hope this helps, or at least gives you the right answers to give so someone can help you better.
- Thread starter
- #3
Thanks for your quick response...
MF doesn't like to see the 'EXEC'.
The purpose of the procedure is to 'gather' information from various tables into a view, which I then access using a cursor (embedded SQL).
The cursor presents no problem, and I can call the procedure from my unix script or from SQLPLUS, but I must be within the 'current' session in order to access the view...hence the call from within the program. Once I leave the 'current' session, the data within the view 'disappears' (context sensitive).
MF doesn't like to see the 'EXEC'.
The purpose of the procedure is to 'gather' information from various tables into a view, which I then access using a cursor (embedded SQL).
The cursor presents no problem, and I can call the procedure from my unix script or from SQLPLUS, but I must be within the 'current' session in order to access the view...hence the call from within the program. Once I leave the 'current' session, the data within the view 'disappears' (context sensitive).
-
1
- #4
fredericofonseca
IS-IT--Management
the thing is that there is not "EXEC" instruction.
There is although a EXECUTE as you can see by the following example.
Regards
Frederico Fonseca
SysSoft Integrated Ltd
There is although a EXECUTE as you can see by the following example.
Code:
*****************************************************************
* Sample Program 9: Calling a Stored Procedure
*
* This program connects to ORACLE, prompts the user for a
* department number, then calls a PL/SQL stored procedure named
* GET_EMPLOYEES, which is stored in package CALLDEMO. The
* procedure declares three PL/SQL tables ast OUT formal
* parameters, then fetches a batch of employee data into the
* PL/SQL tables. The matching actual parameters are host tables.
* When the procedure finishes, it automatically assigns all row
* values in the PL/SQL tables to corresponding elements in the
* host tables. The program calls the procedure repeatedly,
* displaying each batch of employee data, until no more data
* is found.
* Use option dbms=v6 when precompiling this sample program.
*****************************************************************
IDENTIFICATION DIVISION.
PROGRAM-ID. sample9.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(15) VARYING.
01 PASSWD PIC X(15) VARYING.
01 DEPT-NUM PIC S9(9) COMP.
01 EMP-TABLES.
05 EMP-NAME OCCURS 10 TIMES PIC X(10).
05 JOB-TITLE OCCURS 10 TIMES PIC X(10).
05 SALARY OCCURS 10 TIMES PIC S9(6)V99 COMP-3.
01 DONE-FLAG PIC S9(9) COMP.
01 TABLE-SIZE PIC S9(9) COMP VALUE 10.
01 NUM-RET PIC S9(9) COMP.
01 SQLCODE PIC S9(9) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
01 COUNTER PIC S9(9) COMP.
01 DISPLAY-VARIABLES.
05 D-EMP-NAME PIC X(10).
05 D-JOB-TITLE PIC X(10).
05 D-SALARY PIC Z(5)9.
05 D-DEPT-NUM PIC 9(2).
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
BEGIN-PGM.
EXEC SQL WHENEVER SQLERROR DO
PERFORM SQL-ERROR END-EXEC.
PERFORM LOGON.
PERFORM INIT-TABLES VARYING COUNTER FROM 1 BY 1
UNTIL COUNTER > 10.
PERFORM GET-DEPT-NUM.
PERFORM DISPLAY-HEADER.
MOVE ZERO TO DONE-FLAG.
MOVE ZERO TO NUM-RET.
PERFORM FETCH-BATCH UNTIL DONE-FLAG = 1.
PERFORM LOGOFF.
INIT-TABLES.
MOVE SPACE TO EMP-NAME(COUNTER).
MOVE SPACE TO JOB-TITLE(COUNTER).
MOVE ZERO TO SALARY(COUNTER).
GET-DEPT-NUM.
MOVE ZERO TO DEPT-NUM.
DISPLAY " ".
DISPLAY "ENTER DEPARTMENT NUMBER: "
WITH NO ADVANCING.
ACCEPT D-DEPT-NUM FROM CONSOLE.
MOVE D-DEPT-NUM TO DEPT-NUM.
DISPLAY-HEADER.
DISPLAY " ".
DISPLAY "EMPLOYEE JOB TITLE SALARY".
DISPLAY "-------- --------- ------".
FETCH-BATCH.
EXEC SQL EXECUTE
BEGIN
CALLDEMO.GET_EMPLOYEES
(:DEPT-NUM, :TABLE-SIZE,
:NUM-RET, :DONE-FLAG,
:EMP-NAME, :JOB-TITLE, :SALARY);
END;
END-EXEC.
PERFORM PRINT-ROWS VARYING COUNTER FROM 1 BY 1
UNTIL COUNTER > NUM-RET.
PRINT-ROWS.
MOVE EMP-NAME(COUNTER) TO D-EMP-NAME.
MOVE JOB-TITLE(COUNTER) TO D-JOB-TITLE.
MOVE SALARY(COUNTER) TO D-SALARY.
DISPLAY D-EMP-NAME, " ",
D-JOB-TITLE, " ",
D-SALARY.
LOGON.
MOVE "SCOTT" TO USERNAME-ARR.
MOVE 5 TO USERNAME-LEN.
MOVE "TIGER" TO PASSWD-ARR.
MOVE 5 TO PASSWD-LEN.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
DISPLAY " ".
DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR.
LOGOFF.
DISPLAY " ".
DISPLAY "HAVE A GOOD DAY.".
DISPLAY " ".
EXEC SQL COMMIT WORK RELEASE END-EXEC.
STOP RUN.
SQL-ERROR.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
DISPLAY " ".
DISPLAY "ORACLE ERROR DETECTED:".
DISPLAY " ".
DISPLAY SQLERRMC.
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
STOP RUN.Regards
Frederico Fonseca
SysSoft Integrated Ltd
- Status
Similar threads