Programming Forums > Application Development > Other Programming Languages

Gumby · May 30th, 2006, 11:34 AM

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:

(Toggle Plain Text)

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

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

Nope, won't work. If I do:

(Toggle Plain Text)

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:

(Toggle Plain Text)

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:

(Toggle Plain Text)

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

(Toggle Plain Text)

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

(Toggle Plain Text)

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

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.

Glad to hear you figured it out.

May 30th, 2006, 11:34 AM	#1
Gumby Newbie Join Date: Mar 2006 Location: Andover, MA / Rochester, NY / Nanticoke, PA Posts: 16 Rep Power: 0	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: (Toggle Plain Text) 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 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?

May 31st, 2006, 10:11 AM	#4
Pizentios Programming Guru Join Date: May 2004 Location: Brandon, Manitoba, Canada Posts: 2,023 Rep Power: 7	give this a try: (Toggle Plain Text) SELECT GLOBAL_NAME FROM GLOBAL_NAME; 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. __________________ Profanity is the one language that all programmers understand. Check out my Blog <---updated Nov 30 2007!

May 31st, 2006, 5:45 PM	#5
jim mcnamara Hobbyist Programmer Join Date: Jun 2005 Location: New Mexico Posts: 228 Rep Power: 4	(Toggle Plain Text) 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; / 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Jun 1st, 2006, 10:50 AM	#7
Gumby Newbie Join Date: Mar 2006 Location: Andover, MA / Rochester, NY / Nanticoke, PA Posts: 16 Rep Power: 0	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.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Application Development > Other Programming Languages

PL/SQL - get DB name