Solved

Get data in ascii format

Posted on 2007-04-04
8
677 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 7

Accepted Solution

by:
gattu007 earned 200 total points
ID: 18850835
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
ID: 18852501
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
ID: 18854991
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 200 total points
ID: 18855903
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
 
LVL 1

Author Comment

by:samir25
ID: 18856188
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
ID: 18856642
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
ID: 18857587
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
ID: 18858297
i floated anohter ques on dot net side... similar type.
hence i m closing this question
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

749 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