Using The Sqlplus Copy Command To Populate An Oracle Table From A Non Oracle Table Gives Error Ora-3114
(Doc ID 1414148.1)
Last updated on MAY 11, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Goal
Using Database Gateway for ODBC (DG4ODBC) and the SQLPLUS copy command to populate an Oracle table from a non identical table on a non Oracle database gives the error ORA-3114
SQL> copy from user/password@tnsalias-
> INSERT beiyi_test1(OBJECTID,FIRST_NAME,LAST_NAME,MIDDLE,FACILITY_CODE,-
> DEPARTMENT_NAME,EMPLOYEE_ID,JOB_NUMBER,DEPT,FUND,APPROP) -
> USING SELECT to_number(P.EINO_10171 || J.JOB_NO_13009), P.FNAME_10004, -
> P.LNAME_10003,P.MI_10005,J.FACIL_CD_13028, -
> to_char(J.DEPT_CD_13010),P.EINO_10171,J.JOB_NO_13009, -
> J.DEPT_CD_13010, J.FUND_13016,J.APPROP_13017 -
> FROM JOB_DS@DG4ODBC1 J, PERSONNEL_DS@DG4ODBC1 P, PHYSLOC_DS@DG4ODBC1 L -
> WHERE J.ACT_INACT_13052 = 'A' AND J.SSNO_13001 = P.SSNO_10001 -
> AND J.FACIL_CD_13028 = L.FACIL_CD_28001;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
ERROR:
ORA-03114: not connected to ORACLE
> INSERT beiyi_test1(OBJECTID,FIRST_NAME,LAST_NAME,MIDDLE,FACILITY_CODE,-
> DEPARTMENT_NAME,EMPLOYEE_ID,JOB_NUMBER,DEPT,FUND,APPROP) -
> USING SELECT to_number(P.EINO_10171 || J.JOB_NO_13009), P.FNAME_10004, -
> P.LNAME_10003,P.MI_10005,J.FACIL_CD_13028, -
> to_char(J.DEPT_CD_13010),P.EINO_10171,J.JOB_NO_13009, -
> J.DEPT_CD_13010, J.FUND_13016,J.APPROP_13017 -
> FROM JOB_DS@DG4ODBC1 J, PERSONNEL_DS@DG4ODBC1 P, PHYSLOC_DS@DG4ODBC1 L -
> WHERE J.ACT_INACT_13052 = 'A' AND J.SSNO_13001 = P.SSNO_10001 -
> AND J.FACIL_CD_13028 = L.FACIL_CD_28001;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
ERROR:
ORA-03114: not connected to ORACLE
When the column called LAST_NAME is dropped from the copy statement then the copy works -
SQL> copy from user/password@tnsalias -
> INSERT beiyi_test1(OBJECTID,FIRST_NAME,MIDDLE,FACILITY_CODE,-
> DEPARTMENT_NAME,EMPLOYEE_ID,JOB_NUMBER,DEPT,FUND,APPROP) -
> USING SELECT to_number(P.EINO_10171 || J.JOB_NO_13009), P.FNAME_10004, -
> P.MI_10005,J.FACIL_CD_13028, -
> to_char(J.DEPT_CD_13010),P.EINO_10171,J.JOB_NO_13009, -
> J.DEPT_CD_13010, J.FUND_13016,J.APPROP_13017 -
> FROM JOB_DS@DG4ODBC1 J, PERSONNEL_DS@DG4ODBC1 P, PHYSLOC_DS@DG4ODBC1 L -
> WHERE J.ACT_INACT_13052 = 'A' AND J.SSNO_13001 = P.SSNO_10001 -
> AND J.FACIL_CD_13028 = L.FACIL_CD_28001;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
3895 rows selected from ims@scgis.
3895 rows inserted into BEIYI_TEST1.
3895 rows committed into BEIYI_TEST1 at DEFAULT HOST connection.
SQL>
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |