Home » SQL & PL/SQL » Client Tools » SQL Developer query
SQL Developer query [message #549475] Fri, 30 March 2012 16:18 Go to next message
budz
Messages: 3
Registered: March 2012
Junior Member
Two questions.
1.
I'm using sql developer 3.0.04
I execute set serveroutput on to see output from dbms_output statements.
In a previous version I executed this serveroutput statement ONCE and I could execute many individual anon blocks and all output was visible.
Now I have to execute set serveroutput on for EACH anon block.
Is there a setting or an option that I don't know about?

2.
Is there a way to execute various blocks of code in a sequence. The code I want to execute is below. The code always falls over at the beginning of the 2nd anon block. Any idea?

Any help is much appreciated.
Budz

drop table X cascade constraints;
CREATE TABLE X(
xid NUMBER PRIMARY KEY,
xname VARCHAR2(100) );

drop table y cascade constraints;
CREATE TABLE Y(
yid NUMBER PRIMARY KEY,
yname varchar2(100), 
maxqty number );

CREATE OR REPLACE procedure addx(p_xid number, p_xame varchar2) AS
BEGIN
    INSERT INTO x VALUES (p_xid,p_xame);
END;

CREATE OR REPLACE procedure addY(p_yid number, p_yname varchar2) AS
BEGIN
    INSERT INTO Y VALUES (p_yid,p_yname, 0);
END;

set serveroutput on
prompt TESTING ADD X rows
begin
   addX(1,'one');
   addX(2,'two');
   addX(3,'three');
   addX(4,'four');
EXCEPTION
WHEN others THEN  
      dbms_output.put_line(SQLCODE || ' ' || substr(SQLERRM, 1, 200));
END;

set serveroutput on
prompt ADD Y ROWS;
BEGIN
   --Test successfully add Y rows
   addY(21,'twoone');
   addY(22,'twotwo');
   addY(23,'twothree');
   addY(24,'twofour');
EXCEPTION
WHEN others THEN  
      DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SUBSTR(SQLERRM, 1, 200));
END;


[Updated on: Fri, 30 March 2012 16:55]

Report message to a moderator

Re: SQL Developer query [message #549479 is a reply to message #549475] Fri, 30 March 2012 19:28 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
THANKS! for decent test case.
So what do you think & see?
17:27:21 SQL> @xy
17:27:25 SQL> drop table X cascade constraints;

Table dropped.

17:27:25 SQL> CREATE TABLE X(
17:27:25   2  xid NUMBER PRIMARY KEY,
17:27:25   3  xname VARCHAR2(100) );

Table created.

17:27:25 SQL> 
17:27:25 SQL> drop table y cascade constraints;

Table dropped.

17:27:25 SQL> CREATE TABLE Y(
17:27:25   2  yid NUMBER PRIMARY KEY,
17:27:25   3  yname varchar2(100),
17:27:25   4  maxqty number );

Table created.

17:27:25 SQL> 
17:27:25 SQL> CREATE OR REPLACE procedure addx(p_xid number, p_xame varchar2) AS
17:27:25   2  BEGIN
17:27:25   3  	  INSERT INTO x VALUES (p_xid,p_xame);
17:27:25   4  END;
17:27:25   5  /

Procedure created.

17:27:26 SQL> CREATE OR REPLACE procedure addY(p_yid number, p_yname varchar2) AS
17:27:26   2  BEGIN
17:27:26   3  	  INSERT INTO Y VALUES (p_yid,p_yname, 0);
17:27:26   4  END;
17:27:26   5  /

Procedure created.

17:27:26 SQL> set serveroutput on
17:27:26 SQL> prompt TESTING ADD X rows
TESTING ADD X rows
17:27:26 SQL> begin
17:27:26   2  	 addX(1,'one');
17:27:26   3  	 addX(2,'two');
17:27:26   4  	 addX(3,'three');
17:27:26   5  	 addX(4,'four');
17:27:26   6  END;
17:27:26   7  /

PL/SQL procedure successfully completed.

17:27:26 SQL> set serveroutput on
17:27:26 SQL> prompt ADD Y ROWS;
ADD Y ROWS
17:27:26 SQL> BEGIN
17:27:26   2  	 --Test successfully add Y rows
17:27:26   3  	 addY(21,'twoone');
17:27:26   4  	 addY(22,'twotwo');
17:27:26   5  	 addY(23,'twothree');
17:27:26   6  	 addY(24,'twofour');
17:27:26   7  END;
17:27:26   8  /

PL/SQL procedure successfully completed.

17:27:26 SQL> 

Re: SQL Developer query [message #549522 is a reply to message #549479] Sat, 31 March 2012 22:33 Go to previous messageGo to next message
budz
Messages: 3
Registered: March 2012
Junior Member
Black Swan, thanks.

What do I think?
I think you want me to work for an answer.

What do I see?
I see symbols that I do not know the meaning of.

I also see that you are using SQL*plus (I think).

I'm not using SQL*plus, I'm using SQL Developer.
So I not sure what the various symbols do.

I see an @. e.g @xy.
Stumped me on line one. I don't know the significance of this line.

I see a forward slash.
I try it between two anon blocks. Smile

Not sure when to use a slash and when not to use it.
I can have many Create and Drop table statements in a row without slashes.
I can even add ONE create procedure statement OK
But adding two create procedure statements is a problem. Needs a slash.

Do you have a explanation or link that can help out on this one?

Thanks again,
Cheers,
Budz




Re: SQL Developer query [message #549523 is a reply to message #549522] Sat, 31 March 2012 22:51 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>But adding two create procedure statements is a problem. Needs a slash.
>Do you have a explanation or link that can help out on this one?

Since you took the time to observe & experiment, I'll try to explain what happens.

As a general rule a semicolon (";") terminates a "statement"; which can can be DML or PL/SQL statement.
When you have "END;" that signals the end of a block.
So now you need to actually compile the PROCEDURE & that is done by terminating it with a slash character ("/").
in sqlplus you can execute the most recent command by issuing a slash character.

Good Luck!
Re: SQL Developer query [message #549529 is a reply to message #549523] Sun, 01 April 2012 02:38 Go to previous message
budz
Messages: 3
Registered: March 2012
Junior Member
Thanks BS.
Makes sense.
Enough Oracle for today.
Just time to have a quick ride on the bike before the sun goes down.
Cheers,
Budz
Previous Topic: pl/sql developer showing access violation at address 67614564 in module 'ORA-805.dll' (merged 2)
Next Topic: sql developer data modeler version 3.0.0.700 is not backward compatible
Goto Forum:
  


Current Time: Mon Oct 22 00:18:34 CDT 2018