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

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

Sqlplus Query Output Into File?

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

Forms :: Export Text File With Same File As Imported Text File?

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

Client Tools :: SQL For Saving Query Result In A Text File

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

Client Tools :: Exporting Result To Text File Through Query?

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

Forms :: Text Box To Accommodate Large External File Text

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

SQL & PL/SQL :: Http Output Text Truncating?

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

Precompilers, OCI & OCCI :: Compiling C File (output Of PC File Precompialtion)?

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

Reports & Discoverer :: Special Characters Coming In Text Output Format Of Oracle?

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

Backup & Recovery :: ORACLE Dump File To Text File

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

SQL & PL/SQL :: Take Input From File And Give Output To Other Txt File?

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

SQL & PL/SQL :: Formatting Output File

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

Groupby Query - Not Getting Output?

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

SQL & PL/SQL :: Combine Query Output In One Row

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

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 :: Can Add Description To Output Of Query

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

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

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

SQL & PL/SQL :: Oracle Query For Days Output

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

SQL & PL/SQL :: Column Renaming From Query Output?

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

SQL & PL/SQL :: Query In Vs Exists - Both Are Giving Different Output?

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

Forms :: To Output Query In Vertical Way

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

How To Transfer Output File From One Server To Another Using Stored Procedure

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

Reports & Discoverer :: Suppress Duplicate In Output File

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

Reports & Discoverer :: Rename Report Output File

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

RMAN :: Output File Permissions With ASM / Grid Infrastructure

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

RMAN :: Give Output File Location In Crontab Using ( > )?

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

Generate A Text File

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







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