Skip to Main Content
  • Questions

  • Writing triggers using Dynamic SQL(It does not work)

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Lulekwa.

Asked: October 21, 2002 - 10:09 am UTC

Last updated: November 30, 2006 - 8:48 am UTC

Version: 8.17

Viewed 1000+ times

You Asked

Hi Tom,
I am writing a table level trigger, so that who ever changed a column on a form,report any where should be seen.I have written the below code but it does not work.I am writing it to a file and later run the file with correct code.Iam not getting any error, just my file is empty.
Do you know what might be the problem?I have written same code to update the date_modified and it worked.This one does not work.

CREATE OR REPLACE PROCEDURE USER_ID is

cursor c1 is
SELECT *
FROM TS20_DATE_TABLES
WHERE CS20_OWNER =
(SELECT USERNAME
FROM SYS.DBA_USERS
WHERE USER_ID = UID
)
AND CS20_COLUMN_NAME LIKE '%USER_ID'
AND CS20_DATE_PROCESSED IS NULL
ORDER BY CS20_SEQUENCE;

r1 c1%rowtype;
v_sql_statement varchar2(500);
out_file utl_file.file_type;
BEGIN
out_file := utl_file.fopen('/ora_load/wizard/out', 'wdpk_user_check.user_id.txt', 'w');

open c1;
loop
fetch c1 into r1;
exit when c1%notfound;

v_sql_statement := NULL;

-- Build the dynamic SQL that will create the trigger
v_sql_statement := 'CREATE OR REPLACE TRIGGER ' || r1.cs20_owner || '.' || 'WT_UserId' ||
' BEFORE INSERT OR UPDATE ON ' || r1.cs20_owner || '.' || r1.cs20_table_name ||
' REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW '||
' BEGIN '
||r1.cs20_column_name|| ':= :NEW.' || r1.cs20_column_name || '; END; /'||'';

utl_file.put_line(out_file, v_sql_statement);

UPDATE TS20_DATE_TABLES
SET CS20_DATE_PROCESSED = sysdate
WHERE CS20_SEQUENCE = r1.cs20_sequence
AND CS20_COLUMN_NAME LIKE '%USER_ID';

COMMIT;
end loop;
close c1;

utl_file.fclose(out_file);
EXCEPTION
when others THEN
null;
END USER_ID;
/


I'll appreciate your help.
Thanx in advance.



and Tom said...

You have a


WHEN OTHERS THEN null;


Basically you have said

"Any and all errors, come and get me -- I don't care. I'll just ignore you and pretend you do not exist at all."


A when others that is not followed by RAISE is a bug in my opinion.

Your code is throwing an exception somewhere, you are ignoring it 100%. That is why your code doesn't work as expected.

The ONLY time you catch an exception and do not re-raise it is when you are EXPECTING the exception to occur. For example:


begin
select * into l_rec from t where x = 5;
exception
when no_data_found then
l_rec.c1 := null;
l_rec.c2 := null; -- ok to "ignore" this "error" cause it isn't an
-- error really but an expected condition
end;


that is a valid time to ignore an error. But I am 100% certain that when I see code:

exception
when others then
null;
end;

I am seeing a BUG in the developed code!!!

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Indexes

Dennis, October 21, 2002 - 5:53 pm UTC

Tom,

With 9i making direct grants needed, what do you need to have dynamic SQL creating indexes? I'm going to a lookup table (hey I think you're the one that hinted me in this direction) and making sure they have the authority, but then I get the old 1031 error. They have CREATE TABLE directly granted to them, and they actually own the schema that I'm trying to build the index in (yes, they should just issue the statement since they have the schema, but I'm hoping to ween them off having schema owner once we get the ETL tool in since that's our standard). I have a TAR and we've added CREATE ANY INDEX and ALTER ANY INDEX, to no avail. Yet they say it works for them. I am trying to get them to dump the privs their user has, but they haven't yet. What are your thoughts Sir Tom?

proc
-
CREATE OR REPLACE PROCEDURE WINKID1.perform_ddl(p_object_name IN varchar2, p_operation IN varchar2)
as
TYPE l_record_type is RECORD
(r_object_name varchar2(32),
r_user_id varchar2(32),
r_operation varchar2(32),
r_sql_statement varchar2(1000));
CURSOR l_cursor is
SELECT object_name, user_id, operation, sql_statement
FROM winkid1.ddl_control;
l_record l_record_type;
l_owner varchar2(50);
begin
-- open cursor and do first fetch
OPEN l_cursor;
-- get user
winkid1.who_am_i(l_owner);
-- compare
LOOP
FETCH l_cursor INTO l_record;
EXIT WHEN l_cursor%NOTFOUND;
if (upper(p_object_name) != l_record.r_object_name) then
-- skip record, incorrect user
dbms_output.put_line('Skipping due to object');
elsif (l_owner != l_record.r_user_id) then
-- skip record, incorrect object
dbms_output.put_line('Skipping due to user');
elsif (upper(p_operation) != l_record.r_operation) then
-- skip record, incorrect operation
dbms_output.put_line('Skipping due to operation');
else
-- user is authorized for this operation on this object
execute immediate (l_record.r_sql_statement);
dbms_output.put_line('Statement executed');
end if;
end loop;
close l_cursor;
End;
/

- table and data
09:28:52 WINKID1@HII2:sca02> desc ddl_control
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_ID VARCHAR2(30)
OPERATION VARCHAR2(30)
OBJECT_NAME VARCHAR2(50)
SQL_STATEMENT VARCHAR2(1000)

data as follows
HRDMADM TRUNCATE TEST_A TRUNCATE TABLE HRDMADM.TEST_A
HRDMADM CREATE TEST_IDX1 CREATE INDEX HRDMADM.TEST_IDX1 ON HRDMADM.TEST_A(A) TABLESPACE HRDMADM_64K

- supplementary proc
CREATE OR REPLACE PROCEDURE WINKID1.who_am_i(p_owner OUT varchar2)
as
l_owner varchar2(30);
begin
select user into l_owner from dual;
p_owner := l_owner;
end;
/

Thanks,
Dennis

Tom Kyte
October 21, 2002 - 7:07 pm UTC

The OWNER of that procedure above would need

create ANY index
create ANY table

but that would be bad. I would rather you use AUTHID CURRENT_USER (invokers rights) and then the procedure would execute with the privs of the INVOKER of the routine. that way the invoker needs CREATE INDEX and CREATE TABLE (no ANY) and can get this either directly or via a role.

If you have my book "Expert one on one Oracle" i wrote an entire chapter on this topic in invoker/definer rights.

you are the man

Dennis, October 22, 2002 - 7:30 am UTC

Thanks Tom. Guess I need to hurry up and finish Lewis's book so I can read yours :) I knew you'd solve the problem for me. Oracle Support and I were looking at the wrong id.

Off topic - the font on the front page is horrible as far as reading goes (other than the information postings you have there...the questions and the "ask a question/backlog" box are horrible). Other pages display normally. I noticed this yesterday (which unfortunately was the first time in a week or two since I've been able to hit your site).

Anyway, thank you for your assistance yet again.

Dennis

Tom Kyte
October 22, 2002 - 7:49 am UTC

What browser are you using (they just put a CSS on the site to make it look nicer -- it looks fine in Netscape 7.0)

ah

Dennis, October 22, 2002 - 7:54 am UTC

Yeah that's probably it, I'm on Netscape 4.74. Guess I should update to the current times. It's my work computer so I don't keep it as up to the minute as my home (even though that's only 6.2).

Sorry for the inaccurate information. Guess that's a push to upgrade :)

Dennis

Tom Kyte
October 22, 2002 - 8:44 am UTC

Just for grins, wanted to see the browser types accessing

(just the last 3 weeks -- not all of the entries, there were 2004 DISTINCT browser types, i cleaned them up a bit and these are just the "most popular")


MSIE 5.5; AOL 7.0 271
Mozilla/5.0 (Windows;Netscape6/6.2.3 271
MSIE 5.0; Windows 2000) Opera 6.01 [en] 288
Mozilla/5.0 (Windows;Netscape6/6.2.1 294
Mozilla/4.75 [en] (Win98; U) 295
MSIE 4.01; Windows 98 308
Mozilla/4.77 [en] (Windows NT 5.0; U) 310
MSIE 4.01; Windows 95 312
Mozilla/4.76 [en] (WinNT; U) 327
Mozilla/4.74 [en] (WinNT; U) 334
Mozilla/4.51 [en] (WinNT; U) 343
MSIE 6.0b; Windows 98 355
Mozilla/4.7 [en] (WinNT; I) 358
Mozilla/4.7 [en] (WinNT; U) 361
MSIE 5.0; Windows 2000) Opera 6.05 [en] 400
Mozilla/4.76 [en] (Windows NT 5.0; U) 407
Wget/1.5.3 443
Opera/6.04 (Windows 2000; U) [en] 447
Mozilla/4.77 [en] (Win98; U) 456
MSIE 6.0b; Windows NT 4.0 457
Mozilla/4.75 [en] (Windows NT 5.0; U) 541
MSIE 5.0; Windows 95 788
iSiloX/3.05 Windows/32 833
Mozilla/4.75 [en] (X11; U; HP-UX B.11.00 9000/785) 971
MSIE 5.01; Windows 95 1485
Mozilla/4.79 [en] (Windows NT 5.0; U) 1560
Mozilla/5.0 (X11;Netscape/7.0 1606
MSIE 6.0b; Windows NT 5.0 1930
MSIE 4.01; Windows NT 2138
MSIE 5.01; Windows 98 2593
MSIE 5.5; Windows 95 3164
Mozilla/5.0 (X11;Gecko/ 3219
Mozilla/5.0 (Windows;Netscape/7.0 3536
Mozilla/5.0 (Windows;Gecko/ 5545
MSIE 5.0; Windows NT 8007
MSIE 5.5; Windows 98 8434
MSIE 6.0; Windows 98 9176
MSIE 5.01; Windows NT 14867
MSIE 6.0; Windows NT 4.0 16547
MSIE 6.0; Windows NT 5.1 28480
MSIE 5.0; Windows 98 36253
MSIE 5.5; Windows NT 4.0 38475
MSIE 5.01; Windows NT 5.0 43785
MSIE 5.5; Windows NT 5.0 53770
MSIE 6.0; Windows NT 5.0 141446


sort of disappointing to see so much "windows" and MSIE... oh well - at least Mozilla is getting up there -- that is a recent change

yar for Mozilla

Dennis, October 22, 2002 - 9:42 am UTC

Mozilla rules. That was a good move by Netscape. My only problem with it is that I have found running Mozilla, exiting out, and then trying to run any other browser (including Mozilla) causes the browser to crash, but the pop up killer is extreme :)

Here's a fearful thought about MS. Windows XP requires a full install/re-install if you swap out the motherboard or CPU. Kinda disgusting. I upgraded my computer, swapping the chip and the board, and just plugged in my Win98 hard drive and was up to speed. No reinstalling every app or anything. The "next" button might be hard to spot. It's like four pages.
</code> http://www.extremetech.com/article2/0,3973,10333,00.asp <code>


Back to topic:

When this stuff goes live, I plan on having a non-schema (more like an app) user, and then the schema which the developers will lose access to. Would I switch to definer's rights then for this procedure? Meaning I should give myself create any table/create any index, and then let them run wild? That way they cannot make a table? Or do I just give them Create Table and put 0 quota on everything?

What do you think is the 'best' choice?

Thanks,
Dennis

Tom Kyte
October 22, 2002 - 9:48 am UTC

Try linux -- I've yet to have mozilla crash and burn -- the pop up killer is awesome, coupled with "right click on a image and select 'block images from this site'" -- I don't see ads anymore!


I can highly recommend crossover office </code> http://www.codeweavers.com/home/ <code>for linux users that want to run MS office (i sort of needed word and powerpoint). It let me install work, ppt, xls, outlook and even MS IE5.0/5.5 on linux. I use word and ppt all of the time and IE on those sites that just plain refuse to work with open systems.


I would give them create table and use quotas. Else you have to re-invent security in your procedure.


bonus

Dennis, October 22, 2002 - 10:12 am UTC

Thanks Tom. Ever since I read that article I have had the bug to go to Linux. Well, I had it beforehand, but been able to suppress it. Thanks for the website, that will prove most useful.

Quotas it is then. I like taking the easy road out when it's a good choice.

I'll try not to disturb you anymore today :) But it was a good conversation :)

Have a good one.
Dennis

Oracle Developer

Dawar, August 31, 2004 - 2:57 pm UTC

Tom,

Do you know how to download any one of the following web browse on SuSE Linux?


* Microsoft Internet Explorer version 5.5, 6.0 or higher
* Netscape Navigator version 7 and higher
*Mozilla version 1.3.1 and higher

Thanks for any feed back.

Reagards,
Dawar




Dose any one know hoe to upgrade mozilla or install internet explorer 5.5, 6.0 or higher or SLES 8.

Regards,
D



Worst

Sunil, November 29, 2006 - 12:22 am UTC

This is the worst responce that i have ever seen.

Tom Kyte
November 30, 2006 - 8:47 am UTC

Ok - why.

The poster has a bug - if you disagree, speak up, tell us why.

And then we'll patiently explain why you are wrong - the poster of the original question has a seriously bad nasty bug in their code, period.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library