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
SELECT INTO Statement   The SELECT INTO statement retrieve data from one or more database tables, and then assigns the selected values to the variables or fields. Syntax:

Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

Many of the reports generated from the system calculate the total dollars in a shopper''s purchases. Follow these steps to create a function named TOT_PURCH_SF that accepts a shopp

Assignments in pl/sql The Variables and constants are initialized every time a block or subprogram is entered. By default, the variables are initialized to NULL. Therefore, unle

Row Counterparts of Table Operators SQL does not have counterparts tuple rename, tuple projection, tuple extension, tuple join and tuple compose. To obtain the same effects as

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

Semidifference and NOT - SQL In this section first describe the relational difference operator, named MINUS. Example here shows SQL's closest counterpart of that operator.

How Transactions Guard Your Database The transaction is a sequence of SQL data manipulation statements which does a logical unit of work. The Oracle treats the sequence of SQL

Project Description: We organize an online system called ACPAS we have created a project called EVO that can be use by our customers to integrate their web sites with the Acpas

Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a