Skip Headers

Oracle Access Manager Installation and User's Guide
Release 9.2.0.1.0 for IBM AS/400

Part Number A97616-01
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

8
Using Oracle Access Manager

To use Oracle Access Manager for AS/400, you need to understand the following topics:

Connecting to the Oracle Server

When the network definition is completed, you can connect to Oracle from interactive SQL or your precompiled AS/400 program.

Connecting through the STRSQL (Interactive SQL) Commands

From the AS/400 command line, enter:

STRSQL

Ensure that your interactive STRSQL session uses the SQL syntax library.file.  To ensure this, use STRSQL NAMING(*SQL).  Oracle Access Manager does not support AS/400 library/file syntax.

From the interactive SQL command line, enter the IBM DB2/400 CONNECT TO command to connect to the Oracle server:

CONNECT TO remote_oracle USER userid USING 'pw'

where:

If no userid is specified, Oracle Access Manager uses the default userid and password.  The default userid and password are SCOTT/TIGER.  You can change these with the CHGSQLDFLT command.  Refer to Chapter 6, "Configuring Oracle Access Manager" for more information.

After you are connected, you can issue SQL statements to the remote Oracle server.


Note:

Interactive SQL sends a CREATE TABLE xxx command to Oracle Access Manager for each connection.  Consequently, the job log contains the following message, which can be ignored:

ORA-00901: invalid CREATE command

Connecting from an AS/400 Precompiled Application

Three sample programs are distributed with Oracle Access Manager.  They are SAMP1C, SAMP1CBL, and SAMP1RPG.  Use the CRTORAPKG command to precompile, compile, and bind these programs.  Refer to Chapter 6, "Configuring Oracle Access Manager" for more information about the CRTORAPKG command.

After using the CRTORAPKG command, you can execute the sample programs using the CALL command from the AS/400 command line.  For example:

CALL SAMP1RPG

Each program explicitly connects to a server called GENERIC using an Oracle user name of SCOTT and a password of TIGER.  You can edit these sample programs if you want to test them against a different server or use a different username or password.

After execution, you can issue a SELECT statement for the DEPT table through interactive SQL (STRSQL) to verify that the rows were inserted successfully.

These sample programs (included in Appendix A, "Sample Programs") are in the Oracle Access Manager installation library in the following files:

Table 8-1 Sample Program Files  
Program File

SAMP1C

QSQLCSRC

SAMP1CBL

QCBLLESRC

SAMP1RPG

QRPGLESRC

Connection Management

Connection management consists of connecting to and disconnecting from one or more servers, and using COMMIT and ROLLBACK to coordinate transactions.

Oracle Access Manager connects to an Oracle server using the DB2/400 CONNECT syntax.  The Oracle CONNECT syntax is not supported by Oracle Access Manager.

An application can connect to an Oracle server explicitly or implicitly.  Explicit connection occurs when the DB2/400 CONNECT TO statement is issued.  Implicit connection occurs when the first SQL statement in an application program is not a CONNECT TO statement.  If this occurs, the connection is made to the server specified on the RDB parameter in the IBM CRTSQLxxx command.

After a connection is established, the server becomes active, and all SQL statements are processed on that server.

Oracle Access Manager imposes no restrictions on the number of connections that can be opened at one time.  In a single session, you can connect to multiple Oracle servers and multiple IBM DRDA application servers.  When multiple servers are connected, you can switch between servers using the DB2/400 SET CONNECTION command.

For example:

CONNECT TO LOCAL
INSERT INTO DEPT VALUES (50, 'LOCAL', 'ROW 1')
CONNECT TO ORACLE1 USER SCOTT USING 'TIGER' 
INSERT INTO DEPT VALUES (50, 'ORACLE1', 'ROW 1')
CONNECT TO ORACLE2 USER JOHN USING 'SMITH' 
INSERT INTO DEPT VALUES (50, 'ORACLE2', 'ROW 1')
SET CONNECTION ORACLE1 
INSERT INTO DEPT VALUES (50, 'ORACLE1', 'ROW 2')
SET CONNECTION LOCAL 
INSERT INTO DEPT VALUES (50, 'LOCAL', 'ROW 2')

If multiple connections are open and a COMMIT or ROLLBACK statement is issued, all database servers are requested to COMMIT or ROLLBACK.

You cannot have multiple connections open to the same RDB name within a single session.  For example, the following would be invalid:

CONNECT TO ORACLE1 USER X USING 'PWDX'
CONNECT TO ORACLE1 USER Y USING 'PWDY'

All applications, including interactive SQL (STRSQL), keep all connections open until you do one of the following:

Exiting from Interactive SQL (STRSQL) does not affect the status of any connections that were obtained (and are still active) within Interactive SQL.  All Oracle server connections that are left open are available on subsequent invocations of Interactive SQL within the same job.  However, exiting from a program that uses embedded SQL in the EXEC SQL format will cause any Oracle server connection that was created within that program to be released.


Note:

The DB2/400 RELEASE command followed by a ROLLBACK statement does not end the connection.


Oracle Access Manager connects to an Oracle server using CONNECT type 5 and establishes a protected conversation, as documented in the IBM DB2/400 SQL Programming Guide.


Note:

Because Oracle Access Manager runs as a protected conversation, an attempt to use the DISCONNECT command results in the following message for both TCP/IP and APPC/LU6.2 connections:

SQL0858 "Cannot disconnect relational database
<server_name> due to LU6.2 protected conversation"

Describing Tables

Interactive SQL (STRSQL) does not allow the DESCRIBE TABLE to be issued interactively.  However, column and datatype information can be obtained through interactive SQL (STRSQL) by using the prompt option (PF4), when issuing a SELECT from the command line.  This information can be used by application programmers to understand how Oracle datatypes are mapped to AS/400 datatypes.

Stored Procedures

On the AS/400, you call a stored procedure by using the IBM DB2/400 CALL statement.  The CALL statement can be executed interactively from interactive SQL or embedded in an application within the appropriate "EXEC-SQL END-EXEC" delimiters for the host language.

Oracle Access Manager allows access from AS/400 applications to Oracle stored procedures or packages by providing mapping from the IBM CALL statement to an Oracle stored procedure.

The results of a CALL command can be committed or rolled back.  Oracle Access Manager always assumes that an invocation of stored procedures has changed something on the Oracle server.

Advantages of Stored Procedures

As defined on the AS/400, a stored procedure is a programming construct that can be called from within the SQL environment to perform a set of operations.  The operations can include host language statements, SQL statements, or logic.  When connected to an Oracle server, the host language statements are packaged within a PL/SQL stored procedure on the Oracle server.

Stored procedures in PL/SQL provide the same benefits as procedures in a host language such as C or COBOL.  A PL/SQL procedure that is stored in the Oracle database can be called from several programs.  The use of stored procedures can also enhance the performance of a distributed application.  For example, assume that you want to execute several SQL statements at an Oracle server using interactive SQL.  If stored procedures are not used, then the AS/400 must send a separate request to the Oracle server for each SQL statement.  If the same SQL statements are stored in a stored procedure at the Oracle server, then a single CALL statement on the AS/400 executes the entire block of SQL statements.

Stored Procedure Parameters

Parameters that are used only to pass data to a stored procedure are called IN parameters.  Constant values used as parameters can be used only as IN parameters.  Parameters that are used only to return data from a stored procedure are called OUT parameters.  Parameters that can be used in both directions are called IN OUT parameters.  You must use a host variable to have data returned from an Oracle stored procedure.

Calls to a stored procedure on the Oracle server from within an AS/400 application can use IN, OUT, and IN OUT parameters.  This enables parameters to be passed to the stored procedure and data to be returned.  Calls to a stored procedure from within the interactive SQL environment can only use IN parameters.  You can pass constant parameters to the stored procedure on the Oracle server, but no data can be returned.

Declaring Procedures

When calling an Oracle stored procedure, you must use the DB2/400 DECLARE PROCEDURE statement.  This statement is used to specify the IN, OUT, or IN OUT capability and the datatype of each parameter.  For example:

DECLARE Sample PROCEDURE (IN :arg1 INTEGER, IN OUT :arg2 CHAR(20), 
       OUT :arg3 SMALLINT) (LANGUAGE C SIMPLE CALL) 


Caution:

If a DECLARE PROCEDURE statement is not found for a CALL to a stored procedure, then each parameter defaults to IN OUT, and the datatype for each parameter defaults to the datatype of the associated host variable.


Ensure that the datatypes specified in the DECLARE PROCEDURE statement are compatible with those found in the stored procedure specification of the Oracle stored procedure.  In addition, the input or output modes of each parameter (specified by IN, OUT, or IN OUT in the DECLARE PROCEDURE statement) and the number of parameters must match those of the Oracle stored procedure.

The following example is an extract from an AS/400 C program that calls the Oracle stored procedure GETRESULTS.  The datatypes of parameters match, the input and output modes match, and the number of parameters match (nine parameters are in the DECLARE PROCEDURE statement on the AS/400, in the CALL to the procedure, and in the declaration of the Oracle stored procedure).

EXEC SQL BEGIN DECLARE SECTION; 
char p1[10], p3[10], p5[10]; 
struct {short len; char value[20];} p1a;  /* A VARCHAR */ 
struct {short len; char value[20];} p3a;  /* A VARCHAR */ 
struct {short len; char value[20];} p5a;  /* A VARCHAR */ 
long p2, p4, p6; 
EXEC SQL END DECLARE SECTION; 
. . . . . 
EXEC SQL DECLARE GETRESULTS PROCEDURE 
(IN :par1 CHAR(10), IN :par1a VARCHAR(20), IN :par2 INTEGER, 
 INOUT :par3 CHAR(10), INOUT :par3a VARCHAR(20), 
 INOUT :par4 INTEGER, 
 OUT :par5 CHAR(10), OUT :par5a VARCHAR(20), 
 OUT :par6 INTEGER) 
( LANGUAGE C SIMPLE CALL); 
EXEC SQL CALL GETRESULTS(:p1, :p1a, :p2, :p3, :p3a, :p4, :p5, :p5a,
                         :p6);
. . . . . 
The following is the file "getresults.sql" on the ORACLE server. 
create or replace procedure GETRESULTS 
(A1   IN     char := 'DefA1', A1A IN     VARCHAR2, A2 IN     NUMBER, 
 A3   IN OUT char, A3A IN OUT VARCHAR2, A4 IN OUT NUMBER, 
 A5   OUT    char, A5A OUT    VARCHAR2, A6 OUT    NUMBER) 
IS 
BEGIN 
 A5 := A3; A5A := A3A; A6 := A4; 
 A3 := A1; A3A := A1A; A4 := A2; 
END; 
/ 
~ 

Conversion of Oracle Datatypes to DB2/400 Datatypes

Oracle Access Manager for AS/400 converts Oracle datatypes to DB2/400 datatypes to allow application host variables to store and represent the Oracle data they receive into a format native to the AS/400 environment.

The following table describes these datatype conversions.  In the table, "p" refers to precision, and "s" refers to scale.  On DB2/400, VARCHAR can contain 32 740 characters at most.

Table 8-2 Oracle datatypes mapped to DB2/400 datatypes  
Oracle Server Datatype DB2/400 Datatype

CHAR

CHAR

DATE

DATE

LONG

VARCHAR

LONG RAW

VARCHAR

NUMBER(p,s)

SMALLINT 1<=p<=4, s=0

INTEGER 5<=p<=9, s=0

DECIMAL 10<=p<=31, s=0

or

s<>0, p<=31

FLOAT anything else

RAW

VARCHAR

VARCHAR2

VARCHAR

ROWID

VARCHAR

LONG datatypes or combinations of LONG and LONG RAW datatypes are limited by the DB2/400 restriction that the entire length of data returned for a row must be less than 32 767 bytes.  This restricts LONG and LONG RAW datatypes to a maximum of 32 740 bytes for all the LONG and LONG RAWs returned in a row.

Message and Error Code Processing

Oracle Access Manager communicates all error conditions to the AS/400 application program through the SQLCA.  Because Oracle error codes differ from DB2/400 error codes, a one-to-one mapping is not possible for all error conditions.  Therefore, Oracle Access Manager uses the SQLSTATE field of the SQLCA as a mechanism to communicate Oracle errors to DB2/400.

When a SQLSTATE other than 00000 is returned from Oracle Access Manager, DB2/400 attempts to map the SQLSTATE to a DB2/400 SQLCODE and message.  If it is unable to recognize the SQLSTATE, DB2/400 issues the common message for that class code.  For a list of SQLSTATE class codes, refer to the IBM DB2/400 SQL programming guide.

In some situations, Oracle Access Manager translates a SQLSTATE value into a valid DB2/400 SQLCODE.  This is called a mapped error code.  When mapping occurs, a valid DB2/400 SQLCODE is returned, and all applicable substitution tokens are placed in the SQLERRMC field of the SQLCA.  This enables you to see valid text in the replacement variables of the DB2/400 SQL error message.  Oracle Access Manager currently maps these error codes as indicated in the following table:

Table 8-3 Error Code Mapping  
Oracle Error Code DB2/400 SQLSTATE DB2/400 SQLCODE

ORA-0904 invalid column name

42703

-206

ORA-0932 inconsistent datatypes

42884

-440

ORA-0936 missing expression

42601

-104

ORA-00901 invalid create command - 104

42601

-104

ORA-00911 invalid character

42601

-7

ORA-00923 from keyword not found - 104

42601

-104

ORA-00942 table or view does not exist

42704

-204

ORA-01095 DML statement process 0 rows

02000

+100

ORA-01400 cannot insert NULL into string

23502

-407

ORA-01401 inserted value too large for column

22001

-404

ORA-01403 no data found

02000

+100

ORA-01405 fetched column value is NULL

22002

-305

ORA-01406 fetched column value was truncated

22001

-404

ORA-01422 exact fetch returns more than requested number of rows

21000

-811

ORA-01424 missing or illegal character following the escape character

22025

-130

ORA-01425 escape character must be character string of length 1

22019

-130

ORA-01427 single row subquery returns more than one row

21000

-811

ORA-01438 value larger than specified precision for this column

22001

-404

ORA-01455 converting column overflows integer datatype

22003

-304

ORA-01457 converting column overflows decimal datatype

22003

-304

ORA-01476 divisor is equal to zero

22012

-802

ORA-01479 last character in buffer is not NULL

22024

-302

ORA-01480 trailing null missing from STR bind variable

22024

-302

ORA-01488 invalid nibble or byte in input data

22023

-302

ORA-12154 TNS: could not resolve service name

08003

-30000

ORA-12533 TNS: illegal ADDRESS parameters

08003

-30000

ORA-12541 TNS: no listener

08003

-30000

ORA-12154 TNS: could not resolve service name

08003

-30000

ORA-12560 TNS: protocol adapter error

08003

-30000

If DB2/400 does not recognize a SQLSTATE, then the following message is issued:

SQL7940 SQLSTATE class unknown, SQLSTATE xxxxx...

Examine the job log for the relevant Oracle error that caused the SQLSTATE xxxxx to be issued.

If Oracle Access Manager is unable to map an error code, then it returns a SQLCODE of -969 and a SQLSTATE which applies to the class code of the error.  For example, all syntax errors have a SQLSTATE class code of 42.  Therefore, if Oracle Access Manager receives a syntax parsing error from the Oracle server (which it cannot map), then it returns SQLCODE of -969 and SQLSTATE 42000.

If you are unable to determine the root cause of the error based on the SQLCODE and SQLSTATE returned to the application, then check the job log for more details.  All Oracle errors and message text are displayed in the job log at the time that the error occurs.


Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
Go To Table Of Contents
Contents
Go To Index
Index