Thanks for the question, marc.

Asked: May 29, 2008 - 10:49 am UTC

Last updated: May 30, 2008 - 4:14 pm UTC

Version: 10.2

Viewed 1000+ times

You Asked

I have 2 instances of the same application(same code, different data) running 2 different schemas, on the same db instance. I would like to create a function in a package to return the app name I am currently in. I could use a table with a flag, but I do not want to take i/o hit. I am thinking of using the compiler directive in a package to tell me which app i am on. I was thinking of user the schema name as the $$if expression in the package. How do I use the schema name in a compiler directive.

CREATE OR REPLACE package which_app
RETURN STRING AS
BEGIN
$IF schema in ('APP-A','APP-A-QA1','APP-A-QA2','APP-A-DEV1') $THEN
app CONSTANT VARCHAR (10) := 'APP-A';
$ELSE
app CONSTANT VARCHAR (10) := 'APP-B';
$END
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

usage:
BEGIN
IF which_app.app = 'APP-A' THEN
dbms_out.put_line ('app-a');
END IF;
END;

is this possible?

and Tom said...

ops$tkyte%ORA10GR2> create or replace function who_am_i return varchar2
  2  as
  3  begin
  4          return sys_context( 'userenv', 'current_schema' );
  5  end;
  6  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant execute on who_am_i to scott;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2>
scott%ORA10GR2> select sys_context( 'userenv', 'current_schema' ), ops$tkyte.who_am_i
  2    from dual
  3  /

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
-------------------------------------------------------------------------------
WHO_AM_I
-------------------------------------------------------------------------------
SCOTT
OPS$TKYTE

sys_context( 'userenv', 'current_schema' ) will return the current schema name, the executing schema name - which in a definers rights routine will be the owner of the routine....

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

marc weinstock, May 30, 2008 - 11:24 am UTC

I was trying to use a package constant for speed, which is faster using a constant.  But the difference is so small it does not matter.

SQL> 
SQL> 
SQL> BEGIN
  2     FOR x IN 1 .. 100000 LOOP
  3        IF SYS_CONTEXT ('userenv', 'current_schema') = 'DEV' THEN
  4           NULL;
  5        END IF;
  6     END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.87
SQL> 
SQL> BEGIN
  2     FOR x IN 1 .. 100000 LOOP
  3        IF p_properties.dealing_bank = 'A' THEN
  4           NULL;
  5        END IF;
  6     END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

thanks

May 30, 2008 - 3:11 pm UTC

well....


ops$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3          dealing_bank constant varchar2(30) := sys_context( 'userenv', 'current_schema' );
  4  end;
  5  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant execute on my_pkg to scott;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2>
scott%ORA10GR2> exec dbms_output.put_line( ops$tkyte.my_pkg.dealing_bank );
OPS$TKYTE

PL/SQL procedure successfully completed.

best of both...

Many schema's

marc weinstock, May 30, 2008 - 3:37 pm UTC

My issue with the "my_pkg" above was in development and in qa. There could be many schemas, but only 2 applications. APP_A and APP_B. In qa schema names might be: qa_app_a_1, qa_app_a_2, qa_app_a_3 and qa_app_b_1, qa_app_a_b, qa_app_b_3 where there are 3 environments for app_a and 3 for app_b. My original approach had an "IN" clause for each app.

SYS_CONTEXT ('userenv', 'current_schema')
 in ('APP-A','APP-A-QA1','APP-A-QA2','APP-A-DEV1')

I do not see a way to do this inside of the db with out on the fly processing.

This will not work:

CREATE OR REPLACE package which_app
$IF SYS_CONTEXT ('userenv', 'current_schema')
     in ('APP-A','APP-A-QA1','APP-A-QA2','APP-A-DEV1') $THEN
    app CONSTANT VARCHAR (10) := 'APP-A';
$ELSE
    app CONSTANT VARCHAR (10) := 'APP-B';
$END
END;

Currently all my database builds are shell scripts so my workaround is to have the shell script to parse the package and compile it on each install with a hard coded app constant.

CREATE OR REPLACE package which_app
    app CONSTANT VARCHAR (10) := 'APP-A';
END;

BTW, 99.99% of the code for the 2 apps is the same, I have 1 formula that is different. But gets hit 1000s of times a day.

May 30, 2008 - 4:14 pm UTC


ops$tkyte%ORA10GR2> create or replace function get_name return varchar2
  2  is
  3  begin
  4          IF SYS_CONTEXT ('userenv', 'current_schema')
  5               in ('APP-A','APP-A-QA1','APP-A-QA2','APP-A-DEV1')
  6          THEN
  7                  return 'APP-A';
  8          ELSE
  9                  return 'APP-B';
 10          END if;
 11  end;
 12  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3          the_name constant varchar2(30) := get_name;
  4  end;
  5  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_output.put_line( my_pkg.the_name );
APP-B

PL/SQL procedure successfully completed.

Nice Trick

marc weinstock, May 30, 2008 - 5:03 pm UTC

I would not have thought of using a function against a constant. I guess it gets assigned at time of of the first call for the rest of the session and not at compile time. (not that it matters for my issue, since the schema name will never change)

thanks.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Many schema's

Nice Trick

More to Explore

PL/SQL demos

PL/SQL docs

compiler directive using schema name.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Many schema's

Nice Trick

More to Explore

PL/SQL demos

PL/SQL docs