Accessing the variable from pl sql bolck

Shell Programming and Scripting
1

I hve a PL SQL block in unix where i define a variable "var_px_cat" and use it for taking count

SELECT COUNT(*) INTO var_px_cat
FROM A
WHERE B = '$CATEGORIE_ID';

Now how do I access the variable "var_px_cat" in unix after exiting from pl sql block.

2 
echo "
SET SERVEROUT ON SIZE 100000
SET FEED OFF
DECLARE 
  var_px_cat number:=0;
BEGIN
DBMS_OUTPUT.enable(1000000);
SELECT COUNT(*) INTO var_px_cat
FROM A 
WHERE B = '$CATEGORIE_ID';
DBMS_OUTPUT.PUT(var_px_cnr);
DBMS_OUTPUT.NEW_LINE;
END;
/
 " | sqlplus user/pswd@someplace  | read myvar
echo "myvar is now $myvar"

There are other ways - write to a file with UTL_FILE then read the file contents

3

Hi theeights

#! /bin/ksh

aaa=`sqlplus -s username/password@sid <<END >outputfile.txt
SET SERVEROUTPUT ON;
DECLARE
var_px_cat number;
BEGIN
SELECT COUNT(1) 
INTO var_px_cat
FROM A
WHERE B='$CATGORIE_ID';
DBMS_OUTPUT.PUT_LINE (var_px_cat);
END;
/
exit;
END`

a=`head -1 outputfile.txt`

echo $a
4

If the declaration of the PL/SQL variable was solely for passing its value to the Unix shell, then you don't really need it.

...
count=`sqlplus -s username/password@sid <<EOF
set pages 0 feed off
SELECT COUNT(*)||''
FROM A 
WHERE B = '$CATEGORIE_ID';
exit
EOF`
echo "Count = $count"
...

tyler_durden