FW: [sqr-users] How do I use a variable file path for flat files?

["O'Neil, Tara" <tara.oneil@marquette.edu>]

In this code I check which instance I am in and assign the appropriate
file.
Otherwise you could create a run control to insert the file name.
Thanks.
Tara


let $DatabaseName = '*unknown*'
#ifdef ORACLE
begin-SELECT loops=1 
DBNAME    &DBNAME
    let $DatabaseName = &DBNAME
FROM PSDBOWNER
end-SELECT
#else
begin-SELECT loops=1 
DBNAME    &DBNAME
    let $DatabaseName = &DBNAME
FROM PSPRCSRQST
end-SELECT
#end-if
    show        'Data Base Name =  ' $DatabaseName              
    show 'unix or nt?' 
    #ifdef UNIX
     show 'unix -'
      #else
       #ifdef NT
       show 'nt -'
     #end-if
    #end-if
    if $prcs_process_instance = '' 
      let $output-file1     = 'c:\tmp\oir_newst_aid.txt'
      let $output-file2     = 'c:\tmp\oir_newst.txt'
      let $output-file3     = 'c:\tmp\oir_renewst_aid.txt'
      let $output-file4     = 'c:\tmp\oir_renewst.txt'
    else
     if $DatabaseName = 'SATST' or $DatabaseName = 'SADEV' or
$DatabaseName = 'SACONV' 
         or $DatabaseName = 'SAPROD' or $DatabaseName = 'SATRN' or
$DatabaseName = 'SAPRO'
        !show 'unix or nt?' 
        #ifdef UNIX
         !show 'unix -'
         let $output-file1 =
'/psoft/data/finaid/outbound/oir_newst_aid.txt' 
         let $output-file2 = '/psoft/data/finaid/outbound/oir_newst.txt'

         let $output-file3 =
'/psoft/data/finaid/outbound/oir_renewst_aid.txt' 
         let $output-file4 =
'/psoft/data/finaid/outbound/oir_renewst.txt' 
          #else
           #ifdef NT
             if $DatabaseName = 'SATST'
                let $output-file1 =
'P:\psoft\mu\TST\DATA\finaid\outbound\oir_newst_aid.txt' 
                let $output-file2 =
'P:\psoft\mu\TST\DATA\finaid\outbound\oir_newst.txt' 
                let $output-file3 =
'P:\psoft\mu\TST\DATA\finaid\outbound\oir_renewst_aid.txt' 
                let $output-file4 =
'P:\psoft\mu\TST\DATA\finaid\outbound\oir_renewst.txt' 
             end-if
             if $DatabaseName = 'SADEV'
                let $output-file1 =
'P:\psoft\mu\DEV\DATA\finaid\outbound\oir_newst_aid.txt' 
                let $output-file2 =
'P:\psoft\mu\DEV\DATA\finaid\outbound\oir_newst.txt' 
                let $output-file3 =
'P:\psoft\mu\DEV\DATA\finaid\outbound\oir_renewst_aid.txt' 
                let $output-file4 =
'P:\psoft\mu\DEV\DATA\finaid\outbound\oir_renewst.txt' 
             end-if
             if $DatabaseName = 'SAPROD'
                let $output-file1 =
'P:\psoft\mu\PROD\DATA\finaid\outbound\oir_newst_aid.txt' 
                let $output-file2 =
'P:\psoft\mu\PROD\DATA\finaid\outbound\oir_newst.txt' 
                let $output-file3 =
'P:\psoft\mu\PROD\DATA\finaid\outbound\oir_renewst_aid.txt' 
                let $output-file4 =
'P:\psoft\mu\PROD\DATA\finaid\outbound\oir_renewst.txt' 
             end-if
             if $DatabaseName = 'SACONV' or $DatabaseName = 'SATRN' or
$DatabaseName = 'SAPRO'
                !show 'nt -'
                let $output-file1 =
'P:\psoft\mu\TST\DATA\finaid\outbound\oir_newst_aid.txt' 
                let $output-file2 =
'P:\psoft\mu\TST\DATA\finaid\outbound\oir_newst.txt' 
                let $output-file3 =
'P:\psoft\mu\TST\DATA\finaid\outbound\oir_renewst_aid.txt' 
                let $output-file4 =
'P:\psoft\mu\TST\DATA\finaid\outbound\oir_renewst.txt' 
             end-if
         #end-if
       #end-if
     end-if   
    end-if  

-----Original Message-----
From: sqr-users-bounces+tara.oneil=marquette.edu@sqrug.org
[mailto:sqr-users-bounces+tara.oneil=marquette.edu@sqrug.org] On Behalf
Of Jae Lee
Sent: Monday, April 05, 2004 4:05 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] How do I use a variable file path for flat files?

Hi,

In my SQR(v. 6.2) programs I've been hard-coding the directory and file 
names when I read from or write to flat files. (for ex.    let 
$outfilename =
'/u02/sct/banner/banHRPROD/asu/export/sis/UppsDemogra.txt'
open $outfilename as 1 for-writing...)

  We have Oracle 8.x db that runs on a UNIX machine.  This is obviously 
not a desirable solution... When I move my SQR programs from TEST to 
PROD, the DBA has to change the file path in the program... If I can 
define the file path and name for flat files just like the report files 
in the UNIX shell script that runs SQR program, it would be great!  Can 
I do this, and if I can, how?  Below is how I name the report files in a

shell script:

#
# Define files
#
DD_PRNT01=$HOME/pzrlbec_$$.lis ; export DD_PRNT01
DD_PRNT02=$HOME/pzrlbec_$$_01.log ; export DD_PRNT02
DD_PRNT03=$HOME/pzrlbec_$$_02.lis ; export DD_PRNT03
....
#
# Run the program
#
$SQRDIR/sqr $EXEDIR/pzrlbec.sqt [db connection string] -RT -F$DD_PRNT01 
-F$DD_PRNT02 -F$DD_PRNT03 $3 | tee $LOGFILE

but I know this -F argument is for report files only....

As an alternative, If I can somehow pass our ban$ORACLE_SID 
environmental variable to the SQR program, that will work!

I'd appreciate any help I can get on this.... I am new to UNIX, Oracle 
and SQR...  Thank you folks!

-- jae

.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:.

                            +=================================+
   ,-~~-.___.               |           Jae S. Lee            |

   / |  '     \             |      Applications Analyst       |

  (  )        0             | Information Technology Services |

   \_/-, ,----'             |      Room 1109 Raley Hall       |   
      ====           //     |  Appalachian State University   |

     /  \-'~;    /~~~(O)    |         Boone, NC 28608         |
    /  __/~|   /       |    |Ph. 828/262-6283 Fax 828/262-2236|  
  =(  _____| (_________|    +=================================+
                         
.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:.



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users