Re: Some way to list all constraints?

From: kaeli <tiny_one_at_NOSPAM.comcast.net>
Date: Wed, 18 Feb 2004 07:47:09 -0600
Message-ID: <MPG.1a9d28d79d683b1c989c3b@nntp.lucent.com>

In article <qb5530h4tv38n9usff3ce7u0l063j0dmic_at_4ax.com>, x_at_y.z enlightened us with...
> Thanks Vu.
>
> Thanks Kaeli.
>
> This stuff is sooooo simple when you know what you're talking about... -)
>
>

Actually, even when you do, it isn't always. :)

I have a few other neat things I found, in case you ever need them...

To reset a sequence:
CREATE or REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2) as

        curr_val INTEGER;
BEGIN

	EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';
	EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' 
INTO curr_val;
	EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by 
-'||curr_val;
	EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' 
INTO curr_val;
	EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by 

1';
END reset_sequence;

To see a sequence value w/o updating it by selecting: CREATE or REPLACE PROCEDURE seeSeqVal (sequencename IN VARCHAR2) as

	curr_val INTEGER;
	str  VARCHAR2(2000) := null;
BEGIN
	str := 'SELECT last_number from user_sequences where 
sequence_name=:1';
	EXECUTE IMMEDIATE str INTO curr_val USING sequencename;
	dbms_output.put_line(to_char(curr_val));

END seeSeqVal;

To call those procedures:
SET SERVEROUTPUT ON
must be typed before invoking...

exec reset_sequence('SEQNAME');
exec seeSeqVal('SEQNAME');

Other neat things to see:
Errors:
SELECT * FROM USER_ERRORS;
Keys:
SELECT index_name FROM user_indexes;
Sequences:
SELECT * from user_sequences;
Triggers:
select trigger_name, trigger_body from user_triggers;

HTH

-- 
--
~kaeli~
Never argue with an idiot! People may not be able to tell 
you apart.
http://www.ipwebdesign.net/wildAtHeart
http://www.ipwebdesign.net/kaelisSpace

Received on Wed Feb 18 2004 - 07:47:09 CST