User 1791
I want to test a [function-based **spatial** index]( using db<>fiddle for Oracle 18c.

In order to create the index, I need to create a record in Oracle Spatial's `USER_SDO_GEOM_METADATA` table (tehcnically, it's a view). 

The metadata record needs to include the function's schema/owner name: `my_owner.my_function(...)` —or— `mdsys.sdo_geometry(...)`, etc..

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
 'my_owner.my_function(shape)', -- 🡄 look here.
 sdo_dim_array (
  sdo_dim_element('X', 567471.222,  575329.362, 0.5),
  sdo_dim_element('Y', 4757654.961, 4769799.360, 0.5)

In db<>fiddle, the owner/schema name seems to be **temporary**/system-generated:

    select sys_context( 'userenv', 'current_schema' ) from dual


So, the schema name changes every time hit the run button. If that's the case, then for **custom** functions, perhaps it doesn't make sense to enter the temporary schema name — since the schema name is always changing.


Does that make it impossible to test the function-based spatial index in db<>fiddle that uses a custom function? Or have I misunderstood?
Top Answer
Jack Douglas
You can just use `sys_context` to get the schema name dynamically:


Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.