Calling a teradata stored procedure by shell script

Database
Enthusiast

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

11 REPLIES
Enthusiast

Re: Calling a teradata stored procedure by shell script

Any comments please?

Enthusiast

Re: Calling a teradata stored procedure by shell script

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''

Enthusiast

Re: Calling a teradata stored procedure by shell script

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

Enthusiast

Re: Calling a teradata stored procedure by shell script

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'


Enthusiast

Re: Calling a teradata stored procedure by shell script

Thank you Vikas. :)

Enthusiast

Re: Calling a teradata stored procedure by shell script

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

Enthusiast

Re: Calling a teradata stored procedure by shell script

Any comment on this?

Enthusiast

Re: Calling a teradata stored procedure by shell script

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

Re: Calling a teradata stored procedure by shell script

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