Remote operations in pl sql, PL-SQL Programming

Remote Operations:

As the illustration shows below, the PL/SQL subprograms can execute the dynamic SQL statements which refer to the objects on a remote database:

PROCEDURE delete_dept (db_link VARCHAR2, dept_num INTEGER) IS

BEGIN

EXECUTE IMMEDIATE 'DELETE FROM dept@' || db_link ||

' WHERE deptno = :n' USING dept_num;

END;

The targets of remote procedure calls (RPCs) can also contain the dynamic SQL statements. For illustration, assume that the following stand-alone function, that returns the number of rows in the table, reside on the Chicago database:

CREATE FUNCTION row_count (tab_name CHAR) RETURN INT AS

rows INT;

BEGIN

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;

RETURN rows;

END;

From an unspecified block, you may call the function remotely, as shown below:

DECLARE

rows INTEGER;

BEGIN

rows := row_count@chicago('emp');

Posted Date: 10/6/2012 8:35:25 AM | Location : United States







Related Discussions:- Remote operations in pl sql, Assignment Help, Ask Question on Remote operations in pl sql, Get Answer, Expert's Help, Remote operations in pl sql Discussions

Write discussion on Remote operations in pl sql
Your posts are moderated
Related Questions
%ISOPEN The Oracle closes the SQL cursor automatically after executing its related SQL statement. As a result, the %ISOPEN forever yields FALSE.

Wrapping and unwrapping in SQL Operators WRAP and UNWRAP in connection with attributes whose declared types are tuple types. Example shows how extension and projection can be

Procedural Constraint Enforcement (Triggers) SQL has an alternative method of addressing database integrity, involving event-driven procedural code. The special procedures th

Ensuring Backward Compatibility   The PL/SQL Version 2 permits some abnormal behavior which Version 8 disallows. Particularly, Version 2 permits you to (i) Make the forw

I want to implement heap sort algorithm in pl sql please share the source code for guidance

Iterative Control: LOOP  Statements The LOOP statement executes a series of statements multiple times. There are 3 forms of LOOP statements: LOOP, WHILE-LOOP, & FOR-LOOP. LOOP

Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to the

Cursors   To execute the multi-row query, the Oracle opens an unnamed work region which stores the processing information. The cursor names the work region, access the informa

TYPES OF EVOLUTION - Sequential evolution                  :                    Minor changes in the gene pool of a population from one generation to the next, with the resul

V ariables and Constants in PL/SQL The PL/SQL permits you to declare constants and variables, and then use them in SQL and procedural statements anywhere in the expression. Th