Determining What Procedures / Triggers / Functions Update A Table
Jul 2, 2010
I'm trying to determine which procedures (in or out of packages), triggers or functions insert to or update particular tables in Oracle 10.2.0.4.I've got a far as looking at user_dependencies, and have written the following sql based on that:
CODEselect o.name, decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE',
8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY',
[code]....
This isn't how I'd write the code for production, it's just research code at the moment.
This tells me most of what I need to know, but it doesn't allow me to remove those procedures etc that just select from the table I'm interested in. The vast majority of the dependencies are just selects. In a previous role I used SQL Server 2000 and I could get this information from sysdepends, filtering on the resultobj column to remove the select only dependencies - so I'll be a little surprised if Oracle doesn't have this facility.
View 1 Replies
ADVERTISEMENT
Apr 16, 2013
I have my database structured to where i have my tables, constraints, and Rows populated in each table which is 4 tables in particular. for one I want to be able to List all four tables with its content and constraints all at once. What i want to with the PL/SQL is to be able to program a prompt to where the user can just enter the individual order information and the data goes to what ever appropriate column or Table it needs to go to. Now should I be mostly be using procedures, functions or triggers to make this possible.
View 9 Replies
View Related
Oct 6, 2010
Is it possible to call procedures and functions in Database Triggers?
View 1 Replies
View Related
Sep 21, 2010
i want to do a schema export from Database A. There are hundreds of users under this schema.I have to import this schema into other database say B. My question's are:
1) Do i need to pre-create only schema user or all the users under it.
2) Will the schema export all the roles,procedures,packages,synonyms,funsctions and triggers?
View 1 Replies
View Related
Oct 31, 2012
I want to to use Java stored procedures and triggers within the Oracle 11g XE ?
View 3 Replies
View Related
Dec 15, 2011
I need to print out all the triggers/procedures from an Oracle 9iForms. When I open an Oracle 9iform in the Oracle form builder, I click on FILE/CONVERT but cannot see the triggers/procedures.
View 2 Replies
View Related
May 4, 2010
I think there is no limit for number of procedures and functions can be written in a package, but little much confusion.
What is the maximum number of Procedures and Functions can be written in a package? is such type of limit is there or not ?
View 4 Replies
View Related
Apr 6, 2011
how many functions and Procedures are exist in Package. Is it possible to identify through query.
Eg: I have one Package:- ABC. This ABC package having 2 functions and 3 procedures. I want, total no.of functions and total no.of procedures through query.
View 1 Replies
View Related
Nov 12, 2012
Procedures, triggers and other object with character changed.
Every character was replaced by ã? the content of procedures and other objects after importing a schema for production approval.
Example:
EXCEPTION
WHEN OTHERS THEN
p_msgerro := p_msgerro ||
' Erro na selec?o da Unidade Administrativa. ' ||
The character (?) deveria ser (ã)
View 4 Replies
View Related
Feb 10, 2012
Why do stored procedures and functions improve performance?
A. They reduce network round trips.
B. They reduce the number of calls to the database and decrease network traffic by bundling commands.
C. They reduce the number of calls to the database and decrease network traffic by using the local PL/SQL engine.
D. They allow the application to perform high-speed processing locally.
E. They postpone PL/SQL parsing until run time.
I think the answer should be A and B but i came across answers as B and E Can u explain me what is the difference between option A and B and does it postpone parsing till run time?
View 1 Replies
View Related
Feb 16, 2013
i have employee table i want to update salary with all employee 5 percent
View 4 Replies
View Related
Oct 14, 2011
How to call stored procedures or functions from a database trigger?
View 1 Replies
View Related
Aug 10, 2011
I am looking at an existing utility which inserts data into configuration tables. The utility is fairly basic, you simply add the UPDATE / INSERT / DELETE sql commands to a .sql file, set up a few params in a .sh script in order to tell it which Database / Schema to run against and away it goes, doing some logging, etc on the way.
Most of the time this is fine. However there is one table that causes big performance problems. This large table holds rating data and it has two large triggers on it. It also gets updated quite a bit with new rating tariffs.
The triggers check that many fields are not null or are certain values... but they also check that dates of the rates do no overlap, etc. So, in short, they do a lot of work. I can see that these are the main performance obstacle. I have no ability to alter or disable these triggers, this is a core table supplied by the vendor and as such I cannot manipulate it.
So looking at the things I can change, what am I left with?... only the way I load the data..
I can consider using SQLloader in order to handle INSERTS or using the APPEND hint in order to perform a direct path insert rather than having individual INSERT statements.
I can try to ensure that my data is sorted along the same lines as the index on the table in order to ensure that I am updating the index nodes in as streamlined way as possible. I can improve performance still more, or even circumnavigate the drag of the triggers?
View 5 Replies
View Related
Jul 26, 2012
For triggers, is it possible to do separate actions on insert , update and delete. For example, if insert is the case, do select; if update is the case, do select from another table and so on?
View 1 Replies
View Related
Mar 25, 2013
How can I determine version of oracle database from APEX. Is there any built in table or view is available in APEX? Is it possible from Shared Component?
View 5 Replies
View Related
Dec 4, 2010
I have to write two triggers on same table for auditing different columns of different users(may be different modules).
I will have an audit table in which i will insert data such as (user_id,module_id,column_name,old_col_val,new_col_val,timestamp)
Now different users from different modules will update the data on same table may be same columns from different front end forms!
If we write directly, we will not be able to know which column is updated by which user.
My question is in this case how can we control the triggers to raise differently?
View 3 Replies
View Related
Aug 28, 2007
whats the best way to modify a table via a procedure for example if i have a table with records: id, fn, ln, address, tel, dob
how would i go about creating a procedure that modifies a record. The problem is what param needs to go in? i could certainly have different procedures that modify different things like one for modifying tel, one for modifying address, but is there a way to combine it all into ONE procedure so that if user wanted he could put in exec modifyTable(2, 099898787) to modify tel and then modifyTable(2, 'a new address') to modify address... ?
View 6 Replies
View Related
Jul 9, 2008
Triggers for oracle database. What I am trying to do is copy original data from one table to another table prior to an update, insert, or delete occurring. Basically we are trying to keep a transactional history, with out having to restore the data. Here is what I have created to date, however it is not executing consistently.
CREATE OR REPLACE TRIGGER DATA_COPY BEFORE
INSERT
OR DELETE
OR UPDATE ON "DB1"."TABLE_1"
[code].......
View 10 Replies
View Related
Sep 14, 2012
I am soon engaging in a project where we will deploy three triggers to each table in a customers live database. One each for Inserts,Updates and deletes.
These triggers write or update a row in a separate table on a different schema.
We have experienced locking issues on these tables previously with SQL Server. Are there settings we can look at to improve performance?
View 2 Replies
View Related
Feb 22, 2011
i need to pass table as input parameter in stored procedures. during the run time, i am getting error
CREATE OR replace TYPE emp_type IS OBJECT (
id NUMBER(4),
ename VARCHAR2(10));
CREATE OR replace TYPE emp_type_tab
IS TABLE OF EMP_TYPE;
CREATE OR replace PROCEDURE Test_proc (in_emp_type IN EMP_TYPE_TAB)
AS
BEGIN
FOR i IN 1.. in_emp_type.COUNT LOOP
dbms_output.Put_line(in_emp_type.Id(i));
END LOOP;
END;
/
View 10 Replies
View Related
Oct 17, 2012
I am new to Oracle Workspace Manager. I have a trigger that fills my IDs every time i insert a record. I created the trigger before I enable the version of my table. After version was enabled in my table, I can no longer find my trigger but have these triggers instead:
OVM_DELETE_7 OVM_INSERT_7 OVM_UPDATE_7
What I wanted to do is Query the triggers that I created on my tables. Is there a way to do that without disabling the version on my table? I have too many version-enabled tables and that would be a hassle disabling the version in every table just for that query.
View 1 Replies
View Related
Jul 8, 2010
I have a function that returns the situation for one month for some database. I need to implement it in some report medium for one year. The one year function works ok.
My problem is when trying to make another function that runs the monthly function 12 times and that error is "PLS-00653: aggregate/table functions are not allowed in PL/SQL scope".am trying to get around some restrictions and somehow until this part things seem to be ok.
I tried to use a union with 12 blocks but it works very slow in the reporting environment and now i want to try to make another function that runs another function 12 times depending on the parameter.
here is the code (there might be some name misuse since i had to change the names of the original code -
CREATE OR REPLACE
FUNCTION anual(monthh in varchar2, year IN VARCHAR2)
return anual_REP_var PIPELINED is
BR anual_REP:=anual_REP(NULL,NULL, NULL,NULL);
contor INT(2);
[code]...
View 2 Replies
View Related
May 14, 2010
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
View 4 Replies
View Related
Jul 21, 2011
I have table test1(id,name) and table test2(id,,name)
Now when I update name column of a row on test1 I want the same value to be updated for the same id in test2.
So I wrote this trigger but its not working
create trigger test_trigger after update on test1 for each row
begin
update test2 set name=new.name where test2.id=id
end
/
View 9 Replies
View Related
Jun 15, 2013
I have 2 tables order_items and items.
Order_items Items
Item_id Item_id
Quantity Price
In normal sql statement: select sum(order_items.quantity*items.price) sales_price
from order_items,items
where order.item_id=items.item_id;
I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.
View 2 Replies
View Related
May 29, 2012
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/
View 3 Replies
View Related
Jul 19, 2011
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
View 4 Replies
View Related
May 16, 2013
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
View 3 Replies
View Related
Aug 24, 2013
My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
View 2 Replies
View Related
Feb 6, 2011
I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:
Query 1
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
Query 2
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)
When i execute these two above queries, it keeps executing indefinitely.
View 4 Replies
View Related
