PL/SQL :: Create A Trigger To Check If Synonym Already Exists In DB
Mar 29, 2013
How can I create a trigger to check if synonym already exists in db and if exists then don't create synonym.
my work: ( this is just like an outline i prepared)
select * from all_synonyms;
declare
s_exists number;
begin
-- check whether the synonym exists
select 1 into s_exists from all_synonyms;
-- an error gets raise if it doesn't
exception when no_data_found then
-- DDL has to be done inside
execute immediate ' create or replace synonym';
end;
/
i have a procedure like create or replace procedure studrec( a in sid%rowtype)asi sid%rowtype;beginselect sid into ifrom students; i need to check whether sid is exist in the variable i or not
But i think if table CON_TEST doen't exist, an error message will appear. I know that in SQL Server we can check if table exists or not. So, i wonder if we can do that in Oracle?
By the way, is there any way to run a file script that contents TABLES, STORED, ... on a developed PC connect to oracle db server? (in case, i'm developing on PC, using Net Service Name to conect to Oracle DB Server)
i have also another concern as per below xml file.
My requirement is to identify perticular node ,whose having PriorValue attribute present in <pi:the Actual_Comp_Change> tag,those record should return.
1. Below TXN_LOG table Expected to have 0.5 to 1 million records & TXN_LOG_HIST table will have max 3 million records.
2. On daily basis system will move records from TXN_LOG to TXN_LOG_HIST.
3. The below mentioned check will be performed for each transaction, With out this check currently my process is doing 10tps. (transaction per second).
The below validation we are currently using which is overhead for our transactions processing. Also the below mentioned check will be performed for each transaction, With out this check currently my process is doing 10tps. (transaction per second).
BEGIN SELECT COUNT(1) INTO l_exists FROMTXN_LOG WHERE AC_NO = p_acho TXN_INIT_DATE = p_txn_date AMOUNT = p_amt
[code]....
Is there any fastest method to check above record exists validation from the both the table?
I have schema a and b in the same database with synonym table a (schema a) to schema b. When I try to disable trigger from table a in schema b; I got this message:
ORA-00942: table or view does not exist
I have granted all permissions to schema b and I am able to SELECT FROM table a in schema b
Using a cursor and loop method records are inserted into the header and line tables. How do I code the IF statement prior to the INSert statement such that Insert if record does not exist else Update the record. If the record for insert fails in the line, the same record should be deleted from the header table as well and transaction should rollback. The Ora version used is 11.2.0.2.
CURSOR C1 IS SELECT H.* FROM rex_head_extract h WHERE TRUNC(h.create_date) BETWEEN v_begin_date AND v_end_date; FOR I IN C1 (v_begin_date, v_end_date)
[Code]....
--- Need to perform a check if record exists here prior to insert INSERT INTO tran_head (TRAN_SEQ_NBR, ORG_NUMBER, STORE_NUMBER, TRAN_DATE,
I have exported and imported a schema from one server to another. In the source schema, I have a public synonym. I do not know the name of that synonym. In the destination schema, the public synonym is missing. How to create the public synonym which is missing in the destination database? In the source, I queried dba_synonyms, all_synonyms... but it returns no rows selected.
I have exported and imported a schema from one server to another. In the source schema, I have a public synonym. I do not know the name of that synonym. In the destination schema, the public synonym is missing. How to create the public synonym which is missing in the destination database?In the source, I queried dba_synonyms, all_synonyms... but it returns no rows selected.
from sysdba i grant to my user hospital creat any synonym but give me this error why ?
SQL> conn sys as sysdba Enter password: Connected. SQL> grant create any synonym to hospital 2 ;
Grant succeeded.
SQL> conn hospital Enter password: Connected. SQL> create public synonym Sur 2 for surgeries; create public synonym Sur * ERROR at line 1: ORA-01031: insufficient privileges
I have an interesting problem. I have a server in the UK and I have three databases globally - UK (FINUKQ1), NY (FINNYQ1) and TK (FINTKQ1). The NY and TK databases will be migrated to the UK server as part of a consolidation project. Obviously, Time Zones and DST are the biggest concern for me.Here was my plan for the implementation (concentrating on Time Zone/DST issues).
Added to .profile: -------------------- if [[ "$ORACLE_SID" = "FINTKQ1" ]]; then export TZ="Asia/Tokyo" elif [[ "$ORACLE_SID" = "FINNYQ1" ]]; then export TZ="America/New_York" else unset TZ fi echo "TZ=$TZ"
Once environment has been set start database and listener.
Change database TZ: ----------------- alter database set time_zone='Asia/Tokyo'; alter database set time_zone='America/New_York';
Check database TZ: select dbtimezone from dual;
Add trigger: ------------ CREATE OR replace TRIGGER sys.timezone_check AFTER startup ON DATABASE DECLARE
[code]...
The trigger was there to put the database in restricted session if it is started in the wrong TZ and an error written to the alert log. The check was done on the dbtimezone and the dbname. Quite simple. Only issue is that it doesn't work as I wanted as dbtimezone and TZ are separate and disparate entities. i.e. I can start the TK database up in UK time (which will be incorrect for this project), and check the dbtimezone and it will show as 'Asia/Tokyo'.
Is there a better way of checking? Any trigger like this to compare TZs etc?
What is the trigger which should be used to check certain field value after posting the query.
Example:
I have executed the query and the records are fetched. There is one field I want to check if it is null then it should be enabled, else, keep it disabled.
I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:
create or replace trigger t_triggername before insert on tbl_tablename for each row begin if(:new.user_id <> :old.user_id) then insert into tbl_tablename(user_id, location) values (:new.user_id, :new.location); end if; end;
what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:
create or replace trigger t_triggername before insert on tbl_tablename for each row begin if(:new.user_id <> :old.user_id) then insert into tbl_tablename(user_id, location)
I have 20 or so tables, partitioned by range, indexed etc...soon these tables will be gone and i have to recreate them with the same definitions. I have to write a procedure(or script, it's up to me) which must:
1- check if table A exist in the scheme 2- if not create the table as the original ddl.
for 1 i've used smth like ------------------------- SELECT count(*) INTO a FROM user_objects WHERE object_name='A';
[code]....
so my question is how and what is the cleanest and fastest way to do this.? it's a lot DDLs,
I'm new to oracle DB,i've been given access to Oracle as a user. when i try to create a table under my default schema i get the insufficient privileges error.
How do i check from the system views if i have create table privilege under my own schema?
how to create the trigger that count the last 7 day about from sale table if the last 7 day amount is greater then 10000 then update the commission 2% other wise do nothing
I cant create a trigger for the following problem "write a trigger to change the "hiredate"(in emp tale) in the default format even if the user enters it in some other format, say year/mon/date..."
How do I create a trigger for the age. I want an alert to appear when the date of birth field is less than the age of 17. The formula is age=sysdate-dateofbirth
i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.
create or replace trigger tri_update_test after delete on test declare pragma autonomous_transaction;
[code]...
trigger created sucessfully.And i try to delete data from the test
delete from test where id=5;
Output:
ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4 ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'
I'm trying to create a trigger and procedure to go along with my DDL. Everything is created just fine, but when I try to execute an update on the table monitored by the trigger I get the following error:
update housing set group_num = 1 where room_num = 10
ERROR at line 1: ORA-00937: not a single-group group function ORA-06512: at "YANKEEFAN146.VIOLATION_CHK", line 6 ORA-04088: error during execution of trigger 'YANKEEFAN146.VIOLATION_CHK'
/* DDL */ create table violation_type (violation_num number(1) primary key, violation_def varchar2(100) not null) organization index tablespace mi257_data; [code]....
I'm looking to create an after logon trigger... my question is - can this trigger be created by any user (i.e. other than sys) and - if so - what's required to ensure it fires for EVERY other user that log's on to the database?
CREATE OR REPLACE TRIGGER tri1 AFTER UPDATE ON dept FOR EACH ROW declare pragma AUTONOMOUS_TRANSACTION; BEGIN execute immediate 'create table dept_dum as select * from dept'; END; /
SQL> update dept set deptno=23 where deptno=10; update dept set deptno=23 where deptno=10 * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SCOTT.TRI1", line 6 ORA-04088: error during execution of trigger 'SCOTT.TRI1'
I am running this as scott user
My requirement is to
create table as select * from table_name where flag=1
This has to be done parallel for all the tables for which this flag is enabled and by that trigger delete all those rows which were backed up as table.
We have an application is must be connected to our database for specific requirements in our company but this application has a very bug thing as we must write the super DB password "Like HR password as example" clear in some files and these files must be shared so developers can use the HR password to do any action !!! I know that this application is a problem but we have to install
I can do this by creating trigger on each table will restrict DML. As example: if the operating system user is XXX, the trigger restrict the action. But not logic at all to create more than 1000 triggers on schema (This will impact badly on DB performance).
So, i need to create one trigger to fire before doing any DML on all schema tables. As example: If "MMM" the administrator operating system user trying to do insert action, he can do the action. BUT If "DEV" the developer operating system user trying to do insert action, the trigger must fire here to restrict this action.
Be noted also, i need this trigger not depend on any specific tool like Toad as any user can simply rename the exe file for toad then he can pass the trigger. At least, trigger must depend on (Operating system user & Action_type)
i want to give privilege of create trigger,procedures and functions privileges to a user "A"on the schema "B". how can i do it. i've already given select,insert,update,delete privilege to user "A"
how can user "A" create trigger(etc.) on tables of user "B".
