PL/SQL :: Spool File With CTE Query?
Jul 10, 2013
I am using something like below , the query runs fine in oracle SQL developer.But I would like to generate a text file by running the script, so I added the Spool command, and those settings,
I tried to run it in SQL developer or SQLplus, they both fail.In SQL developer: I got: ORA-00933: SQL command not properly ended00933. 00000 - "SQL command not properly ended"*Cause: *Action:Error at Line: 16 Column: 1,542In SQLPlus, but I got an error: SP2-0734: unknown command beginning "CteRace As..." - rest of line ignored.it seems it doesn't understand the common expression table. -- SQL code below:set heading offset feedback offset newpage noneset echo offset termout
[code]...
View 3 Replies
ADVERTISEMENT
Mar 7, 2011
I would like to store my sql query output into text file.Like for example:
select name from emp where emp_id=101;
Here output should be in text file as
swapna.
I dont want to use spool statement here,since If I use it,spool statement will also be printed in text file which is not my requirement.I just want to take only output.
View 1 Replies
View Related
Apr 27, 2012
How to spool the Japanese characters in table using UTL_FILE. I tried with utl_file.fopen it's general,it's spooling. but i am not sure it this right way or not. in this case we need to change any character.
We can't see this characters in TOAD. Only possible in PLSQL developer
create table test1(name varchar2(1000),fname varchar2(1000))
insert into test1 values ('名を入力してください。','姓を入力してください。')
commit;
View 6 Replies
View Related
Jan 20, 2011
I use sqplus in oracle to output the command output to text file .
I use below set environment variable.
SQL> set echo off;
SQL> set linesize 3999;
SQL> set feedback off;
SQL> set feedback off;
SQL> set termout off;
SQL> set pagesize 0;
SQL> spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
In ouput file , it look like
SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
060010007 10007
:
:
:
SQL> spool off;
any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"
View 1 Replies
View Related
Jul 26, 2011
To add application name in a spool file, I am using the below way.
sqlplus username/pwd@tns @xyz.sql APP0115
SQL> define appname="'&1'"
Enter value for 1: APP0115
SQL> prompt &appname
'APP0115'
SQL> spool &appname._html_jobs.csv;
SP2-0768: Illegal SPOOL command
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
But I am getting the above error in the spool file clause because of single quote printing infront of the spool file. But the method of defining a character is "'&1'". So I cannot avoid this single quote in the define clause.
'APP0115'
print the appname like APP0115 instead 'APP0115'.then only I can use this in the spool file clause?
View 3 Replies
View Related
Jun 6, 2013
Oracle version Oracle Database 11g Release 11.2.0.1.0 - 64bit Production running on CentOS Linux release 6.0 (Final), kernel 2.6.32-71.29.1.el6.x86_64.
I am having a hard time spooling a file and displaying special Brazilian characters, even though I can see them correctly in SQLDeveloper:
LEOPOLDO COUTO DE MAGALHÃES JÚNIOR
Spool:
LEOPOLDO COUTO DE MAGALH?ES JUNIOR
I've tried changing the NLS_LANG at the session level, but that cannot be done. I don't want to change the default language of my DB, but really need these characters to display correctly in a file.
View 16 Replies
View Related
May 22, 2013
I'm running a sql file in Unix (using SQLPLUS command) and i want the file output to be sent to local windows directory. I specified the windows directory name in my Spool command (Spool C:/<Directory Name> ) , But it is not working
Our Unix server is a FTP server but i don't want to FTP the spool file from unix to Windows as the spool file is Huge and it takes hours for the transfer to complete (and we have to run the script multiple times).
Is there a way i can have the spool file created in Local windows directory when i run my sql script in Unix?
View 5 Replies
View Related
Jul 19, 2012
I am calling a stored procedure from a shell script. This stored procedure is having a CLOB object as an OUT parameter. How to write the data in CLOB object a existing file which is there in my client system. Below is the shell and sql scripts.
Shell script
( echo "hello" ) > /root/file.txt
sqlplus -s $user/$pass@$tns @/root/proc.sql /root/file.txt << EOF
EOFSQL script
set pages 0
set trimspool off
[Code]...
spool offThis code writes contents of the OUT variable to file.txt, but my existing data ('hello') is lost.
I figured the it...Use append in the spool command...
View 1 Replies
View Related
Aug 20, 2010
how to create a file in a folder based on todays date. i need to know how to define a variable in sqlplus and assign a value to it.Here is the code below. The code gets executed without creating a spool file.
DEFINE _DATE = replace('C:\_sysdate_EU001.csv', '_sysdate_', TO_CHAR(SYSDATE, 'DD-MON-YYYY'))
spool _DATE
set serveroutput on size 100000
select * from dual;
spool off
View 14 Replies
View Related
Jan 20, 2011
I use sqplus in oracle to output the command output to text file .
I use below set environment varialble.
SQL> set echo off;
SQL> set linesize 3999;
SQL> set feedback off;
SQL> set feedback off;
SQL> set termout off;
SQL> set pagesize 0;
SQL> spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
In ouput file , it look like
SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
060010007 10007
:
:
:
SQL> spool off;
any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"
View 8 Replies
View Related
Jul 15, 2013
I would like to spool a clob column to a flag file, however some of the clob are greater than 32k, and I have to have the same record in a single line in the file. Is there any way to achieve this through spooling?
set heading off
set feedback off
set term off
set long 1000000
set longchunksize 500000
set line 32767
set trimspool on
set pagesize 50000
spool file.txt
@--this is my select statement.
spool off
exit
View 1 Replies
View Related
Oct 11, 2011
I want to spool CLOB data into flat file ?
View 6 Replies
View Related
Nov 21, 2012
I am trying cleaner and simple way to spool out all privileges of a user "ABC" in a way that it generates grant statement for them.
View 6 Replies
View Related
Jan 8, 2013
I am trying to Spool a Package Body into a file using sqlplus. I tried 2 days:
WAY1
SET LINESIZE 32000;
SET PAGESIZE 0;
SET TRIMSPOOL ON;
SET ECHO OFF;
SET TERM OFF;
SET FEEDBACK OFF;
[code]....
WAY2
.....
SELECT dbms_metadata.get_ddl( 'PACKAGE', 'SPI_xxxx_PCK', 'owner') FROM dual;
......
In Way1, I got different DDL or partial DDL. In Way2, I got only first 2 lines of Package body. I am running this command from Schema user only.
View 7 Replies
View Related
May 9, 2010
m spooling the results of some performance tests to a text file, the test script im using calls various sql statements in other scripts and spools to the results, stats and time to the same file...Howver I dont want to spool all the results... i only want the stats and time.. how can i do this?
my script looks like:
=====================================================
SET TERMOUT OFF
SET AUTOTRACE ON STAT
SPOOL f:/testresults.txt
[code]...
View 39 Replies
View Related
Oct 26, 2011
I need to do the following via the sql plus command prompt :
1. Execute a large ".pls" script file which does various inserts and updates to a table.
2. Spool the output of "select * from updatedtable;" to a text file to see the changes made.
3. Rollback all the updates and inserts in the script.
View 2 Replies
View Related
Mar 5, 2010
I want to ask if we could create a sub folder using the SPOOL command. I tried the following but it gave "Cannot create Spool file" error.
SPOOL C:TESTABCABC.sql
where TEST is already present in the C drive whereas I want SPOOL command to create a sub-directory named 'ABC' and then create the spool file 'ABC.sql' isnside it.
Is this possible ? My OS version is MS XP.
View 4 Replies
View Related
Oct 25, 2011
I have a problem spooling flat files in utf 8. I have 3 different sql that needs to be spooled regulary in utf8 format.the first two containing special chars is spooled without any problem, but the third that is containg only numbers and pipe-s is spooled in ansi format.
i have set nls_lang=.utf8 in my shell script, but that only worked at the first two sql the last doesnt work.
I have also tried to convert file using iconv command, but the output is still in ansi (only works if i put a special char in the ansi file then it converts to utf8 without problem) if i open the file in notepad and save as utf8 then it works, but i cannnot reach the same from unix script.
View 4 Replies
View Related
Jan 3, 2011
I am looking forward for getting the output of audit records in a non-editable format as in pdf or something of that sort. Is it possible from Oracle 10g Rel-2?Can i spool output as PDF format?
View 6 Replies
View Related
Oct 23, 2012
I am trying to spool data from tables into flat files. I am using the following scripts to accomplish it
1. A cmd file (windows) that makes a call to a sql file
2. The SQL file which generates another query file at the run time, depending upon the table name passed to it
3. The run time query file , that executes the final query and spools the data into a txt file | delimited
For e.g. :
Actual command passed C:Spool_utilityspool_utility TABLE_NAME
E.g. of the spool utility file :
@echo off
SET dbuser=XX@YY
SET dbpw=xxxx
echo %date% - %time% - Start > %1%log.txt
echo START
sqlplus -s %dbuser%/%dbpw% @spool_utility.sql %1>%1.txt
echo %date% - %time% - Done >> %1%log.txt
echo DONE
E.g. of the spool_utility.sql
set echo off
SET newpage 0
SET feedback off
SET linesize 32767
set pagesize 0
[code]........
The above file generates a table_name.sql file with the actual table name at run time and gets executed and the output is written to the table_name.txt file.
This works perfectly fine. But the issue is when someone passes some wrong table name or if there is a actual run time error while executing the query , the error with details itself itself gets written to the end spool file.
For e.g. : if i do this just to generate an error and execute it from command line, the query generates an error and writes the error to the spool file , but at the command prompt where I executed the command I do not see any error and the process seems to have run perfectly well
set xxx on xxx off as above
spool &1.sql;
Prompt Select * from &1 where rownum><10---this will cause the issue
spool off
set termout ON
@ &1
EXIT
Eg of spool file generated :
from table_name WHERE rownum><=10 *
ERROR at line 62:
ORA-00936: missing expression
My question is, is there any way i can capture this runtime error and return this error to my calling sql script spool_utility.sql and then propagate it to the calling command file and do some tasks for eg removing the spool file and writing the actual error to a log file . Basically any way to know at my OS calling level that the entire spooling operation was unsuccessful.
View 8 Replies
View Related
Nov 22, 2012
I have a master table A that has 10+ columns along with data and a configuration table B that has only 2 columns i.e table name and column name. For ex:-
Table A:-
C1 C2 C3 C4 C5 C6
-- -- -- -- -- -----
1 2 3 4 3 6
4 5 6 5 5 5
Table B:-
Table_Name Column_Name
--------- ----------
A C1
A C2
A C3
Now I need to write a sql script that should take the table A Columns defined in Table B and then based on the columns it should spool the columns output from Table A.
For above ex:- I need to spool the C1,C2,C3 columns data from Table A in to some Unix path using SQL SCRIPT.
View 2 Replies
View Related
Jan 3, 2011
I have a PLSQL script.
sqlplus
$DB_ACCT << EOSQL
set heading off
set termout off
set pagesize 0
[code]....
My output in FILE.txt looks like this
SQL>
SQL> Select col_with_a||col_with_b||col_with_c from
2 alphabet_table;
abc
[code]....
I tried several options by putting set echo off, etc.What do I include in the sql script to get the output as :
abc
abc
abc
without the SQL prompt and the statement, and the spool off command?
View 2 Replies
View Related
Jul 4, 2013
I have 11.2.0.3 Db in HP UX 11.31, I have to spool a table output in csv format which contains arabic letters in few columns. my spool output is not showing the arabic letters properly.
[oratest@]$ echo $NLS_LANGAmerican_America.
AL32UTF8 V$NLS_PARAMETERS NLS_LANGUAGE
AMERICANNLS_TERRITORY
AMERICANLS_CURRENCY
$NLS_ISO_CURRENCY
AMERICANLS_NUMERIC_CHARACTERS
.,NLS_CALENDAR
[Code]....
View 0 Replies
View Related
Oct 17, 2011
My SQLPULS script below generates 2 million records and it works fine without any errors, which I run from my batch file. (sqlplus -S %CONNECT% @"SCRIPTSmysql.sql"
The script itself runs 2 hours and exits via sqlplus.exe properly without any errors based on errorlevel from my batch and proper generated data. However, I have noticed that spool output file stops generating any records after 1 hour. It appears that client sqlplus.exe is sleeping or verifying something after spooling completed exactly the same period of time that takes to generate my out file. Is there a way to configure my script to avoid this 1 hour sleep/verification process?
whenever sqlerror exit sql.sqlcode
alter session set current_schema=blah;
/
set linesize 1000
set feedback off
[Code]....
View 3 Replies
View Related
Feb 1, 2012
I would like to use the Spool command to export data for other purposes within the application. We would like to use tab delimiter to seperate the fields but the client wants to know if the text datatype fields can be wrapped in double quotes along with the tab delimiter..
SQL>create table test (id number(2), first_name varchar2(15), last_name varchar2(15),var_no number(4),
type varchar2(1),type_no number(12));
Table created.
SQL> insert into test values(1,'mary','ross',132,'S',12);
1 row created.
SQL> insert into test values(3,'Sue','Bill',432,'S',12);
1 row created.
I tried the below spool command to use tab delimited for all the fields but not sure how to wrap double quotes for only the text fields and also would to have the column names in the 1st row but don't seem to get the full column name in the csv file.
set echo off
set feedback off
set linesize 1000
set pagesize 4000
set trim on
set headsep off
set colsep '' (used tab between the quote)
spool test.csv
select id,first_name,last_name,var_no,type,type_no from test;
spool off
View 10 Replies
View Related
Feb 4, 2004
How can I put an Oracle sqlplus query output into file?
SQL> select myfield from userDB where IMSI like '22803%' and rownum < 11;
I would like to put the output in a file, say '/tmp/mydata.dat'
View 6 Replies
View Related
Dec 15, 2010
Encountering an issue with an Oracle external table. We get the following error when we load a particular file in this table:
ORA-12801: error signaled in parallel query server P000
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
We have narrowed this down to a text field in the file that contains the following text (text obfuscated):
XXXXXXXX ł XXXXXXXXXXXXXXXXXXXXXXXXXXXXX
This is nominally 40 characters long, which matches the maximum field size, but it is being rejected because it appears the 'ł' character is causing Oracle to interpret the length as 41 characters instead. If I remove a X then the file loads without issue.
We tried this in a new schema we created where we added the same table and used the same file. There was no problem at all. The oracle database has the following settings:
NLS_CHARACTERSETUTF8
NLS_NCHAR_CHARACTERSETUTF8
NLS_LENGTH_SEMANTICSBYTE
NLS_LANGUAGEAMERICAN
The table is defined as follows:
CREATE TABLE XXXXXXXXXXXXXXXXXXXXXX.XXXXXXXXX_INTERFACE
(
XXXXXXXXXXXXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXXXXX VARCHAR2(40 CHAR),
[code]........
We tried adding the following attributes but they did not seem to make any difference:
CHARACTERSET UTF8
STRING SIZES ARE IN CHARACTERS
View 6 Replies
View Related
Mar 9, 2011
I am trying to get query output into textfile.The following procedure is working fine by creating directory as follows in sys user.The output is getting onto text file and it seen on server machine even.My question is I want to see the text file on local machine also instead of everytime connecting to server machine drive.How can I perform that?
create or replace directory INFO_DIR as 'D:Swapna';
grant read, write on directory INFO_DIR to fairpoint;
CREATE OR REPLACE PROCEDURE FAIRPOINT.utl_file_test_write_xls(filename in VARCHAR2 )
IS
output_fileutl_file.file_type;
v_pathVARCHAR2(500);
v_stringVARCHAR2(4000);
v_sqlstrVARCHAR2(2000);
BEGIN
BEGIN
[code]....
View 5 Replies
View Related
Dec 1, 2008
I'm currently new to Oracle 10g SQL and I need to query a large library file.I need to find the different first keywords associated with references written by authors who have more than 10 references in the readings table.
View 4 Replies
View Related
Oct 6, 2012
I want to load query result into .xls file with column names by using plsql.
Like this i need to generate 4 files and zip it then send mail with attachement of that zip file.
Can we automate in PLSQL? If it is possible pls share the script.
Ex: select ename,eno,dept,deptno,sal,hiredate from employee;
ENAME ENO DEPT DEPTNO SAL HIREDATE like this we need to store the query result into .xls file.
View 1 Replies
View Related
