Jens Forsberg Trusted Contributor.
Trusted Contributor.
462 views

Upgrading from Oracle 11g to Oracle 12c

Jump to solution

I'm using mainly the INT/GNT project type in Visual COBOL and I'm planning to upgrade from Oracle 11g to Oracle 12c. But since INT/GNT requires the 32-bit Pro*COBOL compiler and the only 32-bit edition among the 12c editions is the Client Edition, I'm planning to install the 64-bit Standard Edition (including the database) and the 32-bit Client Edition (including Pro*COBOL).

What do you think of that?

Am I wrong in that INT/GNT requires 32-bit Pro*COBOL?

0 Likes
1 Solution

Accepted Solutions
Jens Forsberg Trusted Contributor.
Trusted Contributor.

Re: Upgrading from Oracle 11g to Oracle 12c

Jump to solution

This is what finally worked for me. To be sure it worked and that previous attempts hadn't left any trace on my computer I re-installed Windows 10. After that I did the following:

I downloaded Oracle 12c Standard edition. I chose to use the "Windows built-in account" and to uncheck the "Create as container database" option.

I checked the environment variables and saw that Oracle had added [ORACLE_HOME]\bin to Path.

I checked the Oracle entries in the Registry (the ones I know of), and from what I could see everything looked normal (and why shouldn't it).

I unlocked the HR user. I used SQL Developer for this. There are probably other ways to do it.

I added the HR user in tnsnames.ora, i.e. "HR = (DESCRIPTION = ... (SERVICE_NAME = orcl.lan)))".

I installed Visual Studio Professional 2017 and Visual COBOL.

I created an INT/GNT project and added the x64 Solution platform in Configuration Manager.

I added a cbl file to the project and named it Program1.cbl.

I opened the cblproj file in a text editor and replaced the following line with the lines after that. NOTE: This is probably not how you should do it, but since I have had problems with adding directives to Pro COBOL from within Visual COBOL this is the way I chose. NOTE: The sqldebug and verbose options might be omitted if you don't want the more detailed output.

<Compile Include="Program1.cbl" />

<ItemGroup>
  <Compile Include="Program1.cbl">
    <SQLDirectives>P(cobsql) COBSQLTYPE=ORACLE sqldebug verbose END-COBSQL sqlcheck=full
      comp5=yes picx=varchar2 p(cp) ENDP</SQLDirectives>
  </Compile>
</ItemGroup>

Went back to Program1.cbl and entered the following code.

IDENTIFICATION DIVISION.
PROGRAM-ID. Program1.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 pp PROCEDURE-POINTER.
01 W10-CONNECT.
05 W10-USR PIC X(10).
05 W10-USR-LEN PIC S9(4) COMP-5.
05 W10-PWD PIC X(10).
05 W10-PWD-LEN PIC S9(4) COMP-5.
05 W10-DB PIC X(10).
05 W10-DB-LEN PIC S9(4) COMP-5.
01 W10-DIVERSE.
05 W10-DUMMY PIC X(3).
EXEC SQL include sqlca END-EXEC.
EXEC SQL begin declare section END-EXEC.
01 W900-CONNECT.
05 W900-USR PIC X(10) VARYING.
05 W900-PWD PIC X(10) VARYING.
05 W900-DB PIC X(10) VARYING
EXEC SQL end declare section END-EXEC.
PROCEDURE DIVISION.
SET pp TO ENTRY "orasql12".
MOVE 'hr' TO W10-USR.
MOVE 2 TO W10-USR-LEN.
MOVE 'hr' TO W10-PWD.
MOVE 2 TO W10-PWD-LEN.
MOVE 'HR' TO W10-DB.
MOVE 2 TO W10-DB-LEN.
MOVE W10-USR TO W900-ORA-USER-ARR.
MOVE W10-USR-LEN TO W900-ORA-USER-LEN.
MOVE W10-PWD TO W900-ORA-PASSWD-ARR.
MOVE W10-PWD-LEN TO W900-ORA-PASSWD-LEN.
MOVE W10-DB TO W900-ORA-DB-ARR.
MOVE W10-DB-LEN TO W900-ORA-DB-LEN.
EXEC SQL
CONNECT :w900-USR IDENTIFIED BY :W900-PWD USING :W900-DB
END-EXEC.
DISPLAY 'SQLCODE' sqlcode 'SQLERRM' sqlerrm
EXEC SQL
SELECT dummy INTO :W10-DUMMY FROM dual
END-EXEC.
DISPLAY W10-DUMMY.
END PROGRAM Program1.

The program output "X", i.e. the content of DUMMY in DUAL.

Again, this is maybe not the way you should do it, but it worked. Any suggestions of improvement are welcomed.

0 Likes
10 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Upgrading from Oracle 11g to Oracle 12c

Jump to solution

INT/GNT project types can use either the 32-bit or 64-bit version of the Oracle Client/Pro*COBOL but you have to install the correct one for the bitism of the project you are compiling.

INT/GNT projects can be set to either x86 or x64 platform targets just like native .exe/.dll projects. If you set it to x86 then you need to install the 32-bit Oracle client. If you set it to x64 then you have to install the 64-bit Oracle client.

0 Likes
Jens Forsberg Trusted Contributor.
Trusted Contributor.

Re: Upgrading from Oracle 11g to Oracle 12c

Jump to solution
But "x86" seems to be locked in Visual Studio. I can't find a way to change it. Or are you supposed to edit directly in the cblproj file?
0 Likes
Jens Forsberg Trusted Contributor.
Trusted Contributor.

Re: Upgrading from Oracle 11g to Oracle 12c

Jump to solution

By adding the x64 solution platform in Configuration Manager I was able to select it (it wasnt't there from the start). So now I'm able to build with Oracle 12c. Perfect, thanks.

But of course a new problem arose. I have a simple program with this code in the Procedure Division:

EXEC SQL
    CONNECT :w01-username
    IDENTIFIED BY :w01-password
    USING :w01-db
END-EXEC.

EXEC SQL
    SELECT dummy INTO :w01-dummy FROM dual
END-EXEC.

When I debug and come to the first EXEC SQL statement the program crashes with this message:

    173 Called program file not found in drive/directory: SQLADR

The schema I'm trying to connect to is the HR schema which is included in Oracle 12c. The schema is unlocked and I can connect to it in SQL Developer. The value of w01-db is the same as the SERVICE_NAME in tnsnames.ora, "orcl.lan".

I read an earlier post about this which suggested adding LITLINK or INITCALL in the directives. I tried that, but it didn't help.

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Upgrading from Oracle 11g to Oracle 12c

Jump to solution

For 64-bit .int code you will need to load the Oracle library before you connect so that it can locate the required entry point.

01 pp procedure-pointer.

           set pp to entry "ORASQL12"

If you do this at the beginning of your program that does the connect it should work fine as long as the Oracle bin folder is in the PATH.

 

0 Likes
Jens Forsberg Trusted Contributor.
Trusted Contributor.

Re: Upgrading from Oracle 11g to Oracle 12c

Jump to solution

Yes, now I can connect to the database. Thank you very much.

Unfortunately it now stops on the second EXEC SQL statement and shows the following error:

    114 Attempt to access item beyond bounds of memory: (Signal 11)

Any idea what that might be?

 

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Upgrading from Oracle 11g to Oracle 12c

Jump to solution

An RTS 114 error is a catch-all error that normally has to do with memory corruption of some sort.

Are you certain that the connection was successful, i.e. SQLCODE=0?

Can you execute any other SQL statements without getting a 114 error?

0 Likes
Jens Forsberg Trusted Contributor.
Trusted Contributor.

Re: Upgrading from Oracle 11g to Oracle 12c

Jump to solution

Erm, well, when I check SQLCODE and SQLERRM I see these values:

SQLCODE: -0000000001

SQLERRM: {Length=72} : "8.Error while trying to retrieve text for error ORA-00001. "

So something went wrong during the connection obviously.

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Upgrading from Oracle 11g to Oracle 12c

Jump to solution

It sounds like this is an Oracle setup issue. I Googled it and see several entries like:

Correct the ORACLE_HOME path.

There could be two oracle clients in the system.

I had the same issue, the reason being my ORACLE_HOME was pointed to the oracle installation which was not having the tns.ora file.

Changing the ORACLE_HOME to the Oracle directory which is having the tns.ora solved it.

0 Likes
Jens Forsberg Trusted Contributor.
Trusted Contributor.

Re: Upgrading from Oracle 11g to Oracle 12c

Jump to solution

I finally got it to work. I found some code on the Internet which I "re-arranged" so it suited my program. I will take a deeper look into the code and remove unnecessary parts, if any, and get back and present what worked for me so other members can use it if they want to.

0 Likes
Jens Forsberg Trusted Contributor.
Trusted Contributor.

Re: Upgrading from Oracle 11g to Oracle 12c

Jump to solution

This is what finally worked for me. To be sure it worked and that previous attempts hadn't left any trace on my computer I re-installed Windows 10. After that I did the following:

I downloaded Oracle 12c Standard edition. I chose to use the "Windows built-in account" and to uncheck the "Create as container database" option.

I checked the environment variables and saw that Oracle had added [ORACLE_HOME]\bin to Path.

I checked the Oracle entries in the Registry (the ones I know of), and from what I could see everything looked normal (and why shouldn't it).

I unlocked the HR user. I used SQL Developer for this. There are probably other ways to do it.

I added the HR user in tnsnames.ora, i.e. "HR = (DESCRIPTION = ... (SERVICE_NAME = orcl.lan)))".

I installed Visual Studio Professional 2017 and Visual COBOL.

I created an INT/GNT project and added the x64 Solution platform in Configuration Manager.

I added a cbl file to the project and named it Program1.cbl.

I opened the cblproj file in a text editor and replaced the following line with the lines after that. NOTE: This is probably not how you should do it, but since I have had problems with adding directives to Pro COBOL from within Visual COBOL this is the way I chose. NOTE: The sqldebug and verbose options might be omitted if you don't want the more detailed output.

<Compile Include="Program1.cbl" />

<ItemGroup>
  <Compile Include="Program1.cbl">
    <SQLDirectives>P(cobsql) COBSQLTYPE=ORACLE sqldebug verbose END-COBSQL sqlcheck=full
      comp5=yes picx=varchar2 p(cp) ENDP</SQLDirectives>
  </Compile>
</ItemGroup>

Went back to Program1.cbl and entered the following code.

IDENTIFICATION DIVISION.
PROGRAM-ID. Program1.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 pp PROCEDURE-POINTER.
01 W10-CONNECT.
05 W10-USR PIC X(10).
05 W10-USR-LEN PIC S9(4) COMP-5.
05 W10-PWD PIC X(10).
05 W10-PWD-LEN PIC S9(4) COMP-5.
05 W10-DB PIC X(10).
05 W10-DB-LEN PIC S9(4) COMP-5.
01 W10-DIVERSE.
05 W10-DUMMY PIC X(3).
EXEC SQL include sqlca END-EXEC.
EXEC SQL begin declare section END-EXEC.
01 W900-CONNECT.
05 W900-USR PIC X(10) VARYING.
05 W900-PWD PIC X(10) VARYING.
05 W900-DB PIC X(10) VARYING
EXEC SQL end declare section END-EXEC.
PROCEDURE DIVISION.
SET pp TO ENTRY "orasql12".
MOVE 'hr' TO W10-USR.
MOVE 2 TO W10-USR-LEN.
MOVE 'hr' TO W10-PWD.
MOVE 2 TO W10-PWD-LEN.
MOVE 'HR' TO W10-DB.
MOVE 2 TO W10-DB-LEN.
MOVE W10-USR TO W900-ORA-USER-ARR.
MOVE W10-USR-LEN TO W900-ORA-USER-LEN.
MOVE W10-PWD TO W900-ORA-PASSWD-ARR.
MOVE W10-PWD-LEN TO W900-ORA-PASSWD-LEN.
MOVE W10-DB TO W900-ORA-DB-ARR.
MOVE W10-DB-LEN TO W900-ORA-DB-LEN.
EXEC SQL
CONNECT :w900-USR IDENTIFIED BY :W900-PWD USING :W900-DB
END-EXEC.
DISPLAY 'SQLCODE' sqlcode 'SQLERRM' sqlerrm
EXEC SQL
SELECT dummy INTO :W10-DUMMY FROM dual
END-EXEC.
DISPLAY W10-DUMMY.
END PROGRAM Program1.

The program output "X", i.e. the content of DUMMY in DUAL.

Again, this is maybe not the way you should do it, but it worked. Any suggestions of improvement are welcomed.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.