Pl sql export text file

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 12 years, 8 months ago. Active 3 years, 8 months ago. Viewed 55k times. So I'd also like the data split across multiple files; out1. Anyone have any idea how to do this? Jagger 9, 7 7 gold badges 46 46 silver badges 84 84 bronze badges. Oracle Training. Please Check oracleappstechnical. Nice blog, Thanks For Sharing this informative article. Such a nice blog, I really like what you write in this blog, I also have some relevant information about if you want more information.

Appreciating the persistence you put into your blog and the detailed information you provide. I found another one blog like you Oracle Cloud Infrastructure. Actually I was looking for the same information on internet for Oracle Cloud Infrastructure and came across your blog.

I am impressed by the information that you have on this blog. Thanks once more for all the details. TOm: I was testing with a simple text file and excel read it fine A,,, B,,, Can't I just create any query and stick ',' between the columns, save it to my client machine and then open it in Excel like spool emp. January 23, - am UTC.

Hi Tom, I Have a script in which based on some cursor values it is creating some text file. April 03, - pm UTC. You'll really want to write a program in order to do your custom data extraction, sqlplus is a rather simple command line tool to talk to Oracle, it really isn't for programming. April 04, - am UTC. That said, you need "something" on the desktop to a connect to oracle b invoke the plsql Now, once you tell me what that is, we can start talking about HOW to do this.

If you just need to extract a CSV file on a client, and you have sqlplus installed, you can just put into a file: spool data. Tom replied cryptically with a question. Allen, May 14, - pm UTC. Cryptic to me anyway.

The question, PRS asked wasn't answered. I and our DBA have been searching and searching or googling and googling. You had no answer either but rather, and excuse me for saying it, seemed to deflect the topic. It is a serious problem for us. Tom replied: only if the database server can SEE that directly itself otherwise, it would be very virus like, wouldn't it.

May 16, - am UTC. The file system does not exist. So, my database - asktom on asktom. If it could, think about it for a moment, would that not be very virus like. I did not deflect anything, I said "of course NOT" Think about this for a moment, how could it work - if the machine the database software is on has no access to this file system, how could a process that is part of the database software see this file system?

You have to make the file system available to the database server if you want the database server to have access to the file system. No deflection No obfuscation The answer is: make the file system available to the database server. If you say "we cannot, we split the machines" Then all we can say is "sorry, we cannot do magic, you need to find a way to get the files available to the database server OR find another way entirely to do it".

Oh, but wait, your line 4 in that example is part of the parameter list Hi Tom, I have a flat file which contains a signle record with demilited by ','. Now I wanted to create a oracle table using this record: e. Please remember that flat file columns are not fixed, which means now we have received a file containing 36 months as given above, but in future we may a get a file containing 24 months. So how to create a oracle table using such file values. August 06, - am UTC. Hi Tom, Thanks for your useful tips, I have written the below procedure, which is getting failed while reading the file Hello Sanjeev, can you access the files at all?

Is there a directory named 'Test'? The problem maybe is not the procedure logik but the access to the files. Regards Marcus. So let me know is there any way by which I can access all the columns of that line from the flat file so that I can create an oracle table using those columns. This table I will be using to make pivoting of the measures based on the time dimension Which is the part of the column mentioned in the flat file e.

August 08, - pm UTC. August 20, - am UTC. Rajeev, September 29, - am UTC. It is working with a problem that, once the XML file reached the size of 34KB it is not writing any more data. Your suggetions will be helpful.

A shell script internally transfers the file to Mainframe based daa mart system. We do this on a daily basis. Our files are too large and on an average their size remains about 40 GB. Sometimes it goes upto 90 GB when data is accumulated for multiple days. This consumes lot of time , hrs sometimes even more in file writing. Can you please suggest us a better way of doing this? January 28, - am UTC. Actually, I cannot imagine dumping gb to a flat file - that seems like a "bad idea".

But whatever. Thank you very much Tom. However,It is like sending each commissionable transaction from Oracle incentive compensation tables approx 1 million trxs daily are commissionable out of total approax 8 million transactions in our POS system to reporting system on mainframe.

If we send the data for more than weeks, then this problem arises. If you find some better way to do this,please share with me. Thanks again for your time and effort. January 30, - pm UTC. My point was "probably do not need mainframe reporting system", you already have the data in a pretty competent reporting system.

Hi tom - I wanted to dump a file onto my server in double-quick time, after reading some good stuff on www. Why can I parallelize a result set in the package below using cursor select Is this a limitation, or am I doing something stupid? February 21, - pm UTC. What is being 'parallelized' here? There is no way we have to tell your code "hey, you are to return the 3rd quarter of the data - now do your stuff.

Even if we could - how would YOU know how to get the 3rd quarter? We can execute queries in parallel and send the slices of data to your routines execute your routine against each bit of the parallel result set.

We cannot execute your code in parallel. The examples need to be succinct, so the exception handling got chucked. I still don't understand why a cursor variable passed into an exposed packaged function doesn't work and passing in "cursor select Parallelises nicely Maybe the question should be "what's the difference between a cursor variable and a select statement cast as a cursor, to a parallel pipelined function?

February 22, - pm UTC. The sql is already opened, already run, already executed, it is out of our control - we are NOT opening it there, someone else did and frankly - we cannot tell if that SQL is parallel or not - it doesn't matter if it is - we already started executing it, it is going - it is too late to play games with it. Very very very different. We have some SQL to slice up, we do so and we invoke your routine N times - each with a different slice of data to process.

The only way to run your code in parallel is to take a query that is an INPUT to the code and parallelize the query and send a slice of the output to each copy of your routine. In order to do that, we need to control very finely the opening of that cursor - to parallelize it with the code.

You cannot open and the change the way it should be opened after the fact. You have to combine the opening of the query with the invocation of the routine itself. Neil, February 23, - am UTC.

February 23, - am UTC. If you send it an already opened result set, it is too late to do that. Hi tom, Here is my scenario.. The output will be just adding ID column to the sample.

Hope I am clear. Please let me know if I am not. I am trying to have the above process automated. Thanks, John. You do not tell us what this client is You do not tell us what software the client has You cannot do this in plsql, since plsql runs on the server and the server cannot write to your filesystem. In fact, I could create a form that would let the client upload their file to the server and then the server returns to them a brand new file no appending, we cannot write to YOUR filesystem - a program that runs on your client could, but we don't have anything there.

Hi Tom, I changed you code a bit so that it could bulk collect any given query it concatenates every column with the separator and replaces the columns with this concatenation. The only limitation is the maximum chars per line. Decorreram: The output file has MB.

Although I would like to see some one compare the two. Cheers, Manel. A reader, August 04, - am UTC. How to make this in a single column. August 04, - pm UTC. I have been asked to write in one file the whole content of a table, this table has six CLOB columns and around rows. This file will be used to load the file in a table in a mysql database so the format is given to me.

None of this clobs is very long the maximun size of some of them is around characters. But I am having a behaviour I cannot understand and that I need to avoid in order to have the file well formatted I have tried in If no clob column has a line feed then no extra line feed is included at the end of the record.

I suspect I am missing something. December 16, - am UTC. You have the control you have and nothing more. If you want a well formed file, you'll be writing a bit of code to do it. January 04, - pm UTC. Can you tell us instead what your goal is - not how you are trying to achieve said goal - but what the goal itself is. XML is good for a very few things, massive data interchange on this scale - no, not so.

Can we have junk stuff like bcp? A reader, January 29, - pm UTC. Can we have such junk in Oracle? January 29, - pm UTC. August 04, - am UTC. I would say your specification is lacking however, it is incomplete. What if the quoted text contains quotes itself? How do you deal with that? You have my template above as a starting point, refine it to your needs once you answer all of the possible questions one could raise about your specification. Hi Tom, I created a pl-sql procedure to read from a MS word document.

How can I display the data from the BLOB column in exactly the same manner it was stored in word file? September 27, - pm UTC. They have a binary document format - you would have to know how to process a word document from start to finish. Why not just return the word document "as is" to the client?

We can use Oracle text to extract the text from the document no formatting or turn it into html which won't be close to being an exact copy formatting wise or another document format like RTF.

Reading from Word document. Please let me know ,how can i do this in oracle 9i. January 05, - am UTC. Can you please tell how to modify the fopen to use a larger linesize. Look at the last parameter there. Any advantage of using a function to return a cursor and write flat file in perl. Is the first method faster? January 23, - pm UTC. Hi, Tom, This link is no longer working. April 12, - pm UTC. It works like a charm. Unfortunately after we create the CSV output on the database machine we need to scp the file to a web server where the file can be downloaded by an "curl" process.

You probably know that Apex will allow a user to invoke a stored procedure directly as long as it's been granted execute privileges to the public user. My question has to do with how best to get the data from the table into a form that is HTTP output ready. I have thought about creating a temporary clob from the CSV output and then just using htp. I've also thought about using bulk collect I have a well defined query and don't really need to do the dynamic sql stuff but it seems that I would do the bulk collect only to loop through it to do the HTTP output so why not just do it at the time of the original query.

Just wondering what you would do in a case like this? It is a very small result set several thousand rows of 8 or 9 columns , so not really concerned with long processing times.

Thanks for any thoughts you might have. May 31, - pm UTC. June 17, - pm UTC. What are the other options? Tom, Thank you first of all! In one of the reviews above, you haven't actually mentioned what the other options are for the task - extract 50 gigs worth of data from a table to file.

Could you mention some options that you use for such tasks please? Actually, I cannot imagine dumping gb to a flat file -that seems like a "bad idea". Thanks, Sri. If you want to use a ref cursor, you'll have to rewrite the code. I'm not sure what "exception" you are facing, you give absolutely no real information here to see what is going on.

TOm , i was able to rewrite the code I am using the following code and got the error:Data type inconsistent. November 16, - am UTC.

I need an example I can run. You will however need to have as many bind variables as you have columns selected. Hi Tom, Thank you for the function, it is very useful. I encountered a situation that I don't know how to explain; if the select returns only one line, then at the end of the line, in the exported file, appears a dot ".

If I have more lines, then the dot doesn't appear anymore. Example: only 1 line chebib;chbib7 hotmail. November 17, - pm UTC. Yes, you are right, it seems that the dot is visible only using view option from Total Commander; if I edit the file, it doesn't appear anymore. Thank you! ORA invalid file operation. Hi Tom I am facing the below problem and have not been able to get it solved with the help of Unix administrator too till now on the assumption that it might be a directory permission related problem Following code is trying to create a file in host server directory Please help in this.

Thanks a lot. November 21, - pm UTC. Hi Tom Thanks for above reply, there was permission problem with oracle account. That problem was solved, but came another. In short, files are not getting created on server from where jobs are run, but on server where DB is installed.

Recently I got same jobs scheduled to run on S2 accessing D2 database test installed on H2 server but files are getting created on H2 instead of S2. Actually files should be created on S2 only as jobs are being run there only. November 23, - am UTC. Think about this for a minute. Say you run this from your PC. Do you really want the database server to be able to reach out over the network and arbitrarily select your PC and create a file on it?

And then when your coworker runs this on their PC, the same thing happens - a file gets created on their machine. Think about that for a minute, long and hard : Now, how do you go about getting machine 1 to write a file on machine 2?

It is rather straight forward. You mount the disk that machine 2 has explicitly exported onto machine 1. Machine 1, due to your setup, due to your explicit request, due to your explicit configuration and security rules, can now write to the file system owned by machine 2.

If you want the files to be created on the client you shall either a mount the file system so the database has access to the file system. The schema are only on one instance. Any recommendation as to what should be the efficient way of doing this? November 28, - am UTC. Thank you for your response. How can I loop through the 18K schema's to get an output? Thank you very much. November 30, - am UTC.

Your followup made me go back and look again: here's what I found in case it helps anyone else. My test data set is one million lines with average row length of Reading and transforming the lines to CSV format takes 17 seconds.

December 06, - am UTC. December 07, - pm UTC. A reader, January 25, - am UTC. Hi Tom, There is report generation in production for every month end. Using open cursor in proc then app server write the file.

But its very slow. BCP - It is a very fast option. Use it for large volumes of data. Log in or register to rate. Join the discussion and add your comment. You have many options when exporting data from a database. In this article, we will show how to do it. When we administer a SQL Database, we always have to work with files. This new article shows how to handle them using PowerShell.

Getting Started Let's look at each of the ways we can export the results of a query. PowerShell PowerShell is an extremely popular command line shell to automate tasks. The results will be the following: 4. A message specifying that the statement is valid should be displayed: Keep the default values: Select Run immediately to export the data immediately: The file created will be similar to this one: 5.

WriteLine String. Show ex. ToString ; Dts. Close ; myFile. Open The try structure is used to handle exceptions.