Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to use special character in procedure call?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to use special character in procedure call?
# 1  
Old 04-15-2013
How to use special character in procedure call?
Hi,
I have following script which calls sql to create staging table like t1_rpt_1day_stg,t1_rpt_7day_stg

The following line has '*' and causing all data in directory to show up in email notification but if I give all column names then it works,
Please let me know how to use '*' instead of giving all column names.

Code:
'create table t1_rpt_<parameterized>_stg as select * from t1_rpt_tmt'

--shell script
Code:
load_data_to_oracle()
{
# truncate or create stage table first
${ORACLE_HOME}/bin/sqlplus ${ORACLE_USER}/${ORACLE_PASSWD}@${ORACLE_SID} << EOF > ${TMP_LOG_FILE} 2>&1
set serveroutput on
@${CREATE_STAGE_SQL}
COMMIT;
QUIT;
EOF

## Function Main

# variables
CREATE_STAGE_SQL=${SQL_DIR}/stage_create.sql

load_data_to_oracle
    exit 0

---sql to create staging table

Code:
stage_create.sql
declare
v_sql_string varchar2(4000);
begin
# some code
     execute immediate 'create table t1_rpt_<parameterized>_stg as select * from t1_rpt_tmt';
#some error checking     
end;

Thanks
Sandy
# 2  
Old 04-15-2013
First of all it seems really strange to me that you noticed path-name expansion (globbing) inside an SQL block. Maybe I'm missing something.

But you can disable path-name expansion (globbing) by simply calling:
Code:
set -f

This User Gave Thanks to Yoda For This Post:
sandy162
# 3  
Old 04-15-2013
Quote:
Originally Posted by sandy162
Hi,
I have following script which calls sql to create staging table like t1_rpt_1day_stg,t1_rpt_7day_stgThanks
Sandy
Not sure what you mean without seeing actual example because the '*' just tells oracle to return all columns, but try it like this:
Code:
param=

load_data_to_oracle ()
{
crt_stage_tbl_results=`${ORACLE_HOME}/bin/sqlplus -s ${ORACLE_USER}/${ORACLE_PASSWD}@${ORACLE_SID} <<EOF
set linesize 500 heading off feedback off
whenever sqlerror exit 1
create table t1_rpt_$param_stg as select * from t1_rpt_tmt;
exit 0
EOF`
if [[ $? = 1 ]] then
  ts=`date "+%Y-%m-%d-%H.%M.%S"`
  echo "`echo "$crt_stage_tbl_results" | sed 's/^[ \t\n]*/'$ts' /'`" >> ${TMP_LOG_FILE}
  echo `date "+%Y-%m-%d-%H.%M.%S"` "**The above error occurred creating staging table(t1_rpt_$param_stg)." >> ${TMP_LOG_FILE}
  return 1
fi
return 0
}

This User Gave Thanks to spacebar For This Post:
sandy162
# 4  
Old 04-16-2013
thanks, actually requirement is to call different environment in same
shell script that is why I am giving path names here and using config file to keep all these parameter values.

---------- Post updated at 06:32 AM ---------- Previous update was at 06:28 AM ----------

thanks much, problem is after creation of stage table is successfully completed; a email generates (mail -s) and contents of that email has all the sqls from above query (which is fine) but the '*' causing list of directorties/files in folder to show up in the email content too.
# 5  
Old 04-17-2013
Bumping up posts or double posting is not permitted in these forums.

Please read the rules, which you agreed to when you registered, if you have not already done so.

You may receive an infraction for this. If so, don't worry, just try to follow the rules more carefully. The infraction will expire in the near future

Thank You.

The UNIX and Linux Forums.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to call a stored procedure inside a loop?

How to call a stored procedure inside a loop export PATH=$ORACLE_HOME/bin:$PATH ORACLE_SID=xxxx; export ORACLE_SID ORAENV_ASK=NO TODAY_DATE=$(date +"%Y%m%d") LOGFILE=/var/opt/gogd/cust/scripts/${TODAY_DATE}_sap_cust_rel.log echo 'Connected' PARCUST=sap_cust_rel.par sqlldr... (1 Reply)
Discussion started by: ashwanth
1 Replies

2. Shell Programming and Scripting

Call procedure multiple time

Hi, I have following script which calls sql to create staging table.How do I call load_data_to_oracle() multiple times so that it creates 4 staging as follows. 1.t1_rpt_1day_stg 2.t1_rpt_7day_stg 3.t1_rpt_30day_stg 4.t1_rpt_CTD_stg --shell script load_data_to_oracle() { #... (9 Replies)
Discussion started by: sandy162
9 Replies

3. Shell Programming and Scripting

Shell Script for call a procedure in Oracle DB

Hi everyone! I'm new with Shell Scripting, and I have to do a shell script to call a procedure, which have 2 input parameters, the directory(from server) and the txt file (which have informations to update/insert in DB). I have to create a shell script to execute that procedure for each txt... (5 Replies)
Discussion started by: renatoal
5 Replies

4. Programming

How to call sqlloader from stored procedure!!! Advise

Hi , I have a dellimited .dat file and a sqlloader. I want to call the sqlloader from a oracle stored procedure. The procedure should return the result of sqlloader's execution. (3 Replies)
Discussion started by: Haque123
3 Replies

5. Programming

help remote procedure call

am beginig with unix c code for sun remote procedure call You are to write a program using RPC facility to do the following: 1- The clients reads in a variable length array from the command line 2- The client offers a menu for the user to choose what to do with the array: a. Sort the array... (1 Reply)
Discussion started by: auto2000
1 Replies

6. UNIX for Dummies Questions & Answers

help remote procedure call

i am beginig with unix c code for sun remote procedure call to do the following: 1- The clients reads in a variable length array from the command line 2- The client offers a menu for the user to choose what to do with the array: a. Sort the array b. Add the array c. Find max d. Find... (1 Reply)
Discussion started by: auto2000
1 Replies

7. Programming

help remote procedure call

i am beginig with unix c code for sun remote procedure call to do the following: 1- The clients reads in a variable length array from the command line 2- The client offers a menu for the user to choose what to do with the array: a. Sort the array b. Add the array c. Find max d. Find... (1 Reply)
Discussion started by: auto2000
1 Replies

8. UNIX for Advanced & Expert Users

help remote procedure call

i am beginig with unix c code for sun remote procedure call to do the following: 1- The clients reads in a variable length array from the command line 2- The client offers a menu for the user to choose what to do with the array: a. Sort the array b. Add the array c. Find max d. Find... (1 Reply)
Discussion started by: auto2000
1 Replies

9. Shell Programming and Scripting

need to call 3 stored procedure at the same time

Hi GUYS, I need to trigger 3 stored procedure at the same time.. I know how to trigger the stored procedure. If anybody can tell me how to trigger 3 different process at the same time parallelly.. that would be helpful.. Thanks for your help in advance, Magesh (1 Reply)
Discussion started by: mac4rfree
1 Replies

10. UNIX for Advanced & Expert Users

How to call SQL procedure from UNIX Shellscript ?

Hi All I would be thankful to you all if you will guide me the steps to call a stored proc. from unix shell script. that stored proc. could be parameterised or parameterless developed in SQL. Any info. in this topic would help me..... Thanks in advance.... (1 Reply)
Discussion started by: varungupta
1 Replies
Login or Register to Ask a Question