| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: call sql oracle 8.i with unix script fails
"Ana C. Dent" <anacedent_at_hotmail.com> schreef in bericht news:Xns98B7979DFCADanacedenthotmailcom_at_69.28.173.184...
> "Ed Yin" <vraagme_at_hotmail.nl> wrote in > news:55d2c$45a931d4$50398baa$10404_at_news.chello.nl: >
>>> "Ed Yin" <vraagme_at_hotmail.nl> wrote in >>> news:497fd$45a8d9e9$50398baa$23489_at_news.chello.nl: >>> >>>> >>>> "Ana C. Dent" <anacedent_at_hotmail.com> schreef in bericht >>>> news:Xns98B6C3B00D8B6anacedenthotmailcom_at_69.28.173.184... >>>>> "Ed Yin" <vraagme_at_hotmail.nl> wrote in >>>>> news:4abdb$45a7d753$50398baa$18065_at_news.chello.nl: >>>>> >>>>>> hello, >>>>>> >>>>>> maybe someone can help me out on this script it will only execute >>>>>> 1 sql statement depending on what file_name what is wrong with >>>>>> what i am trying to do? >>>>>> >>>>>> >>>>>> i try to call a sql like this depending on interface type >>>>>> >>>>>> this shell is called rim started like this ./rim POR AL >>>>>> where POR >>>>>> is type en AL = $2 file name in this test >>>>>> >>>>>> >>>>>> $1=INTTYPE >>>>>> file_name='test.por' >>>>>> typeO=ORD >>>>>> typeP=POR >>>>>> >>>>>> if $1=$typeO >>>>>> then >>>>>> sqlplus -s / @ord_controle.sql $2 $file_name <<F / >>>>>> echo processing $typeO $1 $sql1 $file_name >>>>>> else $1=$typeP >>>>>> sqlplus -s / @por_controle.sql $2 $file_name <<F / >>>>>> echo processing $typeP $1 $sql2 $file_name >>>>>> show errors; >>>>>> F >>>>>> fi >>>>>> exit 0 >>>>>> >>>>>> thanks Ed >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> First make ity work, then make it fancy. >>>>> I've done *nix for 20+ years & Oracle for 10+ >>>>> & for the life of me I have no idea what you are doing. >>>>> It kind of appears you are trying some form of here script, >>>>> which make absolutely no sense inside a shell's "if" statement. >>>> >>>> what I am trying to do is this: >>>> >>>> inside a unix shell there is 1 sql command and it uses processtype >>>> parameter $1 , company $2 and parameter $file_name as collected >>>> filename wich conatins the data witch we need to process and what i >>>> want to try is this >>>> >>>> if parameter $1 is equal to process type ORD >>>> then do >>>> >>>> "sqlplus -s / @ord_controle.sql $2 $file_name <<F >>>> / >>>> show errors; >>>> F " >>>> this sql statement is checking the transmission data form the file >>>> in a transmission table and what i want to do is adding next >>>> statement or sql inside this shell script. >>>> >>>> >>>> if paramater $1 is equal to POR >>>> then next sql is needed to perform checks in database transmission >>>> table >>>> >>>> "sqlplus -s / @por_controle.sql $2 $file_name <<E >>>> / >>>> show errors; >>>> E" >>>> >>>> extra note is besides the procestype also the extension of the file >>>> is differend and i parsed it into parameter $ext but i did not used >>>> it. >>>> >>>> hope even when it is not clear to you it gives an idea what i try to >>>> do. >>>> >>>> thanks Ed >>>> >>>> >>>> >>>> >>> >>> A little knowledge is a dangerous commodity. >>> IMO, I would not use in combination @myname.sql AND <<E (here script) >>> I use one or the other but NEVER tried using in combination. >>> Try "encapsilation" create small working & bulletproof scripts which >>> do 1 thing well & one thing only. Call/invoke it when appropriate. >>> >>> Unless I am still sleep walking, you have NEVER actually stated what >>> is wrong. You imply a problem, but I have no idea what you are seeing >>> & what you are posting about. >>> >>> Typically many scripts run in background. I see NO benefit of >>> including SHOW ERROR; >>> 'cuz your code can't take action based upon its output.
> > I am about ready to abandon this thread. > You insist upon telling what you think is happening & what you are doing > rather than simply doing cut & paste to show EXACTLY what is or is not > happening. > You seem to be on some sort of *nix system of unknown name & version. > You seem to be using some sort of shell; (csh, sh, bash, which???) > Visibility into what is or is not happening can be obtained by: > $ sh -x ./rim POR AL > > Rhetorical question - Do you actually understand what $1 or $2 are > & how they should be used in a script? > > HAND!
All these replies thanks, i solved my problem in pl/sql itself and the unix shell goes like this:
# **************************************************************************** # # # **************************************************************************** # # Prologue - rim.sh # **************************************************************************** # Run Inbound Interface Manager # # DESCRIPTION # UNIX script for executing the inbound interface manager # # $Revision: 3.33 $ # # $Author: cnunley $ # PARAMETERS # p1 - interface type, e.g. ORD, POR, SKU # INITIATED BY # Usually initiated within ... as scheduled process or requested process. # NOTES # (1) If changes are made to this script, check the CLIENT directory for # client-specific versions which may also need to be changed. # ****************************************************************************#
#
echo $1 $2 $3 $4
if [ "$1" = "ASN" ]
then
asnc_to_asn
fi
if [ $1 = "POR" -o $1 = "por" -o $1 = "ORD" -o $1 = "ord" -o $1 = "VEN" -o $1 = "ven" -o $1 = "CUS" -o $1 = "cus" -o $1 = "LOT" -o $1 = "lot" -o $1 = "ANR" -o $1 = "anr" -o $1 = "SKU" -o $1 = "sku" -o $1 = "CYC" -o $1 = "cyc" -o $1 = "RTE" -o $1 = "rte" -o $1 = "DMD" -o $1 = "dmd" -o $1 = "PRD" -o $1 = "prd" -o $1 = "BOL" -o $1 = "bol" -o $1 = "BOM" -o $1 = "bom" -o $1 = "ASN" -o $1 = "asn" -o $1 = "SLT" -o $1 = "slt" -o $1 = "ser" -o $1 = "SER" -o $1 = "lps" -o $1 = "LPS" ] then
comp_code=\'$2\'
if [ $1 = "POR" -o $1 = "por" ]
then
ext="por"
sql=sql2
ext="ord"
sql=sql1
ext="asn"
elif [ $1 = "VEN" -o $1 = "ven" ]
then
ext="ven"
elif [ $1 = "CUS" -o $1 = "cus" ]
then
ext="cus"
elif [ $1 = "LOT" -o $1 = "lot" ]
then
ext="lot"
elif [ $1 = "ANR" -o $1 = "anr" ]
then
ext="anr"
elif [ $1 = "SKU" -o $1 = "sku" ]
then
ext="sku"
elif [ $1 = "CYC" -o $1 = "cyc" ]
then
ext="cyc"
elif [ $1 = "RTE" -o $1 = "rte" ]
then
ext="rte"
elif [ $1 = "DMD" -o $1 = "dmd" ]
then
ext="dmd"
elif [ $1 = "PRD" -o $1 = "prd" ]
then
ext="prd"
elif [ $1 = "BOL" -o $1 = "bol" ]
then
ext="bol"
elif [ $1 = "BOM" -o $1 = "bom" ]
then
ext="bom"
elif [ $1 = "SLT" -o $1 = "slt" ]
then
ext="slt"
elif [ $1 = "SER" -o $1 = "ser" ]
then
ext="ser"
elif [ $1 = "LPS" -o $1 = "lps" ]
then
ext="lps"
fi
cd $TRANSIN
#remap all the .csv files
echo Searching for files of type: .csv in directory: $TRANSIN
n=`find . -name "*.csv" -print | grep -v '/.*/' | wc -l`
echo Found $n files.
if test $n -ge 1
then
for a in *.csv
do
# Create name for second file to indicate run in progress. Peter Klosky
A="$a"_run
echo Processing file: $a $A
ls -l $a
file_name=\'$a\'
if ln $a $A
then
date
#call inbound.immain
sqlplus -s / <<Z
declare
l_retcode NUMBER;
l_comp_code VARCHAR2(4);
l_file_name VARCHAR2(60);
l_proc_stat NUMBER;
begin
l_comp_code := $comp_code;
l_file_name := $file_name;
l_retcode :=
mt_inbound.immain($comp_code, 'FILE', $file_name,l_proc_stat);
end;
echo Job for $a done, server_host subjob will remove $A
date
# give server_host some time to rename from .tmp to real extension
sleep 4
else
echo $A already exists, no run.
fi
echo Searching for files of type: $ext in directory: $TRANSIN n=`find . -name "*.$ext" -print | grep -v '/.*/' | wc -l` echo Found $n files.
if test $n -ge 1
then
for a in *.$ext
do
# Create name for second file to indicate run in progress. Peter Klosky
A="$a"_run
echo Processing file: $a $A
ls -l $a
file_name=\'$a\'
if ln $a $A
then
date
#call inbound.immain
sqlplus -s / <<Z
declare
l_retcode NUMBER;
l_retcode2 NUMBER;
l_comp_code VARCHAR2(4);
l_file_name VARCHAR2(60);
l_proc_stat NUMBER;
begin
l_comp_code := $comp_code;
l_file_name := $file_name;
l_retcode :=
inbound.immain($comp_code, 'FILE', $file_name,l_proc_stat);
end;
if [ $1 = "ORD" -o $1 = "POR" ]
echo $1 $2 $file_name
then
sqlplus -s / @immain_controle.sql $1 $2 $file_name
fi
exit 0
Received on Wed May 02 2007 - 13:34:19 CDT
 |
 |