Get data in ascii format

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
LVL 1
samir25Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MohanKNairCommented:
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
samir25Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SujithData ArchitectCommented:
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
samir25Author Commented:
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
SujithData ArchitectCommented:
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
samir25Author Commented:
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
samir25Author Commented:
i floated anohter ques on dot net side... similar type.
hence i m closing this question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.