Skip to Main Content
  • Questions
  • Before/after insert,update,delete trigger working on SQL*Plus but not in SQL Developer

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Suvrojoti.

Asked: January 25, 2018 - 4:40 pm UTC

Last updated: January 26, 2018 - 10:35 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hello,

So I have this very basic trigger script

set SERVEROUTPUT ON;
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;


as expected it is showing the print line when running in sql* plus. But when done in sql developer the only output is '1 row inserted'.

It will be very helpful if any possible cause is provided.

Thanks so much..:)

and Chris said...

Rating

  (2 ratings)

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

Comments

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.
Chris Saxon
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>
Chris Saxon
January 26, 2018 - 10:35 am UTC

:)

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