IV77898: IO issue with the script that is generated for the table


We found that the script that is automatically created for outbound integration objects to create the table is improperly formed. After creating the IO project and testing the DB connection, identify the Table Name you want to use. Then click Generate SQL for Table. The format of that table is as follows. Note the lack of spaces (underscores) between Last Name, First Name and Work Phone. There also is an issue with the ! field.

-- Auto generated script for ORACLE. CREATE TABLE jso_tripeople
( TRIRIGA_RECORD_ID VARCHAR2(1000) ,
TRANSACTION_ID VARCHAR2(1000) ,
! VARCHAR2(1000) ,
LAST NAME VARCHAR2(1000) ,
FIRST NAME VARCHAR2(1000) ,
ID VARCHAR2(1000) , COMPANY VARCHAR2(1000) ,
WORK PHONE VARCHAR2(1000) ,
MOBILE VARCHAR2(1000) ,
EMAIL VARCHAR2(1000) )

As a temporary fix, change the script by adding an underscore (_) between First Name, Last Name, and Work Phone, and change the exclamation point (!) to the word Attention. Execute the script in SQL Developer.

CREATE TABLE xxx_tripeople (
TRIRIGA_RECORD_ID VARCHAR2(1000) ,
TRANSACTION_ID VARCHAR2(1000) ,
Attention VARCHAR2(1000) ,
LAST_NAME VARCHAR2(1000) ,
FIRST_NAME VARCHAR2(1000) ,
ID VARCHAR2(1000) ,
COMPANY VARCHAR2(1000) ,
WORK_PHONE VARCHAR2(1000) ,
MOBILE VARCHAR2(1000) ,
EMAIL VARCHAR2(1000)
)

The query report labels are what controls external table column names. The integration object was designed to use the exact report label as the external table column name, and is not designed to adjust for spaces, punctuation, etc. The documentation does touch on this, but it was noticed the doc is missing the word “column”. The integration developer needs to ensure the query report labels of the integration object outbound query has no spaces, punctuation, etc. The documentation will be updated so that it is clear that the exact report labels are used as the external table column names, so they must be valid SQL column names (no spaces, special characters, start with a letter, etc.).

Continue reading

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s