All Forums Database
kushal.a.gupta 6 posts Joined 04/14
30 Jan 2015
Calling a teradata stored procedure by shell script

Hi,
I have a stored procedure that takes 3 IN  parameters and 1 OUT  parameter.
Ist IN : 1 or 2
2nd IN : database schema
3rd IN : table name
4th OUT : returns message
call sp_test ('1', 'scott', 'emp', msg);
 Working of SP is like if the first IN parameter is '1' it displays 5 top rows of a input  table. ( This I have done using a dynamic sql and cursor with return only). And if the first IN parameter is '2' it deletes some particular rows of the input table.
 
Now, I want to call this procedure in shell script. and my questions are as below :
1) I want to handle the answer set in shell( which will be produced when I will pass 1st parameter as '1').
2) How to pass the IN parameter in shell.
3) How to handle the OUT parameter in shell.
As I dnt have any knowledge of shell any help with some piece of shell script will be appreciated.
 
Thanks in  advance.
-Kushal

Kushal Gupta
kushal.a.gupta 6 posts Joined 04/14
01 Feb 2015

Any comments please?

Kushal Gupta

vikas_yadav 19 posts Joined 09/12
02 Feb 2015

Hi Khushal,
Below code will help you to acheive your requirement.

##################################################################

#!/bin/ksh

 

TDPId=**********

LoginId=******

Paswrd=*******

in_param=$1

 

 

 

bteq<<EOF

.logon ${TDPId}/${LoginId},${Paswrd};

.set format off;

.set TITLEDASHES OFF;

.set foldline off;

.set width 3000;

--Remove below comment if you want to export result to a file

--.EXPORT REPORT FILE=/home/path/bteq/sp_output,CLOSE;

CALL  dbms_output  ('$in_param');

 

--.export reset;

 

.logoff;

.quit;

EOF

 

################################################################

##Save below file as run_sp.sh
Run Commnad:

run_sp.sh ''vikas''

 

kushal.a.gupta 6 posts Joined 04/14
03 Feb 2015

Hi Vikas,
Thanks for the response that worked. Thanks a lot.
Need one more small help. I want to make the shell as menu based. I have written the below code but it is throwing me the error " run_test1.sh[21]: syntax error at line 36 : `<' unmatched".
 
#!/bin/ksh
tdpid=******
loginidd=******
paswrd=*******

n_parm1=$1
n_parm2=$2
n_parm3=$3
n_parm4=$4

while :
do
 clear
 echo " ******** Task performing script ******** "
 echo "1. task1 "
 echo
 echo "2. task2 "
 echo
 echo "3. EXIT"
 echo
 echo -n "Please enter any option or options between [1 - 4]"
     read opt;
 case $opt in
1)
 echo "Performing task1 . . . .";
bteq<<EOF
.logon ${tdpid}/${loginidd},${paswrd};
call free_space('$n_parm1','$n_parm2','$n_parm3',$n_parm4);
.logoff;
.quit;
EOF;;
2)
 echo "Performing task2 . . . .";
who | more;;
3)
 echo "Bye $USER";
 break 1;;
*)
 echo "$opt is an invaild option";
 echo "Press [enter] key to continue. . .";
 read enterKey;;
  esac
done
 
Your help on this will be appreciated. :)
-Kushal

Kushal Gupta

vikas_yadav 19 posts Joined 09/12
03 Feb 2015

I don't think case stmnt supports here document in unix.
you can try below solution :
###file run_sp1.sh#####

#!/bin/ksh

TDPId=*******

LoginId=*******

Paswrd=*********

in_param=$1

 

 

while :

 do

  clear

  echo " ******** Task performing script ******** "

  echo "1. task1 "

  echo

  echo "2. task2 "

  echo

  echo "3. EXIT"

  echo

  echo -n "Please enter any option or options between [1 - 4]"

      read opt;

  case $opt in

 1)

  echo "Performing task1 . . . .";

  /home/data/bteq/run_sp.sh $TDPId $LoginId $Paswrd $in_param >>log_file

   echo "Performing task1 . . . .";;

 2)

  echo "Performing task2 . . . .";

 who | more;;

 3)

  echo "Bye $USER";

  break 1;;

 *)

  echo "$opt is an invaild option";

  echo "Press [enter] key to continue. . .";

  read enterKey;;

   esac

 done

#################################################

 

###file run_sp.sh####################

 

#!/bin/ksh

 

TDPId=$1

LoginId=$2

Paswrd=$3

in_param=$4

 

 

 

bteq<<EOF

.logon ${TDPId}/${LoginId},${Paswrd};

.set format off;

.set TITLEDASHES OFF;

.set foldline off;

.set width 3000;

--Remove below comment if you want to export result to a file

.EXPORT REPORT FILE=/home/data01/bteq/sp_output,CLOSE;

CALL  vy255003.dbms_output_put_line  ('$in_param');

.export reset;

 

.logoff;

.quit;

EOF

######################################################

 

run command:

run_sp1.sh ''test'

 

 

 

kushal.a.gupta 6 posts Joined 04/14
03 Feb 2015

Thank you Vikas. :)

Kushal Gupta

kushal.a.gupta 6 posts Joined 04/14
04 Feb 2015

Vikas need your help once again.
I am exporting the of BTEQ into a file say file1.txt. Export is working fine.
Now, I want that the file.txt data get vanished every time when I run the bteq and it should have only the new data of export on every run.
Is there any such option for that?
Thanks in advance!
-Kushal

Kushal Gupta

kushal.a.gupta 6 posts Joined 04/14
08 Feb 2015

Any comment on this?

Kushal Gupta

Priya01 10 posts Joined 08/14
09 Feb 2015

Hi Kushal,
Put this line of code just prior to your bteq statement.
for example, if "/abc/efg/file.txt " is the file name with path.
then put this
>/abc/efg/file.txt
 
Hope this helps.
Thanks,
Priya

ramesh.bheri 2 posts Joined 05/11
11 Jun 2016

Hi All,
I'm new to stored procedure in Teradata. Im trying to create a simple store procedure which would insert one record into table using unix script.
I'm unable to create as it is throwing error invalid sql statement.
Can you please tell me what mistake im doing.
#!/bin/ksh
bteq<<EOF
.logon ***/***,**;
create procedure p1()
begin
insert into x values('abc');
.logoff;
.quit;
EOF
 

dnoeth 4628 posts Joined 11/04
11 Jun 2016

You can't create SPs in BTEQ like this (but in SQL Assistant).
The SP source code must be stored in a file and then it's:

.COMPILE FILE = my_sp_source.txt;

 

Dieter

ramesh.bheri 2 posts Joined 05/11
11 Jun 2016

Hi Dieter ,
thanks for the clarification .
My actual requirement is like i wil have a table which contains set of insert statement . I need to run all those insert statements picking one by one from the table using stored procedure and dynamic sql . 
Can you please guide me how to start with.
And if the same procedure needs to be implemeneted through unix , Do i need to stored that procedure in a file ( my_sp_source.txt);
#!/bin/ksh
bteq<<EOF
.logon ***/***,**;
.COMPILE FILE = my_sp_source.txt;
CALL PROCEDURE PROC_NAME ;
.logoff;
.quit;
EOF
Please let me know , If this is the rite approach for this in unix.
 
Thanks

You must sign in to leave a comment.