Solved

Oracle SQL - formating a returned string from a query

Posted on 2014-09-09
3
509 Views
Last Modified: 2014-09-09
I'm doing a number of queries that return a result string. These strings are placed in a text file and then emailed via a PowerBasic exe.  The queries retrun multiple "records", each within a RESULT_STRING that is placed in an array.

A simple example of the result strings piece of the query would look like:
B.PRODUCT_NAME || '* (#'||TO_CHAR(A.PRODUCT_ID) ||')' AS RESULT_STRING

Open in new window


The width of the product name varies and the max length is unknown at the time.
I would like to format the string within the SQL if possible so that it would be set up in columns.  The result would look like:
PRODUCT1          10234
PROD2                  31323
MYPRODUCT              16
.....

Open in new window


Is this possible within SQL ?
My other alternative is to handle it within the calling program, but I was hoping I could do it in the query.
0
Comment
Question by:GNOVAK
3 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 250 total points
ID: 40312488
This is normally better done in the front-end..
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 40312632
the width might be unknown but hopefully something you can take a guess at and simply pick something big enough

user RPAD to create columns at fixed positions.    For example:

select rpad(col1,100) || rpad(col2,20) || rpad(col3,50) as result_string from yourtable

Note, the total concatenation must still be less than or equal to 4000 bytes
0
 

Author Closing Comment

by:GNOVAK
ID: 40313011
Thanks - I thought there might be some magic I'm missing and just wanted to make sure. Appreciate the help.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

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…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

776 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