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
Description: Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that pr

Develop Data Business Intelligence Project Project Description: We are linking our Microsoft SQL Database to GoodData Business Intelligence. We are seeking somebody who has e

Use Native Dynamic SQL A few programs (a normal-purpose report writer for illustration) should build and process a variety of SQL statements at run time. Therefore, their full

Parameter and Keyword Description: package_name: This construct identifies the package. AUTHID Clause: This determine whether all the packaged subprograms impleme

Defining and Declaring Collections To create the collections, you must define a collection type, and then declare the collections of that type. You can define the VARRAY types a

OPEN-FOR Statement The OPEN-FOR statements execute the multi-row query related with a cursor variable. It also allocates the resources used by the Oracle to process the query a

i NEED THE QURIES TO SOME OF THE PROBLEMS USING plsql

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

Cursors   To execute the multi-row query, the Oracle opens an unnamed work region which stores the processing information. The cursor names the work region, access the informa

Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t