nvanwyen / hive-odci

ODCI Hive interface for Oracle databases 12c or higher
http://www.mtihq.com
Other
3 stars 0 forks source link

Need Ad-Hoc binding support for SYS_CONTEXT #4

Open nvanwyen opened 7 years ago

nvanwyen commented 7 years ago

The use of saved/shared bindings is clunky for Ad-Hoc use. They’re fine for static work, but can be more of a burden than a asset when bound to views.

Example ....

-- Append each to a SYS_CONTEXT, grouped by name (session level only)
--
exec hive_binding.env( 'plc_xxx_test_11', '%test1%' );
exec hive_binding.env( 'plc_xxx_test_11', '%test2%' );
exec hive_binding.env( 'plc_xxx_test_11', '%test3%' );

-- Retrieve the bindings, from SYS_CONTEXT, by grouped name
--
select hive_binding.env( 'plc_xxx_test_11' ) binding from dual;

BINDING
--------------------------------------------------------------------------------
BINDS(BIND('%test1%', 9, 1), BIND('%test2%', 9, 1), BIND('%test3%', 9, 1))

-- Apply the same logic to the HIVE_Q ...
--
select * from table( hive_q( 'select id_nbr
                                from plc_xxx 
                              where clob_obj like ?
                                and text_dat like ?
                                and mail_txt like ?
                               limit 10',
                           hive_binding.env( 'plc_pnr_test_11' ),
                           null ) );
nvanwyen commented 7 years ago

This issue is dependent on issue #6 and will be addressed once that issue is resolved