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 FEBRUARY 23, 2012

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.3 - Release: 11.1 to 11.2
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 ims/ims0311@scgis -
> 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 ims/ims0311@scgis -
> 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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms