You can translate the question and the replies:

Error while inserting data with bulk load activated on Oracle Data source

Hi, I’m making tests on data file load using Denodo Express 8.0 for Windows 64 bits. I have an Oracle instance running on the same computer. I run catldr.sql as sysdba on the Oracle database. I created a data source (dstrcr) and activated "Use Bulk Data Load APIs". I created a very simple table on Oracle and a base view on this table. When i try to insert data in the base view with the request "insert into bvtrcrtmppsa select 'test' from dual()" i have the error : Finished with error: Error executing data movement from view pdual3818fcb2-25ac-4bfc-8837-0fd9b44dd470 to source dstrcr Exception of plan pdual3818fcb2-25ac-4bfc-8837-0fd9b44dd470: com.denodo.vdb.util.tablemanagement.TableManagerException: Error executing sqlldr command Can you help me ? Here are the vdp script : CREATE OR REPLACE DATASOURCE JDBC ds_trcr DRIVERCLASSNAME = 'oracle.jdbc.OracleDriver' DATABASEURI = 'jdbc:oracle:thin:@###' USERNAME = '###' USERPASSWORD = '###' ENCRYPTED CLASSPATH = 'oracle-12c-v12.2.0.1' DATABASENAME = 'oracle' DATABASEVERSION = '12c' FETCHSIZE = 1000 VALIDATIONQUERY = 'SELECT COUNT(*) FROM SYS.DUAL' INITIALSIZE = 4 MAXIDLE = -1 MINIDLE = 0 MAXACTIVE = 20 EXHAUSTEDACTION = 1 TESTONBORROW = true TESTONRETURN = false TESTWHILEIDLE = false TIMEBETWEENEVICTION = -1 NUMTESTPEREVICTION = 3 MINEVICTABLETIME = 1800000 POOLPREPAREDSTATEMENTS = false MAXOPENPREPAREDSTATEMENTS = -1 PROPERTIES ('includeSynonyms'='false', 'remarksReporting'='false') DATA_LOAD_CONFIGURATION ( BATCHINSERTSIZE = 200 BULK_LOAD_CONFIGURATION ( SQLLDR_EXECUTABLE_LOCATION = 'C:\app\###\virtual\product\12.2.0\dbhome_1\bin\sqlldr.exe' ) USEEXTERNALTABLES ( ONMOVEREAD = false, ONMOVEWRITE = true ) ); CREATE OR REPLACE WRAPPER JDBC bv_trcr_tmp_psa DATASOURCENAME=ds_trcr SCHEMANAME='###' RELATIONNAME='TMP_PSA' OUTPUTSCHEMA ( c1 = 'C1' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='10', sourcetypeid='12', sourcetypename='VARCHAR2') SORTABLE ); CREATE OR REPLACE TABLE bv_trcr_tmp_psa I18N fr_euro ( c1:text (sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '10') ) CACHE OFF TIMETOLIVEINCACHE DEFAULT ADD SEARCHMETHOD bv_trcr_tmp_psa( I18N fr_euro CONSTRAINTS ( ADD c1 (any) OPT ANY ) OUTPUTLIST (c1 ) WRAPPER (jdbc bv_trcr_tmp_psa) );
user
15-02-2021 13:30:39 -0500

3 Answers

Hi, I was able to reproduce the same error when I executed an insert query “insert into <view_name> select ‘test’ from dual()”. The error occurs due to the use of “**DUAL()**” which is a Denodo predefined stored procedure. Hence, I was not able to insert data into the table of the oracle database using the “**DUAL()**” stored procedure. To insert data into Oracle through the corresponding base view, I would use any one of the following statements in the VQL shell of the Virtual DataPort Administration Tool : * I would insert the data using the insert statement as “**insert into <view_name> values(<column_value1>,<column_value2>)**" * On the other hand, I would use a valid select statement of the Denodo view with the required columns in the insert statement. For instance, I would specify “**insert into <view_name> select <column_name> from <view_name>"**. For more information, you can refer to [**Oracle**](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/bulk_data_load/oracle) of the Virtual DataPort Administration guide. Hope this helps!
Denodo Team
16-02-2021 07:47:30 -0500
Hi, I still have a problem. The first method works fine : insert into bvtrcrtmppsa values('TT'); --> 1 rows affected. The second method does not work. I have a base view bv_trcr_tmp_psa_sour on a table with 100 rows. When i execute the query : insert into bvtrcrtmppsa select C1 from bvtrcrtmppsasour; I have the error : Finished with error: Error executing data movement from view ppa159660c-786e-421a-b529-206bff76c8fddfe9465a-be92-4cb5-b6c566ffe0-014f-4ea9-aa5a-c3a6a99c7770 to source dstrcr Exception of plan ppa159660c-786e-421a-b529-206bff76c8fddfe9465a-be92-4cb5-b6c566ffe0-014f-4ea9-aa5a-c3a6a99c7770: com.denodo.vdb.util.tablemanagement.TableManagerException: Error executing sqlldr command Can you help me ? Here is the creation of bv_trcr_tmp_psa_sour : CREATE OR REPLACE WRAPPER JDBC bv_trcr_tmp_psa_sour DATASOURCENAME=ds_trcr SCHEMANAME='TRCR' RELATIONNAME='TMP_PSA_SOUR' OUTPUTSCHEMA ( c1 = 'C1' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='42', sourcetypeid='12', sourcetypename='VARCHAR2') SORTABLE ); CREATE OR REPLACE TABLE bv_trcr_tmp_psa_sour I18N fr_euro ( c1:text (sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '42') ) CACHE OFF TIMETOLIVEINCACHE DEFAULT ADD SEARCHMETHOD bv_trcr_tmp_psa_sour( I18N fr_euro CONSTRAINTS ( ADD c1 (any) OPT ANY ) OUTPUTLIST (c1 ) WRAPPER (jdbc bv_trcr_tmp_psa_sour) );
user
17-02-2021 10:19:15 -0500
Hi, I am able to insert the data using the following SQL statement sucessfully. Assume my target table(tmp_psa) has one field and its name is c1. ``` insert into tmp_psa select c1 from tmp_psa_source; ``` Usually, the error you are experiencing will occur, only when the field name in the select statement is different from the target field name in the insert statement. Suppose, if it is different in your case then you could use the alias name in the select statement to solve this error as shown below. ``` insert into tmp_psa select storeid AS c1 from tmp_psa_source; ``` Also, I would suggest you to check the **VDP.log** file for the detailed description for the error. If the issue persists and you are a valid support user, you may open a Support Case at the [Denodo Support Site](https://support.denodo.com/) and the Support Team will assist you. Hope this helps!
Denodo Team
05-03-2021 03:27:34 -0500
You must sign in to add an answer. If you do not have an account, you can register here