I have a trigger on insert which sets a :new field if it is net set.Basically:
CREATE OR REPLACE TRIGGER my_schema.T_OBS_BEF_IU
BEFORE INSERT OR UPDATE ON my_schema.OBS FOR EACH ROW
BEGIN
If :new.work_db is null Then
:new.work_db := my_package$PARAMETER.Get_label ('DEFAULT_WORK_DB'); -- function returning varchar2(200)
End if;
END;
/
It usually works fine. With jdbc, when the application insert a null value, the trigger replaces it with the good value.When the application can determine the non-null value, it is written in the table.
But when the trigger is restarted to a savepoint, due to a table lock, the second execution of this trigger goes wrong. The input :new.work_db value is not null anymore, nor has the correct value. Most of the time, it has the ascii 00 value (NUL), sometimes another weird character.
Do you know if my trigger code is correct, or if there would be some limitations ?
I am working on convertion of triggers from SQL SERVER to Oracle. I got them converted using a tool. But not sure how to correct the below trigger which uses '*' in sql server.
Code in SQL SERVER :-
CREATE TRIGGER INSERT_AUDIT ON T_AUDIT FOR INSERT AS INSERT INTO T_AUDIT_LOG SELECT 'INSERT','AFTER', CURRENT_TIMESTAMP, NULL,* FROM INSERTED
Converted into Oracle:-
CREATE OR REPLACE TRIGGER INSERT_AUDIT BEFORE INSERT ON T_AUDIT FOR EACH ROW INSERT INTO T_AUDIT_LOG VALUES ( 'INSERT', 'AFTER', SYSTIMESTAMP, NULL, * );
END;
how can I remove the *. I tried replacing the * with the rest column names prefixing with :NEW.
the last 3 columns when are c_data1,c_data2,c_data3 as :- INSERT INTO T_AUDIT_LOG VALUES ( 'INSERT', 'AFTER',SYSTIMESTAMP NULL, :NEW.c_data1,:NEW.c_data2,:NEW.c_data3); But this gives error:- PLS-00049: bad bind variable 'NEW.C_DATA1' PLS-00049: bad bind variable 'NEW.C_DATA2' PLS-00049: bad bind variable 'NEW.C_DATA3'
I am facing a problem while inserting primary keys using a sequence. Following is my case
I have a Master Table, say M_A, and 2 detail tables D_1 and D_2. I am trying to generate primary keys for the master and detail table as well as the reference keys for the detail tables using sequence.
I created a pre-insert trigger, say preInsertTRIG
proc_ABC(pri_key_master OUT VARCHAR2, pri_key_detail1 OUT VARCHAR2, fk_detail1 OUT VARCHAR2, pri_key_detail2 OUT VARCHAR2, [Code] ........
I am able to insert the P.K of the master table as well as P.K of one of the detail table. However, it fails to insert the P.K of 2nd detail table and reference keys for both the detail tables. I know there are other simple methods available in Forms, but I have to do it by this procedure only
i have a table students create table students (name varchar2(10),rolno number(10),sub1mark number(10),sub2mark number(10),total number(10),percentage number(10),status varchar2(10))
i am go to create trigger on students after entering the values of name,rolno,sub1mark,sub2mark the values of total & percentage come automatically .trigger is
CREATE OR REPLACE TRIGGER UCSETH.students_comm_trig BEFORE INSERT ON UCSETH.STUDENTs FOR EACH ROW BEGIN set NEW.total = new.sub1mark+new.sub2mark; set new.percentage=new.total/2;
i'm getting ORA-04091: error when using a trigger to subtract ncopies values of table (rental ; after i insert a NEW record in it ) from cno value of table (copies) to give me the remaining no of copies of a book .
I have a multi record block based on a view. All records in the view are displayed in the block by use of Post-Query trigger when entering the form.
The block has 5 items as follows:
1) RECORD_STATUS = a non-base table column which is a checkbox. 2) ITEM_TYPE = a text-item which has an LOV attached. 3) ITEM_TEXT = a text-item which is free format text. 4) LAST_UPDATE_DATE a date column 5) STATUS = a text item either 'Open' or 'Closed'
The LOV is based on a table of Item Types with values say, 'Type1', upto 'Type9'.
I have a Wnen-New-Record-Instance trigger which 'Posts' changes to the database. This has been included as i want to limit the values of the ITEM_TYPE column to values which have not been previously used.
Consider this scenario...
The block has 3 records.
record 1 has 'Closed' status so no updates are allowed. record 2 has 'Open' status so updating of Item_Text is allowed. record 3 has 'Open' status so updating of Item_Text is allowed.
I check the RECORD_STATUS checkbox on record2.
(This sets the RECORD_STATUS checkbox to a checked value and changes the STATUS column to 'Closed' by When-Checkbox-Changed trigger.) At this point the record has not been saved so if you uncheck the checkbox , then the STATUS column will go back to 'Open'. However at this point i will leave it as Checked (Closed).
I then insert a new record, only values Item4 to Item 9 are correctly shown in the LOV. I select Item4.
I then go back to the previous record and uncheck the Checkbox to say that i wish to leave it 'Open' after all (in effect no changes have occurred), then the STATUS column correctly reverts back to 'Open' by my WCC trigger. If i then SAVE the changes, the new record has been inserted on the database correctly, however the LAST_UPDATED_DATE from the record which was checked and then unchecked has also been updated incorrectly even though no net changes have actually occurred.
(because i am using WNRI trigger to limit the List of Values on the LOV column, this has incorrectly set the previous records LAST_UPDATED_DATE column to be Sysdate.)
Table LEAVE Column Type Null Description APP_NO Number(6,0) Not Null PK Leave Application Number ECN Number(6,0) Not Null FK Employee Code Number APP_Date Date Not Null Date of Application From_Date Date Not Null Date from which the leave starts TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved Remark VARCHAR2(100) Nullable Reason to be put if status is rejected [code]....
What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the _USED values of the corresponding LEAVE_TYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.
For example if 3 rows are inserted in the LEAVES table as INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null); INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null); INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');
Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_ UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'
I tried with the following trigger, but is compiling with a warning (not showing what the warning is)
CREATE OR REPLACE TRIGGER leaveentitle AFTER INSERT ON LEAVES FOR EACH ROW BEGIN UPDATE LEAVEENTITLE LVE SET LVE.SL_USED = SL_USED+(CASE WHEN :NEW.LEAVE_TYPE = 'SL'&& NEW.STATUS='APPROVED' THEN :NEW.NO_OF_DAYS SL_UNUSED=SL_ENTITLED - SL_USED ELSE 0 END), [code]....
I am developing form, but there is issue when I Press F11 to query data.I make trigger form when-new-record-instance to assign values for item.But when i Press F11 then Block no clear.
11g RAC environment, I manually shutdown the database with "shutdown immediate" and stop the listener with "lsnrctl stop". Later I manually startup only the database but the listener is also started automatically at almost the same.
Why would this happen ? I just don't want the listener to startup automatically at that time. What can i do to prevent the listener from auto-starting when i startup the database manually.
My client get the error Ora-12514 when he tries to connect to the db.Well;
1) we restart the services: "OracleServiceOrcl" and listener but nothing changes 2) We restart the server and nothing changes 3) We checked the dump and he was done on March,26th. No db export yesterday. Is there any link with the clock change of this week-end (location of the server: France)? 4) The lsnrctl services listener command run successfully but it doesn't mention the instance ORCL.
I just shutdown the database and it seems to take a while, don't know if it's stuck or just takes awhile. I try to restart it in another session and get this error:
'ORA-01012: not logged on'
1) Shutdown SQL> connect sys/Welcome1 as sysdba Connected. SQL> shutdown normal _
2) So I try to restart it as sysdba on another session and get this error Connected to an idle instance. SQL> startup ORA-01012: not logged on
'm using oracle rac 10.2.0.4.0 with 3 node and os SLES 10 sp 3 in early days we get error vip and lsnr service on my rac shutdown n restart itself,what happen to my rac ?
Oracle Restart 11.2.0.3 w/ASM. To improve redundancy we're planning to change the network configuration in our Oracle Restart environment from using 1 LAN interface to a bonded interface consisting of 2 interfaces.So the change will be from using "eth0" to using "bond0" consisting of "eth0" and "eth1". Are there any changes that must be done in the Oracle Restart configuration to reflect these changes?
For a one week, oracle database (v 9.0.1.0.0) is hanging frequently.I could't showdown/restart the database. After restart the server (RedHat), the database is opened without any problem.after 9 or 10 hours once again hanging. too large spaces are availble in my HardDisk.
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 1152M sga_target big integer 0
[code]....
in scenario above, the database do not using ASMM, and spfile If I wan to increase db_cache_size parameter, do i need to rebounce instance?
I install oracle 11g2 on centos, in prerequisite checks step ,oracle database show this failed "Oracle Restart integrity Failed", I don't know what should i do ?
I just finished my first course in SQL for Oracle 11g at my local community college. We used SQL*Plus for the coursework and for the next class I need to use SQL Developer. Initially, I installed the Developer and was unable to establish a test connection due to the listener not being installed. I fiigured out that I needed to use the Network Configuration Assistant to add and configure the listener. After doing so, I was able to connect using Developer. The issue, however, is that any time I restart my computer, the TNS Listener service disappears from the list of services in Task Manager and I have to delete and then add and configure the service again using Network Configuration Assistant. Obviously, the Listener still exists because I can delete it and then recreate it.
I am fairly new to Oracle and have recently started learning the basics of Data Guard. I have set-up a Primary 11g database which successfully ships and applies logs to a Single Standby (on the same server) as expected. Manual switchovers work fine; however, I have found a slight problem when performing switchovers using the Data Guard Broker utility.
The switchover is successful- but the broker cannot restart the Primary instance. The strange thing is that when I perform a switchover in the opposite direction (i.e. the new Primary goes back to standby again) it works fine. The error is receive is: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.
The majority of posts I have seen on this topic seem to point to incorrect listener / tnsnames set up but they have exactly the same values. If both databases are identical, why does it work one way but not the other? I will provide any necessary files to pinpoint where the issue could be.
We are unable to connect our admin account for some unknown reason. We keep receiving the following message "Invalid Login Credentials". We tried to change the admin credentials with sqlplus. We tried the usual procedure "apxchpwd" successfully, but we still cannot log in.We then tried to create a new admin2 account with the following pl/sql block.
SQL> connect APEX_030200 as sysdba Enter password: Connected. [code]....
I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.
I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".
I have recently setup a 2 node Rac on oracle linux 5.4 with oracel 11gR2, the installation went smoothly and all the cluster resources are up and running however the data is not syncing across the nodes, when I create a table it shows up on the other node but when I insert rows into the tables they don't show up on the other node and when I restart the cluster the inserted rows are completely gone even from the node where I inserted them.
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
I am searching the simplest way for ad hoc MINUS.I do:
SELECT * FROM uam_rss_user_XXXXXXX WHERE host_name IN ('XXX0349', 'XXX0362', 'XXX0363', 'XXX0343', 'XXX0342', 'XXX0499', [code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
