SQL Plus Delimited Output File. Ad hoc queries anyone? Something that has always been a pain is when you need a fast report generated from your latest SQL script. You probably just resort to executing the query in TOAD or another query tool, and then saving it off to a file. That is about 5 mouse clicks too many and you just wish you could click a button to run your script and dump out the results to a nicely formatted text file. Using SQL Plus and a batch script, you can set up an ad hoc query tool for yourself that dumps out the results to a delimited output. This is actually quite simple: You need three files, a) the SQL query you want to run, b) the SQLPlus script that will run your query and c) a batch program to run your SQLPlus script. Here is how I did it: a) query. Out. sql - This contains a query set up for pipe- delimited output. You can change the delimiter to whatever you wish. Here is a sample query - SELECT field. FROM {table}WHERE {criteria}; b) query. To. File. sql - This contains the SQL Plus script including formatting options so you can dump out a file with your results and no other fluff. Here are the settings I used - set termout offset feedback offset verify offset echo offset pagesize 0set linesize 2. Value mydate noprintselect to_char(sysdate, 'YYYYMMDD') dt from dual; spool rpt& mydate. Out. sql; spool offexit; c) run. Query. bat - This is the simple batch program that calls SQLPLUS with your username and password and the script to run. Here is what this would look like - SQLPLUS /@ @query. To. File. sql. I set up the above in a directory for these ad hoc queries and then edit my query. From: Tina Ridgley <tlridgley_at_yahoo.com> Date: Fri, 10:25:45 -0700 (PDT) Message-Id: <[email protected]> All, I am attempting to create a tab delimited txt file of data using sqlplus. I am able to.Out. sql file and click on the run. Query. bat which I added to a toolbar. Try it out and let me know if you found this useful or if you have any better ideas.. How do I spool to a CSV formatted file using SQLPLUS? With newer versions of client tools, there are multiple options to format the query output. The rest is to spool it to a file or save the output as a file depending on the client tool. Here are few of the ways: Using the SQL*Plus commands you could format to get your desired output.
From: Manning, Stephen (calanais) <Stephen.Manning_at_calanais.com> Date: Fri, 20:47:45 +0100 Message-Id: <[email protected]> Tina Try removing the chr(13) from your select, its been appended to the value of. SQLPlus settings to generate tab-separated data file. But for tab-delimited, set colsep Chr(9). Execute sql script in batch file with sqlplus command. I need to export the contents of a Table from my schema to a Tab-delimited text file. Howto export Table into tab delimited text file. 732412 Dec 8, 2010 6:28 PM (in response to Kodiak_Seattle). Use SPOOL to spool the output to a file. For example,SQL> SET colsep . SQL> SET pagesize 2. SQL> SET trimspool ON. SQL> SET linesize 2. SQL> SELECT * FROM scott. EMPNO,ENAME ,JOB , MGR,HIREDATE , SAL, COMM, DEPTNO. SMITH ,CLERK , 7. DEC- 8. 0, 8. 00, , 2. The text files are in an industry-standard format such as tab-delimited or comma-delimited (CSV). Loading. Suppose also that you want to create a tab-delimited text file. sqlplus SYSTEM/ password. ALLEN ,SALESMAN , 7. FEB- 8. 1, 1. 60. WARD ,SALESMAN , 7. FEB- 8. 1, 1. 25. JONES ,MANAGER , 7. APR- 8. 1, 2. 97. MARTIN ,SALESMAN , 7. SEP- 8. 1, 1. 25. BLAKE ,MANAGER , 7. MAY- 8. 1, 2. 85. CLARK ,MANAGER , 7. JUN- 8. 1, 2. 45. SCOTT ,ANALYST , 7. DEC- 8. 2, 3. 00. KING ,PRESIDENT, ,1. NOV- 8. 1, 5. 00. TURNER ,SALESMAN , 7. SEP- 8. 1, 1. 50. ADAMS ,CLERK , 7. JAN- 8. 3, 1. 10. JAMES ,CLERK , 7. DEC- 8. 1, 9. 50, , 3. FORD ,ANALYST , 7. DEC- 8. 1, 3. 00. MILLER ,CLERK , 7. JAN- 8. 2, 1. 30. SQL Developer Version pre 4. Alternatively, you could use the new /*csv*/hint in SQL Developer./*csv*/. For example, in my SQL Developer Version 3. Now you could save the output into a file. SQL Developer Version 4. New in SQL Developer version 4. No need of the hint in the query. SET SQLFORMAT csv. Now you could save the output into a file.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |