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
Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Negation (NOT, ¬) - SQL There are three rows instead of just two. As you can see, ¬ p is defined as in two-valued logic (2VL) when p is either true or false, but ¬ (unknown) i

DECLARE : This keyword signals the beginning of the declarative section of the PL/SQL block, that contains local declarations. The Items declared locally exist only within the

Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by

DBMS_OUTPUT: The Package DBMS_OUTPUT enables you to display output from the PL/SQL subprograms and blocks, that makes it easier to test and debug them. The procedure put_ line

Create a Oracle procedure to produce vertical output format when selecting rows from a database table.

Processing Transactions This part describes how to do the transaction processing. You learn the fundamental techniques that safeguard the consistency of your database, involvin

Creating and Destroying Base Tables: Example shows an SQL command to create the base table counterpart of the ENROLMENT variable Example  Creating a base table. CREATE T

Oracle 11 G new features associated with this release:- Enhanced ILM  - Information Lifecycle Management (ILM) has been around for the almost 10 years, but Oracle has made

Row Operators The Row operators return or reference the particular rows. ALL retains the duplicate rows in the result of a query or in an aggregate expression. The DISTINCT el