Programming Forums - PL/SQL

Programming Forums (http://www.programmingforums.org/forumindex.php)

- Other Programming Languages (http://www.programmingforums.org/forum38.html)

- - PL/SQL - get DB name (http://www.programmingforums.org/showthread.php?t=10079)

Gumby

May 30th, 2006 11:34 AM

PL/SQL - get DB name

Hey,

I want to get the database name in a PL/SQL trigger.

At the SQLPlus prompt, these are some examples I've tried:

fsdevl75@fsdev> select sys_context('userenv', 'db_name') from dual;



SYS_CONTEXT('USERENV','DB_NAME

---------------------------------------------------------------------------

13



fsdevl75@fsdev> select '&_CONNECT_IDENTIFIER' from dual;

old   1: select '&_CONNECT_IDENTIFIER' from dual

new   1: select 'fsdev' from dual



'FSDEV'

--------------------------------

fsdev



fsdevl75@fsdev> desc v$database;

ERROR:

ORA-04043: object "SYS"."V_$DATABASE" does not exist

I want what _CONNECT_IDENTIFIER returns (fsdev in this case), but I can't figure out any way to do this in a trigger. Can anybody help with this?

Pizentios

May 30th, 2006 3:00 PM

Quote:

Originally Posted by Gumby

Hey,

I want to get the database name in a PL/SQL trigger.

At the SQLPlus prompt, these are some examples I've tried:

fsdevl75@fsdev> select sys_context('userenv', 'db_name') from dual;



SYS_CONTEXT('USERENV','DB_NAME

---------------------------------------------------------------------------

13



fsdevl75@fsdev> select '&_CONNECT_IDENTIFIER' from dual;

old   1: select '&_CONNECT_IDENTIFIER' from dual

new   1: select 'fsdev' from dual



'FSDEV'

--------------------------------

fsdev



fsdevl75@fsdev> desc v$database;

ERROR:

ORA-04043: object "SYS"."V_$DATABASE" does not exist

I want what _CONNECT_IDENTIFIER returns (fsdev in this case), but I can't figure out any way to do this in a trigger. Can anybody help with this?

Hey,

I would probably see if i could do a SELECT INTO into a var, then return the var (or do what ever you want with the results).

here's a example on SELECT INTO: http://www.postgresql.org/docs/8.1/i...QL-SELECT-INTO

Gumby

May 30th, 2006 4:50 PM

Quote:

Originally Posted by Pizentios

Nope, won't work. If I do:

select '&_CONNECT_IDENTIFIER' into myVar from dual;

Then myVar will just hold the string, '&_CONNECT_IDENTIFIER'. It doesn't replace it with the actual value like it does from the SQLPlus prompt. As far as I can tell, _CONNECT_IDENTIFIER can't be used in a trigger/stored procedure.

On a side note, I can also do this:

fsdevl75@fsdev> select * from PS.PSDBOWNER where ownerid = user;



DBNAME     OWNERID    S

---------- ---------- -

FSDEV      FSDEVL75   U

FSDEVL75   FSDEVL75   U



fsdevl75@fsdev>

But it returns 2 rows in this case, and only one of them is the correct DBName (PS.PSDBOWNER is apparently a PeopleSoft specific table).

Pizentios

May 31st, 2006 10:11 AM

give this a try:

SELECT GLOBAL_NAME FROM GLOBAL_NAME;

i have seen that on a few pages i have been reading about your subject. Here's the link to the page: http://www.bijoos.com/oracle/ora_qa.htm

I have never used SQLPlus, so i am kinda in the dark on what works on your system.

jim mcnamara

May 31st, 2006 5:45 PM

SET SERVEROUT ON SIZE 10000

DECLARE

    DBNAME  VARCHAR2(32):=null;

BEGIN

    SELECT name

    INTO DBNAME

    FROM v$database;

    DBMS_OUTPUT.ENABLE;

    DBMS_OUTPUT.PUT_LINE('DATABASE NAME = ' || DBNAME);

END;

/

This is the basic code to get the db name - it displays it. For a trigger you would not want a display.

Gumby

Jun 1st, 2006 8:53 AM

Quote:

Originally Posted by jim mcnamara

SET SERVEROUT ON SIZE 10000

DECLARE

    DBNAME  VARCHAR2(32):=null;

BEGIN

    SELECT name

    INTO DBNAME

    FROM v$database;

    DBMS_OUTPUT.ENABLE;

    DBMS_OUTPUT.PUT_LINE('DATABASE NAME = ' || DBNAME);

END;

/

This is the basic code to get the db name - it displays it. For a trigger you would not want a display.

Yes, that would make life much easier, but unfortunately, I don't have access to v$database. From what I've heard when similar problems to this cropped up in the past, they're not going to grant access to it either. Beuacracy is fun!

Gumby

Jun 1st, 2006 10:50 AM

Well, thank you all for your help - I'm just going to do a workaround. I made a new table that mapped the server names to the database names, then wrote a function that selects global_name from global_name (which gives me the server name), strips the .world from the end of it, and then uses that to find the database name.

Pizentios

Jun 1st, 2006 11:08 AM

Quote:

Originally Posted by Gumby

Glad to hear you figured it out.

All times are GMT -5. The time now is 7:51 AM.