L_colval := l_colval || l_columnValue || ',' While ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loopĭbms_sql.column_value( l_theCursor, i, l_columnValue ) Įlsif l_descTbl(i).col_type in (1,8,9,96,112) then Pipe row('alter session set cursor_sharing = force ') L_status := dbms_sql.execute(l_theCursor) L_collist := l_collist || l_descTbl(i).col_name||',' If l_tname is null then l_tname := end if Įxecute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ' Įxecute immediate 'alter session set nls_timestamp_format=''yyyy-mm-dd hh24:mi:ssff'' ' Įxecute immediate 'alter session set nls_timestamp_tz_format=''yyyy-mm-dd hh24:mi:ssff tzr'' ' ĭbms_sql.parse( l_theCursor, p_query, dbms_sql.native ) ĭbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ) ĭbms_sql.define_column(l_theCursor, i, l_columnValue, 4000) L_nls sys.odcivarchar2list := sys.odcivarchar2list() L_theCursor integer default dbms_sql.open_cursor So here is my quick hack to get convert single row inserts into multiple row inserts without too much effort.Ĭreate or replace function as_insert(p_query varchar2, p_batch int default 10) return sys.odcivarchar2list pipelined as But how can we get to that utopia with set of insert statements? We could totally rewrite the script with bind arrays and the like, but that’s a big job. Of course, we developers have all had drummed into our programming minds over the years that database operations should be done in sets not row by row. That will replace the literals with bind variable values and hence all of the INSERTs will be sharable.Ī set of INSERT statements is inherently a row by row operation. Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','IND This is easily solved with a couple of ALTER SESSION wrappers at the head and tail of the script.Īlter session set cursor_sharing = force Even for a simple INSERT, that’s a lot of parsing work for the database. There is two things that are going to slow us down here.Įvery insert is a brand new statement since it contains literal values. But for larger numbers of rows then you might sitting around for a long time waiting for that script to finish. For a small number of rows, that is no problem. That’s pretty cool but no matter what tool you use, ultimately you end up with a set of insert statements that will load 1 row at a time into the database. Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','IND. Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_CON1','INDEX') Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_TS1','INDEX') Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_OBJ3','INDEX') Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_FILE#_BLOCK#','INDEX') SQL> select /*insert*/ * from t where rownum <= 10 Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Last Successful login time: Wed 17:05:49 +08:00
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |