INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Processing a result set output to a Table

Processing a result set output to a Table

(OP)
I have the following SQL*Plus query (executed via a Cobol program) that I'd like to output to a Table:

REPHEADER CENTER 'CUSTOMER NAME AND ADDRESS' SKIP2
COLUMN CLIENT_ID HEADING 'CUSTOMER #'
COLUMN CUST_NAME HEADING 'CUSTOMER NAME'
COLUMN ADD_1 HEADING 'ADDRESS LINE 1'
COLUMN ADD_2 HEADING 'ADDRESS LINE 2'
COLUMN ADD_3 HEADING 'ADDRESS LINE 3'
SELECT A.CLIENT_ID, A.CUST_NAME, A.ADD_1, A.ADD_2, A.ADD_3
FROM CUSTOMER A
WHERE CUST_TYPE LIKE 'A%'
ORDER BY A.CUST_ID;

Any help would be appreciated - Thanks in advance.

Rick

RE: Processing a result set output to a Table

Define "output to a table".

Do you mean you want to create a new database table filled with the values you've selected, or perhaps that you want to insert that data into an existing table?

Or do you mean nothing at all to do with database tables, and that you just want to present the results of your query in some kind of tabular form?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

RE: Processing a result set output to a Table

Sonofosa --- looks like you are still trying to get the formatted output onto your Cobol program as per your thread thread1662-1717209: Oracle dynamic PL-SQL and SQL*Plus - if that is the case there is no point in creating new threads as it won't get you the answer you wish for.

If on the other hand your question is different then could you be more precise on what you are attempting to do

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Processing a result set output to a Table

(OP)
Chris,

Thank you for your response - a table variable is what I was looking for.

Frederico:

You have been following me around every since I started posting, making assumptions and alway sending me links. I was a member previously (under a different handle), circa 2002 (for almost 8 years) and I developed a web based Direct Marketing tool that generated SQL from user enter free form Marketing data. Most of the SQL help came from very smart people at Tek-Tips. A youtube demo of the tool: http://www.youtube.com/watch?v=d9bKlQIVLoA

Rick

RE: Processing a result set output to a Table

then lets not make any assumptions.

What operating system(s) you using
What COBOL vendor and version
What Oracle client version
What SQL*Plus version

Will you be doing this across multi platforms or are you developing for a single OS

What is the expected output of your script should it be executed from a command line SQL*Plus, and what is the expected output should it be executed from a COBOL program (on which case only the Select bit of the SQL*Plus code you gave us can be executed as the remaining parts are SQL*Plus specific and can not be done directly from COBOL)

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Processing a result set output to a Table

(OP)
Frederico,

I don't have all the facts - it's a IBM Z/OS operating system, using Oracle's PRO Cobol (version 10.1). As I've stated previously, I can produce a report with all SQL*Plus features, control breaks and headings, column name changes, report titles, computes etc. I can do this running a Cobol program in batch mode (the online submission of my tool submits JCL to the internal reader that triggers the Job - output must be retrieved via TSO). This satisfies one mode of submission, the other mode (online) is what I'm looking to accomplish. The approach I'm currently exploring is to save the generated Batch output to a Database table, then retrieve and display (with vertical and horizontal scrolling) via the tool. Another point, the SQL statements are all dynamically generated. Lastly, I'm new to Oracle.

Thanks,
Rick

RE: Processing a result set output to a Table

both online (CICS) and batch (TSO) can behave the same for this particular purpose.

Define the JCL you submit from either CICS or TSO so that it outputs the result of your sql*plus script to a temp dataset, and then a very last step on the JCL reads this dataset as is and loads onto a database table.

Such table would normally require a structure similar to the following

request_id - used so the submitter knows the request to read back to display
line_id - used to order the lines
report_line - to avoid having to define a clob, and depending on your own requirements I would advise that this is made up of several varchar(2000) which you populate as needed

An additional table is normally used to contain the request_id, description and other data that is useful to allow the user to identify the report they submitted.

The above is a approach that is used by many applications regardless of what tool produces the report - it works.



Quote:


As I've stated previously, I can produce a report with all SQL*Plus features, control breaks and headings, column name changes, report titles, computes etc. I can do this running a Cobol program in batch mode

Curious about this - could you please post here a small working JCL, corresponding COBOL program and sqlplus script

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Processing a result set output to a Table

(OP)
Frederico,

The customer is quite happy with the batch option - so I've moved on to other tasks.

Rick

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close