SQL & PL/SQL :: Formatting The TEXT In Exported File?
Nov 15, 2011
How to make the multiple lines of character in single line. Below is the example where i am getting the value when we export the data from the database table.
----------------------
Welcome Aboard customer contact based on Account activation.
The current selection criteria for Welcome Aboard is based at the service level. Therefore, each time a customer connects a service, Welcome Aboard material is mailed to the customer. This is causing the following problems;
Customer complaints. If a service or a number of services are connected on a particular day, it will result in (ie except for some minor exclusions) a single Welcome Aboard pack being sent to each individual customer. As you can expect for corporate accounts, they receive a high volume of this material. Other problems occur when, service/s are deactivated and reactivated, each time a Welcome aboard pack will be distributed.
High costs to Telstra. Each MNET service connected may produce a Welcome aboard pack. This volume of material currently costs Telstra over $500,000 per quarter for Welcome Aboard alone.
------------------------------
here is what i want to the above data to export:
----------------------
Welcome Aboard customer contact based on Account activation.The current selection criteria for Welcome Aboard is based at the service level. Therefore, each time a customer connects a service, Welcome Aboard material is mailed to the customer. This is causing the following problems;Customer complaints. If a service or a number of services are connected on a particular day, it will result in (ie except for some minor exclusions) a single Welcome Aboard pack being sent to each individual customer. As you can expect for corporate accounts, they receive a high volume of this material. Other problems occur when, service/s are deactivated and reactivated, each time a Welcome aboard pack will be distributed. High costs to Telstra. Each MNET service connected may produce a Welcome aboard pack. This volume of material currently costs Telstra over $500,000 per quarter for Welcome Aboard alone.
View 3 Replies
ADVERTISEMENT
Feb 26, 2013
I have a query then I write it to a text file using utl_file. However, I'm having some trouble formatting the data itself on the text file. =(
How would I align these records against its header considering the length of the Name/subscriber no. column which is not constant. Would LPAD work?
ID Name Subscriber No
1 James Smith 12454654
123 Scott Kevin Joseph 4564564
21 Dana Marice Curtis 455
2 Stephanie Hans Walter 23134
View 1 Replies
View Related
Apr 28, 2011
I am trying to import the database and i see the following error:
IMP-00051: Direct path exported dump file contains illegal column length imp abruptly stops. my source and destination database is as follows:
Source: 9.2.0.8
Destinatin: 11g-r2.
I used exp with the following options:
direct=y
buffer=899989898
recordlength=64000
File=1.dmp,2.dmp,3.dmp,4.dmp,5.dmp,6.dmp,7.dmp,8.dmp,9.dmp,10.dmp,11.dmp,12.dmp,
13.dmp,14.dmp,15.dmp,16.dmp,17.dmp,18.dmp,19.dmp,20.dmp,21.dmp,22.dmp,23.dmp,24.
dmp,25.dmp,26.dmp,27.dmp,28.dmp,29.dmp,30.dmp
grants=y
compress=y
FILESIZE=25g
owner=BRM
Using imp with the following option:
indexes=n
buffer=99989898
recordlength=64000
File=1.dmp,2.dmp,3.dmp,4.dmp,5.dmp,6.dmp,7.dmp,8.dmp,9.dmp,10.dmp,11.dmp,12.dmp,
13.dmp,14.dmp,15.dmp,16.dmp,17.dmp,18.dmp,19.dmp,
20.dmp,21.dmp,22.dmp,23.dmp,24.dmp,25.dmp,26.dmp,27.dmp,28.dmp,29.dmp,30.dmp
analyze=n
grants=y
fromuser=brm
touser=brm
statistics=none
why my imp failing?
View 4 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
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
Aug 26, 2013
I need the below output in oracle reports 6i.
this is a testing string retrieved from database.
now what i need is that to make bold the specific words in a string came from database.
View 2 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
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
Apr 19, 2010
i have exported data from excel. data is exported successfully but with it some characters are also joined. it happened with every row in the table. what is to be done to remove it? and what to do with columns whose data type is number? Data is as follows underlined are the char. which are joined while transfer
eid ename age salary
123 MARK 31 40000
View 11 Replies
View Related
Apr 22, 2012
We are using reports 3.0 and I want the report output exported to a excel sheet.. I got package contains some procedures, they have the below line where my report writer throwing error
TYPE ExcelCells IS TABLE OF ExcelCell;
encountered the symbol ";" when expecting one of the following .[@% not null range index.I doubt whether Reports 3.0 support "TYPE".
View 4 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
Feb 11, 2011
is there anyway to convert plb file to sql file or text file
View 20 Replies
View Related
Dec 14, 2006
I want to load data to a table and from a simple file text, using a Vb.net application which will connect to a oracle 10g , or a SqlServer or a MySql database, depending the params.
When i connect to a SqlServer Database i use the sql command "BULK INSERT CODPOSTAL2 FROM file.txt with( DATAFILETYPE = 'char',FIELDTERMINATOR = ';', ROWTERMINATOR = '
')"m" and it works fine.
With a DB Mysql i use "LOAD DATA INFILE file.txt INTO TABLE CODPOSTAL2 FIELDS TERMINATED BY ';''" and also works.
My problem is with Oracle. I tried the same example as MySql, but it gaves the error "wrong" ou "unknown command". I also tried in Sql*Plus but it seems to not recognised the command "LOAD".
Another thing, i can't use the Oracle Loader, it must be like this.
View 5 Replies
View Related
Aug 2, 2010
i want to convert an excel file into text and then into oracle table.
View 6 Replies
View Related
Apr 11, 2013
suppose i have a file named chk.txt and I have write 10 line in that file and i just want to write some text at line 5 or line first then how we can do this ?
View 3 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
Apr 4, 2013
I create a text file with cobol, and now I need to remove the last character of each line with pl / sql. For example:
12 *
23 *
45 *
I need to remove *
View 7 Replies
View Related
Feb 23, 2013
How to write to text file using oracle? And how do I handle spaces/next line? (i was trying to use spaces(ch(32)), however it is just converted into squares in the text file.)
View 5 Replies
View Related
Oct 9, 2013
i want table data exported to txt file, data is around 3200000,
View 14 Replies
View Related
May 3, 2011
I want to import text from text file. How i can import in oracle forms ( oracle 9i)
View 7 Replies
View Related
Aug 8, 2012
I would like to load a text file into an oracle table.
View 10 Replies
View Related
Apr 14, 2010
I have text file with data in UNIX. I want to clear only records(make empty the file) using UTL_FILE..
I know we can remove the text file from UNIX and create file again with same name...
Is there any other way??
View 5 Replies
View Related
Feb 9, 2011
We are receiving data from our upstream through text file, they are extracting that text file from their oracle table.In the table in ename colmn some names are having the special character like
Chng
OReilly
We are loading the data from that text file into our oracle tables and the names are loading as it is.Now we are extracting data as text file from our tables which is loaded from or upstream,then the special character is changing as the names are changing as Chng OReilly. We are loading the data from the text file into our tables and the names are loading as
Chng OReilly and i tried to replace the special character with while loading but it is loading as .If I try to change the character with '(Apostrophe) while loading it is changing.
The data type of the column is VARCHAR2(20)
NLS_CHARACTERSET is UTF8
Version Oracle 9i
View 6 Replies
View Related
Jun 18, 2013
I want to load data from oracle table to flat file(csv/text file). Is there SQL query to do this?
View 14 Replies
View Related
Sep 4, 2012
I have a report that runs fine if I run it from the developer whether to preview or to file. The users are running this report from an Oracle Forms app and have a drop down for the destination format. If they run it to PDF for example, the report runs fine. If they run it to TXT132, TXT180, or TXT they get a "REP-1219 'FIELD_NAME' has no size -- length or width is zero".
Of course I am unable to get into the test application right now and as I mentioned above, I am unable to duplicate the error when I run the report from the developer. I am not sure why I would get a REP-1219 only when running to a Text file.
View 9 Replies
View Related
Oct 8, 2013
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
I am trying to write the Turkey character stored in the table in VARCHAR2 to Unix file. But when the text is written to the unix, the characters are coming as junk.
The output of the file it is writing is as below after the execution
Fis Içe Aktarma Olusturuldu Header
Fis Içe Aktarma Olusturuldu
Fiş İçe Aktarma Oluşturuldu
Instead I expect the chararecter to be as "Fiş İçe Aktarma Oluşturuldu Header" which when converted to English will show as "Created Import Plug Header".
DROP TABLE TEST_JUNK_CHAR
/
CREATE TABLE TEST_JUNK_CHAR (primary_description VARCHAR2(400))
/
INSERT INTO TEST_JUNK_CHAR VALUES('Fiş İçe Aktarma Oluşturuldu Header')
[code]....
View 13 Replies
View Related
May 27, 2011
Just want to know on how to export text file from oracle? I have here the query on how to import.
begin
in_file := TEXT_IO.FOPEN(:path_file,'r');
LOOP
BEGIN
SYNCHRONIZE;
TEXT_IO.GET_LINE (in_file, linebuff);
v_no_errors := TRUE;
[code].....
But this is my first time to export the text file.
View 2 Replies
View Related
Feb 9, 2011
We are receiving data from our upstream through text file, they are extracting that text file from their oracle table.In the table in ename colmn some names are having the special character like '
Ch'ng
O'Reilly
We are loading the data from that text file into our oracle tables and the names are loading as it is.Now we are extracting data as text file from our tables which is loaded from or upstream,then the special character ' is changing as ¿the names are changing as Chang Reilly.
We are loading the data from the text file into our tables and the names are loading as Chang O¿Reilly and i tried to replace the special character ¿ with ' while loading but it is loading as ¿.
If I try to change the character ¿ with '(Apostrophe) while loading it is changing.
The data type of the column is VARCHAR2(20)
NLS_CHARACTERSET is UTF8
View 27 Replies
View Related
Dec 24, 2010
I'm working on a report (written with Oracle report builder 6i) for the SMS system.I want the the report results (list of mobile numbers) to be outputted directly into a text file.I have accomplished that with the following parameters:
DESTYPE = File
DESNAME = C:file_name
MODE = character
this work well for the first time but in the subsequent runs, the report rewrite the same file deleting the result from the run before.can I manage to have each run result written in separate file with the name formatted in specific way like (sms_parametervalue_date) or something. or at least is it possible to change the system parameter DESNAME via user parameter at runtime, so I can input the new file name into the parameter form.
View 1 Replies
View Related
Sep 25, 2013
I have uploaded the text file since format is not fitting here.I have a table called xyz and it has two colums called QU and collectionID..The data is stored in the table is like Scenario 1 but I want to extract the data to look like Scenario 2
Scenario 1
QU CollectionID
Blade: qcusrea11 / NOB1, null
null, SMRE11
null, SMRE21
null, SMRE31
null, SMRE41
[code]....
Scenario 2
QUCollectionID
Blade: qcusrea11 / NOB1 SMRE11
Blade: qcusrea11 / NOB1 SMRE21
Blade: qcusrea11 / NOB1 SMRE31
Blade: qcusrea11 / NOB1 SMRE41
[code]...
View 3 Replies
View Related
