SQL & PL/SQL :: Query Output Into Text File
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
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
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
May 30, 2011
Is it possible to get the same name of file name from imported text file?
What i've is, I import the text file then I exported it.
can I get the same name of the imported text file?
View 2 Replies
View Related
May 17, 2012
I want to execute a query which automatically saves the result of a set of queries in a text file and have to send that as an attachment thru a mail (using UTL_MAIL or UTL_SMTP) thru a stored procedure...saving the result in a text file in Toad.
View 2 Replies
View Related
Dec 12, 2012
I'm running this query on sql developer trying to export large file but its not executing.
set head off
spool c:myoracle.txt
select txt_name_insurer||'~'||txt_policy_number from Table_Name where rownum<'10';
spool off
set head on
Error:- line 1: SQLPLUS Command Skipped: set head on
View 16 Replies
View Related
Dec 30, 2010
I have a requirement in one of my forms screen.I have a text box(large text area) which should display a help text file when i move my cursor on the topics displayed on the screen.know the code and the properties to be changed in the text box to accommodate large external file text.
View 1 Replies
View Related
Oct 12, 2011
I have a requirement to capture a large number character string which is an output from http request. I created a CLOB datatype to capture it. I see an issue of truncating of the characters after 1998 character's (Block 1, below). If i assign the same ouput put and assign to variable of CLOB type and displaying 3794 Character as required(Block 2) which i am expecting the same in Block 1 too to see entire string ....
For your reference pl copy below link and past in explorer to see th entire output put.
********
http://gissms-stg-05:8399/arcgis/rest/services/ESRI_Route_NA_SA_CF/NAServer/Service%20Area/solveServiceArea?facilities=-95.452487%2C2 9.789048&barriers=&polylineBarriers=&polygonBarriers=&defaultBreaks=1&travelDirection=esriNATravelDirectionFromFa cility&outputGeometryPrecision=&outputGeometryPrecisionUnits=esriDecimalDegrees&f=json
**************************************************************
BLOCK : 1
declare
l_text3 CLOB;
begin
[code]...
View 3 Replies
View Related
May 4, 2010
after 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.
View 1 Replies
View Related
Jan 27, 2012
I have developed one rdf in Text Output Format.In this some special characters is coming for Text Output format of rdf.Shall i do any adjustments in layout? How to remove these special characters?
View 3 Replies
View Related
Apr 9, 2011
Oracle10g to Sybase12.5 Migration:- How a Oracle dump file can be converted to any text file/xls file which will be loaded in sybase database later through BCP.
means
1.Exporting objects as dump file from Oracle.
2.Is there any tool/process available that can convert this into csv/txt/xls file.
3.This files can be loaded in sybase.
View 2 Replies
View Related
Oct 15, 2013
i 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.
View 7 Replies
View Related
Sep 6, 2012
CREATE 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 ||
View 33 Replies
View Related
Jun 25, 2013
I 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
View 2 Replies
View Related
Aug 13, 2010
If 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 ....
View 2 Replies
View Related
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
May 10, 2011
i 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?
View 20 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
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
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
Aug 5, 2010
I 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]...
View 8 Replies
View Related
Jul 14, 2010
I 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"
View 4 Replies
View Related
Feb 3, 2012
SQL> 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.
View 6 Replies
View Related
Dec 27, 2011
i 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
View 3 Replies
View Related
Apr 9, 2012
We 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
View 2 Replies
View Related
Feb 16, 2010
In Oracle report, there is no duplicate rows I am seeing in the report output screen but getting duplicate rows in output file generated.
View 8 Replies
View Related
May 10, 2012
I'm calling myreport.rdf from a form using the rwservlet and destype=mail and desformat=pdf. The report file is attached to the email as myreport.pdf. However, I want to have the output file named myfilename.pdf. Is there any way to accomplish this short of using destype=file and then renaming it and sending that file?
View 2 Replies
View Related
Jul 10, 2013
I've noticed that the backup files produced by RMAN (Database is 11.2.0.2.0 Enterprise Edition) )on a RHEL5.5. server which is running ASM have group permissions assigned to ASMADMIN (so the file permissions are assigned to ORACLE:ASMADMIN) as opposed to OINSTALL or any other group.
While this is not an issue (it's not currently impacting us in any way) I'm curious as to how and why this happens. I suspect it is related to the fact that the oracle executable has these permissions also but I can't find any material to confirm my suspicion.
View 2 Replies
View Related
Aug 2, 2013
I need to take RMAN full backup by every Sunday night and Wednesday night . I have the netbackup script which will take the backup to media.
Question is:1. We have 15 - backup scripts for 15 DB in that server , so if i configure crontab for backup do i need to give all 15 scripts one by one or (*) will work . ie) 00 20 * * 3,7 /app/oracle/rman/scripts/hot_db_<sid>.sh
instead of this if i give : 00 20 * * 3,7 /app/oracle/rman/scripts/hot_db_*.sh then it will works fine or not ? 2.
Already my backup will create .output file in the same location , do i need to give output file location in crontab using ( > ) ? 3.
Is the above crontab timing is correct ? (3,7) for Wednesday and Sunday 8pm? so Sunday is 0(zero) or 7 ?
View 6 Replies
View Related
Apr 28, 2008
i have be requested to create a .txt file. Its for a program that will read the txt file i create to produce a letter i.e I will be extracting, TITLE, FORENAME, SURNAME etc
MR
JOE
BLOGGS
Here is the my code so far,
SELECT
LPA_INPUT.INPUT_TITLE,
LPA_INPUT.INPUT_SURNAME,
LPA_HISTORY.LPA_AMT,
LPA_HISTORY.ELIG_RATE,
LPA_HISTORY.RATE_REBATE,
LPA_HISTORY.RR_AMT,
LPA_HISTORY.LPA_APPLIC,
LPA_HISTORY.LPA_AMT
FROM LPA_HISTORY, LPA_INPUT
WHERE LPA_HISTORY.CLAIM_NO = LPA_INPUT.CLAIM_NO
----------------------------------------------------
Iv been asked to have these eight fields looping over and over for all the records in the database, So im not sure how to do that and how to generate it in a txt file?! I have to produce it in a script and not by a Export/Import wizard in sql server mangement studio!!!
View 11 Replies
View Related
