Sqlplus Query Output Into File?
Feb 4, 2004How 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'
ADVERTISEMENT
SQL & PL/SQL :: Query Output Into Text File
Mar 9, 2011I 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]....
Client Tools :: How To Get SQL Query Output Into Text File Without Spool Statement
Mar 7, 2011I 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.
Client Tools :: Delimited Output From Sqlplus
Mar 3, 2010I have a requirement to get a delimited output file by executing a select query.
For e.g.
select id, name, age from customers;
i need the output as,
id,name,age
123,devi,23
34,abi,20
4900,infy,23
i tried select id||','||name||','||age from customers;
but am getting the following output....
id||','||name||','||age
123,devi,23
34,abi,20
4900,infy,23
But i want to remove those pipes in between the column name.
I tried colsep also... but there am getting the output as.,
id,name,age
123, devi, 23
34, abi, 20
4900, infy, 23
some unwanted spaces in between...but i want the output as this...
id,name,age
123,devi,23
34,abi,20
4900,infy,23
the query which am using is stored in a .sql file.
Convert Oracle Sqlplus Output Into ASCII Format?
Sep 20, 2012I 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
PL/SQL :: Arabic Letters In Sqlplus Spool Output In CSV Format
Jul 4, 2013I 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]....
Precompilers, OCI & OCCI :: Compiling C File (output Of PC File Precompialtion)?
May 4, 2010after precompiling the cmpre.pc file, i got cmpre.c file. when i try compiling this .c file, am facing the following error...
servername:/.../home/usr/compile-> cc -g cmpre.c
ld: 0711-317 ERROR: Undefined symbol: .sqlorat
ld: 0711-317 ERROR: Undefined symbol: .sqlcxt
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.
PL/SQL :: How To Print SQL File Name In Same File Using Sqlplus
Jun 26, 2012Oracle 10g on Linux platform.
I am trying to print the name of .sql script file in that file but could not. Following is the file:
test.sql
select '&0' from dual;But instead of printing, it is asking for input:
SQL> @test.sql
Enter value for 0:
how can i print the file name.
SQL & PL/SQL :: Take Input From File And Give Output To Other Txt File?
Oct 15, 2013i need a script which can take input from a file and give output to other txt file, something like below :
input.txt
001
002
003
004
Query:
select name from employee where employeecode=<ENTRY FROM INPUT.TXT FILE>
Result should go to OUTPUT.TXT File.
SQLPlus Script To Run In Batch File
Nov 1, 2010I am trying to create a batch file which will be executed with windows scheduled task. This batch file will have sqlplus script running Oracle query. I can run this query from the command prompt, no problem,
This is the sqlplus scipt to run query:
C:\Documents and Settings\testuser>sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 1 19:54:25 2010
Copyright © 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: TEST1
Enter password:
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production
TEST1@DB> spool testuserfile
here goes my query
TEST1@DB> spool off
TEST1@DB> exit
Disconnected from Oracle Database 10g Release 10.2.0.4.0 - Production
C:\Documents and Settings\testuser>
How do I put this in a batch file. I was able to figure how to logon, but I get stuck at the spool command. My batch file starts like this:
cd C:\Documents and Settings\testuser
sqlplus TEST1/Password
Client Tools :: Conditionals In A SQLPlus Batch File?
Oct 23, 2012From within a DOS batch file I make the following call:
sqlplus [User]/[password]@[server] @batch_script.sql [Server] [User]
Within the file batch_script.sql I have the following lines:
spool output_batch.lst
connect &&2/[password]@&&1;
@SomeOtherFile.sql
I want to change the last line to something like this:
if &&1 = 'Something' then
@SomeOtherFile1.sql
else
@SomeOtherFile2.sql
end if;
I get the following result
SQL> if &&1 = "something" then
SP2-0734: unknown command beginning "if &&1 = "..." - rest of line ignored.
I took a shot at using the "If" statement and missed.
Client Tools :: How To Substitute Value From A Query In Sqlplus
Apr 2, 2012I need generate a script like this.
spool loadvalues.sql
INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM TABLE_NAME;
spool off;
I would like to substitute TABLE_NAME with each value from the following query;
select table_name from user_tables
where table_name like 'SALES%'
so there are about 45 SALES tables ex: SALES_IDAHO,SALES_MICHIGAN etc.so my spool file(loadvalues.sql) should look like this ex:
INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM SALES_IDAHO;
sales_michingan should have script like
INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM SALES_MICHIGAN;
Precompilers, OCI & OCCI :: Running Query In PRO*C That Works In SQLPLUS
Sep 4, 2008I have a query similar to the one below which is embedded in a PRO*C program. The query works fine when run in the PRO*C program against a 8i database but fails with an "ORA-02015: cannot select FOR UPDATE from remote table" error when run against a 10g database. The PRO*C program is executing the sql using "EXEC SQL".
QUERY: Select last_name, first_name from Member
where ....
FOR UPDATE OF LAST_NAME;
The other thing to note is this SQL query works fine via sqlplus in a 10g environment.
ADDITIONAL DETAILS: The above query is selecting data from a base table via a user view
VIEW: select * from
otherschema.member@connection_identifier;
This view was created in this manner to allow the user account access to the underlaying table without creating explicit permissions.
Client Tools :: Run A Query In Background In Toad Or Sqlplus
Feb 3, 2011i do have a set of monthly process, where i need to run query one after the other. Some query will take one hours time to complete but some will take 10 hours or more to execute. My problem is that we have wait or machine shouldn't hang or log off during that time ...
I am executing the query in TOAD and windows SQL*Plus. Query can run Parallel at a time.
Is the there anyway to run the query in the background like what we do in the unix nohup command in toad?
Pass Variables For UserID And Password To Sqlplus In Windows From Text File?
Jul 23, 2013I would like to store user-id, password and the TNS-Entry in a small text file and pass them to a script to log in and execute another script on the database from Windows.
Example of the text file samp.txt that stores user info:
username=Jdoe
password=candybar
db_sid:orcl
main script main.bat is as follows:
--read the samp.txt do not know how to do it
sqlplus -s &username/&password@&db_sid @db_info.sql
db_info.sql contents:
column tm new_value file_time noprint
select to_char(sysdate, 'YYYYMMDD') tm from dual ;
spool C:\server\DB_Report_&file_time..log
select * from v$instance;
spool off;
exit
Client Tools :: Display Clob Column In Text File In Sqlplus
Mar 30, 2012I have extracted data from table and write into one text via sqlplus utility in shell scripts. i got correct output. i am having two issues on the output file
1) Outfile file size is huge high compare then table segment data.
2) last column having extra space.
The output column is clob datatype. so i have added set long 50000 and set longchunksize 50000 parameter. after adding these only i got above issues. without two options, i am not getting this isssue but lines are wrapped.
#Set the scripts Path
SCRIPTS_PATH="/usr/local/ccms/gpa/svr/scripts"
echo $SCRIPTS_PATH
[Code]....
Performance Tuning :: HIBERNATE Query Runs Slow But Ok In SQLPLUS
Sep 14, 2012I have a query which takes 5 minutes when run through the java app which uses hibernate. I've cut and pasted the SQL directly from hiberate trace file and run it in sqlplus/sqldeveloper and it runs instantly (0.01 seconds)(uses the index all ok and explain plan looks good - see below.) I don't know how to get the explain plan when it's running through the app or why it should be any different anyway as the query is identical.
My query is as follows:
SELECT /*+ INDEX (SPD SPD_SEQ_CODE) */ SPD.*
FROM SEQ_ADDR_DATA SPD, SEQ_ADDR_LEVELS SPL
WHERE SPD.SPVR_ID = '10'
AND SPL.SPLE_ID = SPD.SPLE_ID
AND SPL.SPLE_LEVEL <= '2' AND SPDA_ID NOT IN
[code]....
SQL & PL/SQL :: Formatting Output File
Sep 6, 2012CREATE OR REPLACE PROCEDURE A516907.proc_data_T_DTIA_TS_ADDR
is cursor CSR1 IS SELECT * FROM T_DTIA_TS_ADDR ;
v_file UTL_FILE.FILE_TYPE ;
BEGIN
v_file := UTL_FILE.FOPEN( 'DATA_PUMP_DIR' , 'T_DTIA_TS_ADDR.txt','w',32767);
FOR CS1 IN CSR1
[Code]....
The procedure above is dynamically generated. It's simple enough in that I am calling "the parent procedure" by passing the table name in. After this the procedure for creating the output data file for that table is generated and that's what we have above. I am doing this to load data into DB2.
What I need is to be able to generate the output such that if the column is of type varchar, char (anything non numeric) then the data should have " around it. If the data is of type number it should not have ".
I suppose I could use something as follows in lieu of the procedure above
Declare
cursor c1 is select column_name, data_type from user_tab_Cols where table_name = 'Table Name here';
-- I would like to know how to use the utl_file.put_line
-- where if the column data type is varchar 2
-- I could get ' ||"|| CS1.TS_ADDR_TY_CD||" || '
-- and if it's a number -- || CS1.CB_TS_DTIS_ID ||
Groupby Query - Not Getting Output?
Jun 25, 2013I have a table with data as follows
Source name address city
File Y N N
File N N Y
DB Y N Y
DB N Y N
XML Y Y N
I am trying to get output as follows
Source Y/N CountName CountAddress
File Y 1 0
File N 1 2
DB Y 1 1
DB N 1 1
XML Y 1 1
XML N 0 0
SQL & PL/SQL :: Combine Query Output In One Row
Aug 13, 2010If I have below two sql statment.
select count(*) from table_a;
select count(*) from table_b;
How can I output it in one row together like below
Table_a | Table_b|
10 | 20|
give me detailed example ....
SQL & PL/SQL :: Query To Get Output In Particular Format
Mar 8, 2010I 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.
SQL & PL/SQL :: Can Add Description To Output Of Query
May 10, 2011i have created a table called table2,with only one column as text,
SQL> select regexp_replace(text,'[:]',chr(10)) text from table2;
TEXT
--------------------------------------------------------------------------------
1
amar
11-jan-2011
15000
2
manju
22-feb-2011
20000
and i have to get output like this,
TEXT
--------------------------------------------------------------------------------
ID
1
NAME
amar
DATEOFJOINING
11-jan-2011
SALARY
150000
is it possible to get output like above?
Format Output File By Command Spool
Jan 20, 2011I 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"
SQL & PL/SQL :: Redirect Output Of A Command To Variable Or File?
Jun 18, 2010I 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.
PL/SQL :: Spool File Output From Unix To Windows
May 22, 2013I'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?
SQL & PL/SQL :: Oracle Query For Days Output
Aug 5, 2010I want to pull out the output only when Day is 1 and 2 and 3. I don't want if either Day is 1 or Day is 2 or Day is 3 is present.
Test Case
--Creat Table
create table dummy_name
(name varchar2(5) not null,
day number(3) not null);
--Insert Values
insert into dummy_name values ('A', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 3);
insert into dummy_name values ('B', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 1);
[code]...
SQL & PL/SQL :: Column Renaming From Query Output?
Jul 14, 2010I want to be able to name a column created from my query.
Query is:
select A.OrigRef, A.DisplayName, A.ExtCode, count(CalcId)
from OrigRefView A, CalcView B where A.OrigRef = B.NewRef and A.OrigRef like 'AB%'
group by A.OrigRef, A.DisplayName, A.ExtCode
order by A.SusRef
and it returns the Count in "column4" Is there a way I can get the query to output a different column name without creating a whole new table (i.e. not by creating a new table for my query output and then running a new procedure at the end to rename the column...)?
i.e. I want "column4" to read "CountofCalId"
SQL & PL/SQL :: Query In Vs Exists - Both Are Giving Different Output?
Feb 3, 2012SQL> select count(*) from emp where empno not in ( select mgr from emp );
COUNT(*)
----------
0
SQL> select count(*) from emp T1
2 where not exists ( select null from emp T2 where t2.mgr = t1.empno );
COUNT(*)
----------
9
I fired above query and I totally confused that why both are giving different output.
Forms :: To Output Query In Vertical Way
Dec 27, 2011i have output query in 1 row, for example
Id Name Salary
100 John 2000
i need to output the same query in vertical way like:
100
John
2000
How To Transfer Output File From One Server To Another Using Stored Procedure
Apr 9, 2012We are running a store procedure by calling it from shell script.The shell script is running in Server1 and the stored procedure is running in server2(which is the data base server.).
Functionality of the stored procedure:Generating a tab delimited file and place in a directory.
Requirement: The requirement is that the store should be modified in such a way that it should drop the output file to Server1 itself(The server from where it is getting called).
Attaching the store procedure for reference. mIn the store procedure,we have a variable "lv_file_dir" which currently refers to the directory location in Databse server(Server 2) We have to change this SP such that it drops the generated file in Server1 itself(From where the shell script is running).
Attached File(s)
Procedure.txt ( 3.28K )
Number of downloads: 4