Using a host variable, PL-SQL Programming

Using a Host Variable

You can declare the cursor variable in the PL/SQL host environment like an OCI or Pro C program. To use the cursor variable, you should pass it as a host variable to PL/SQL. In the Pro C illustration, you pass a host cursor variable and a selector to the PL/SQL block that opens the cursor variable for the chosen query:

EXEC SQL BEGIN DECLARE SECTION;

...

/* Declare host cursor variable. */

SQL_CURSOR generic_cv;

int choice;

EXEC SQL END DECLARE SECTION;

...

/* Initialize host cursor variable. */

EXEC SQL ALLOCATE :generic_cv;

...

/* Pass host cursor variable and selector to PL/SQL block. */

EXEC SQL EXECUTE

BEGIN

IF :choice = 1 THEN

OPEN :generic_cv FOR SELECT * FROM emp;

ELSIF :choice = 2 THEN

OPEN :generic_cv FOR SELECT * FROM dept;

ELSIF :choice = 3 THEN

OPEN :generic_cv FOR SELECT * FROM salgrade;

END IF;

END;

END-EXEC;

The Host cursor variables are well-suited with any query return type. They act just like the PL/SQL cursor variables.

Posted Date: 10/4/2012 4:03:51 AM | Location : United States







Related Discussions:- Using a host variable, Assignment Help, Ask Question on Using a host variable, Get Answer, Expert's Help, Using a host variable Discussions

Write discussion on Using a host variable
Your posts are moderated
Related Questions
I want someone to write a TSQL function that returns the name of the ODBC DSN. I will use the queries below, to get information about the connection, but none of these return th

%FOUND Until the SQL data manipulation statement is executed, the %FOUND yields NULL. Afterward, the %FOUND yields TRUE, when an INSERT, UPDATE, or DELETE statement affected o

Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t

Read-Only Operator (+) - SQL The term read-only operator to the mathematical term function. Here I just need to add that the SQL standard reserves the term function for read-

Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e

Substitution and Instantiation - SQL It shows how NULL might appear in substitution for a parameter of a predicate and how it might thus participate in instantiation of that p

Example of Shorthand for a row constraint Example: Shorthand for a row constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Mark_in_range CHECK (Mark BETWEEN 0 AND 100);

Attributes: Just similar to variable, an attribute is declared with a name and datatype. The name should be exclusive within the object type. The datatype can be any Oracle ty

Lightweight system to provide and take info from workers in the field and office, have basic design outlined already just require build and implementation Desired Skills CSS,

Use the PLS_INTEGER Datatype When you require to declare an integer variable, use the datatype PLS_INTEGER that is the most efficient numeric type. That is as the PLS_INTEGER