Oracle if updating field

But how does one make a field required for updates?

For instance, we have a table with a field that keeps track of which application last updated the table MYTABLE ------ ID PK APPLICATION_ID MORE_STUFF I want to write a PL/SQL trigger that requires the application to specify the appilication_id, and throw an error if the application fails to provide a value. this SQL command should fail: "UPDATE MYTABLE set MORE_STUFF = 'foo' WHERE ID = 123;" IOW, how does one determine the update list in a PL/SQL trigger?

oracle if updating field-90oracle if updating field-19oracle if updating field-18oracle if updating field-83

UPDATE: Fascinated by the seemingly unexplainable wild amount of "db file sequential read", I did a little bit of searching, and found out that strange things can happen with the lob index after mass DELETEs. If this is it, I would rebuild the lob column entirely.

Plan Hash: 4085453680 update WSP_BUNDLE set BUNDLE_DATA = :1 where NODE_ID = :2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 264 0.00 0.00 0 0 0 0 Execute 400 30.59 382.88 141451 1623163 3233827 400 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 664 30.59 382.88 141451 1623163 3233827 400 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 87 Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE WSP_BUNDLE (cr=8753 pr=707 pw=706 time=0 us) 1 INDEX UNIQUE SCAN WSP_BUNDLE_IDX (cr=3 pr=0 pw=0 time=0 us cost=2 size=104 card=1)(object id 75730) Elapsed times include waiting on following events: Event waited on Times Max. At the same time I've other tables in the same schema with blobs (CLOB to be more concrete) that contains 600 k rows where similar updates work correctly. I'm not an Oracle DBA, I'm from Java world :) I would examine USER_LOBS (or DBA_LOBS) to see how the "good" and "slow" LOB columns differ in their definitions.

Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 141251 5.53 328.04 direct path write 402 0.09 0.43 SQL*Net more data from client 142158 1.04 11.89 direct path read 200 0.03 0.07 Disk file operations I/O 1 0.00 0.00 SQL*Net message to client 400 0.00 0.00 SQL*Net message from client 400 0.29 0.50 log file switch (private strand flush incomplete) 5 0.05 0.23 asynch descriptor resize 139723 7.46 8.57 buffer busy waits 2 0.00 0.00 log file switch (checkpoint incomplete) 3 0.18 0.27 log file sync 2 0.00 0.00 Could anybody explain/hint me what's going on? The Metalink note ID 66431.1 describes this and may be of interest to you, if you have access there.

Everyday we need to send the zipfiles to our client. DECLARE my_blob BLOB; my_bfile BFILE; my_length INTEGER; my_id INTEGER; my_bfile_offset NUMBER := 1; my_blob_offset NUMBER := 1; my_in_bfile_size NUMBER; my_in_blob_size NUMBER; bytes_rd NUMBER; bytes_wt NUMBER; BEGIN my_bfile := BFILENAME (ORACLE_DIR, 'test.zip'); SELECT attachment_data INTO my_blob FROM tbl_attach WHERE attachment_id = 105 FOR UPDATE; DBMS_LOB.fileopen (my_bfile, DBMS_LOB.file_readonly); DBMS_LOB.

I am not very good in Oracle stored procedures and suddenly I have been faced with a situation to update the blob field.

Leave a Reply

  1. index of dating jpgs 09-Sep-2017 20:33

    Our great quality assurance and customer service means all you have to worry about is looking good in your photo.