usage of calculated index with a synonym for the function

[Norbert Winkler]

Hi,

in a large table OUR_TABLE we have a calculated index for a persons-name
with a simple soundex function OUR_SOUNDEX in the same schema (OUR_SCHEMA)
like the table, thats different to my "select"-schema (WORK_SCHEMA):
...
from OUR_SCHEMA.OUR_TABLE d
where OUR_SCHEMA.OUR_SOUNDEX(d.PERSON_NAME) =
OUR_SCHEMA.OUR_SOUNDEX('Meier, Hans');

But for building a testing environment schema-names are different
(OUR_SCHEMA --> TEST_SCHEMA, WORK_SCHEMA --> WORK_TEST)

Now I'm trying to create universal scripts with synonyms:

in WORK_SCHEMA
CREATE OR REPLACE SYNONYM OUR_SCHEMA_OUR_SOUNDEX
FOR OUR_SCHEMA.OUR_SOUNDEX;

in WORK_TEST
CREATE OR REPLACE SYNONYM OUR_SCHEMA_OUR_SOUNDEX
FOR TEST_SCHEMA.OUR_SOUNDEX;

But
..
from OUR_SCHEMA_OUR_TABLE d
where OUR_SCHEMA_OUR_SOUNDEX(d.PERSON_NAME) =
OUR_SCHEMA_OUR_SOUNDEX('Meier, Hans');

uses a full table scan in WORK_SCHEMA and TEST_SCHEMA.

Is there another way to create universal scripts.

--
Norbert
Oracle9i Enterprise Edition Release 11.2 64Bit

[Lisa]

Wooooooooow nobody answered you!
You could use this SQL to find out what schema you're in:
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

(that'll tell if youre in work_schema or test_schema)

Then you can do an execute immediate and create the index.
Here's the code:
declare
v_name varchar2(100);
v_sql varchar2(200);
begin
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') into v_name FROM dual;
v_sql := 'CREATE OR REPLACE SYNONYM OUR_SCHEMA_OUR_SOUNDEX FOR ' ||
v_name || '.OUR_SOUNDEX;' ;
dbms_output.put_line (v_sql);
execute immediate v_sql;
end;

Hope that helps.