rem
rem	Script:		c_sql.sql
rem	Author:		Jonathan Lewis
rem	Dated:		June 1999
rem	Oracle:		8.1.5.0 (NT)
rem	Purpose:	Open ended select in pl/sql
rem
rem	Notes:
rem	The procedures are:
rem		open a cursor
rem		fetch next row
rem		close a cursor
rem		do all three at once in a single call
rem
rem	Based on the assumption that a single select can only be 1,000 columns
rem	the column types are stacked in a 'pseudo-2'dimensionalarray, by offsetting 
rem	each set of column types by 1000 x number cursors.
rem
rem	Each time the cursor is fetched, the types are unstacked to a simple
rem	array that code can use.  Ditto the column sizes.
rem
rem	Could do more explicit types:
rem		1	varchar2
rem		2	Number
rem		8	Long
rem		9	varchar
rem		12	Date
rem		23	Raw
rem		24	Long raw
rem		69	Rowid
rem		96	Char
rem
rem	Earlier versions of Oracle will need to reduce varchar2(4000)
rem
rem	NB - You do NOT need to do this in 8.1.5, as the support for
rem	dynamic SQL in that version is very good.  It just happened that
rem	I was running 8.1.5 when I decided to sketch out this code.
rem
rem	The only thing this package does not use is the BIND call, used
rem	to associate a 'symbolic' variable-name in a dynamic SQL or PL/SQL 
rem	statement with the relevant real variable in the package doing
rem	the call.
rem	

create or replace package execute_select as
	type	date_array	is table of date index by binary_integer;
	type	number_array	is table of number index by binary_integer;
	type	varchar2_array	is table of varchar2(4000) index by binary_integer;

	v_dates		date_array;		-- output array for dates
	v_numbers	number_array;		-- output array for number
	v_varchar2s	varchar2_array;		-- output array for varchar2

	v_types		varchar2_array;		-- Visible 'external' types for last called cursor

	v_col_names	varchar2_array;		-- Set to column names at 1st parse
	v_col_types	varchar2_array;		-- set to 'internal' column types at 1st parse
	v_col_sizes	number_array;		-- Set to max-length at 1st parse

	function open_cursor(i_string in varchar2) return number;
	function fetch_row(i_cursor in number) return boolean;
	procedure close_cursor(i_cursor in number);

	function select_one_row (i_string in varchar2) return boolean;

end;
.
/


create or replace package body execute_select as

c_max_cols	constant pls_integer  := 1000;  -- Oracle 8.1.5 limit

v_cursor_ct	number := 0;		-- top of cursor stack
v_cursors	number_array;		-- stack of Oracle's cursor handles
v_col_counts	number_array;		-- stack of columns per cursor
v_type_stack	varchar2_array;		-- '2d' stack of column types
v_size_stack	number_array;		-- '2d stack of column sizes'

--
--	Private procedure to use the DEFINE_COLUMN mechanism by unstacking
--	from the private arrays of types and sizes into the simple public array.
--

procedure define_columns(i_cursor in number) is

v_cursor	number;
v_col_count	number;

v_empty_dates		date_array;
v_empty_numbers		number_array;
v_empty_chars		varchar2_array;

begin
	v_cursor	:= v_cursors(i_cursor);
	v_col_count	:= v_col_counts(i_cursor);

	v_dates := v_empty_dates;
	v_numbers := v_empty_numbers;
	v_varchar2s := v_empty_chars;

	for i_ct in 1..v_col_count loop
		v_numbers(i_ct) := null;
		v_varchar2s(i_ct) := null;
		v_dates(i_ct) := null;

		v_types(i_ct) := v_type_stack(c_max_cols * i_cursor + i_ct);
		v_col_sizes(i_ct) := v_size_stack(c_max_cols * i_cursor + i_ct);

		if (v_types(i_ct) = 'N') then
			dbms_sql.define_column(
				v_cursor,
				i_ct,
				v_numbers(i_ct)
			);
		elsif (v_types(i_ct) = 'D') then
			dbms_sql.define_column(
				v_cursor,
				i_ct,
				v_dates(i_ct)
			);
		elsif (v_types(i_ct) = 'V') then
			dbms_sql.define_column(
				v_cursor,
				i_ct,
				v_varchar2s(i_ct),
				v_col_sizes(i_ct)
			);
		else
			dbms_sql.define_column(
				v_cursor,
				i_ct,
				v_varchar2s(i_ct),
				v_col_sizes(i_ct)
			);
		end if;

	end loop;

end define_columns;

/**************************************************************/

/*
	Opens a cursor, and parses the incoming string
	Call Oracle's DESCRIBE to get the names, lengths and 
	types of the selected columns.

	Stacks all this information internally, then uses it to
	associate (through the DEFINE call) the relevant public
	array items with expected columns.

	Executes the cursor, and returns a pointer into the
	private stack that needs to be passed in for all further
	uses for the code

	There is a bug in the describe procedure, which results in
	the precision and scale of numeric types being returned as 0,
	hence the comment below where number types are given the 
	max_length value, rather than the precision value for their length.
*/

function open_cursor(i_string in varchar2) return number is

	v_empty_types		varchar2_array;

	v_empty_col_names	varchar2_array;
	v_empty_col_types	varchar2_array;
	v_empty_col_sizes	number_array;

	v_col_descs		dbms_sql.desc_tab;
	v_col_count		number;

	v_cursor		number;
	v_status		number;

	v_junk	varchar2(60);
begin
	v_types := v_empty_types;

	v_col_names := v_empty_col_names;
	v_col_types := v_empty_col_types;
	v_col_sizes := v_empty_col_sizes;

	v_cursor := dbms_sql.open_cursor;

	dbms_sql.parse(
		v_cursor,
		i_string,
		dbms_sql.native
	);

	dbms_sql.describe_columns(
		v_cursor,
		v_col_count,
		v_col_descs
	);

	v_cursor_ct := v_cursor_ct + 1;
	v_cursors(v_cursor_ct) := v_cursor;
	v_col_counts(v_cursor_ct) := v_col_count;

	for i_ct in 1..v_col_count loop

		v_col_names(i_ct) := v_col_descs(i_ct).col_name;
		v_col_types(i_ct) := v_col_descs(i_ct).col_type;

		if (v_col_types(i_ct) = 2) then
			v_types(i_ct) := 'N';
		elsif (v_col_types(i_ct) = 12) then
			v_types(i_ct) := 'D';
		elsif (v_col_types(i_ct) = 1) then
			v_types(i_ct) := 'V';
		else
			v_types(i_ct) := 'V';
		end if;


		if v_types(i_ct) = 'N' then
-- Bug			v_col_sizes(i_ct) := v_col_descs(i_ct).col_precision;
			v_col_sizes(i_ct) := v_col_descs(i_ct).col_max_len;
		else
			v_col_sizes(i_ct) := v_col_descs(i_ct).col_max_len;
		end if;

		v_type_stack(c_max_cols * v_cursor_ct + i_ct) := v_types(i_ct);
		v_size_stack(c_max_cols * v_cursor_ct + i_ct) := v_col_sizes(i_ct);

	end loop;

	define_columns(v_cursor_ct);

	v_status := dbms_sql.execute(v_cursor);

	return v_cursor_ct;

end open_cursor;

/**************************************************************/

/*
	The user passes in the stack pointer for an existing
	cursor and this code calls the define_columns routine
	to unstack the details before fetching the next row
	into the public arrays
*/

function fetch_row(i_cursor in number) return boolean is

v_cursor	number; 
v_col_count	number; 

begin

	define_columns(i_cursor);

	v_cursor	:= v_cursors(i_cursor);
	v_col_count	:= v_col_counts(i_cursor);

	if (dbms_sql.fetch_rows(v_cursor) != 1) then
		return false;
	else
		for i_ct in 1..v_col_count loop
			if (v_types(i_ct) = 'N') then
				dbms_sql.column_value(
					v_cursor,
					i_ct,
					v_numbers(i_ct)
				);
			elsif (v_types(i_ct) = 'D') then
				dbms_sql.column_value(
					v_cursor,
					i_ct,
					v_dates(i_ct)
				);
			elsif (v_types(i_ct) = 'V') then
				dbms_sql.column_value(
					v_cursor,
					i_ct,
					v_varchar2s(i_ct)
				);
			else
				dbms_sql.column_value(
					v_cursor,
					i_ct,
					v_varchar2s(i_ct)
				);
			end if;
		end loop;	
	end if;

	return true;

end fetch_rows;

/**************************************************************/

/*
	Close a cursor, given the stack pointer.
	In the lucky case that the stack pointer is at top of
	stack, then we can safely drop the stackpointer by one
	and reuse the space from the 'describe' arrays.

	Hint:  always close your 'jpl-cursors' in reverse order.

*/

procedure close_cursor(i_cursor in number) is
begin
	dbms_sql.close_cursor(v_cursors(i_cursor));
	if i_cursor = v_cursor_ct then
		v_cursor_ct := v_cursor_ct - 1;
	end if;

end close_cursor;

/**************************************************************/

/*
	Given a string:
	Parse it,
	Fetch one row
	Close the cursor.

	Doesn't bother to check if a second row was available
*/


function select_one_row (i_string in varchar2) return boolean is

v_cursor	number;
v_junk		boolean;

begin

	v_cursor := open_cursor(i_string);
	v_junk := fetch_row(v_cursor);
	close_cursor(v_cursor);			

end select_one_row;

end;
.
/