CREATE OR REPLACE TYPE BODY QDynamicImpl
AS
STATIC FUNCTION ODCITableDescribe(
rtype OUT anytype,
p_stmt IN VARCHAR2,
p_invalidate VARCHAR2 := 'Y' )
RETURN NUMBER
IS
atyp anytype;
cur INTEGER;
numcols NUMBER;
desc_tab dbms_sql.desc_tab2;
t_cnt pls_integer := 0;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE( cur, p_stmt, DBMS_SQL.NATIVE );
DBMS_SQL.DESCRIBE_COLUMNS2( cur, numcols, desc_tab );
DBMS_SQL.CLOSE_CURSOR( cur );
--
ANYTYPE.BEGINCREATE( DBMS_TYPES.TYPECODE_OBJECT, atyp );
FOR i IN 1 .. numcols
LOOP
atyp.addattr( desc_tab( i ).col_name ,
CASE desc_tab( i ).col_type
WHEN 1 THEN
DBMS_TYPES.TYPECODE_VARCHAR2
WHEN 2 THEN
DBMS_TYPES.TYPECODE_NUMBER
WHEN 9 THEN
DBMS_TYPES.TYPECODE_VARCHAR2
WHEN 11 THEN
DBMS_TYPES.TYPECODE_VARCHAR2 -- show rowid as varchar2
WHEN 12 THEN
DBMS_TYPES.TYPECODE_DATE
WHEN 208 THEN
DBMS_TYPES.TYPECODE_VARCHAR2 -- show urowid as varchar2
WHEN 96 THEN
DBMS_TYPES.TYPECODE_CHAR
WHEN 180 THEN
DBMS_TYPES.TYPECODE_TIMESTAMP
WHEN 181 THEN
DBMS_TYPES.TYPECODE_TIMESTAMP_TZ
WHEN 231 THEN
DBMS_TYPES.TYPECODE_TIMESTAMP_LTZ
WHEN 182 THEN
DBMS_TYPES.TYPECODE_INTERVAL_YM
WHEN 183 THEN
DBMS_TYPES.TYPECODE_INTERVAL_DS
END , desc_tab( i ).col_precision , desc_tab( i ).col_scale ,
CASE desc_tab( i ).col_type
WHEN 11 THEN
18 -- for rowid col_max_len = 16, and 18 characters are shown
ELSE
desc_tab( i ).col_max_len
END , desc_tab( i ).col_charsetid , desc_tab( i ).col_charsetform );
END LOOP;
atyp.ENDCREATE;
anytype.BEGINCREATE( dbms_types.typecode_table, rtype );
rtype.SETINFO( NULL, NULL, NULL, NULL, NULL, atyp, dbms_types.typecode_object, 0 );
rtype.ENDCREATE();
RETURN odciconst.success;
EXCEPTION
WHEN OTHERS THEN
anytype.begincreate( dbms_types.typecode_object, atyp );
atyp.addattr( 'ERROR' , dbms_types.typecode_varchar2 , NULL , NULL , 4000 , NULL , NULL );
atyp.endcreate;
anytype.begincreate( dbms_types.typecode_table, rtype );
rtype.SetInfo( NULL, NULL, NULL, NULL, NULL, atyp, dbms_types.typecode_object, 0 );
rtype.endcreate();
RETURN odciconst.success;
END;
--
STATIC FUNCTION ODCITablePrepare(
sctx OUT QDynamicImpl,
ti IN SYS.ODCITabFuncInfo,
p_stmt IN VARCHAR2,
p_invalidate VARCHAR2 := 'Y' )
RETURN NUMBER
IS
prec pls_integer;
scale pls_integer;
len pls_integer;
csid pls_integer;
csfrm pls_integer;
elem_typ anytype;
aname VARCHAR2(30);
tc pls_integer;
BEGIN
tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );
--
sctx := QDynamicImpl( elem_typ, p_stmt, UPPER( SUBSTR( p_invalidate, 1, 1 ) ), NULL );
RETURN odciconst.success;
END;
--
STATIC FUNCTION ODCITableStart(
sctx IN OUT QDynamicImpl ,
p_stmt IN VARCHAR2 ,
p_invalidate VARCHAR2 := 'Y' )
RETURN NUMBER
IS
cur INTEGER;
numcols NUMBER;
desc_tab dbms_sql.desc_tab2;
type_code PLS_INTEGER;
prec PLS_INTEGER;
scale PLS_INTEGER;
len PLS_INTEGER;
csid PLS_INTEGER;
csfrm PLS_INTEGER;
schema_name VARCHAR2(30);
type_name VARCHAR2(30);
version VARCHAR2(30);
attr_count PLS_INTEGER;
attr_type ANYTYPE;
attr_name VARCHAR2(100);
rc SYS_REFCURSOR;
BEGIN
cur := dbms_sql.open_cursor;
dbms_sql.parse( cur, p_stmt, dbms_sql.native );
dbms_sql.describe_columns2( cur, numcols, desc_tab );
sctx.cur := cur ;
FOR i IN 1 .. numcols
LOOP
type_code := sctx.ret_type.getattreleminfo( i , prec , scale , len , csid , csfrm , attr_type , attr_name );
CASE type_code
WHEN dbms_types.typecode_char THEN
dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
WHEN dbms_types.typecode_varchar2 THEN
dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
WHEN dbms_types.typecode_number THEN
dbms_sql.define_column( sctx.cur, i, CAST( NULL AS NUMBER ) );
WHEN dbms_types.typecode_date THEN
dbms_sql.define_column( sctx.cur, i, CAST( NULL AS DATE ) );
WHEN dbms_types.typecode_urowid THEN
dbms_sql.define_column( sctx.cur, i, CAST( NULL AS urowid ) );
WHEN dbms_types.typecode_timestamp THEN
dbms_sql.define_column( sctx.cur, i, CAST( NULL AS TIMESTAMP ) );
WHEN dbms_types.typecode_timestamp_tz THEN
dbms_sql.define_column( sctx.cur, i, CAST( NULL AS TIMESTAMP WITH TIME ZONE ) );
WHEN dbms_types.typecode_timestamp_ltz THEN
dbms_sql.define_column( sctx.cur, i, CAST( NULL AS TIMESTAMP WITH LOCAL TIME ZONE ) );
WHEN dbms_types.typecode_interval_ym THEN
dbms_sql.define_column( sctx.cur, i, CAST( NULL AS interval YEAR TO MONTH ) );
WHEN dbms_types.typecode_interval_ds THEN
dbms_sql.define_column( sctx.cur, i, CAST( NULL AS interval DAY TO second ) );
END CASE;
END LOOP;
RETURN odciconst.success;
EXCEPTION
WHEN OTHERS THEN
cur := dbms_sql.open_cursor;
sctx.cur := cur ;
dbms_sql.parse( sctx.cur, 'select :msg from dual union all select :err from dual union all select :stm from dual', dbms_sql.native );
dbms_sql.bind_variable( sctx.cur, 'msg', 'Oops, not a valid query?' );
dbms_sql.bind_variable( sctx.cur, 'err', dbms_utility.format_error_stack );
dbms_sql.bind_variable( sctx.cur, 'stm', p_stmt );
dbms_sql.define_column( sctx.cur, 1, 'x', 32767 );
cur := dbms_sql.execute( sctx.cur );
RETURN odciconst.success;
END;
--
MEMBER FUNCTION ODCITableFetch(
self IN OUT QDynamicImpl,
nrows IN NUMBER,
outset OUT anydataset )
RETURN NUMBER
IS
c1_col_type pls_integer;
type_code pls_integer;
prec pls_integer;
scale pls_integer;
LEN pls_integer;
csid pls_integer;
csfrm pls_integer;
schema_name VARCHAR2(30);
type_name VARCHAR2(30);
version VARCHAR2(30);
attr_count pls_integer;
attr_type anytype;
attr_name VARCHAR2(100);
v1 VARCHAR2(32767);
n1 NUMBER;
f1 FLOAT;
d1 DATE;
ur1 urowid;
ids1 interval DAY TO second;
iym1 interval YEAR TO MONTH;
ts1 TIMESTAMP;
tstz1 TIMESTAMP WITH TIME ZONE;
tsltz1 TIMESTAMP WITH LOCAL TIME ZONE;
BEGIN
outset := NULL;
IF nrows < 1 THEN -- is this possible???
RETURN odciconst.success;
END IF;
--
IF dbms_sql.fetch_rows( self.cur ) = 0 THEN
RETURN odciconst.success;
END IF;
--
type_code := self.ret_type.getinfo( prec , scale , LEN , csid , csfrm , schema_name , type_name , version , attr_count );
anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );
outset.addinstance;
outset.piecewise();
FOR i IN 1 .. attr_count
LOOP
type_code := self.ret_type.getattreleminfo( i , prec , scale , LEN , csid , csfrm , attr_type , attr_name );
CASE type_code
WHEN dbms_types.typecode_char THEN
dbms_sql.column_value( self.cur, i, v1 );
outset.setchar( v1 );
WHEN dbms_types.typecode_varchar2 THEN
dbms_sql.column_value( self.cur, i, v1 );
outset.setvarchar2( v1 );
WHEN dbms_types.typecode_number THEN
dbms_sql.column_value( self.cur, i, n1 );
outset.setnumber( n1 );
WHEN dbms_types.typecode_date THEN
dbms_sql.column_value( self.cur, i, d1 );
outset.setdate( d1 );
WHEN dbms_types.typecode_urowid THEN
dbms_sql.column_value( self.cur, i, ur1 );
outset.seturowid( ur1 );
WHEN dbms_types.typecode_interval_ds THEN
dbms_sql.column_value( self.cur, i, ids1 );
outset.setintervalds( ids1 );
WHEN dbms_types.typecode_interval_ym THEN
dbms_sql.column_value( self.cur, i, iym1 );
outset.setintervalym( iym1 );
WHEN dbms_types.typecode_timestamp THEN
dbms_sql.column_value( self.cur, i, ts1 );
outset.settimestamp( ts1 );
WHEN dbms_types.typecode_timestamp_tz THEN
dbms_sql.column_value( self.cur, i, tstz1 );
outset.settimestamptz( tstz1 );
WHEN dbms_types.typecode_timestamp_ltz THEN
dbms_sql.column_value( self.cur, i, tsltz1 );
outset.settimestampltz( tsltz1 );
END CASE;
END LOOP;
outset.endcreate;
RETURN odciconst.success;
END;
--
MEMBER FUNCTION ODCITableClose(
self IN QDynamicImpl )
RETURN NUMBER
IS
c INTEGER;
t_id NUMBER;
BEGIN
c := self.cur;
dbms_sql.close_cursor( c );
IF self.invalidate = 'Y' THEN
SELECT object_id
INTO t_id
FROM user_objects
WHERE object_name = $$PLSQL_UNIT -- name of your type!
AND object_type = 'TYPE BODY';
-- invalidating of the type body forces that ODCITableDescribe is executed for every call to the function
-- and we do need that to make sure that any new columns are picked up
dbms_utility.invalidate( t_id );
END IF;
RETURN odciconst.success;
END;
END;