Visit Our UNIX and Linux User Community


stored procoedure bind variables in shell


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting stored procoedure bind variables in shell
# 1  
Old 10-23-2009
Data stored procoedure bind variables in shell

Hi ,

I have a pl/sql procedure which takes its input from a shell script, and deletes rows from db table based on the input.
I've dbms_output.put_line statments in procedure which i want to capture and display in the script

for example, if no rows have been deleted, i have to stop executing the rest of the script.

the issue is that the dbms_output statements are not displayed on the screen even when the serveroutput is on

Can anyone give me ideas on how to implement this.
# 2  
Old 10-23-2009
When calling put_line from other than an anonymous block, the output is buffered. To retrieve the output, use get_line.

Code:
create or replace procedure TestProc as
  2  begin
  3  dbms_output.put_line( 'This is TestProc' );
  4  end;
  5  /

Code:
cat Test
sqlplus -s scott/tiger << !
  set serveroutput on
  set feedback off
declare
  RES varchar2(256);
  S number(10);
begin
  TestProc;
  dbms_output.get_line( RES,S );
  dbms_output.put_line( RES );
end;
/
!

./Test 
This is TestProc


Previous Thread | Next Thread
Test Your Knowledge in Computers #903
Difficulty: Medium
There are less than 10 million lines of code in the Linux kernel as of 2018.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need Help: Shell script to call sql session with variables stored in .txt file

Hi, I need help in writing a shell script which can read data from a text file (Cancel_ID.txt) and then calls sqlplus session (Cancel.sql) with the first line parameter of the text file ("0322600453") till all rows are not completed. ... (4 Replies)
Discussion started by: Khan28
4 Replies

2. Shell Programming and Scripting

How to get OUT parameter of a stored procedure in shell script?

I am invoking a SQL script from shell script. This SQL script will invoke a stored procedure(which has the OUT parameter). I want to have the OUT parameter in the shell script as a variable. Is this possible? (6 Replies)
Discussion started by: vel4ever
6 Replies

3. Shell Programming and Scripting

using sed command to display contents where line numbers are stored in variables

if i want to display the contents of a file between say line number 3 and 10 then i use the following command sed -n '3,10p' filename if this 3 was contained in x and 10 was contained in y then how wud this command modified? sed -n '$x,$yp' filename does not work..please advise (2 Replies)
Discussion started by: arindamlive
2 Replies

4. Shell Programming and Scripting

Oracle Query results to be stored in variables using unix

I want to store the sql query output into a variable #!/bin/ksh ORACLE_SID=DB01; export ORACLE_SID; export FILE_PATH=/home/asg/Tmp # Order Checking echo " removing old files " rm $FILE_PATH/Malformed_Order.txt echo " Enter the Malformed Order ....!" read orders echo "Regrade... (5 Replies)
Discussion started by: Nareshp
5 Replies

5. Shell Programming and Scripting

shell script: Bind variable not declared

Hi Friends, I am trying to run a sql query from shell script as below but I get "Bind variable "1" not declared" error. 1.sh shell script has following: sDb="abc/xyz@aaa" a="1.sql" sqlplus -s $sDb @$a $1 1.sql file has following: spool Result.tmp append select cust_name from orders... (1 Reply)
Discussion started by: ppat7046
1 Replies

6. Shell Programming and Scripting

Oracle Query results to be stored in variables

Hi I would like to know if there is a way to just have one ORACLE connection established, using which we can execute different queries and store the results under different variables. For e.g the following uses to two silent ORACLE connection to store the result under two different... (4 Replies)
Discussion started by: ashokjaysiv
4 Replies

7. UNIX for Advanced & Expert Users

Escaping special character stored in variables : perl

Hi just for regular use i m working on small module written in perl for getting date in specified format like i have to specify date format and then seperator to seperate date i am 95% done. now i m sure explanation i gave is not good enough so i am putting output here : C:\Documents and... (2 Replies)
Discussion started by: zedex
2 Replies

8. UNIX for Advanced & Expert Users

SQLPLUS and update statements using bind variables

Hello- The code below works fine expect that it does not update the table CTL_INTERFACE "red highlight". Any idea what I'm doing wrong here? Thanks:D # coNNECT to the database and insert a row then get the new row id cycle_id=`sqlplus -S $XXX_USER/$XXX_PW@$XXX_CONNECT << EOF SET ECHO... (3 Replies)
Discussion started by: Nomaad
3 Replies

9. Shell Programming and Scripting

Shell arrays in oracle stored procedure

Is it possible to pass unix shell arrays in Oracle stored procedure? Is yes, how? Thanks (6 Replies)
Discussion started by: superprogrammer
6 Replies

10. UNIX for Advanced & Expert Users

Environmental Variables - where stored ?

Hi all ! Yesterday I defined an environmental variable PATH, but today when I restarted machine, I could not see that it was stored any place. Is there any file where I could save the settings ? I have quite a few env.variables defined, so I need a smarter way to define. regards D (5 Replies)
Discussion started by: DGoubine
5 Replies

Featured Tech Videos