Fetch and insert Database DBNAME in the query ?

[winona]

Hi,

Is there any system variable which returns the current database we are in?

This is my below query and works fine. As of now it inserts a blank in “Dbname” column.

I have to insert the current dbname in that

INSERT INTO SYSADM.PS_FZAP_AUDIT_SOX (oprid, oprdefndesc,lastsignondttm,
fzap_reason_cd,descr254,dbname,lastupdoprid,lastupddttm)
select distinct :new.oprid,:new.oprdefndesc,:new.lastsignondttm,’ ‘,’ ‘,’ ‘,’ ',sysdate
from sysadm.psroleuser b
where b.roleuser = :new.oprid
and (b.rolename = ‘FAS Change Admin’ or b.rolename=‘PeopleSoft Administrator’);

I know this will return

select value from v$parameter
where name = ‘db_name’ , but not sure how to include in the query. When i inserted it popped an error

[JohnDorlon]

This should do it: SYS_CONTEXT (‘USERENV’, ‘DB_NAME’)

SYS_CONTEXT is worth knowing. It’s great for stuff like this.

From: winona [mailto:bounce-winona@toadworld.com]

Sent: Monday, January 20, 2014 4:50 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Fetch and insert Database DBNAME in the query ?

Fetch and insert Database DBNAME in the query ?

Thread created by winona

Hi,

Is there any system variable which returns the current database we are in?

This is my below query and works fine. As of now it inserts a blank in “Dbname” column.

I have to insert the current dbname in that

INSERT INTO SYSADM.PS_FZAP_AUDIT_SOX (oprid, oprdefndesc,lastsignondttm,

                                  fzap_reason_cd,descr254,dbname,lastupdoprid,lastupddttm)                                                                                                              

select distinct :new.oprid,:new.oprdefndesc,:new.lastsignondttm,’ ‘,’ ‘,’ ‘,’ ',sysdate

from sysadm.psroleuser b

where b.roleuser = :new.oprid

and (b.rolename = ‘FAS Change Admin’ or b.rolename=‘PeopleSoft Administrator’);

I know this will return

select value from v$parameter

where name = ‘db_name’ , but not sure how to include in the query. When i inserted it popped an error

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

[winona]

Thanks John, i gave like below

INSERT INTO SYSADM.PS_FZAP_AUDIT_SOX (oprid, oprdefndesc,lastsignondttm,

fzap_reason_cd,descr254,dbname,lastupdoprid,lastupddttm)

values (‘0071030’,‘taylor’,‘20-jan-2014 4:39:40 675432’,’ ‘,’ ‘,SYS_CONTEXT (‘USERENV’, ‘DB_NAME’),’ ',sysdate);

I got the below error

ORA-01855: AM/A.M. or PM/P.M. required

[winona]

INSERT INTO SYSADM.PS_FZAP_AUDIT_SOX (oprid, oprdefndesc,lastsignondttm,

fzap_reason_cd,descr254,dbname,lastupdoprid,lastupddttm)

values (‘0071030’,

‘tylor’,

‘20-jan-2014 4:39:40 675432’,

’ ',

’ ',

SYS_CONTEXT (‘USERENV’, ‘DB_NAME’),

’ ',

sysdate);

I got the below error

ORA-01855: AM/A.M. or PM/P.M. required

[Amino1]

There should be hour format specifyed: to_date(‘20-jan-2014 4:39:40’, ‘dd-mon-yyyy hh24:mi:ss’)

[winona]

Hi,

I get this error only after inserting SYS_CONTEXT (‘USERENV’, ‘DB_NAME’ for dbname column not for date

[JohnDorlon]

I say this with a smiley face, but I am serious. Learn this now. You should never, ever, ever put a date in a SQL statement without the to_date function. It might work right on your computer and/or database, and not work at all on another one. Never, ever, ever.

I don’t know how sys_context would cause this error, but the error will go away if you use to_date.

[winona]

thanks john. Since this query worked without “to_date” i did not worry much. But i just executed this statement in my toad SELECT sys_context(‘USERENV’, ‘ACTION’) FROM DUAL; and was surprised that it did not return any statement. rather

select value from v$parameter

where name = ‘db_name’

works.

Now again i am having issues in inserting

[JohnDorlon]

Read the manual.

This one (action) returns a value that you previously set with dbms_application_info.set_action. If you never set anything with dbms_application.set_action,
then you won’t get anything back with sys_context(‘USERENV’,
‘ACTION’)

From: winona [mailto:bounce-winona@toadworld.com]

Sent: Tuesday, January 21, 2014 8:41 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Fetch and insert Database DBNAME in the query ?

RE: Fetch and insert Database DBNAME in the query ?

Reply by winona

thanks john. Since this query worked without “to_date” i did not worry much. But i just executed this statement in my toad SELECT sys_context(‘USERENV’, ‘ACTION’) FROM DUAL; and was surprised
that it did not return any statement. rather

select value from v$parameter

where name = ‘db_name’

works.

Now again i am having issues in inserting

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

[JohnDorlon]

oops, I just noticed that your date value is really a timestamp. So you would use to_timestamp instead. And your insert statement would be:

INSERT INTO SYSADM.PS_FZAP_AUDIT_SOX (oprid, oprdefndesc,lastsignondttm,

fzap_reason_cd,descr254,dbname,lastupdoprid,lastupddttm)

values (‘0071030’,

‘tylor’,

TO_TIMESTAMP(‘20-jan-2014 4:39:40 675432’, ‘DD-mon-yyyy hh24:mi:ss FF’)

’ ',

’ ',

SYS_CONTEXT (‘USERENV’, ‘DB_NAME’),

’ ',

sysdate);

[winona]

John,

I haven’t told my full issue here. Actually i am using this in my trigger statement like below.

CREATE OR REPLACE TRIGGER SYSADM.FZAP_AUDIT_TRIGGER

AFTER UPDATE OF lastsignondttm

ON SYSADM.PSOPRDEFN

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

DECLARE

Mutating EXCEPTION;

PRAGMA EXCEPTION_INIT (Mutating, -4091);

errorm VARCHAR2 (2000);

BEGIN

INSERT INTO SYSADM.PS_FZAP_AUDIT_SOX (oprid,

oprdefndesc,

lastsignondttm,

fzap_reason_cd,

descr254,

dbname,

lastupdoprid,

lastupddttm)

SELECT DISTINCT :new.oprid,

:new.oprdefndesc,

:new.lastsignondttm,

’ ',

’ ',

(SELECT VALUE

FROM v$parameter

WHERE name = ‘db_name’),

’ ',

SYSDATE

FROM sysadm.psroleuser b

WHERE b.roleuser = :new.oprid

AND ( b.rolename = ‘FAS Change Admin’

OR b.rolename = ‘PeopleSoft Administrator’);

EXCEPTION

WHEN Mutating

THEN

errorm := SQLERRM;

– DBMS_OUTPUT.PUT_LINE(‘prompt 13’);

–INSERT_LOG('Error at Level ‘||LOG_LEVEL||’ with error message '||errorm);

WHEN NO_DATA_FOUND

THEN

NULL;

– DBMS_OUTPUT.PUT_LINE(‘prompt 14’);

END;

[winona]

So when i use this statement (SELECT VALUE

FROM v$parameter WHERE name = ‘db_name’),

I recently added this now, before this trigger was working perfectly fine. I am getting error.

I just discussed with my DBA and v$parameter can be used and we have privilege to that.

So when i login to my dev system i get this error.

Return: 4098 - ORA-04098: trigger ‘SYSADM.FZAP_AUDIT_TRIGGER’ is invalid and failed re-validation

Failed SQL stmt:UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_TIMESTAMP(:1,‘YYYY-MM-DD-HH24.MI.SS.FF’) WHERE OPRID = :2