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
Literals A literal is an explicit numeric, string, character, or Boolean value not represented by an identifier. Numeric literal 147 and the Boolean literal FALSE are some of

Declarations in SQL Your program stores values in the variables and constants. As the program executes, the value of the variables can change, but the values constants cannot.

Nested Tables versus Index-by Tables The Index-by tables and nested tables are just similar. For e.g.  They have similar structure and their individual elements are accessed in

%ISOPEN The Oracle closes the SQL cursor automatically after executing its related SQL statement. As a result, the %ISOPEN forever yields FALSE.

Example of GROUP BY and COLLECT Operator Example: Using GROUP BY and COLLECT to obtain C_ER2 SELECT CourseId, CAST ( COLLECT (ROW (StudentId, Mark)) AS ROW (Studen

Anatomy of a Table: Figure shows the terminology used in SQL to refer to parts of the structure of a table. As you can see, SQL has no official terms for its counterpa

Components of an Object Type: An object type encapsulates the operations and data. Therefore, you can declare the methods and attributes in an object type specification, but no

Why Use Cursor Variables ? Primarily, you use the cursor variables to pass the query result sets between the PL/SQL stored subprograms and different clients. Neither PL/SQL nor

IN OUT Mode An IN OUT parameter passes initial values to the subprogram being called and return efficient values to the caller. Within the subprogram, an IN OUT parameter acts

Adding Table Constraints ALTER TABLE ENROLMENT ADD CONSTRAINT NameNotNull CHECK (Name IS NOT NULL) ; ALTER TABLE ENROLMENT ADD CONSTRAINT PK_StudentId_CourseId PRIM