Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Robert.

Asked: November 08, 2011 - 8:17 pm UTC

Last updated: December 12, 2014 - 10:31 am UTC

Version: 10.2g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have been using the following table. As you can see from the query, it has grown fairly large. I've given examples of some of the data (you'll see in a moment why only the first 10):

SQL> describe assignment_questions_v2;
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_ID       NOT NULL VARCHAR2(40)
 ASSIGNMENT_ID       NOT NULL NUMBER(38)
 QLIST          CLOB

SQL> select count(1) from assignment_questions_v2;

  COUNT(1)
----------
     26722

SQL> select user_id, assignment_id, qlist from (select user_id, assignment_id, qlist, row_number() over (order by assignment_id, user_id) row_num from assignment_questions_v2) where row_num <= 10;

USER_ID      ASSIGNMENT_ID
---------------------------------------- -------------
QLIST
--------------------------------------------------------------------------------
student1       1200
102:103:104:105:125:1153:1418:1820:1877:1715:1747:296:297:654

student2       1200
102:103:104:105:125:1153:1418:1820:1877:1715:1747:296:297:654

student2       2853
2484:2485:2486:2487:2488:2489:2490:2491:2492:2493:2494:2495:2498:2499:2500:2501:

student1       2864
131:132:959

bartajan       2954
2792:2793:2794:2795:2796:2797:2798:2799:2800:2801:2802:2803:2804:2805:2806:2886:

bartajan       2959
190:191:192:193:194:195:196:197:198:199:200:313:697:698:711:2386:2387:2388:2389:

dennisandy       2961
3014:3015:3016:3017:3018:3019:3020:3021:3022:3023:3024:3025:3026

dennisandy       2962
3029:3030:3031:3032:3033:3034:3035:3036:3041:3051:3052:3064:3066:3067:3068:3069:

pfnm745        2964
3:5:14:1333:1334:3121:3122:3123:3124:3125:12

dennisandy       2965
1:2:4:6:7:12:15:1335:1336:483:484:485:487:2542:2543:2544:2545:2546:2547:2548:254


Following your comment to me that I am violating database principles by concatenating question numbers within a single field, I decided that this table would be a good one to experiment with breaking up the data into separate records. So I created this new table:


SQL> describe assignment_questions_v3;
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 STUDENT_ID       NOT NULL VARCHAR2(40)
 HW_ID        NOT NULL NUMBER(38)
 SERIAL_NO       NOT NULL NUMBER(38)
 PB_ID        NOT NULL NUMBER(38)


I then wrote a Java method, convertInstantiatedAssignments(), that would select all the data from the old table, split the qlist field into individual numbers, and enter them as separate rows into the new table. Wanting to make only one DB call per qlist, I looked around on the Web for ways to insert multiple rows into a single table at once, and I found this formulation:

insert into new_table select * from (select row1_field1, row1_field2, ... from dual) union (select row2_field1, row2_field2, ... from dual) union ...

So for each qList, I formulated this query and sent it to the database.

The first time I did it, I did not order the data from assignment_questions_v2. The transfer of data seemed to be going well, but it suddenly failed at row 16606. I've reproduced below the first and last few records from the log, so you can see exactly where it failed:


INFO: DataConversion.convertInstantiatedAssignments: SELECT * FROM assignment_questions_v2
DataConversion.convertInstantiatedAssignments: insert 1 giving 7 total rows: INSERT INTO assignment_questions_v3 (student_id, hw_id, serial_no, pb_id) SELECT * FROM  (SELECT 'student1', 4275, 1, 4247 FROM  dual ) UNION  (SELECT 'student1', 4275, 2, 4264 FROM  dual ) UNION  (SELECT 'student1', 4275, 3, 4263 FROM  dual ) UNION  (SELECT 'student1', 4275, 4, 4266 FROM  dual ) UNION  (SELECT 'student1', 4275, 5, 4289 FROM  dual ) UNION  (SELECT 'student1', 4275, 6, 4290 FROM  dual ) UNION  (SELECT 'student1', 4275, 7, 4400 FROM  dual )
DataConversion.convertInstantiatedAssignments: insert 2 giving 45 total rows: INSERT INTO assignment_questions_v3 (student_id, hw_id, serial_no, pb_id) SELECT * FROM  (SELECT 'johnadam', 6140, 1, 2966 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 2, 2967 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 3, 2968 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 4, 2969 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 5, 2970 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 6, 2971 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 7, 2972 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 8, 2973 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 9, 2974 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 10, 2975 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 11, 2976 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 12, 2977 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 13, 2978 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 14, 2979 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 15, 2980 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 16, 2981 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 17, 2982 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 18, 2983 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 19, 2984 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 20, 2985 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 21, 2986 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 22, 2987 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 23, 2988 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 24, 2989 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 25, 2990 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 26, 2991 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 27, 2992 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 28, 2994 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 29, 2995 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 30, 2996 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 31, 2997 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 32, 2998 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 33, 2999 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 
34, 3000 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 35, 3001 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 36, 3002 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 37, 3003 FROM  dual ) UNION  (SELECT 'johnadam', 6140, 38, 3004 FROM  dual )
DataConversion.convertInstantiatedAssignments: insert 3 giving 56 total rows: INSERT INTO assignment_questions_v3 (student_id, hw_id, serial_no, pb_id) SELECT * FROM  (SELECT 'donia.arthur', 3130, 1, 2664 FROM  dual ) UNION  (SELECT 'donia.arthur', 3130, 2, 2672 FROM  dual ) UNION  (SELECT 'donia.arthur', 3130, 3, 2673 FROM  dual ) UNION  (SELECT 'donia.arthur', 3130, 4, 2674 FROM  dual ) UNION  (SELECT 'donia.arthur', 3130, 5, 2675 FROM  dual ) UNION  (SELECT 'donia.arthur', 3130, 6, 2677 FROM  dual ) UNION  (SELECT 'donia.arthur', 3130, 7, 2678 FROM  dual ) UNION  (SELECT 'donia.arthur', 3130, 8, 3425 FROM  dual ) UNION  (SELECT 'donia.arthur', 3130, 9, 3426 FROM  dual ) UNION  (SELECT 'donia.arthur', 3130, 10, 3706 FROM  dual ) UNION  (SELECT 'donia.arthur', 3130, 11, 6058 FROM  dual )
...
DataConversion.convertInstantiatedAssignments: insert 16605 giving 151064 total rows: INSERT INTO assignment_questions_v3 (student_id, hw_id, serial_no, pb_id) SELECT * FROM  (SELECT 'schertzr', 5439, 1, 2742 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 2, 2709 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 3, 2680 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 4, 2706 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 5, 2708 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 6, 2772 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 7, 3088 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 8, 2937 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 9, 2936 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 10, 2940 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 11, 3071 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 12, 3073 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 13, 3367 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 14, 2770 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 15, 2767 FROM  dual ) UNION  (SELECT 'schertzr', 5439, 16, 3055 FROM  dual )
DataConversion.convertInstantiatedAssignments: insert failed on try 16606 after 151064 rows inserted: INSERT INTO assignment_questions_v3 (student_id, hw_id, serial_no, pb_id) SELECT * FROM  (SELECT 'schertzr', 5440, 1, 1 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 2, 2 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 3, 3 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 4, 4 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 5, 5 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 6, 6 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 7, 7 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 8, 12 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 9, 14 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 10, 15 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 11, 1333 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 12, 1334 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 13, 1335 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 14, 1336 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 15, 1337 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 16, 3121 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 17, 3122 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 18, 3123 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 19, 3124 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 20, 3125 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 21, 64 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 22, 69 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 23, 70 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 24, 71 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 25, 72 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 26, 74 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 27, 75 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 28, 76 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 29, 1338 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 30, 1339 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 31, 5417 FROM  dual ) UNION  (SELECT 'schertzr', 5440, 32, 4819 FROM  dual )
java.sql.SQLException: ORA-00918: column ambiguously defined

        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1706)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1674)
        at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:275)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:224)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:224)
        at com.epoch.db.DBTables.tryUpdate(DBTables.java:542)
        at com.epoch.db.DataConversion.convertInstantiatedAssignments(DataConversion.java:1410)
        at org.apache.jsp.admin.dbUpdate.autoConvert_jsp._jspService(autoConvert_jsp.java:185)


So then I decided to order the data as I was extracting it from the table and see what happened. This time, the transfer of data failed at record 10! I've copied below the complete log from this attempt.

INFO: DataConversion.convertInstantiatedAssignments: SELECT * FROM assignment_questions_v2 ORDER BY assignment_id, user_id
DataConversion.convertInstantiatedAssignments: insert 1 with 14 rows giving 14 total rows: INSERT INTO assignment_questions_v3 SELECT * FROM  (SELECT 'student1', 1200, 1, 102 FROM  dual ) UNION  (SELECT 'student1', 1200, 2, 103 FROM  dual ) UNION  (SELECT 'student1', 1200, 3, 104 FROM  dual ) UNION  (SELECT 'student1', 1200, 4, 105 FROM  dual ) UNION  (SELECT 'student1', 1200, 5, 125 FROM  dual ) UNION  (SELECT 'student1', 1200, 6, 1153 FROM  dual ) UNION  (SELECT 'student1', 1200, 7, 1418 FROM  dual ) UNION  (SELECT 'student1', 1200, 8, 1820 FROM  dual ) UNION  (SELECT 'student1', 1200, 9, 1877 FROM  dual ) UNION  (SELECT 'student1', 1200, 10, 1715 FROM  dual ) UNION  (SELECT 'student1', 1200, 11, 1747 FROM  dual ) UNION  (SELECT 'student1', 1200, 12, 296 FROM  dual ) UNION  (SELECT 'student1', 1200, 13, 297 FROM  dual ) UNION  (SELECT 'student1', 1200, 14, 654 FROM  dual )
DataConversion.convertInstantiatedAssignments: insert 2 with 14 rows giving 28 total rows: INSERT INTO assignment_questions_v3 SELECT * FROM  (SELECT 'student2', 1200, 1, 102 FROM  dual ) UNION  (SELECT 'student2', 1200, 2, 103 FROM  dual ) UNION  (SELECT 'student2', 1200, 3, 104 FROM  dual ) UNION  (SELECT 'student2', 1200, 4, 105 FROM  dual ) UNION  (SELECT 'student2', 1200, 5, 125 FROM  dual ) UNION  (SELECT 'student2', 1200, 6, 1153 FROM  dual ) UNION  (SELECT 'student2', 1200, 7, 1418 FROM  dual ) UNION  (SELECT 'student2', 1200, 8, 1820 FROM  dual ) UNION  (SELECT 'student2', 1200, 9, 1877 FROM  dual ) UNION  (SELECT 'student2', 1200, 10, 1715 FROM  dual ) UNION  (SELECT 'student2', 1200, 11, 1747 FROM  dual ) UNION  (SELECT 'student2', 1200, 12, 296 FROM  dual ) UNION  (SELECT 'student2', 1200, 13, 297 FROM  dual ) UNION  (SELECT 'student2', 1200, 14, 654 FROM  dual )
DataConversion.convertInstantiatedAssignments: insert 3 with 27 rows giving 55 total rows: INSERT INTO assignment_questions_v3 SELECT * FROM  (SELECT 'student2', 2853, 1, 2484 FROM  dual ) UNION  (SELECT 'student2', 2853, 2, 2485 FROM  dual ) UNION  (SELECT 'student2', 2853, 3, 2486 FROM  dual ) UNION  (SELECT 'student2', 2853, 4, 2487 FROM  dual ) UNION  (SELECT 'student2', 2853, 5, 2488 FROM  dual ) UNION  (SELECT 'student2', 2853, 6, 2489 FROM  dual ) UNION  (SELECT 'student2', 2853, 7, 2490 FROM  dual ) UNION  (SELECT 'student2', 2853, 8, 2491 FROM  dual ) UNION  (SELECT 'student2', 2853, 9, 2492 FROM  dual ) UNION  (SELECT 'student2', 2853, 10, 2493 FROM  dual ) UNION  (SELECT 'student2', 2853, 11, 2494 FROM  dual ) UNION  (SELECT 'student2', 2853, 12, 2495 FROM  dual ) UNION  (SELECT 'student2', 2853, 13, 2498 FROM  dual ) UNION  (SELECT 'student2', 2853, 14, 2499 FROM  dual ) UNION  (SELECT 'student2', 2853, 15, 2500 FROM  dual ) UNION  (SELECT 'student2', 2853, 16, 2501 FROM  dual ) UNION  (SELECT 'student2', 2853, 17, 2502 FROM  dual ) UNION  (SELECT 'student2', 2853, 18, 2503 FROM  dual ) UNION  (SELECT 'student2', 2853, 19, 2504 FROM  dual ) UNION  (SELECT 'student2', 2853, 20, 2505 FROM  dual ) UNION  (SELECT 'student2', 2853, 21, 2506 FROM  dual ) UNION  (SELECT 'student2', 2853, 22, 2507 FROM  dual ) UNION  (SELECT 'student2', 2853, 23, 2508 FROM  dual ) UNION  (SELECT 'student2', 2853, 24, 2509 FROM  dual ) UNION  (SELECT 'student2', 2853, 25, 2510 FROM  dual ) UNION  (SELECT 'student2', 2853, 26, 2511 FROM  dual ) UNION  (SELECT 'student2', 2853, 27, 2608 FROM  dual )
DataConversion.convertInstantiatedAssignments: insert 4 with 3 rows giving 58 total rows: INSERT INTO assignment_questions_v3 SELECT * FROM  (SELECT 'student1', 2864, 1, 131 FROM  dual ) UNION  (SELECT 'student1', 2864, 2, 132 FROM  dual ) UNION  (SELECT 'student1', 2864, 3, 959 FROM  dual )
DataConversion.convertInstantiatedAssignments: insert 5 with 36 rows giving 94 total rows: INSERT INTO assignment_questions_v3 SELECT * FROM  (SELECT 'bartajan', 2954, 1, 2792 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 2, 2793 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 3, 2794 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 4, 2795 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 5, 2796 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 6, 2797 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 7, 2798 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 8, 2799 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 9, 2800 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 10, 2801 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 11, 2802 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 12, 2803 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 13, 2804 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 14, 2805 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 15, 2806 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 16, 2886 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 17, 2887 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 18, 2889 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 19, 2411 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 20, 2412 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 21, 2413 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 22, 2414 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 23, 2415 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 24, 2416 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 25, 2417 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 26, 2418 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 27, 2419 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 28, 2420 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 29, 2421 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 30, 2422 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 31, 2423 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 32, 2823 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 33, 2741 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 34, 3137 FROM  dual ) 
UNION  (SELECT 'bartajan', 2954, 35, 3138 FROM  dual ) UNION  (SELECT 'bartajan', 2954, 36, 3139 FROM  dual )
DataConversion.convertInstantiatedAssignments: insert 6 with 34 rows giving 128 total rows: INSERT INTO assignment_questions_v3 SELECT * FROM  (SELECT 'bartajan', 2959, 1, 190 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 2, 191 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 3, 192 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 4, 193 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 5, 194 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 6, 195 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 7, 196 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 8, 197 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 9, 198 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 10, 199 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 11, 200 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 12, 313 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 13, 697 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 14, 698 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 15, 711 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 16, 2386 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 17, 2387 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 18, 2388 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 19, 2389 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 20, 2390 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 21, 2391 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 22, 2392 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 23, 2393 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 24, 2394 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 25, 2395 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 26, 2396 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 27, 2877 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 28, 2664 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 29, 2672 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 30, 2673 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 31, 2674 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 32, 2675 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 33, 2677 FROM  dual ) UNION  (SELECT 'bartajan', 2959, 34, 2678 FROM  dual )
DataConversion.convertInstantiatedAssignments: insert 7 with 13 rows giving 141 total rows: INSERT INTO assignment_questions_v3 SELECT * FROM  (SELECT 'dennisandy', 2961, 1, 3014 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 2, 3015 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 3, 3016 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 4, 3017 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 5, 3018 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 6, 3019 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 7, 3020 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 8, 3021 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 9, 3022 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 10, 3023 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 11, 3024 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 12, 3025 FROM  dual ) UNION  (SELECT 'dennisandy', 2961, 13, 3026 FROM  dual )
DataConversion.convertInstantiatedAssignments: insert 8 with 33 rows giving 174 total rows: INSERT INTO assignment_questions_v3 SELECT * FROM  (SELECT 'dennisandy', 2962, 1, 3029 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 2, 3030 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 3, 3031 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 4, 3032 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 5, 3033 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 6, 3034 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 7, 3035 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 8, 3036 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 9, 3041 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 10, 3051 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 11, 3052 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 12, 3064 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 13, 3066 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 14, 3067 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 15, 3068 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 16, 3069 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 17, 3074 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 18, 3075 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 19, 3076 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 20, 3077 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 21, 3079 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 22, 3081 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 23, 3084 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 24, 3085 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 25, 3086 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 26, 3089 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 27, 3090 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 28, 3091 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 29, 3116 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 30, 3117 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 31, 3118 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 32, 3119 FROM  dual ) UNION  (SELECT 'dennisandy', 2962, 33, 3120 FROM 
 dual )
DataConversion.convertInstantiatedAssignments: insert 9 with 11 rows giving 185 total rows: INSERT INTO assignment_questions_v3 SELECT * FROM  (SELECT 'pfnm745', 2964, 1, 3 FROM  dual ) UNION  (SELECT 'pfnm745', 2964, 2, 5 FROM  dual ) UNION  (SELECT 'pfnm745', 2964, 3, 14 FROM  dual ) UNION  (SELECT 'pfnm745', 2964, 4, 1333 FROM  dual ) UNION  (SELECT 'pfnm745', 2964, 5, 1334 FROM  dual ) UNION  (SELECT 'pfnm745', 2964, 6, 3121 FROM  dual ) UNION  (SELECT 'pfnm745', 2964, 7, 3122 FROM  dual ) UNION  (SELECT 'pfnm745', 2964, 8, 3123 FROM  dual ) UNION  (SELECT 'pfnm745', 2964, 9, 3124 FROM  dual ) UNION  (SELECT 'pfnm745', 2964, 10, 3125 FROM  dual ) UNION  (SELECT 'pfnm745', 2964, 11, 12 FROM  dual )
DataConversion.convertInstantiatedAssignments: insert failed on try 10 with 25 rows after 185 total rows inserted: INSERT INTO assignment_questions_v3 SELECT * FROM  (SELECT 'dennisandy', 2965, 1, 1 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 2, 2 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 3, 4 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 4, 6 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 5, 7 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 6, 12 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 7, 15 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 8, 1335 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 9, 1336 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 10, 483 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 11, 484 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 12, 485 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 13, 487 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 14, 2542 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 15, 2543 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 16, 2544 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 17, 2545 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 18, 2546 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 19, 2547 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 20, 2548 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 21, 2549 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 22, 2550 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 23, 2551 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 24, 2552 FROM  dual ) UNION  (SELECT 'dennisandy', 2965, 25, 2553 FROM  dual )
java.sql.SQLException: ORA-00918: column ambiguously defined
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1706)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1674)
        at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:275)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:224)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:224)
        at com.epoch.db.DBTables.tryUpdate(DBTables.java:542)
        at com.epoch.db.DataConversion.convertInstantiatedAssignments(DataConversion.java:1416)
        at org.apache.jsp.admin.dbUpdate.autoConvert_jsp._jspService(autoConvert_jsp.java:185)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)


Eventually I gave up and did a separate insert for every record, and that worked fine. My question is, WHY did the queries fail when they did? I Googled ORA-00918, and it doesn't seem relevant to my case here. I found one oblique reference to the possibility of ORA-00918 when using INSERT INTO SELECT * FROM, but no details.

-- Bob

and Tom said...

thank goodness this did not work and you are here asking a question.

I'm not even going to begin to address the original question - but only because your approach is so horribly wrong!

This has to be rewritten to use bind variables, plain and simple. And then you can use batching to batch it up (so you can send say 100 rows at a time to the server, in batch)


I found this entry for you to read:

http://blog.lishman.com/2008/06/jdbc-batch-updates.html

It looks technically accurate and I like that he compared alternative approaches.

But - all of them use bind variables. If you do not use bind variables - then each and every unique sql statement you execute will have to be COMPILED. You'll find that you spend 90-95% of your time in the database COMPILING your sql, not actually executing it.

I cannot stress this enough - these single row inserts MUST use binds - no other approach for this problem should even be remotely considered.

I would not allow your current program to execute in any database I was responsible for - you would kill the shared pool and probably kill the instance performances and scalability wise.



Also, your use of UNION is bad - again, glad it did not work at all. It should have been UNION ALL.

A union B = distinct(A+B) <<<=== extra step, distinct
A union all B = (A+B) <<<=== no distinct

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I see, but no joy

Robert Grossman, November 09, 2011 - 12:22 pm UTC 

Of course! I knew there was a better way to do it.  Funny how this solution didn't come up on my Google search.

But my Java code is now giving me a runtime exception.

<code>DataConversion.convertInstantiatedAssignments: INSERT INTO assignment_questions_v3 (student_id, hw_id, serial_no, pb_id) VALUES (?, ?, ?, ?) 
DataConversion.convertInstantiatedAssignments: batch insert failed on last insert.
java.lang.ClassCastException: org.apache.commons.dbcp.DelegatingPreparedStatement cannot be cast to oracle.jdbc.OraclePreparedStatement
 at com.epoch.db.DataConversion.convertInstantiatedAssignments(DataConversion.java:1414)
 at org.apache.jsp.admin.dbUpdate.autoConvert_jsp._jspService(autoConvert_jsp.java:193)


The relevant Java code is:

import java.sql.PreparedStatement;
import oracle.jdbc.OraclePreparedStatement;

...

            pstmt = con.prepareStatement(insertBld.toString());
            ((OraclePreparedStatement) pstmt).setExecuteBatch(100);
</code>
Tom Kyte
November 09, 2011 - 12:32 pm UTC

give a self contained, 100% complete, tiny as all get out program I can compile and run on my computer and I'll take a look at it.

make it as small as possible.
no need for any error handling.

just connect, prepare, go into a loop to generate some data, bind in the loop, add to batch, then execute batch, close up and exit.

java execute batch

Keith, November 09, 2011 - 3:33 pm UTC

In your example you are using Oracle batching. To use Oracle batching you have to use OraclePreparedStatement. DBCP provides a mechanism to get the OraclePreparedStatement, but I believe that is probably unnecessary. Instead, use the standard JDBC batching. In that case you use addBatch() and excuteBatch() methods and it is your responsibility to count the number of items in the batch and execute the batch when you have reached your batch size (say 50-100). This will simplify your code considerably as you will not have to understand how to get to the vendor implementation of PreparedStatement from DBCP.
Tom Kyte
November 09, 2011 - 3:45 pm UTC

thanks, appreciate the followup!

Ad java execute batch

Martin Vajsar, November 09, 2011 - 5:45 pm UTC

According to the documentation, though the addBatch and executeBatch (Standard update batching) do work with Oracle's JDBC driver, there is no performance advantage. You need to use the Oracle update batching mechanism described there to get the performance gain through the array insert. Look for the section titled "Limitations in the Oracle Implementation of Standard Batching" on the following page (this is the 11g documentation, 10g seems to be identical in this chapter):
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/oraperf.htm#i1056232
(The setExecuteBatch call shown in Robert's followup is generally correct. This construct works in my code - when using Oracle's JDBC driver - and I believe it makes my code to use the array insert behind the scenes. Additionally, no need to count inserted rows - batches are created automatically and transparently by the driver.)

Java execute batch: things have changed!

Stew Ashton, November 10, 2011 - 4:33 am UTC


Martin is right if you use the 11GR1 drivers, but with the 11GR2 drivers Oracle says use standard batching:

Oracle recommends that you use JDBC standard features when possible. This recommendation applies to update batching as well. Oracle update batching is retained primarily for backwards compatibility. http://download.oracle.com/docs/cd/E11882_01/java.112/e16548/oraperf.htm#i1059054 How about a test?
drop table T;
create table T (VAL varchar2(127));
alter system flush shared_pool;
Now run this Java code:
import java.sql.Connection;
import java.sql.PreparedStatement;
import oracle.jdbc.pool.OracleDataSource;
public class BatchInsert {
 public static void main(String[] args) {
  OracleDataSource ods = null;
  Connection conn = null;
  PreparedStatement ps = null;
  Boolean doCommit = true;
  try {
   ods = new OracleDataSource();
   ods.setURL("jdbc:oracle:thin:@//127.0.0.1:1522/STEWVMO11G2.world");
   ods.setUser("stew");
   ods.setPassword("stew");
   conn = ods.getConnection();
   conn.setAutoCommit(false);
   ps = conn.prepareStatement("Insert into t values(?)");
   for (int i = 0; i < 100; i++) {
    ps.setString(1, i + "xxxxxxxxxxxxxxxxx");
    ps.addBatch();
   }
   ps.executeBatch();
  } catch (Exception e) {
   doCommit = false;
   e.printStackTrace();
  } finally {
   try {
    if (conn != null) {
     if (doCommit) {
      conn.commit();
     } else {
      conn.rollback();
     }
     conn.close();
    }
    System.out.println("Finished BatchInsert.");
   } catch (Exception e) {
    e.printStackTrace();
   }
  }
 }
}
Check the result:
> select EXECUTIONS, ROWS_PROCESSED
from v$sql where sql_text like 'Insert into t values(%';
EXECUTIONS             ROWS_PROCESSED
---------------------- ---------------
1                      100
Martin, can you do this test with the 11GR1 driver? How many executions are done?

Oops! Test no good...

Stew Ashton, November 10, 2011 - 5:08 am UTC


I tried this test with several versions of the Oracle Driver from 11.0.1.7 back to 9.2.0.1, and every time V$SQL said there was 1 insert of 100 rows. So I don't know how to check if "real" batching is done or not, sorry!

Maybe avoid Java?

Kim Berg Hansen, November 10, 2011 - 5:32 am UTC

Very good points on batch executing from Java and remember bind variables and everything.

If I understand Robert correctly he has data in assignment_questions_v2 with a colonseparated string of questions (qlist), and he is now trying as a onetime operation to transform those data into assignment_questions_v3 where there is a record for each question.

For that purpose he has created a Java method, convertInstantiatedAssignments(), that parses the colonseparated string and inserts records.

My preference would have been to do that conversion inside the database. Something like this:

with assignment_questions_v2 as (
   select 'student1' user_id, 1200 assignment_id,
          '102:103:104:105:125:1153:1418:1820:1877:1715:1747:296:297:654' qlist
   from dual union all
   select 'student2', 1200,
          '102:103:104:105:125:1153:1418:1820:1877:1715:1747:296:297:654'
   from dual union all
   select 'student2', 2853,
          '2484:2485:2486:2487:2488:2489:2490:2491:2492:2493:2494:2495:2498:2499:2500:2501'
   from dual union all
   select 'student1', 2864,
          '131:132:959'
   from dual union all
   select 'bartajan', 2954,
          '2792:2793:2794:2795:2796:2797:2798:2799:2800:2801:2802:2803:2804:2805:2806:2886'
   from dual union all
   select 'bartajan', 2959,
          '190:191:192:193:194:195:196:197:198:199:200:313:697:698:711:2386:2387:2388:2389'
   from dual union all
   select 'dennisandy', 2961,
          '3014:3015:3016:3017:3018:3019:3020:3021:3022:3023:3024:3025:3026'
   from dual union all
   select 'dennisandy', 2962,
          '3029:3030:3031:3032:3033:3034:3035:3036:3041:3051:3052:3064:3066:3067:3068:3069'
   from dual union all
   select 'pfnm745', 2964,
          '3:5:14:1333:1334:3121:3122:3123:3124:3125:12'
   from dual union all
   select 'dennisandy', 2965,
          '1:2:4:6:7:12:15:1335:1336:483:484:485:487:2542:2543:2544:2545:2546:2547:2548:254'
   from dual
)
--
-- end-of-test-data
--
select
user_id,
assignment_id,
to_number(regexp_substr(qlist,'[^:]+',1,column_value)) question_id
from assignment_questions_v2,
table(
   cast(
      multiset(
         select  level
         from  dual
         connect by level <= length(regexp_replace(qlist,'[^:]')) + 1
      )
      as sys.OdciNumberList
      )
   )
;


(A technique I got from Solomon Yakobson at the Oracle Forums, thanks Solomon :-)

That way converting from the v2 table to the v3 table could be done in one single INSERT statement?

Tom Kyte
November 10, 2011 - 1:34 pm UTC

you know, I jumped the gun on that one. I saw the java with the literals and my eyes bugged out so much I focused on that.

Yes, this would be a much much much better approach - no code at all - just a single sql statement!

PS.

Kim Berg Hansen, November 10, 2011 - 6:09 am UTC

Now I get the meaning of the SERIAL_NO column :-)

insert into assignment_questions_v3
select
user_id,
assignment_id as hw_id,
column_value as serial_no,
to_number(regexp_substr(qlist,'[^:]+',1,column_value)) as pb_id
from assignment_questions_v2,
table(
   cast(
      multiset(
         select  level
         from  dual
         connect by level <= length(regexp_replace(qlist,'[^:]')) + 1
      )
      as sys.OdciNumberList
      )
   )
;


;-)

PPS - the ORA-00918

Kim Berg Hansen, November 10, 2011 - 6:22 am UTC

Just as an aside to Robert:

SQL> select 555, 'THOMAS', 555 from dual;

       555 'THOMA        555
---------- ------ ----------
       555 THOMAS        555


SQL> select * from (
  2  select 555, 'THOMAS', 555 from dual
  3  );
select * from (
       *
ERROR at line 1:
ORA-00918: column ambiguously defined


SQL> select * from (
  2  select 555 empno, 'THOMAS' ename, 555 mgr from dual
  3  );

     EMPNO ENAME         MGR
---------- ------ ----------
       555 THOMAS        555


When you don't alias your hardcoded data, the data values become your column names. In the above example the "select *" complains that there are 2 columns named "555".
So somewhere in your data you have a case with probably same number for HW_ID and PK_ID that also happen to be the first of the "union" list.

But that would never have happened if you had used bind variables ;-)

Java update batching

Martin Vajsar, November 10, 2011 - 7:50 am UTC

I've traced the executions of the following three scenarios (using 10g JDBC driver):

1) Oracle update batching (setExecuteBatch)
2) Standard update batching (addBatch/executeBatch)
3) No batching at all.

First two approaches yielded the same trace file (one execution count), while the third was different (execution count equal to number of rows). Additionally, when I introduced an error in the processing, the first two approaches produced java.sql.BatchUpdateException, while the third produced java.sql.SQLException. It seems that - contrary to the documentation - the standard update batching also uses array insert. That is rather good news, though it's confusing the documentation seems to be wrong on this.

However, I still prefer Oracle update batching, as it does not require me to create batches "by hand". I just call the setExecuteBatch and I'm done. Of course, that would not be possible if I strove for database independence.

wow

Robert Grossman, November 10, 2011 - 3:20 pm UTC

Y'all have been extremely helpful. Kim, you read me right, it was a one-time operation. I'll need to spend some time trying to understand what your single SQL statement is doing. But the reminder on the binds is still useful for when I need to insert new data into the new tables.

ORA-00918 explanation

Robert Grossman, November 10, 2011 - 3:37 pm UTC

Oh, and thanks for the ORA-00918 explanation. Now I know.

Pass an ARRAY as a bind

Timo Raitalaakso, November 22, 2011 - 4:36 pm UTC

You might also consider using only a single ARRAY bind while inserting many rows.

create table si (s number(19),s2 number(19));
create or replace type nums is object ( n1 number(19), n2 number(19));
create type sit is table of nums;



    
private static void insertArray(Connection c, List elems) 
            throws SQLException {
        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("SIT", c);
        ARRAY a = new ARRAY(ad, c, elems.toArray());
        OraclePreparedStatement ops 
           = (OraclePreparedStatement)c.prepareStatement(
                        "insert into si " +
                        " select *" +
                        "    from table(?)");
        ops.setARRAY(1, a);

        System.out.println(ops.executeUpdate());
        ops.close();
        c.commit();
    }


More about that http://rafudb.blogspot.com/2009/08/load-using-java.html

how to do multi records insert in single SQL

Mahi, December 12, 2014 - 9:07 am UTC

I have MySQL insert in the following pattern (as an example providing only 2 lines)

INSERT INTO tab1 VALUES (1,'ANDROID','Mobile'),(2,'Linux','Server');

The similar way If I have to insert multiple records with single batch insert in Oracle , how can we achieve that (not through bind variables, because I have some huge data from MySQL to load into oracle)
I assume oracle8i/9i exp dump file used to contain the similar batch insert ..correct me if I am wrong.


Tom Kyte
December 12, 2014 - 10:31 am UTC

dump files contain raw data. The import programs read that raw data and bind them to inserts that use binds. The only way to efficiently load a non-trivial amount of data (you say you have "huge" data) will be to array process data with bind variables.

that non-standard syntax above is not support by Oracle.

you might consider this:
http://www.oracle.com/technetwork/database/migration/omwb-getstarted-093461.html
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.