Version 21 p_serial_44: Oracle error: fetch z70 error
- Article Type: General
- Product: Aleph
- Product Version: 20
Description:
I am doing some testing in version 21. When I run the serial-44 it completes with a status of "done with errors".
The error that shows in the log is
Oracle error: fetch z70
ORA-01007: variable not in select list
It does produce a printout, which seems to be accurate. What does that error mean and how do we fix it?
Note: We are multi-ADM and have "tiered vendors", that is, our z70 records reside in the shared sys50 library rather than the individual ADM libraries.
Resolution:
[This Resolution is the same as that for KB 16384-47617, which is a similar problem.]
I find that I get the
Oracle error: fetch z70
ORA-01007: variable not in select list
in util f/4 for sys50 z70.
In SQL, we see:
sys50@ALEPH1> desc z70
Name Null? Type
----------------------------------------- -------- ----------------------------
Z70_REC_KEY NOT NULL CHAR(25)
Z70_REC_KEY_2 CHAR(35)
Z70_EDI_VENDOR_CODE_TYPE CHAR(3)
Z70_EDI_FOR_ORDER_ONLY CHAR(1)
Z70_NAME_KEY CHAR(40)
Z70_SUB_LIB_KEY CHAR(5)
Z70_ALPHA CHAR(1)
Z70_VENDOR_NAME VARCHAR2(150)
Z70_CONTACT_1 VARCHAR2(200)
Z70_CONTACT_2 VARCHAR2(200)
Z70_CONTACT_3 VARCHAR2(200)
Z70_CONTACT_4 VARCHAR2(200)
Z70_CONTACT_5 VARCHAR2(200)
Z70_COUNTRY CHAR(10)
Z70_MATERIAL_TYPE VARCHAR2(20)
Z70_NOTE VARCHAR2(200)
Z70_TERMS_SIGN CHAR(1)
Z70_TERMS_PERCENT CHAR(5)
Z70_DELIVERY_TYPE_1 CHAR(2)
Z70_DELIVERY_TYPE_2 CHAR(2)
Z70_DELIVERY_TYPE_3 CHAR(2)
Z70_DELIVERY_TYPE_4 CHAR(2)
Z70_DELIVERY_TYPE_5 CHAR(2)
Z70_DELIVERY_DELAY_1 NUMBER(3)
Z70_DELIVERY_DELAY_2 NUMBER(3)
Z70_DELIVERY_DELAY_3 NUMBER(3)
Z70_DELIVERY_DELAY_4 NUMBER(3)
Z70_DELIVERY_DELAY_5 NUMBER(3)
Z70_ACCOUNT_M VARCHAR2(15)
Z70_ACCOUNT_S VARCHAR2(15)
Z70_VENDOR_ACCOUNT VARCHAR2(50)
Z70_OPEN_DATE NUMBER(8)
Z70_UPDATE_DATE NUMBER(8)
Z70_STATUS CHAR(2)
Z70_CON_LNG CHAR(3)
Z70_CURRENCY_1 CHAR(3)
Z70_CURRENCY_2 CHAR(3)
Z70_CURRENCY_3 CHAR(3)
Z70_CURRENCY_4 CHAR(3)
Z70_DEFAULT_ORDER_DELIVERY CHAR(2)
Z70_LE_LETTER_TYPE NUMBER(2)
Z70_ED_LETTER_TYPE NUMBER(2)
Z70_LI_LETTER_TYPE NUMBER(2)
Z70_LE_SEND_METHOD CHAR(5)
Z70_ED_SEND_METHOD CHAR(5)
Z70_LI_SEND_METHOD CHAR(5)
Z70_ADDITIONAL_VENDOR_CODE CHAR(20)
Z70_PROVIDER_TYPE CHAR(3)
Z70_PLAIN_HTML CHAR(1)
Z70_CATALOGER CHAR(10)
Z70_LOCATE_BASE VARCHAR2(20)
Comparing this to the ./alephm/sql_tab/z70_create.sql (and to the usm50 z70), we see that the sys50 z70 lacks the following column:
Z70_UPD_TIME_STAMP CHAR(15)
-- a column which was supposed to have been added by the 20-21 Upgrade Express. I find in the ./log.20120522.140639/1022.log that sys50 was *not* included as an ADM library in the "Added field Z70_UPD_TIME_STAMP to Z70 in ADM libraries" processing.
I see the following in both the v20 and the v21 sys50 tab100:
! DOC-TYPE-ADM=Y
! Changed to test JBOSS
It seems to me that, since sys50 has the ADM table z70, it needs to be treated as an ADM library -- at least in running Upgrade Express.
I suggest that you:
1. Uncomment the DOC-TYPE-ADM=Y line in sys50 tab100 and
2. rerun Upgrade Express Step 1022
This should update the sys50 z70 properly.
If you find that sys50 works OK otherwise with DOC-TYPE-ADM=Y commented out, you may want to consider re-commenting it out after the above is completed.
- Article last edited: 10/8/2013