Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Converting Access Databases to APEX

4187794Feb 19 2020 — edited Feb 19 2020

Hello all,

We are using 19c currently.  I have come across an 11g guide:

https://docs.oracle.com/cd/E11882_01/appdev.112/e12509/appmgr.htm#AEMIG104

Is there a more up to date guide out there somewhere? I cannot even find the referenced exporter tool at this point - and the only migration references I see in APEX sticks to converting Oracle Forms, no reference of other db technologies.

Appreciate any assistance,
This post has been answered by bkmcdaniel on Feb 19 2020
Jump to Answer

Comments

Jonathan Lewis

You could use hashing (e.g. dbms_crypto.hash()) to create a hash value for the lob column - either as a function-based index or as a virtual column that you index, then change your code to have two predicate involving your input bind variable, something like:

where
        dbms_crypto.hash(blob column) = dbms_crypt.hash({your value})
and     dbms_Lob.compare(blob column, {your value} = 0

realistically you probably won't have any hash collisions, technically you might which is why you would use the indexed hash value as the first check then the actual value.
You may have to mess around a bit to work out the best way of setting this up (especially in 11.2). And you'll probably need to use substrb() on the hash value before you can index it.
See https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_crypto.htm
I don't think there are any licence implications of using dbms_crypto, but you'd need to check that.
Gathering stats on the table will become expensive if you do this (see https://jonathanlewis.wordpress.com/2014/03/02/auto-sample-size )

Regards
Jonathan Lewis

Sebino

Virtual columns do not seem to be null-friendly, or am I mistaken ?
Because this blobColumn may be null (and quite often is), which make index creation fail with:

ORA-01405: fetched column value is NULL

Well - actually I must find a way to sneak around this error that is returned by the call to DBMS_CRYPTO.HASH (nothing to do with virtual column)...
Sebino

Jonathan Lewis
Answer

If dbms_crypto.hash doesn't accept a NULL input than use a CASE expression to generate the index, something like the following (1 is the value for the symbolic constant HASH_MD4):

create index t1_b1 on t1(case when b1 is not null then substrb(dbms_crypto.hash(b1,1),1,16) end)

The same messy expression will have to be used in your first predicate.
Or:

alter table t1 add hash_md4 generated always as (
        case when b1 is not null then substrb(dbms_crypto.hash(b1,1),1,16) end
) virtual
;

create index t1_b1 on t1(hash_md4);

Regards
Jonathan Lewis

Marked as Answer by Sebino · Jul 5 2021
1 - 3

Post Details

Added on Feb 19 2020
#access, #apex-discussions
5 comments
884 views