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
Varrays versus Nested Tables The Nested tables are differing from varrays in the following ways: 1)  Varrays have a maximum size, while nested tables do not. 2)  Varrays are

Bulk Fetching The illustration below shows that you can bulk-fetch from a cursor into one or more collections: DECLARE TYPE NameTab IS TABLE OF emp.ename%TYPE; TYPE S

Use the RETURNING Clause Frequently, the application requires information about the row affected by a SQL operation, for illustration, to produce a report or take a subsequent

Project Description: I want a database for large governmental and private data sets on one country that will be easily extended to other countries in the future. Also, the datab

Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server

INSERT Statement The INSERT statement adds fresh rows of data to the specified database table or view. Syntax:

UTL_FILE: The Package UTL_FILE permits your PL/SQL programs to read & write operating system (OS) text files. It gives a restricted version of the standard OS stream file I/O,

Semidifference via NOT IN and a subquery SELECT StudentId FROM IS_CALLED WHERE Name = 'Devinder' AND StudentId NOT IN (SELECT StudentId FROM IS_ENROLLED_ON WHER

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

Define basic operators of relational algebra with an example each