Solved

Get data in ascii format

Posted on 2007-04-04
8
670 Views
Last Modified: 2013-12-18
hi
I have a requirement in which i need to fetch some desired data from the oracle database and the result should be in ascii format. how is this possible. what language media do i need to select. is it possible through asp.net?i m using oracle 10g
pls suggest
thanks a lot
0
Comment
Question by:samir25
8 Comments
 
LVL 7

Accepted Solution

by:
gattu007 earned 200 total points
Comment Utility
You can use the below metalink script to unload data

Subject:       Script: Unload Data to Fixed-Width Text
        Doc ID:       Note:1019522.6



---------------------------------------------------

--  Generate the unload script

---------------------------------------------------

spool unload_fixed2.sql

select 'SET HEADING OFF FEEDBACK OFF ECHO OFF VERIFY OFF SPACE 0 PAGESIZE 0

TERMOUT OFF'

  from dual

/

 

--Calculate the sum of all output field lengths and set the output record size

select 'SET LINESIZE '

       || (sum(decode(data_type,

                      'CHAR',data_length,

                      'VARCHAR',data_length,

    'VARCHAR2',data_length,

                      'DATE',14,

   'NUMBER',decode(data_precision,

                                   '',&default_precision+2,

greatest(data_precision-data_scale,1)+decode(data_scale,0,0,1)+data_scale)+1,

                      'FLOAT',&default_precision+2,

           data_length)))

  from dba_tab_columns

 where owner=upper('&&owner')

   and table_name=upper('&&table_name')

/

 

--  Generate an appropriate SQL*Plus COLUMN command to control formatting of

--  each output field

select 'COLUMN ' || rpad('"'||column_name||'"',32)

       || ' FORMAT '

       || rpad(decode(data_type,

                   'CHAR','A'||data_length,

                   'VARCHAR2','A'||data_length,

                   'VARCHAR','A'||data_length,'DATE','A14',

'NUMBER',decode(data_precision,'',rpad('0',&default_precision-&default_scale,'9'

)||'.'

||rpad('9',&default_scale,'9'), rpad('0',greatest(data_precision-data_scale,1),'

9') ||

decode(data_scale,0,'','.') ||

decode(data_scale,0,'',rpad('9',data_scale,'9'))),

'FLOAT',rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&default_

scale,'9'),

                 'ERROR'),40)|| ' HEADING ''X'''

  from dba_tab_columns

 where owner=upper('&&owner')

   and table_name=upper('&&table_name')

 order by column_id

/

--  Generate the actual SELECT statement to unload table data

select 'SPOOL &&owner..&&table_name..DAT'

  from dual

/

column var1 noprint

column var2 noprint

select 'a' var1, 0 var2, 'SELECT '

  from dual

union

select 'b', column_id, decode(column_id, 1, '    ', '  , ')||

decode(data_type,'DATE','to_char('||'"'||column_name||'"'||',''YYYYMMDDHH24MISS

'') '||'"'||column_name||'"'  ,

                       '"'||column_name||'"')

  from dba_tab_columns

 where owner=upper('&&owner')

   and table_name=upper('&&table_name')

union

select 'c', 0, 'FROM &&owner..&&table_name'

from dual

union

select 'd', 0, ';'

  from dual

 order by 1,2

/

select 'SPOOL OFF'

  from dual

/

select 'SET TERMOUT ON'

  from dual

/

 

spool off

-----------------------------------------------------------------------------

--  Generate the SQL*Loader control file

-----------------------------------------------------------------------------

set lines 120 pages 0

spool &&owner..&&table_name..CTL

select 'a' var1, 0 var2, 'OPTIONS(DIRECT=TRUE)'

  from dual

union

select 'b', 0, 'LOAD DATA'

  from dual

union

select 'c', 0, 'INFILE  ''&&owner..&&table_name..DAT'''

  from dual

union

select 'd', 0, 'BADFILE  &&owner..&&table_name..BAD'

  from dual

union

select 'e', 0, 'DISCARDFILE  &&owner..&&table_name..DSC'

  from dual

union

select 'f', 0, 'DISCARDMAX 999'

  from dual

union

select 'm', 0, 'INTO TABLE &&owner..&&table_name'

  from dual

union

select 'n', column_id,

rpad(decode(column_id,1,'(',',')||'"'||column_name||'"',31)

                       || decode(data_type,

 'CHAR','CHAR('||data_length||')',

                                 'VARCHAR','CHAR('||data_length||')',

                             'VARCHAR2','CHAR('||data_length||')',

                    'DATE','DATE(14) "YYYYMMDDHH24MISS"',

 'NUMBER','DECIMAL

EXTERNAL('||decode(data_precision,

'',&default_precision+2, greatest(data_precision-data_scale,1)+decode(data_scale

,0,0,1)+data_scale+1)

              ||')',

                                 'FLOAT','DECIMAL

EXTERNAL('||to_char(&default_precision+2)||')',

  'ERROR--'||data_type)

                       || ' NULLIF ("' ||column_name||'" = BLANKS)'

  from dba_tab_columns

 where owner = upper('&&owner')

   and table_name = upper('&&table_name')

union

select 'z', 0, ')'

  from dual

 order by 1, 2

/

 

spool off

 

-----------------------------------------------------------------------------

--  Cleanup

-----------------------------------------------------------------------------

clear column

clear break

clear compute

undef owner

undef table_name

undef default_precision

undef default_scale
0
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 100 total points
Comment Utility
use "set markup html on" to generate the output in html format. This will generate the output as html table and each record will be displayed as a single row. Convert this into text format.
0
 
LVL 1

Author Comment

by:samir25
Comment Utility
i didnt understand a word you wrote. i may have a query which will fetch some data from multiple tables of a database. and i need to use asp.net.

0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 200 total points
Comment Utility
Show a sample input data and the expected output.

There are built-in functions
ASCII - returns the ascii value of a character
ASCIISTR - converts a string to its ascii equivalent in the database character set

You can consider using them.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:samir25
Comment Utility
Well to be honest I am new to this ... I have no data at the moment as still my machine doesnt have oracle installed.
but an application that i need develop soon is i need to query the oracle 10g db and the result i need to convert in ascii format.
after going thru the net i understand that ascii format is simply text ... so what is the extension of the file then?

also i need just concepts at the moment. I want to know what will I need to do to solve this. I should have been more clear. is it not possble to develop an interface on asp.net and ask the user for the parameters he want for a product and then run the query using those parameters and get the final output as a file (in ascii format)? pls help clarify
thanks a lot for your feedback
0
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
If you have to provide an interface to the user to choose the columns then you approach should be fine. Otherwise, there are a lot of ways to do this.

For example you can use the sqlplus spool command to get the data into a text file.
0
 
LVL 1

Author Comment

by:samir25
Comment Utility
i dont know much about the ascii format. I only read thru googling. pls help clarify me that ascii format means text file? if its txt file then why do we call it ascii format we can call it txt format. there must be some difference right?

what will be the file extension of the file then?

basically when we ship r product to the customer we used to send a hard copy of the detials of the product. but now we need to send it in the e-data form (electronic data) in ascii format. so maybe we need to create an interface where i will run a query and then fetch the respective data. but finally data should be in ascii format.

can u suggest me how can i get the data in ascii format. do u think i should float this question in asp.net side?
0
 
LVL 1

Author Comment

by:samir25
Comment Utility
i floated anohter ques on dot net side... similar type.
hence i m closing this question
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now