Visit Our UNIX and Linux User Community


Accessing the variable from pl sql bolck


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Accessing the variable from pl sql bolck
# 1  
Old 09-14-2009
Accessing the variable from pl sql bolck

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.
theeights
# 2  
Old 09-14-2009
Code:
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  
Old 09-14-2009
Hi theeights

Code:
#! /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  
Old 09-14-2009
Quote:
Originally Posted by theeights
...
Now how do I access the variable "var_px_cat" in unix after exiting from pl sql block.
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.

Code:
...
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

Previous Thread | Next Thread
Test Your Knowledge in Computers #678
Difficulty: Medium
Wi-Fi positioning systems use the positions of local cellular towers to identify the location of a Wi-Fi device.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. Shell Programming and Scripting

Significance of ':-' while accessing a variable

Hi I was trying to understand what ':-' means when used with variables echo ${x:-10} if Thanks (4 Replies)
Discussion started by: zulfi123786
4 Replies

3. Shell Programming and Scripting

accessing variable from while loop

Hi all, Here is an outline of the problem: #variable declared at start of script x=0; #a function that increments x by 1 every 10 seconds incrementX(){ increments x every 10 seconds; } #i want this to output the value of x every second. The problem is that x is always reported... (3 Replies)
Discussion started by: free2rhyme2k
3 Replies

4. Shell Programming and Scripting

Accessing local variable

Hi, Would like to know the purpose and accessing of local variable as in below code snippet: a=123 ( a=321; ) echo "a = $a" #This will print 123 How to access local a variable which is assigned with value 321 ?. .. (3 Replies)
Discussion started by: IND123
3 Replies

5. Shell Programming and Scripting

Accessing a variable from another file

HI all, How can i access a variable that is defined in another file as: $$Name= "abhinav; in my script. The catch is that it has 2 $s behind it... Thnaks ---------- Post updated at 10:36 AM ---------- Previous update was at 10:29 AM ---------- the file from which i have to... (0 Replies)
Discussion started by: abhinav192
0 Replies

6. Shell Programming and Scripting

Accessing a variable from another script

Hi, Can anyone assist me on how to access a variable in a shell script from another script. for ex, Script-1 ------- #! /bin/sh c=10 Where as, i would like to access the velue of variable c in another script 'Script-2'. Thankyou to all in advance !! :b: :b: (2 Replies)
Discussion started by: little_wonder
2 Replies

7. Programming

accessing mysql from ms-sql

Hi, I was trying to access Mysql Server sitting in Linux Box from MS-SQL through linked server concept. I am getting the error as : "Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary... (0 Replies)
Discussion started by: DILEEP410
0 Replies

8. UNIX for Advanced & Expert Users

Accessing PL/SQL OUT variables in Korn Shell Script

Hello All, I was just wondering if there is any direct way to access PL/SQL OUT variables from Korn Shell Script. I could already figure out how to return a single value back from PL/SQL to Shell Script (using bind variable). But, what if we want to return multiple values? One option I... (4 Replies)
Discussion started by: bright_future
4 Replies

9. Shell Programming and Scripting

Shell script for accessing a file in the network drive and invoking oracle sql loader

Hi, Please let me know if anybody is having a solution handy for the below tasks... It would be helpful if somebody can resolve my query. I am new to unix and oracle environment and looking for some online reference for completing a task. Task: Check if the network drive exists Check... (0 Replies)
Discussion started by: sayydevara
0 Replies

10. HP-UX

Accessing databases using sql*plus on HP box

When I try to connect to databse from HP box using the following command sqlplus <username>@<databasename> I get this error message: ksh: <database name> : not found I can connect to the database outside the UNIX box using sqlplus though. Looks like the problem is the way HP Unix is... (4 Replies)
Discussion started by: dtonse
4 Replies

Featured Tech Videos