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
Wrapping and unwrapping in SQL Operators WRAP and UNWRAP in connection with attributes whose declared types are tuple types. Example shows how extension and projection can be

COSMOZOI C THEORY - Richter (1865) proposed the cosmozoic theory that says that life came by spores (cosmozoa) or other particles from other planets on the earth.

Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff

Ensuring Backward Compatibility   The PL/SQL Version 2 permits some abnormal behavior which Version 8 disallows. Particularly, Version 2 permits you to (i) Make the forw

Scope Rules You cannot declare an exception twice in the similar block. Though, you can, declare the similar exception in 2 different blocks. The Exceptions declared in a bloc

Keyword & Parameter Description: boolean_expression: This is an expression which yields the Boolean value that is TRUE, FALSE, & NULL. character_expression: This

Using a join on 3 tables, select 5 columns and 10 rows from the 3 tables without the use of a Cartesian product Query: SELECT E.LAST_NAME, E.FIRST_NAME, S.BUILDING, S.BRAN

JOIN and AND in SQL In this Section is all about one operator, JOIN. SQL's closest counterpart, NATURAL JOIN, has already been covered. Here we look at several other "join" op

Naming Conventions The similar naming conventions apply to all PL/SQL program items and units including the variables, cursors, constants, cursor variables, procedures, exception

Using research notes and Oracle documentation plan and execute an upgrade of an installation of Oracle 10g to Oracle 11g release 1. To do this you must show in screen shots and wri