sue Tons
asked on
How to Turn an update into a procedure and update automatically?
I can update this one column UWI but I would like to turn this into a procedure, where if there is a new uwi in the table which meet the requirement, it updates automatically.
UPDATE well_core_analysis a
SET a.active_ind = 'N'
WHERE active_ind = 'Y'
AND a.analysis_source = 'GOVT'
AND uwi IN (SELECT uwi
FROM (SELECT DISTINCT e.string02 AS License_num,
b.uwi,
e.string03 AS Province,
b.analysis_source,
b.row_quality,
b.active_ind
FROM data_entry.well_core_analy sis b
join well a
ON b.uwi = a.uwi
join well_license c
ON a.uwi = c.uwi
join license l
ON c.license_id = l.license_id
join publicdata.entity e
ON l.license_id = e.string03 || '|'|| e.string02
join publicdata.l_entity_docume nt led
ON e.entity_id = led.entity_id
join publicdata.document d
ON led.document_id = d.document_id
join publicdata.l_document_docu ment_type lddt
ON d.document_id = lddt.document_id
WHERE b.analysis_source = 'GOVT'
AND lddt.document_type_id = 7
AND Upper(document_name) LIKE '%.PDF'
AND b.active_ind = 'Y'
AND b.row_quality = 'VALID'
AND e.string03 = 'MB')) ;
UPDATE well_core_analysis a
SET a.active_ind = 'N'
WHERE active_ind = 'Y'
AND a.analysis_source = 'GOVT'
AND uwi IN (SELECT uwi
FROM (SELECT DISTINCT e.string02 AS License_num,
b.uwi,
e.string03 AS Province,
b.analysis_source,
b.row_quality,
b.active_ind
FROM data_entry.well_core_analy
join well a
ON b.uwi = a.uwi
join well_license c
ON a.uwi = c.uwi
join license l
ON c.license_id = l.license_id
join publicdata.entity e
ON l.license_id = e.string03 || '|'|| e.string02
join publicdata.l_entity_docume
ON e.entity_id = led.entity_id
join publicdata.document d
ON led.document_id = d.document_id
join publicdata.l_document_docu
ON d.document_id = lddt.document_id
WHERE b.analysis_source = 'GOVT'
AND lddt.document_type_id = 7
AND Upper(document_name) LIKE '%.PDF'
AND b.active_ind = 'Y'
AND b.row_quality = 'VALID'
AND e.string03 = 'MB')) ;
It depends on your data model. Cause the first thing to consider would be a trigger, which evaluate INSERTs or UPDATEs and process your logic when it applies.
"if there is a new uwi in the table which meet[s] the requirement, it updates automatically."
As ste5an suggested, that sounds more like a requirement for a database trigger than for a procedure.
Do you have the option to create a database trigger (or a combination of triggers) to do this task? If not, you can use a procedure, but you will need something to run your procedure regularly. This could be:
1. a database job, scheduled with dbms_scheduler
2. an OEM job (in Oracle Enterprise Manager, assuming that you use OEM)
3. a crontab job (that will need to be able to launch SQL*Plus and run a *.sql script)
4. some other job scheduler
But, with any of these job scheduler options, there will be a time lag between when the record(s) get(s) created, and when your procedure runs.
Using a database trigger would avoid the time lag, but you would need a combination of:
1. a row level trigger (that includes the "for each row" clause) to put the new rowid into a temporary holding area
2. a temporary holding area (this could be a global temporary table or a PL/SQL array)
3. a statement-level trigger (with no "for each row" clause) to retrieve the rowid(s), actually do the update you want for each now record, and clear the temporary holding area, If you use a global temporary table, clearing it will happen automatically.
This multi-step approach is required because a "for each row" trigger is not allowed to select from the triggering table itself, but a "for each row" trigger is the only way to detect a new row efficiently.
As ste5an suggested, that sounds more like a requirement for a database trigger than for a procedure.
Do you have the option to create a database trigger (or a combination of triggers) to do this task? If not, you can use a procedure, but you will need something to run your procedure regularly. This could be:
1. a database job, scheduled with dbms_scheduler
2. an OEM job (in Oracle Enterprise Manager, assuming that you use OEM)
3. a crontab job (that will need to be able to launch SQL*Plus and run a *.sql script)
4. some other job scheduler
But, with any of these job scheduler options, there will be a time lag between when the record(s) get(s) created, and when your procedure runs.
Using a database trigger would avoid the time lag, but you would need a combination of:
1. a row level trigger (that includes the "for each row" clause) to put the new rowid into a temporary holding area
2. a temporary holding area (this could be a global temporary table or a PL/SQL array)
3. a statement-level trigger (with no "for each row" clause) to retrieve the rowid(s), actually do the update you want for each now record, and clear the temporary holding area, If you use a global temporary table, clearing it will happen automatically.
This multi-step approach is required because a "for each row" trigger is not allowed to select from the triggering table itself, but a "for each row" trigger is the only way to detect a new row efficiently.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.