Page History

Table of Contents

Overview

In ORACLE, To create a cursor variable, you define a REF CURSOR type, then declare cursor
variables of corresponding type.

Declaring REF CURSOR Type and Cursor Variables, passing Cursor Variables As Parameters can be declared in a PL/SQL.

A REF CURSOR is commonly declared in the ORACLE package and use it as a OUT PARAMETER of the ORACLE PL/SQL.

However, in case of the ALTIBASE HDB, Since it doesn't provide a package functionality currently, Typeset should be created in advance, and use it.

The procedures to use REF CURSOR should be divided into plural categories in ALTIBASE HDB as follows.

Create the user-defined type which is a REF CURSOR, and create a typeset which containing a corresponding type
Create the stored procedure, which having OUT parameters.
Execute a procedure.

Since ALTIBASE HDB doesn't provide a package functionality, Once a typeset is created with user-defined type, it can be passed between procedures.

Thus, a typeset, a combination of user-defined types using "CREATE TYPE" statement, can be made using "CREATE TYPESET" statement.

ORACLE

Code Block
CREATE TYPE emp_ary AS VARRAY(50) OF varchar2(4000);

ALTIBASE HDB

Code Block
CREATE TYPESET typeset_1 AS TYPE emp_rec_type IS RECORD( Name VARCHAR(20), Job_id VARCHAR(10), Salary NUMBER(8)); TYPE emp_arr_type is TABLE OF emp_rec_type INDEX BY INTEGER; END; /

If you wish to see further about the method of passing result set which described above in detail, refer to this page(Cursor Variables)

ORACLE	ALTIBASE HDB	Comments
CREATE OR REPLACE PACKAGE ref_cursor_pkg AS TYPE ref_type IS REF CURSOR; PROCEDURE ref_cursor_pro(v_result OUT ref_type, v_sql in VARCHAR2); END; / CREATE OR REPLACE PACKAGE BODY ref_cursor_pkg AS PROCEDURE ref_cursor_pro(v_result OUT ref_type, v_sql IN VARCHAR2) AS begin OPEN v_result FOR v_sql; END;/	CREATE OR REPLACE TYPESET my_type AS TYPE my_cur IS REF CURSOR; END; / CREATE OR REPLACE PROCEDURE opencursor ( v_result OUT my_type.my_cur, v_sql IN VARCHAR(200) ) AS BEGIN OPEN y_result FOR v_sql; END; /	In ORACLE, A declaration of the type is possible during a creation of the package. While in ALTIBASE HDB, Typeset, a set of the type, should be created ahead of use. after then, A type is available might be used in "CREATE PROCEDURE" statement.