Receiving Value from plsql package.


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Receiving Value from plsql package.
# 1  
Old 11-09-2001
Receiving Value from plsql package.

Hi,

I am working on a shell script that fires a plsql package. I would like the package to return a value to the shell script and then the shell script to return it to the javascript. Is it possible for the shell script to accept the return status from the plsql package and then pass it back to the calling java script? The call to the package from the shell script looks like:

echo "=== Calling the processing procedure..."
sqlplus hotman/hotman > /dev/null << EOF
begin
pho_glovia_pkg.move_data_prc('$r_status');
end;
/
EOF

When I run it like this it won't call the package, but if I go to the package and remove the return parameter and remove the "('$r_status')" from the shell script it runs just fine.

Thanks,

Amber Taylor
# 2  
Old 11-12-2001
if you are just trying to grab the exit code is is returned to the shell script and can be accessed in the $? variable. If the value is something else but still an integer, have your package exit with the value you want to grab. exit myvalue this will cause the shell to think that myvalue is the return code and it can be accessed in $? not sure if this is what you wanted but hope it helps. I have found that working with a function is easiest that way all you need to do is

value = `sqlplus hotman/hotman << EOF
begin
pho_glovia_pkg.move_data_prc();
end;
/
EOF`

the value returned by the function will be in $value
# 3  
Old 11-14-2001
Thanks for your advice. I was attempting to reuse code :-) that existed in a package. This package normally returns a status and I was trying to pick up that status from the package using a shell script and return it to the java. As a workaround I created a transaction/error table that the java can query after execution of the shell script to find the status of a run. The shell script inserts any errors during the file loads and the package will make the insert on exit for any errors found during the run. This way I can return multiple errors when needed.

Thanks,

Amber Taylor
# 4  
Old 11-14-2001
Hi Amber,

You should be able to do somthing like...

#!/usr/bin/ksh

sqlplus -s scott/tiger@db << EOF > /tmp/result.$$
declare invar number;
declare outvar char(30);
exec myschema.mypkg.myproc(invar, outvar);
dbms_output.putline(outvar);
/
EOF

read result /tmp/result.$$
echo $result
rm -f /tmp/result.$$
exit 0

...if you do a search on the forum from "sqlplus" you should
see lots of good examples. The one above is just a quick and dirty
one.
# 5  
Old 11-14-2001
Thanks!

Amber Taylor
 
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Emergency UNIX and Linux Support

Problem when trying to remove a package using rpm command - error: package is not installed

Hello, i have installed a package by using the command sudo rpm -i filepackage.rpm package filepackage is already installed when i try to remove it, i get an error saying "is not installed": sudo rpm -e filepackage.rpm error: package filepackage is not installed How can... (4 Replies)
Discussion started by: g_p
4 Replies

2. UNIX for Dummies Questions & Answers

Can't install rpm package with --prefix in new path.Error: package is not relocatable

Hello, i have downloaded an rpm package "hadoop-0.20.205.0-1.amd64.rpm" in /usr/local/ directory. I'm trying to install the rpm package in a new path/location (/usr/local/hadoop-0.20.205), but i can't. I did: 1st try: Didn't work sudo rpm -i --prefix=/usr/local/hadoop-0.20.205... (1 Reply)
Discussion started by: g_p
1 Replies

3. Linux

How install a new package without remove old package?

Dear all, I would like to install a new version of package without remove old version on Centos and vice versa. Please give me advice! thanks much, (2 Replies)
Discussion started by: all4cfa
2 Replies

4. Programming

PLSQL

HI, When am creating one package body,its throwing error, Code which am using to create is: CREATE OR REPLACE PACKAGE BODY "MAMPROD"."PKG_QS_ADMIN" AS -- ============================================================================ PROCEDURE CREATE_PARAMETERS IS BEGIN ... (2 Replies)
Discussion started by: Sanal
2 Replies

5. UNIX for Advanced & Expert Users

How to find dependancies of .dstream package (Solaris) & .rpm package( linux)

Friends, Please let meknow, How we can find the dependancies of .dstream package & .rpm package before installation ? For AIX, We can use the inutoc . command to create the .toc file for the bff package, What about Solaris & Linux ? (0 Replies)
Discussion started by: yb4779
0 Replies

6. AIX

mkinstallp package creation failing "no such file: ./usr/lpp/<package name>/inst_root"

Hello, I'm trying to build a (bff) package from an already installed program (clam antivirus) using mkinstallp. However, mkinstallp fails with "no such file: ./usr/lpp/<package name>/inst_root" I'm not sure why all files get created ok except for these particular ones. Any help would be... (2 Replies)
Discussion started by: omonte
2 Replies

7. Shell Programming and Scripting

Pass a value from Unix to PLSQL

Hi all, I need to pass a value from Unix to plsql block so that i can use the variable to in where caluse to get the desired output. I tried useing $variable_name, but it doesn't work. Any kind of help is appreciated. Thanks in advance. (3 Replies)
Discussion started by: bankimmehta
3 Replies

8. Linux

how to restore original package after uninstalling the upgraded package using rpm

have following package installed rpm -qa |grep ADMIN It will give the following package installed: ADMIN-4.0.0.1 Now I will upgrade the ADMIN package using the following command. rpm --upgrade ADMIN-4.1.0.1 It will upgrade the ADMIN packagge to ADMIN-4.1.0.1 Now I want that... (0 Replies)
Discussion started by: amitpansuria
0 Replies
Login or Register to Ask a Question