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
Project Description: I am looking to change FullCalendar to add/delete sql server data when events are removed or dropped from Calendar. Events should only be included by dra

Cursor Attributes   The Cursors and cursor variables have 4 attributes which give you helpful information about the execution of a data manipulation statement. Syntax:

Using ROLLBACK The ROLLBACK statements end the present transaction and undo any change made during the transaction. The Rolling back is helpful for two reasons. Firstly, if yo

Write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive

Need for Dynamic SQL: You need dynamic SQL in the situations as follows: 1) You would like to execute a SQL data definition statement (like CREATE), a data control statemen

Effects of NULL Operator As a general rule-but not a universal one-if NULL is an argument to an invocation of a system-defined read-only operator, then NULL is the result of t

INSERT Command in SQL Loosely speaking, INSERT takes the rows of a given source table and adds them to the specified target table, retaining all the existing rows in the targ

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi

SELECT a.child_fname,a.child_lname,concat(b.parent_title,b.parent_fname), b.parent_lname,b.parent_tphone FROM child a,parent b WHERE a.parent_id=b.parent_id ORDER BY a.child_fnam

Updating Variables For assignment, SQL uses the key word SET, as in SET X = X + 1 (read as "set X equal to X+1") rather than X: = X + 1 as found in many computer languages.