Solved

Get data in ascii format

Posted on 2007-04-04
8
674 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
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
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 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot Question 8 64
Oracle Listener Not Starting 11 44
Can't Access My Database 57 67
Component is listed with a Protocol more than once 3 32
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 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.

803 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