SQL & PL/SQL :: Trigger - Identifying Whether Fired From Oracle
Jun 10, 2010
I have an update trigger that runs a package when fired. I only want this package to run if it is NOT fired from an Oracle Forms application. Ie. A user might update table ABC from an Oracle Form. In this instance the trigger would fire, but I don't want to run the package within the trigger. If the user logs onto PL/SQL developer and updates the table, the trigger would fire and the package must run.
I was thinking of using the field 'program' from v$session. If it is null (it's being fired from an Oracle Forms application) if it has a value (it's coming from SQL*Plus / Pl/sql Developer).
View 2 Replies
ADVERTISEMENT
Sep 4, 2013
There is a Table, on that table there are 2 triggers are written 1) row level trigger 2) statement level trigger. Now i am inserting one row into that table, so which trigger will be fired first and why?
View 3 Replies
View Related
Jul 1, 2011
In my DB,We have a table rqst_list
Create table rqst_list(id Number, --(PK)
pkg_name varchar2(100),
status varchar2(100))
When a record is inserted into this table(rqst_list), a trigger is fired which calls the package which is there in pkg_name.The function does some function.If there are no exceptions while executing the package, then SUCCESS needs to be updated in the table for the id for which the trigger got fired.I have used AFTER INSERT ROW level trigger.
But,the final UPDATE (UPDATE the rqst_list table to SUCCES/FAILURE)to the base table inside the package is not happening.
View 5 Replies
View Related
Mar 14, 2013
On a form I have 2 blocks, shown on different tabs. Relation created by forms wizard. The relation works great when navigating through the blocks, the details for the master are shown fine.
Example for this form is master block is a type of food. Detail block is list of ingredients.When creating a new type of food, if it's = CAKE we insert records based on a select on another table into the ingredients table.Else, the user has to manually create the ingredients.
Upon commit of the form on master block (must be saved to enable detail block tab), the ingredients are not displayed but are there. If I execute query by hitting F8, the records are present.
Why is the populate-details trigger not being fired or what am I missing?
View 4 Replies
View Related
Nov 21, 2012
i have a problem when i try to login to oracle...i found this message error.
ora-01033 oracle initialization or shutdown in progressafter i logged on with / as sysdba...i did the following
SQL> shutdown immediate;
ORA-01507: database not mountedORACLE instance shut down.
SQL> startup mount;
[code]....
View 6 Replies
View Related
Oct 22, 2011
I want to insert data from table A and dump into table B.The data in Table A looks like this:
Level_IDCode Type
ETY_1A......... 100000116
ETY_1B......... 100000116
ETY_1C......... 100000116
ETY_2AA........ 100000117
ETY_2AB........ 100000117
ETY_2AC........ 100000117
ETY_2BA........ 100000117
ETY_2BB........ 100000117
ETY_2BC........ 100000117
ETY_3AAA....... 100000118
ETY_3AAG....... 100000118
ETY_3AAI....... 100000118
ETY_3AAX....... 100000118
[code]....
And I want to dump the data into Table B which will look like this:
Level_ID Code Type Parent_cd
ETY_1A.........100000116null
ETY_1B.........100000116null
ETY_1C.........100000116null
ETY_2AA........100000117A
ETY_2AB........100000117A
ETY_2AC........100000117A
ETY_2BA........100000117B
ETY_2BB........100000117B
ETY_2BC........100000117B
ETY_3AAA.......100000118AA
ETY_3AAG.......100000118AA
ETY_3AAI.......100000118AA
[code]....
I need to populate a column called Parent Cd with the values that sits at the base level of the hierarchy. So, if the code A sits at the base level then its parent is NULL and the parent for code AA, AB, and AC will be A and parent for AAA, AAG will be AA and so on.The level_IDs are upto 10 and there are close to 400,000 records.
View 5 Replies
View Related
Apr 11, 2011
I would like to know, how to:
1) search for a given value in a row, and
2)in that row, identify the name of the column that contains the value
Search scope is only a single row.
View 7 Replies
View Related
May 6, 2013
I have a situation where I need to check if a row in the table locked i.e (Select for Update with NO WAIT applied on it). If yes go fetch the next row .
View 4 Replies
View Related
Oct 4, 2012
Oracle 11gR2
I have on table 'Test' in database and I want to know currently which sql statements are being fired on table 'Test' ?
View 8 Replies
View Related
Sep 5, 2013
I am new to Oracle and I inherited an existing database. I have a materialized view on a table that uses a materialized view log. I have been asked to see if it is possible to categories the rows by change type (inserted, updated or deleted) and populate another materialized view or table accordingly. If this is possible then how can it be achieved.
View 1 Replies
View Related
May 30, 2013
I frequently need to run queries to do things like sum space used by each schema, but excluding all of the default system users. Is there a better way to identify the default users than listing all 30 of them in a "not in" expression? For example in sql server to query user databases you just say "with database_ id > 4". Is there something simple like that in Oracle to identify all default users?
View 6 Replies
View Related
Aug 17, 2010
I am trying one simple operation on Oracle9i DB. I am exporting a table data and trying to import it back. The triggers associated with the insert operation for this table are not being fired during import. Here is the commandline i am using to achieve the same.
1) Exporting the table data.
$ORACLE_HOME/bin/exp SMS_60/SMS_60 file=callfwd.dmp tables=callFwd_customerList TRIGGERS=Y
2) Importing the same data.
$ORACLE_HOME/bin/imp SMS_60/SMS_60 file=callfwd.dmp tables=callFwd_customerList ignore=Y
Import ends with success with all the rows imported in the table but the triggers associated to this "insert" operation.
View 12 Replies
View Related
Oct 25, 2010
I have 10 users in my database, I need to find the time at which a query is fired by any of the users in the database. provide the query for this? I think i have to use V$sql,v$sqlarea or v$sql.
View 1 Replies
View Related
Dec 21, 2011
I need to get a way to find, The client fired the SQL Query is still connected to the database or not. For e.g.
I fire an SQL query using SQL Developer, Query approximately takes 5 mins to complete. As soon as I fire the query SQL devloper hangs and I close it using task manager.
Is there any way I can identify such session where SQL developer fired the query is terminated but the query fired is still running.
I have to identify such Orphan session and terminate it.
View 3 Replies
View Related
Sep 5, 2013
I inherited an existing database. I have a materialised view on a table that uses a materialised view log. I have been asked to see if it is possible to categorise the rows by change type (inserted, updated or deleted) and populate another materialised view or table accordingly. If this is possible then how can it be achieved.
View 12 Replies
View Related
Mar 30, 2011
I'm sure you are all familiar with proxy users, they've been around since 9i: orcl> create user low identified by low;
User created.
orcl> create user high identified by high;
User created.
orcl> grant dba to high;
Grant succeeded.
orcl> alter user high grant connect through low;
User altered.
orcl> connect low[high]/low
Connected.
orcl> sho user
USER is "HIGH"
orcl>
Is there any way that I can find out which of the current sessions was proxied, and through what user? I know that from within the session I can query my userenv context and find out, but I can't see how to do it otherwise. It must be possible: the audit trail records both the real user and the proxy user.
View 3 Replies
View Related
Sep 14, 2010
I need to increase the size of shared pool for that i need to change the size of parameter sga_target and sga_max_size these are static parameter so that I need to change in spfile first then start instance then these parameter will get change. But when I create pfile from spfile it gives error:
ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
View 9 Replies
View Related
Nov 8, 2005
i am writing a code on form "CANCEL" button which mean to cancel the current user action .now if the last action was execute_query so when user click the cancel button so my logic should capture the last query fired on form through ":SYSTEM.LAST_QUERY" and then execute the last query through EXECUTE_QUERY but my problem is that how i execute my last_query through execute_query.
View 21 Replies
View Related
Oct 24, 2013
I am on Oracle 11.2.0.3 on Linux. In my production database, I am getting this alert, in the alert log:
--the below two lines are from alert log.
Error 604
trapped in 2PC on transaction 50.73.546578. Cleaning up.
--query from a data dictionary view
SQL> select count(*) from DBA_2PC_PENDING;
COUNT(*)
----------
1
When I query the production database I see that there is one row in the DBA_2PC_PENDING view. But I dont' know how to identify which are the databases that are involved in this distributed transaction. That is my first issue - how to identify which are the two databases that are involved in the distributed transaction?
View 10 Replies
View Related
Jul 23, 2012
We having OSB 10.4 on Solaris 10 with SL 24 TL Auto loader ( Tapes doesn't having Bar-code ), while firing file system backup (Sol 10 is the client OS) , it is hanging . and i have logged in to TL console then it shows loading and unloading. while OSB catalog backup is getting fired on the associated media family.
View 2 Replies
View Related
Aug 3, 2013
I am using commit in a trigger as given :
create or replace
trigger comt after insert on tbl_city
declare
pragma autonomous_transaction;
begin
commit;
dbms_output.put_line('Value is committed');
end;
Now when I perform an insert in tbl_city---->trigger fires properly and gives output stream. But If I perform rollback now --->there are the data rollbacked in table.
why?I think after commit(which is in trigger associated at insert to table)there should no any rollback in table.
View 17 Replies
View Related
Apr 12, 2011
I am using SQL*Plus to run this Oracle10g. Here is my problem I have to solve: After a new record is added to the task table, write a trigger that reports the new project balance to the user while updating the customer balance to reflect this additional task cost. Insert a new record to show the trigger works. I somewhat understand triggers,I think I have to combine tables to solve this. Here are my tables/attributes I can use to solve it:
CREATE TABLE customer
(
custnoNUMBER(3)CONSTRAINT ccustpk NOT NULL PRIMARY KEY,
custname CHAR(20) CONSTRAINT ccustnamenn NOT NULL,
phone CHAR(12),
curr_balance NUMBER(9,2) CONSTRAINT ccustbalancenn NOT NULL
[code]...
View 8 Replies
View Related
Nov 29, 2007
I am working with an oracle table that is populated by a trigger on another table.. So Table A is an audit of table B. The trigger also uses sysdate to populate a modification Date column on the Audit table.
I was using this modification Date column in a query interface to get changes that happened on the main table after a certain date/time.
The problem is that there is an application that uses transactions to write to table B and sometime this transaction may not be committed for over a minute so the modification Date is not a reliable way to query the table for changes after a certain time.
Is there a way to update the trigger/create a new one where the sysdate that gets written to the audit table is from when the transaction is committed, not when the transaction starts?
View 2 Replies
View Related
Mar 30, 2010
I am trying to create some PL/SQL that will create a trigger for UPDATES and INSERTS which will update a column on the same row to the system date.
So far I have:
DECLARE
tablename VARCHAR(30) := 'table';
triggername VARCHAR(30) := 'mytrigger_' || tablename;
[Code]....
I have declared the tablename and triggername outside the trigger creation as I need this to be parameterised for a list of tables.
The procedure IsCDCUser just checks it should be updating for this user.
The problem I get is the following error:
Bind Variable "NEW" is NOT DECLARED
View 3 Replies
View Related
Jun 12, 2012
I am writing a after trigger for one of my tables on every insert update and delete for my dataware house staging area. The process here is when ever there is a change in the production database we need to capture this change in our changing area through triggers.
I am able to create the triggers but i am stuck with the exception handling portion of the trigger. I want to write an exception in the trigger where when the staging area is locked or for any other matter the data needs to be able to go to a error table when the staging area is not able to accept the data for some reason.
how i can write this excepyion in the trigger or anyother method i can follow to be able to handle this scenerio.
View 2 Replies
View Related
Oct 30, 2006
I must create an INSERT trigger, on an Oracle table, which will do an insert into my MS-SQL 2000 DB table.
The tables are exactly the same in this case and I desire to insert the entire row that was just insterted into the Oracle table into the MS-SQL table.
I understand how to create an ODBC connection between the DB servers, I just can't seem to understand the trigger syntax.
View 2 Replies
View Related
Jul 1, 2011
How can "call one trigger of item in trigger of form"
View 5 Replies
View Related
Nov 11, 2009
I need to fire the KEY-DUPREC trigger as I click on the key combination Shift+P therefore; I added the following line of code in the frmweb.res file.
80 : 1 : "Shift+P" : 64 : "Duplicate Record"
It worked iff no error was raised. So, if I have a raise form trigger failure in the KEY-DUPREC trigger, a capital P will appear in the text field that called the trigger.
View 2 Replies
View Related
Oct 31, 2011
I would like to create a trigger that will fire whenever any user will access to a Schema of Oracle DB (for each and every login). regardless the access will be through an application or SQLPLUS and this trigger must insert the below information into a table.
1) IP address
2) Machine Name
3) login time
4) logout time
5) name of accessed schema
writing this trigger and creating the table that will hold the required data.
View 6 Replies
View Related
Jan 4, 2013
how can i write a trigger to insert the system clock time in the oracle form?
View 15 Replies
View Related
