Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!uflorida!novavax!hrshcx!hechcx!bobb
From: bobb@hechcx.HEC.HARRIS.COM (Bob Birch)
Newsgroups: comp.unix.questions
Subject: Re: Interfacing SQL and RPT
Summary: SQL*RPT pipe line
Message-ID: <793@hechcx.HEC.HARRIS.COM>
Date: 20 Mar 89 18:36:40 GMT
References: <353@dpscg1.UUCP>
Organization: Harris Computer Systems Fort Lauderdale, FL
Lines: 67



To: zcf1025@dpscg1.UUCP
Subject: Re: Interfacing SQL and RPT
Newsgroups: dpsc.general,dpsc.dmins,dla.database,dla.general,comp.unix.questions
In-Reply-To: <353@dpscg1.UUCP>
Organization: Nova University, Fort Lauderdale, Florida
Cc: 
Bcc: 

In article <353@dpscg1.UUCP> you write:
>I'am trying to pipe an SQL script into an RPT script without any success.
>The SQL script works if i'am executing it at the sql prompt.
>If I execute both in the C shell I receive the message an error in the
>the data dictionary has occured.   
>			              Please help!!!!!!!
>				      Regina Dawley


	The  SQL*rpt uses the ASK command to extract data from stdout. I hope
the following example will be helpful. 

______________________

SYSTEM CONFIGURAION :
______________________	
Harris HCX-9 
UNIX (tahoe) 3.0
ORACLE 5.1.17
SQLPLUS 2.0.14
RPT 1.0.10
________________________

SHELL SCRIPT
________________________
#! /bin/csh

# @(#) SQL script

set input_key = 99999

sqlplus -s scott/tiger<<!
SET heading off
SELECT number_prefix, number_suffix
FROM   data.number_table
WHERE  key = $input_key;
!

____________________
SHELL COMMAND LINE
_____________________
% sql.script | sed -e '/\	 /s///' -e '/\ */s///' -n -e '/[^\\n]/p' | awk '{printf "%s\n%s\n",$1, $2}' | rpt report.rpt report.rpf scott/tiger

The SED and AWK commands are used to format the data from the SQL*PLUS output.

__________________
SQL*PLUS output
___________________
\n
prefix\t suffix\n
\n

___________________
SED and AWK output
__________________
prefix\n
suffix\n