Member Avatar for Obelisk4

Hi all !
I 'll a lucky guy if there 's someone who had experiences in using SQL Developer - connected with Oracle Database ( 10g Express ) ^^
I created a simple TRIGGER to print something out, for example :

create or replace trigger AUTO_OUT
      before insert on EMPLOYEE
      for each row
      begin
            dbms_output.put_line(87);
      end;

And when I tested it on SQL Developer with this :

INSERT INTO EMPLOYEE
VALUES ('John', 'B', 'Smith', '099999781', '09-JAN-65', '731 Fondren Houston TX', 'M', 30000, '333445555', 5);

It didn't work ! So I searched and found a solution, add this before the INSERT :

set serveroutput on;

But that command can't be placed inside of a trigger, and I must run it and each INSERT command together ! So I keep searching and found another direction : write some additional code to the file "glogin.sql" ( or even "login.sql" ) to set the serveroutput on each time you log in . I found that file on my system, but it 's seem I 've no sense in editing its code ... >"<
Sorry for my lecture >"< . But I really need your help ... thanx all ^^

  • 3 Contributors
  • 3 Replies
  • 206 Views
  • 3 Months Discussion Span
  • Latest Post Latest Post by karant

Dani AI

Generated

Short answer and diagnosis (ties to and ): the glogin.sql/login.sql mechanism is a SQLPlus startup feature (site and user profiles) and will affect SQLPlus sessions; the DBMS_OUTPUT display in SQL Developer is a client-side feature and must be enabled there separately. (docs.oracle.com)

Practical fixes for SQL Developer (fastest, no server edits): open the DBMS Output panel (View → DBMS Output), click the green + and add the active connection; that enables the client to poll and show DBMS_OUTPUT.PUT_LINE messages. For script-style runs, Run Script (F5) shows script output; Run Statement (Ctrl+Enter) behaves differently. (databasestar.com)

If the goal is “always on” behavior in SQL Developer: create a small startup SQL file (a one-line directive that turns on server-side output capture) and point SQL Developer at it under Tools → Preferences → Database → Filename for connection startup script; SQL Developer will run that file when a connection opens. For SQL*Plus-wide defaults, edit the site profile at ORACLE_HOME/sqlplus/admin/glogin.sql (or place a personal login.sql on SQLPATH) — note that glogin.sql is site-wide and may require admin rights and can be overwritten by installs/patches. (thatjeffsmith.com)

Troubleshooting notes and an alternative: DBMS_OUTPUT.ENABLE can be used server-side to increase the buffer, but the client still must fetch/display the buffer (SET SERVEROUTPUT in SQL*Plus or the SQL Developer DBMS Output panel). Triggers’ PUT_LINE output is buffered until the statement completes, so enable the client-side fetch before running tests. Example to raise the buffer from PL/SQL:

BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
END;
/

See Oracle’s DBMS_OUTPUT documentation for limits, GET_LINES/GET_LINE behavior and usage notes. (docs.oracle.com)

Summary: for interactive work with SQL Developer prefer the DBMS Output panel or the connection-startup script; edit glogin.sql only when the change must apply to SQL*Plus sessions and the administrator scope is acceptable. (thatjeffsmith.com)

Recommended Answers

All 3 Replies

Member Avatar for debasisdas

once you set the environmental variable by using
SET serveroutput ON;

That will be available for the entire session.

Member Avatar for Obelisk4

once you set the environmental variable by using
SET serveroutput ON;

That will be available for the entire session.

Okay ... So ... It 's seem my SQL Developer may have some errors, cuz after the serveroutput was set ON, it won 't "ON" again (as I said, I must compile it and each command together, if I want to check how my triggers work >"< ) .
I 've just found out that the SQL Developer has another window to display the PRINT or DBMS_OUTPUT results ... but I still want to learn more about the initial variables of a program, thought ... O.O"
Anyway, thank you for your post, debasisdas ^^

Member Avatar for karant

INSERT INTO EMPLOYEE VALUES ('John', 'B', 'Smith', 099999781, '09-JAN-65', 731 Fondren Houston TX', 'M', 30000, 333445555, 5);
INSERT INTO EMPLOYEE
VALUES ('John', 'B', 'Smith', 099999781, '09-JAN-65', '731 Fondren Houston TX', 'M', 30000, 333445555, 5);

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.