• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 687
  • Last Modified:

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
0
samir25
Asked:
samir25
3 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now