• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 951
  • Last Modified:

Shell Scripting - passing variables

I have the below scripts, that I'm trying to get working, but I'm really struggling.

What I'm trying to do is run the dim_query.sql file with 2 dates passed to it, and output the result to a .csv file. These are the following steps:

1) User inputs two dates on an html page and clicks submit
2) These two dates are passed to file1.sh, which in turn are passed to dim_query.sql which then runs the query and saves a .csv file with the query result.

Can someone shed some light on this for me please?


_________________________________________________________________________________

<?php
$output = shell_exec('file1.sh');
?>

_________________________________________________________________________________

**file1.sh**

#!/bin/ksh

export ORAENV_ASK=NO
export ORACLE_SID=client

. /usr/local/bin/oraenv

sqlplus -s pcms/pcms@pcms @dim_query.sql $1 $2

grep \| temp_test.csv > test.csv

_________________________________________________________________________________

**dim_query.sql**

WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
SET PAGESIZE 0
SET LINESIZE 500
SET FEEDBACK OFF
SET ECHO OFF

SPOOL temp_test.csv

SELECT DISTINCT rtrim(cd.cH_doc_id) || '|' ||
    rtrim(cm.attr_178) || '|' ||
    rtrim(cm.attr_97) || '|' ||
    rtrim(cd.title)  || '|' ||
    rtrim(cd.action_date) || '|' ||
    rtrim(cm.attr_179)
    FROM pcms_cm_catalogue cv,
               pcms_cm_phases cp,
               cm_attributes cm,
               pcms_chdoc_data cd
          WHERE cd.product_id in(Select distinct(product_id) from pcms_chdoc_data)
            AND cd.create_date BETWEEN '&1'
                                   AND '&2'
            AND cd.super_type LIKE '%'
            AND cd.ch_doc_type = 'WO'
            AND cd.status LIKE '%'
            AND cd.status != '$TO_BE_DEFINED'
            AND cd.ch_doc_id LIKE '%'
            AND cd.ch_uid = cm.ch_uid
            AND cd.cm_phase = cp.phase_id
            AND NVL (cd.seq, 1) = 1
            AND cp.phase_name LIKE '%'
            AND cd.ch_uid = cv.ch_uid
            AND cv.user_name LIKE '%'
  ORDER BY cd.cH_doc_id ASC,
                cm.attr_178 ASC;

SPOOL OFF

EXIT;

_________________________________________________________________________________
0
PeterErhard
Asked:
PeterErhard
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Kim RyanIT ConsultantCommented:
The logic looks OK. The sql script is set up to save the result in csv format.

One thing that is missing is the two date arguments needed to be passed to the shell script. So you need something like
<?php output = shell_exec('file1.sh $date1 $date2');  ?>

But am not sure how you refer to these dates in the context of your form. There should be some way of referencing form variables.
0
 
PeterErhardAuthor Commented:
Thanks, hopefully someone can come along with more information.
0
 
rockiroadsCommented:
Is the problem in running the sql with the parameters?
Ok, here is one solution

treat your dim_query.sql as a template, change &1 to PARAM1 and &2 to PARAM2

then add this into your shellscript

tmpFile="/tmp/_ee$$.sql"                            
cat dim_query.sql | sed s/PARAM1/"$1"/ > $tmpFile  
cat $tmpFile | sed s/PARAM2/"$2"/ > $tmpFile

# Now tmpFile points to your sql you want to run                                                      
cat $tmpFile                                          

# Remember to delete tmpFile after processing
rm -f $tmpFile                                        
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rockiroadsCommented:
regarding your sqlplus command

sqlplus -s pcms/pcms@pcms @dim_query.sql $1 $2


what is wrong with this is someone can check the process table and get the password

a better way is to run like this

echo $OraPassword | sqlplus -s $OraUserID @$tmpFile >> $results_file 2>&1

OR

do this, create a temporary shellscript which calls sqlplus passing in your tmpFile

        ora_tmp_sql="/tmp/ots_$$.sh"                    
        echo "sqlplus -s /nolog <<EOF" > $ora_tmp_sql        
        echo "connect $OraUserID/$OraPassword" >> $ora_tmp_sql
        echo "set linesize 2000" >> $ora_tmp_sql              
        echo "set wrap off" >> $ora_tmp_sql                  
        echo "@$tmpFile;" >> $ora_tmp_sql                
        echo "quit;" >> $ora_tmp_sql                          
        echo "EOF" >> $ora_tmp_sql                            
        sh $ora_tmp_sql >> $results 2>&1            


Now back to your PHP script, assuming $results was set to some file /work/out.csv

You can then set the content type like this

print "content-type: application/octet-stream\n",    
                'content-disposition: attachment;'.  
                'filename="'. $filename .'"',"\n\n";
exec_shell($filename);

setting content-type like this should allow for a download of a file
0
 
MikeOM_DBACommented:

Except for teraplane's suggested fix, your scripts look ok.

Just be aware that you must supply the dates as per NLS_DATE FORMAT parameter format:
...here................................................v............v.
           AND cd.create_date BETWEEN '&1'  AND '&2'

The better way is that user enters date in some standard format you decide and then use the TO_DATE() function to avoid discrepancies with the NLS_DATE_FORMAT parameter:
...etc...
           AND cd.create_date BETWEEN TO_DATE('&1','MM/DD/YYYY')  
                                                AND TO_DATE('&2','MM/DD/YYYY')

HTH
0
 
MikeOM_DBACommented:

PS: No need to complicate things like rokiroads suggests.

To hide password, create an "identified external" account, grant all necessary permissions and execute sqlplus like this:

sqlplus -s /@pcms @dim_query.sql $1 $2

Also if the pcms database is on same server from which you are executing sqlplus, just set the ORACLE_HOME and ORACLE_SID environment variables and avoid the Net alias link:

sqlplus -s / @dim_query.sql $1 $2


0
 
rockiroadsCommented:
what can I say, Im a complicated individual :)
0
 
PeterErhardAuthor Commented:
Thanks for your responses guys. I've got it passing through variables just fine now, but I have another problem.

My query isn't writing to the .csv file correctly and I'm wondering why.

If I run the below it doesn't work, but if I run the query directly against the database, it returns the result just fine. Is there something I'm missing
out or....?


WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
SET PAGESIZE 0
SET LINESIZE 500
SET FEEDBACK OFF
SET ECHO OFF

SPOOL temp_test.csv

SELECT DISTINCT cd.cH_doc_id,cm.attr_178
    FROM pcms_chdoc_data cd,
    cm_attributes cm
    --where cd.create_date BETWEEN TO_DATE('&1','MM/DD/YYYY') AND TO_DATE('&2','MM/DD/YYYY')

SPOOL OFF

EXIT;
0
 
PeterErhardAuthor Commented:
Ignore me! Annoying simple mistake!
0
 
PeterErhardAuthor Commented:
I'm having further problems, details below, any advice would be greatly appreciated.

******************************************

This works and successfully outputs to the csv file:

./run_dim_query.sh "01/01/2006" "01/07/2006"

However when using the PHP script it doesn't:

******************************************

<?php

$Date1Day = $_POST["Date1Day"];
$Date1Month = $_POST["Date1Month"];
$Date1Year = $_POST["Date1Year"];
$Date2Day = $_POST["Date2Day"];
$Date2Month = $_POST["Date2Month"];
$Date2Year = $_POST["Date2Year"];

$date1 = $Date1Day . "/" . $Date1Month . "/" . $Date1Year;
$date2 = $Date2Day . "/" . $Date2Month . "/" . $Date2Year;

echo $date1;
echo "<br>";
echo $date2;

$output = shell_exec('./run_dim_query.sh $date1 $date2');

echo $output;

?>

**********************************************

The $output variable returns "Enter value for 1:", and doesn't carry on. Why is this?

**********************************************

#!/bin/ksh

export ORAENV_ASK=NO
export ORACLE_SID=client

. /usr/local/bin/oraenv

sqlplus -s pcms/pcms@pcms @dim_query.sql $1 $2

grep \| temp_test.csv > test.csv

*******************************************

WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
SET PAGESIZE 0
SET LINESIZE 500
SET FEEDBACK OFF
SET ECHO OFF

SPOOL temp_test.csv

SELECT DISTINCT rtrim(cd.cH_doc_id) || '|' ||
    rtrim(cm.attr_178) || '|' ||
    rtrim(cm.attr_97) || '|' ||
    rtrim(cd.title)  || '|' ||
    rtrim(cd.action_date) || '|' ||
    rtrim(cm.attr_179)
    FROM pcms_cm_catalogue cv,
               pcms_cm_phases cp,
               cm_attributes cm,
               pcms_chdoc_data cd
          WHERE cd.product_id in(Select distinct(product_id) from pcms_chdoc_data)
            AND cd.create_date BETWEEN TO_DATE('&1','MM/DD/YYYY') AND TO_DATE('&2','MM/DD/YYYY')
            AND cd.super_type LIKE '%'
            AND cd.ch_doc_type = 'WO'
            AND cd.status LIKE '%'
            AND cd.status != '$TO_BE_DEFINED'
            AND cd.ch_doc_id LIKE '%'
            AND cd.ch_uid = cm.ch_uid
            AND cd.cm_phase = cp.phase_id
            AND NVL (cd.seq, 1) = 1
            AND cp.phase_name LIKE '%'
            AND cd.ch_uid = cv.ch_uid
            AND cv.user_name LIKE '%';

SPOOL OFF

EXIT;

*******************************************

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now