Undesrtood the concept of DBMS_OUTPUT but need some more clarification
Suvrojoti Bhunia, January 25, 2018 - 6:16 pm UTC
Still facing the issue.I am not sure what is happening here looks like some problem with DBMS_OUTPUT in sql developer.
Please find the full code below
<
CREATE TABLE "HR"."SUPERHEROES"
( "SH_NAME" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
CREATE OR REPLACE TRIGGER "HR"."BI_SUPERHEROES1"
before UPDATE ON superheroes
for each row
Declare
v_user varchar2(20);
Begin
Select user into v_user from dual;
DBMS_OUTPUT.PUT_LINE('You have UPDATED one hero' || v_user);
end;
/
ALTER TRIGGER "HR"."BI_SUPERHEROES1" ENABLE;>
Now when running an update on the data the dbms_output is showing in sql*plus as 'You have UPDATED one hero HR'
but in sql developer that line is not showing.Seems dbms_output inside the trigger is not working properly.
January 26, 2018 - 10:35 am UTC
If it works in SQL*Plus the trigger is fine.
Have you read the links above and followed Jeff's advice?
Works for me.
thatjeffsmith, January 25, 2018 - 8:42 pm UTC
I will say, there's a bug in the trigger.
<pre>DBMS_OUTPUT.PUT_LINE('You have inserted one hero ' || v_user);</pre>
Needs a space after hero, so the DBMS_OUTPUT comes out as
You have inserted one hero HR
That's not very good grammar, it should probably be
<pre>DBMS_OUTPUT.PUT_LINE('You have inserted one hero, ' || v_user || '.');</pre>
Also, make sure the serveroutput is ON for when you do the INSERT, not creating the trigger.
<pre>
create table superheroes (x integer);
create or replace trigger bi_superheroes
before insert ON superheroes
for each row
ENABLE
Declare
v_user varchar2(20);
Begin
Select user into v_user from dual;
DBMS_OUTPUT.PUT_LINE('You have inserted one hero, ' || v_user || '.');
end;
/
set serveroutput on
insert into superheroes values (100);
commit;
/
Trigger BI_SUPERHEROES compiled
1 row inserted.
Commit complete.
You have inserted one hero, HR.
</pre>
January 26, 2018 - 10:35 am UTC
:)