Oracle most efficent array insert for large flat files

You are commenting using your Facebook account. Notify me of new comments via email. Notify me of new posts via email. Search for: Search. Date: February 16, Author: sqlandplsql 1 Comment. Share this: Twitter Facebook.

Like this: Like Loading Previous Previous post: Install R on Ubuntu. What I want is a utility to write the data to the client side, and also a utilitythat will pick it up from the client side and dump it into a table in my schema.

Is this possible. Kindly answer. April 16, - pm UTC. SQLPlus does not. SPOOL does. As for ruling out sqlldr -- then you are basically "out of luck" -- or you are writing your OWN client program that parses an input and file loads it. Loading it is trivial as well -- if you use sqlldr -- if you rule it out, it's hard.

I'm passing in the :system. And lastly I want to pass TAB as the separator, but when I passed chr 9 as the separator it is not working? August 08, - am UTC. What commas??? Can you show a small example for an assumed example of a block containing 2 records and 2 columns. Tom With the help of your program I'm able to successfully write to the csv file.

But I need column headings. That is I need to have column headings , and underneath it I need to write the data as per the user requirement. Any idea how I can do it. Sorry for the above comment on 'not working' I got confused between column separator and line separator. August 09, - am UTC.

That'll give you your column headings. Tom 1. August 09, - pm UTC. Umm, change 1 to 2 in the loop for i in You would do that after you parse. Search for it like I said and you'll find it. Please explain. August 20, - pm UTC. Please tell me how and where the code should be changed. Regards, Anil. April 10, - am UTC. Look up a page or two where we talk about this already. Dear Tom, I need it in the reverse way. What is the simple way to output the result of an SQL to a textfile.

I will call that query from my application. I won't use SQL Plus. I can use that feature for custom generated reports. April 26, - am UTC. May 21, - am UTC. Hi Tom, I have a many stored procedures which takes some inputs parameters and returns a refcursor.

November 15, - am UTC. Thanks in advance, Wor. January 09, - am UTC. Hi Tom, I am currently working on a system where the user will need to take a dump of the data produced in Oracle Forms and save it as an Excel file. What I would like them to be able to do is save the file to a directory of their choosing on their workstation and not on the server.

Is this possible? Thanks in advance. Regards, Scott. February 04, - pm UTC. Hi Tom, I know a nice way to make Excel sheets from sqlplus. You can use the markup html tag in sqlplus. Here's an example. A reader, April 01, - pm UTC. Why cant it be a procedure straight away? Very curious about this.. April 02, - am UTC. I wanted a function that returned the number of rows written. I then call this function from various procedures. I have gone through the code snippet for adding a column headings April 08, - am UTC.

Hi Tom, I have a requirement that will give the user the option to specify the location and name of the output file for a query. April 20, - am UTC. Oh wait, maybe we don't really want to do that.

Rather -- might you want to list the directories the administrator of the database has deemed "these are OK for the oracle process to write into" and present that list to the end user? Much safer However, that gives you the capability to save to system. Hi Tom, I got your point here I have used Oracle9i for almost 2 years and been certified with 9i.

I have tried it, and it worked fine. Many many thanks I need to extract data from the table and have it in a file too. Currently i use sqlplus's 'spool'. My problem with that approach is because of sqlplus innate tendency to align the columns hence storing excessive space. The query finishes quickly but the output to the terminal or output to file takes much much much longer. April 21, - pm UTC. Very useful tool!

Can U brief How to achieve this. It always raises the error at a specific filesize. April 23, - am UTC. A reader, April 26, - pm UTC. Tom When the data which needs to to dumped to csv contains commas, will the CSV format work? April 27, - am UTC. Hi Good day to you. I have copied your unloader example with slight modifications. It gives the following error. ORA Type of out argument must match type of column or bind variable.

Please help. May 05, - am UTC. Hi Tom Thank you for your immediate response. I searched metalink. Subramanian Natarajan. Tiny irritating feature! Phil, May 18, - am UTC. Hi Tom I've searched high and low and annoyingly I am sure it can be done. I have a sql file that prompts for a few variables.

It then starts a spool and immediately runs another file with a where clause using some of the variables. Problem is, I don't want to see it in the spool - is there a way of hiding this? Top of the export old FROM dsv. May 18, - pm UTC. Use the above to remove the "verification" of substitution variables sorry to have asked a dumb Q! All the values are Right Justified!!! Arindam, June 14, - pm UTC. Tom, I have tried to run this procedure and I have been sucessful, thanks much. But I have a problem.

All the column values comes as right justified. Whether its Varchar2 or Number. I would like to have Varchar's as Left justified and Number's as right. I am using a view to create the file. The view has correct datatype, I mean Varchar's and Numbers. Any insight on this? May be am I am missing something. June 15, - pm UTC. A reader, July 05, - pm UTC. Tom I write a lot of queries for my users.

Please give me your opinion and solution. I want to store all these queries in the database, and pull them up based on the query selected using the forms gui interface.

Now the issue I'm encountering is , each of these queries also take parameters to filter data. I want your advise as to how I should set up these queries for parameters. But how should we dynamically decide which parameters to supply based on the query selected.

I repeat the main issue is with supplying parameters, to the query selected. Thanks a lot in advance. July 06, - am UTC. Each query can have different number of parameters, how do we set the session context for each of them. What do mean by store the metadata about the query. What all do you think we should store in the schema.

The users selects a query The gui interface pull up the associated parameter into and displays items on the interface, so that the user can enter parameters. Once the user enters the parameters, then we need to just call bind over and over for each parameter.

Is that the way you are seeing it? July 06, - pm UTC. Tom I have another interesting proposition here, apart from writing to a csv file, if I also want to display the data returned by the query in the form, what is the best way to do it. This is challenging actually because, each query has different number of columns.

Can we use the ref cursor to achieve this, can I have a ref cursor return a fixed set of 30 values out, irrespective of the number of columns a query has. Yes, obviously if the query has more than 50 columns the query will fail, but that is o. If this is possible then we can build a fixed width block of 30 columns in oracle form and pupulate it using a stored procedure each time.

Now we are creating a table storing the query in it create table t5 q varchar2 ; insert into t5 values 'select ename, empno, job, null, null, null, null from emp' ; commit; II.

Tom, can you give some hint as to how this package should be changed in order to accept queries from the database. I ran into the below error.. July 07, - am UTC. A reader, July 06, - pm UTC. And then when the query is called, how should we use the bind procedure above and pass values to multiple parameters. I already did. What about Blob? Zeenat, September 06, - am UTC. Thanks for the solution Tom. However how do we handle columns of blob data types in such cases?

Appreciate your help, Zeenat. September 06, - am UTC. YOU have to decide what you want to do with one upon detecting it. A reader, September 06, - am UTC. I tried this function with table having BLOB data,but it gave inconsistent data type error. September 07, - am UTC. DOn't know if this related to the subject of question. But it defintely answers some comments here. There is an effective and very fast tool FastReader www. But I still have some hope! Is this the final word on the matter? Any words of wisdom most appreciated!

February 09, - am UTC. Like Tom says, you can do it in Java. Bipin, March 30, - pm UTC. Tom, I have same question as Paul has. I read your book effective oracle by design,, but it seems i need to read it again in order to understnad ref cursors. But in any case, it's very difficult for me to digest this fact. Isn't ref cursor is a cursor after all? As Paul mentioned in above posting, it is a very common requirement to write such code.

Where does Java get this information from? It has to be part of 'opened RefCursor', no? Is there any other reading you can suggest? Thanks again for supporting oracle develoepr community. March 30, - pm UTC. If you know at compile time, we can use a ref cursor. Enhancement request for Oracle? Thanks for your quick answer Tom. I think this is the last question I am going to ask on this subject.

Thanks for your patience. Have a good day. I previously thought I won't followup on this but it seems that I have not made my question clear enough. It expects a cursor? This ref cursor structure of course will be different for different SPs.

In order for me to do this, I need to get access to ref cursor metadata as everything column names, types, of columns etc. So, that's not an option for me. Hope this explains the questions. Thanks again for your help. If you want the declaritive way, where the language and SQL are tightly bound, you'll use ref cursors. I think I will try Java SP. Thanks for your time, Tom. Hi Tom, We are in Oracle9R2 and we would like to preserver our data atleast for 7 years as per the instructions from various authorities.

For this reason we have decided to preserve data in text format. Because we are not sure that if we have taken backup in oracle7 it will work in oracle10g or higher versions at the time of requirement suppose at end of 7th year we require this data Such restoration will make our life difficult. So we have decided to 1. Dump all data in text files using separator character got an answer in this question 2. Kindly guide me which one is best for this step shall we do by exp and imp with indexfile option?

Load this text data into the tables. Can you help me a. April 06, - pm UTC. Just upgrade as you upgrade? Hi Tom, Regarding your answer to my question in this thread, sorry I have not mentioned that, we are in banking industy and every day we are generating huge number of transactions every day".

Due to various reasons our vendor is purging transactions from our production database on a monthly basis. This database is outside our country so we are having little control over it, secondly I can not take decision on this. In this scenario we want to preserve our data in text format.

Can you help me regarding my questions? I also observed that downloading of data of records by using your utility into flat file took around 9 mins.

Such LKM will load the source file into the staging area, and all transformations will take place in the staging area afterward. When the source result set is on a remote database server, an alternate solution to using the agent to transfer the data is to unload it to a file and then load that file into the staging area.

This is usually the most efficient method when dealing with large volumes across heterogeneous technologies. Generate the script required by the loading utility to load the temporary file to the loading table.

A more efficient alternative would be to use pipelines between the "unload" and the "load" utility. Unfortunately, not all the operating systems support file-based pipelines FIFOs. Other databases implement specific mechanisms for loading files into a table, such as Oracle's External Table feature. These loading strategies are implemented into specific KM that create the appropriate objects views, dblinks, etc.

The commands below are extracted from this KM and are provided as examples. You can review the code of this knowledge module by editing it in Oracle Data Integrator Studio. This task drops the loading table. This command is always executed and has the Ignore Errors flag activated. It will not stop the LKM if the loading table is not found. For highly partitioned tables this could potentially lead to a lot of wasted space. However, to load from flat files the statement must include information about where the flat files reside outside the database.

A direct path load parses the input data according to the description given in the external table definition, converts the data for each input field to its corresponding Oracle data type, then builds a column array structure for the data. These column array structures are used to format Oracle data blocks and build index keys. The newly formatted database blocks are then written directly to the database, bypassing the standard SQL processing engine and the database buffer cache. Direct path loads can also run in parallel.

To set the parallel degree for a direct path load, either:. An IAS statement does not automatically perform a direct path load in parallel. In order to enable an IAS statement to perform direct path load in parallel, you must alter the session to enable parallel DML by executing the following statement. Because there is no physical movement of data, an exchange does not generate redo and undo. In other words, an exchange is a sub-second operation and far less likely to impact performance than any traditional data-movement approaches such as INSERT.

The assumption being made in this example is that the data integrity was verified at date extraction time.