Format Output File By Command Spool

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


ADVERTISEMENT

Client Tools :: Format Output File By Command Spool?

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

Client Tools :: Spool Output In PDF Format?

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

PL/SQL :: Arabic Letters In Sqlplus Spool Output In CSV Format

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

PL/SQL :: Spool File Output From Unix To Windows

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

PL/SQL :: Data To Existing File Using Spool Command?

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

Client Tools :: How To Get SQL Query Output Into Text File Without Spool Statement

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

SQL & PL/SQL :: Redirect Output Of A Command To Variable Or File?

Jun 18, 2010

I have a command which get the versions of the schema's for which I dont have access to. It is a utility.

the command goes:

SQL>EXEC ite_dta_tool.dta_open_utl.get_db_version('HH2_SGH21_SS')
DATABASE_VERSION_ID
-----------------------
hh2_ax_db_0.8.1.2.0
Elapsed: 00:00:00.25
SQL>

'HH2_SGH21_SS' is a schema I dont have access to. I have a whole bunch of schemas like this and would like to get versions of all those schemas putting it in a loop in pl/sql.

1: whats the best way get this output to a variable.

2: whats the best way to get the output to a file.

View 8 Replies View Related

Client Tools :: Spool Output And Rollback

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

SQL & PL/SQL :: How To Export Package Body Using Spool Command

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

Client Tools :: Use Spool Command To Export Data For Other Purposes Within Application

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

SQL & PL/SQL :: File Spool Using UTL_FILE

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

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 View Related

Client Tools :: Add Application Name In A Spool File?

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

Globalization :: Spool Brazilian Characters To A File

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

Client Tools :: Creating Spool File Dynamically

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

How To Structure Output Using Windows Command

Sep 4, 2008

The output below is looked mass..

1) the capital letter is the name of column

2) consists 8 data for each column name

Quote: STATISTICS_DATE INSTANCE_NAME GENESIS_PORT
-------------------- ---------------------------------------------------------------- ------------
COUNT TOTAL_TIME MAX_TIME MIN_TIME AVERAGE_TIME
--------- ---------- --------- --------- ------------

03-AUG-08 MatlMgr_UpdateInstance140028
35 6.8685906 .83870006 .06330299 .19624545

View 2 Replies View Related

SQL & PL/SQL :: Write A Command That Will Output DD And Month Only?

Sep 13, 2010

I am trying to write a command that will output DD and Month only.

This is what I have so far:

set serveroutput on size 4000;
declare
todays_date date;
todays_month date;
current_month varchar(20);

[code].....

View 2 Replies View Related

Client Tools :: Spool Clob Column To A Flag File

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

Client Tools :: How To Spool Clob Data Into Flat File

Oct 11, 2011

I want to spool CLOB data into flat file ?

View 6 Replies View Related

SQL & PL/SQL :: How To Get Output In Excel Format

Nov 22, 2012

i need to extract data in excel file through sqlplus .

View 2 Replies View Related

SQL & PL/SQL :: Query To Get Output In Particular Format

Mar 8, 2010

I have attached an SQL script.I would like to have the output in the fashion given below -

PERIOD_START_DATEPERIOD_END_DATEEMPLOYEE_NUMBERFULL_NAMELoan AmountLoan TypePay Value Outstanding Amount
1-Jul-0931-Jul-0924200Jonathan48000048004000
1-Jul-0931-Jul-0924200Jonathan32600022702990

the one in bold and underlined is the Column heading.

View 5 Replies View Related

SQL & PL/SQL :: How To Format Output Of Column

Jun 10, 2011

I have output from select something like :

A_PID B_TEXT id_f
-------------- ----------------------------- -----------
23 B_text sample 9888888

and my question is how can I format the output to have it thinner .

I can do it for text: COLUMN FORMAT B_TEXT A5, but i dont know to apply it for fields with number e.g. A_PID column.

View 4 Replies View Related

SQL & PL/SQL :: How To Output Date Format

Nov 7, 2010

I'm trying to output 1/31/06 into January 31, 2006.Here is what I have so far

declare
v_dt DATE;
v_tx VARCHAR2(2000);
begin

[code]...

But it still won't display month as January.

View 11 Replies View Related

SQL & PL/SQL :: Get Output In Correct Format?

May 15, 2012

I have made below query :

select job,case when deptno=10 then ename ELSE 'null' end e10
,case when deptno=20 then ename ELSE 'null' end e20
,case when deptno=30 then ename ELSE 'null' end e30
from emp
order by 1;

the output is:

JOB E10E20E30
ANALYSTnullSCOTTnull
ANALYSTnullFORDnull
CLERKnullSMITHnull

[code]...

but I want data in one row, i mean for example in case of JOB as 'CLERK' the output should be : i.e.

JOB E1 E2 E3
CLERK MILLER ADAMS JAMES
CLERK SMITH

the complete correct output which I want is:

JOB E1 E2 E3
ANALYST SCOTT
ANALYST FORD
CLERK MILLER ADAMS JAMES

[code]....

View 3 Replies View Related

PL/SQL :: Indexes Output Format?

Jan 16, 2013

currently i am using below version

BANNER                                                                        
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production          
PL/SQL Release 11.2.0.1.0 - Production                                          
CORE     11.2.0.1.0     Production                                                        
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production                         
NLSRTL Version 11.2.0.1.0 - Production                                          
{CODE}

here is my query to find out the list of indexes and positions in a single schema.SELECT

user_indexes.TABLE_NAME||CHR(9)||
user_indexes.INDEX_NAME||CHR(9)||
TRIM(user_ind_columns.COLUMN_NAME)||CHR(9)||
user_ind_columns.column_position ||CHR(9)||

[code]...

i am trying to spool the output to .txt file. in textfile the output should be in below format :

Table_name Index_name column_names
---------------- ---------------- ------------------
ACS_TEST ACS_TEST_IDX COL1,COL2
C_COLLEGE INX_COLL STUDENT_ID,FIRST_NAME,LAST_NAME,DEPT_ID

Note : Based on the position value the column name have to be  placed.

View 2 Replies View Related

SQL & PL/SQL :: Print Output Of Query In Excel Format?

Aug 4, 2010

Is it possible to print the output of a sql query in Excel format without using third party tools like Toad, Hora or any front end Application.

View 10 Replies View Related

SQL & PL/SQL :: Output Data Format From Input Table?

May 18, 2011

how can i get the output format from given input table

View 2 Replies View Related

How To Set Exact Page Size For Query Output Format

Oct 2, 2012

I have following shell script :-

In our testing DB local Server , we are using following script ... We are connecting Via putty ...

CODEexport ORACLE_SID=testdb
sqlplus /nolog <<eof
conn sys/sys as sysdba

[Code].....

QUOTE 1. How can i set pagesize to get good format Our problem is output format is not good .. we set set pagesize from 0 to 120 .. no improvement .. " Again Re-Installed VMware tools also ... no improvbement ....

2. We are planning to set alert message if "Archive destination crossed 60 % Script automatically will generate alert message via mobile or mail.. JUst we will configure crontab ....

View 6 Replies View Related

Convert Oracle Sqlplus Output Into ASCII Format?

Sep 20, 2012

I am using oracle database 9iR2 (9.2.0.8) on windows 2003 server.

Is there any method to convert following output in to ASCII format ??

select ename from scott.emp;

ENAME
--------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved