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
Using PRIOR and NEXT The PRIOR(n) returns the index number that precede index n in a collection. The NEXT(n) returns the index number which succeed the index n. If n has no pr

Number Types The Number types permit you to store the numeric data (real numbers, integers, and floating-point numbers), show quantities, and do computations. BINARY_INTEG

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that accepts an employee ID from the user input and finds whether the employee ID is in the EMP

Using Operator VALUE: As you may expect, the operator VALUE returns the value of an object. The VALUE takes its argument a correlation variable.  For illustration, to return a

IN Mode An IN parameter pass the values to the subprogram being called. Within the subprogram, an IN parameter acts like a constant. And hence, it cannot be assigned a value.

GOTO Statement   The GOTO statement branches categorically to a block label or statement label. The label should be exclusive within its scope and should precede a PL/SQL bloc

Albeit simple method : These all the truth tables give us our first as albeit simple method for proving a theorem: where check whether it can be written in propositional logic

Example of Tables within a Table - SQL Example: Obtaining C_ER from COURSE and EXAM_MARK SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM_MARK AS EM

Query: SELECT * FROM EMPLOYEE1; Select 5 columns and all rows from one table Query: SELECT C_ID, COMPANY, BUILDING, DEPARTMENT, BRANCH FROM CONTRACT;

Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save