Problem:

I have installed Oracle 11g Express and Pro*COBOL precompiler to test this COBOL program with embedded SQL that uses the SCOTT schema:

IDENTIFICATION DIVISION.

       PROGRAM-ID.  ORATEST.

       ENVIRONMENT DIVISION.

       DATA DIVISION.

       WORKING-STORAGE SECTION.

           EXEC SQL INCLUDE SQLCA END-EXEC

           EXEC SQL BEGIN DECLARE SECTION END-EXEC.

           01  USERNAME   PIC X(10).

           01  PASSWD     PIC X(10).

           EXEC SQL END DECLARE SECTION END-EXEC.

       PROCEDURE DIVISION.

           LOGON.

                   MOVE "SCOTT" TO USERNAME

                   MOVE "TIGER" TO PASSWD

                   EXEC SQL

                       CONNECT

                       :USERNAME IDENTIFIED BY :PASSWD

                   END-EXEC.

                   DISPLAY SQLCODE

                   STOP RUN.

However when I run my program I receive an error ORA-01017: invalid username/password; logon denied, what is wrong?

Resolution:

The SCOTT schema contains a number of tables that can be used for testing purposes, and needs to be installed.

The follow these steps to install the schema:

• Open a command prompt
• Change directory to C:\\oraclexe\\app\\oracle\\product\\11.2.0\\server\\rdbms\\admin
  this directory contains a number of SQL scripts including SCOTT.SQL
• Start SQLPlus as follows:
  
  SQLPLUS /AS SYSDBA (this logs onto the database as SYSDBA in order to carry out some admin tasks :
  
  

• Having successfully logged on, the SCOTT.SQL script needs to be run as follows:

  @SCOTT
  
  
  

• Now check the tables exist by running the following query:

  SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER=’SCOTT’
  
  
  

Now run the program again - successfully: