Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to pass value from plsql script to unix bash?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to pass value from plsql script to unix bash?
# 1  
Old 03-01-2010
How to pass value from plsql script to unix bash?
Hi
This is my bash script.i am calling validation.sql and passing a value to it using ${flds[1]}.
i want the cnt variable in plsql script to be passed to unix.



Code:
LOADREC=`sqlplus -s $ORACLE_USR <<-EOF
spool $ORACLE_LOG_FILE;
echo "barani"
@validation.sql #calling the plsql script
${flds[1]} #passing value to the script
 
spool off;
EOF`
 
 
this is my validation.sql script
 
 
 
SET SERVEROUTPUT ON ;
DECLARE 
 
job_name varchar(10) :='&fedexjobname';
 
cnt number; # the value of the variabel should be pass to unix 
 
BEGIN 
 
 
dbms_output.put_line(job_name);
 
 
select count(*) into cnt from TB_AUDIT
where Batchnumber=job_name ; 
 
IF cnt = 0
THEN 
dbms_output.put_line('job was not processed earlier');
ELSE
dbms_output.put_line('job was processed earlier');
END IF; 
EXCEPTION
WHEN OTHERS THEN 
raise_application_error(-20000, 'validation.sql - Unknown Exception Raised: '||SQLCODE||' '||SQLERRM);
ROLLBACK;
END;
/


Thanks.
Last edited by pludi; 03-02-2010 at 01:56 AM.. Reason: code tags, please...
# 2  
Old 03-02-2010
IMake use of a procedure or a function in the plsql script. Return the value of count from the function as a return value or an OUT parameter for a procedure. Try it out and let us know what you come up with.


cheers,
Devaraj Takhellambam
# 3  
Old 03-02-2010
MySQL
If you want just the value of the cnt variable,
1. you can use a stored procedure with OUT parameter.
2. A Function which returns its value.

If you dont want to use the above 2 methods. There one more method.
Where in you can direct the output of the SQL via dbms_output.put_line statement to a file and then you can read from that file.

Let me know your feasibility, then i will give you more details.
# 4  
Old 03-02-2010
call a function
Hi karthik,


if i use a function then how to call the function from bash.

the fuction is a parametised.

and how can get return value of the function in bash.

Thanks.
# 5  
Old 03-02-2010
Try to print the value of cnt in the QL block and see if you get that value from the unix variable LOADREC.

Code:
SET SERVEROUTPUT ON ;
DECLARE
job_name varchar(10) :='&fedexjobname';
cnt number; # the value of the variabel should be pass to unix
BEGIN
select count(*) into cnt from TB_AUDIT
where Batchnumber=job_name ;
dbms_output.put_line(cnt);

IF cnt = 0
THEN
dbms_output.put_line('job was not processed earlier');
ELSE
dbms_output.put_line('job was processed earlier');
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, 'validation.sql - Unknown Exception Raised: '||SQLCODE||' '||SQLERRM);
ROLLBACK;
END;

Code:
echo $LOADREC

# 6  
Old 03-02-2010
Barani,

i was thinking to declare the parameter that you want as output as OUT parameter and calling the function. But yeah... once you log into SQL, taking the parameter from SQL session to UNIX session is not possible.

OK... Leave that.

Lets try the other one.

Direct the output of the SQL session, to a file throught DBMS_OUTPUT.PUT_LINE statements.
and then read the variable from that file.

What do you think abt this one ?
# 7  
Old 03-02-2010
Barani,

Oracles exit statment lets you return a value to the calling shell.
Your validation.sql commandfile could be rewritten like this:
Code:
SET SERVEROUTPUT ON ;
VARIABLE ret number;
DECLARE 
   job_name varchar(10) :='&fedexjobname';
   cnt number; # the value of the variabel should be pass to unix 
BEGIN 
  dbms_output.put_line(job_name);
  select count(*) into cnt from TB_AUDIT where Batchnumber=job_name ; 
  :ret := cnt;
  IF cnt = 0
  THEN 
    dbms_output.put_line('job was not processed earlier');
  ELSE
    dbms_output.put_line('job was processed earlier');
  END IF; 
EXCEPTION
  WHEN OTHERS THEN 
    raise_application_error(-20000, 'validation.sql - Unknown Exception Raised: '||SQLCODE||' '||SQLERRM);
    ROLLBACK;
END;
/
exit :ret

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Pass config file to bash script

I just want to make sure I am understanding how to pass a config file to a bash script . In the below I pass to arguments to a script, then define them in the script as id and config. I then source config using ., if I understand correctly the variables in the config file can now be used by the... (11 Replies)
Discussion started by: cmccabe
11 Replies

2. Homework & Coursework Questions

How to Dynamically Pass Parameter to plsql Function & Capture its Output Value in a Shell Variable?

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted! 1. The problem statement, all variables and given/known data: 2. Relevant commands, code, scripts, algorithms: #! /bin/ksh v="ORG_ID" ... (2 Replies)
Discussion started by: sujitdas2104
2 Replies

3. Shell Programming and Scripting

Pass arguments to bash script

myscript.sh #!/bin/bash ARGA=$1 if ; then echo "${ARGA}:Confirmed" else echo "${ARGA}:Unconfirmed" fi when I run the above script from the command line, i run it as: ./myscript.sh jsmith now some times, i need to runn it this way: (8 Replies)
Discussion started by: SkySmart
8 Replies

4. Shell Programming and Scripting

How to Pass filename to AWK in bash script

I have written a script which works fine, to remove patterns contained in EXCLUDE.DAT from input.txt awk 'BEGIN {n=0;while (getline < "EXCLUDE.DAT" > 0){ex=$0;n++}} {for(var in ex){print var "-" ex $0 ;i++}}' input.txt The last problem I need to solve is how to pass the file... (3 Replies)
Discussion started by: nixie
3 Replies

5. 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

6. Programming

UNIX Shell Script to Create a Document of a PLSQL code.

Hi All, I am supposed to present the documentation for the PLSQL code (PACKAGES, PROCEDURE, FUNCTIONS) of my application. There are sufficient comments in my code. Has anyone written any Shell Script Utility which can parse the PLSQL code and generate some kind of document ( preferrably HTML not... (1 Reply)
Discussion started by: gauravsachan
1 Replies

7. UNIX for Advanced & Expert Users

Calling unix script from sql or plsql

Hi Can anyone please let me know how to call unix scripts from sql or plsql ASAP. (2 Replies)
Discussion started by: ksailesh
2 Replies

8. Shell Programming and Scripting

Bash script pass sentence in block

Hello, I want to know is it possible to pass a block of sentence using bash. For example, I have a script called Test.sh that takes in $1 and $2. and I'm calling Test.sh in a.sh so in a.sh Test.sh '' 'This is a sentence' Because block are separated by space so when I do that, I get... (6 Replies)
Discussion started by: katrvu
6 Replies

9. UNIX for Advanced & Expert Users

calling plsql function in a unix script

Could anyone please help me. I have a function in plsql that returns a number. But i am listing some records through that function using DBMS_OUTPUT.PUT_LINE. I want to catch those records by executing this function through a unix script. The following shows what i did echo "Connected from... (2 Replies)
Discussion started by: cobroraj
2 Replies

10. UNIX for Dummies Questions & Answers

Problem running plsql & unix commands in 1 script

Hi, I need help again. When I run this shell script, it only runs the unld_date.sql piece and exits. How can I structure this to run all the way to the end? When I don't have the unld_date.sql piece in here, everything runs fine from the date compare piece all the way to the end. Thanks in... (5 Replies)
Discussion started by: siog
5 Replies
Login or Register to Ask a Question