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
9 More Discussions You Might Find Interesting
1. UNIX for Dummies Questions & Answers
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
2. Programming
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
3. Shell Programming and Scripting
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
4. Shell Programming and 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
5. Shell Programming and Scripting
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. Solaris
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
7. Shell Programming and Scripting
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
8. Shell Programming and Scripting
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
9. Shell Programming and Scripting
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
LEARN ABOUT MOJAVE
execute
EXECUTE(7) SQL Commands EXECUTE(7)
NAME
EXECUTE - execute a prepared statement
SYNOPSIS
EXECUTE name [ ( parameter [, ...] ) ]
DESCRIPTION
EXECUTE is used to execute a previously prepared statement. Since prepared statements only exist for the duration of a session, the pre-
pared statement must have been created by a PREPARE statement executed earlier in the current session.
If the PREPARE statement that created the statement specified some parameters, a compatible set of parameters must be passed to the EXECUTE
statement, or else an error is raised. Note that (unlike functions) prepared statements are not overloaded based on the type or number of
their parameters; the name of a prepared statement must be unique within a database session.
For more information on the creation and usage of prepared statements, see PREPARE [prepare(7)].
PARAMETERS
name The name of the prepared statement to execute.
parameter
The actual value of a parameter to the prepared statement. This must be an expression yielding a value that is compatible with the
data type of this parameter, as was determined when the prepared statement was created.
OUTPUTS
The command tag returned by EXECUTE is that of the prepared statement, and not EXECUTE.
EXAMPLES
Examples are given in the Examples [prepare(7)] section of the PREPARE [prepare(7)] documentation.
COMPATIBILITY
The SQL standard includes an EXECUTE statement, but it is only for use in embedded SQL. This version of the EXECUTE statement also uses a
somewhat different syntax.
SEE ALSO
DEALLOCATE [deallocate(7)], PREPARE [prepare(7)]
SQL - Language Statements 2010-05-14 EXECUTE(7)