Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

To get value from Oracle Anonymous PLSQL block

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting To get value from Oracle Anonymous PLSQL block
# 1  
Old 05-11-2009
To get value from Oracle Anonymous PLSQL block
Hi All,

i need you help while accessing the value from anonymous PL/SQL block.
Initially our requirement was to get the value from SQL statement. I was doing like...
Quote:
ABC=`sqlplus -s <<EOF
uname/paswd@connectstring
SET HEADING OFF;
SELECT COUNT(*) from XYZ;
exit;
END`
But now i need to use dbms_application_info.set_module in it. So i have planned to use anonymous block instead of SQL statement like...
Quote:
ABC=`sqlplus -s <<EOF
uname/paswd@connectstring
DECLARE
cntl varchar2(20);
BEGIN
dbms_application_info.set_module ('shm.ksh','get count');
SELECT COUNT(*) INTO cntl from XYZ;
dbms_application_info.set_module (null,null);
END;
exit;
END`
but i am not getting the output while echoing $ABC
# 2  
Old 05-11-2009
Code:
set serverout on size 2000
.................

dbms_output.enable(2000);
......
SELECT COUNT(*) INTO cntrl from XYZ;
dbms_output.put_line(to_char(cntrl, '999999') );

will display the value so it is read into the shell variable.
# 3  
Old 05-11-2009
Quote:
Originally Posted by Amit.Sagpariya
But now i need to use dbms_application_info.set_module in it...
You don't get the value in ABC because select-into is capturing it in a pl/sql variable.
Yes, using dbms_output.put_line() you can print out the value so that it ends in ABC.
But. You don't have to keep the select inside the anonymous block, just do it before or after the block.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Call Anonymous block from DB2

Hi, I have a shell script and im trying to call a .sql file. The sql file has a simple Anonymous block. The code is shell script db2 -tvf query.sql the sql file contains SET SERVEROUTPUT ON; BEGIN call dbms_output.put_line( 'Hello' ); END; I get the following error BEGIN... (1 Reply)
Discussion started by: sup
1 Replies

2. Shell Programming and Scripting

Passing the result of an anonymous pl/sql block to a shell script

Hello, Here is the code i have written to get the count of a plsql query back to the unix. function checkforCOIDs { countcheck=`sqlplus -s $1/$2@$3 whenever oserror exit sql.oscode rollback whenever sqlerror exit sql.sqlcode rollback set serverout on size 2000; set head off feedback off... (2 Replies)
Discussion started by: santosh2eee
2 Replies

3. Shell Programming and Scripting

Connecting+Firing plsql to Oracle DB in one go

Hi, I am using the below command to connect to a remote Oracle DB from my unix shell script. sqlplus -s user1/password1@remotedbhost:1252/demo @/tmp/PROC_MS.prc But I get the below error. 1. Can you confirm if my code is correct ? 2. Can you help me overcome the error ? (3 Replies)
Discussion started by: mohtashims
3 Replies

4. Solaris

Please help me how to administrate Oracle/plsql server

I previously worked for the L1 support on DNS/Apache servers in solaris10...... Now our team lead wants me to work on Oracle/plsql server for our new client.... I've to work without training... How can we know that Oracle/plsql server running in our machine ?? How to Administrate... (1 Reply)
Discussion started by: vamshigvk475
1 Replies

5. Shell Programming and Scripting

Calling an Anonymous Block through shell script

Hi, My requirement is to load a LONG datatype data value from one table to another as direct access does not work (DB: ORACLE). eg. SELECT *FROM ALL_VIEWS WHERE TEXT LIKE '%<SEARCH_STRING>%'; As an alternate we are creating a table and trying to insert in it from ALL_VIEWS as direct insert... (2 Replies)
Discussion started by: sanjaydubey2006
2 Replies

6. Shell Programming and Scripting

scope of oracle plsql development /unix shell scripting

I am working in production support in an leading bank and more of my job is more in oracle/plsql. I am learning unix shell scripting and have started development in the same.I would like to know regarding the scope of these two in the industry.Also would like to know about other scripting... (2 Replies)
Discussion started by: jesthomas
2 Replies

7. Shell Programming and Scripting

Invoking SP through Anonymous Block

Hi, I'm writing a Shell script to run a stored procedure through SQL*Plus using the below script and it works well. Test.sh $ORACLE_HOME/bin/sqlplus connect user/pwd@dbname @Test.sql Test.sql call sp_test_procedure() Now, similarly I want to run the below PL/SQL anonymous block... (1 Reply)
Discussion started by: Dev_Dev
1 Replies

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

9. UNIX for Dummies Questions & Answers

How to block an anonymous ftp user?

Could anyone provide information on how to block a specific client machine from being able to log onto anonymous ftp? (10 Replies)
Discussion started by: dennisheazle
10 Replies
Login or Register to Ask a Question