ORA-06512: at "SYS.STANDARD"

[Giani]

Hi DBAs,

I am on Oracle8i Release 8.1.7.4.0.
Currently I am having below problem on dev box.

SQL>connect usert
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.STANDARD", line 609
ORA-06512: at line 7

This is the first time I hit this problem - how do I do to solve this issue

Thanks in advance.

Regards,

[marist89]

Hmmm. Odds are your data dictionary is messed up.

[Giani]

Hi Jeff,

Thanks for the input.

Here is what got from trace file when I connected as internal :
-----------------
*** 2003-04-04 16:22:13.951
*** SESSION ID:(20.571) 2003-04-04 16:22:13.948
Error in executing triggers on connect internal
*** 2003-04-04 16:22:13.951
ksedmp: internal or fatal error
-----------------

Thanks to the above alert I was able to findout which SYS trigger was causing problem.
so it was after logon trigger written a dev DBA. He said that it worked before and
users were able to logon...


SQL>sho user
USER is "SYS"
SQL>select 'Create or Replace trigger ',description,trigger_body
2 from user_triggers where trigger_name = 'RESTRICT_IP';

Create or Replace trigger
restrict_ip
after logon on database
declare
c_exclude varchar2(16) := ' ';
c_exclude1 varchar2(16) := '19.40.60.58';
c_exclude2 varchar2(16) := '19.9.100.1';
c_exclude3 varchar2(16) := '19.9.100.2';
begin
if ( sys_context('userenv','ip_address') = c_exclude1 )
then
raise_application_error( -20001, 'connection refused' );
end if;
if ( sys_context('userenv','ip_address') = c_exclude2 )
then
raise_application_error( -20001, 'connection refused' );
end if;
if ( sys_context('userenv','ip_address') = c_exclude3 )
then
raise_application_error( -20001, 'connection refused' );
end if;
end;


1 row selected.

SQL>alter trigger RESTRICT_IP disable ;

Trigger altered.

SQL>connect usert
Enter password:
Connected.

SQL>connect internal
Connected.
SQL>alter trigger RESTRICT_IP enable ;

Trigger altered.

SQL>connect usert
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.STANDARD", line 609
ORA-06512: at line 7

Is there anyway I can write this trigger properly ?

Regards,

[jmodic]

Originally posted by Giani
Is there anyway I can write this trigger properly ?

You mean "Is there anyway I can write this trigger properly, so it does not look like spagheti code"?

Sure, something like:

Create or Replace trigger
restrict_ip
after logon on database
declare
  c_exclude1 varchar2(16) := '19.40.60.58';
  c_exclude2 varchar2(16) := '19.9.100.1';
  c_exclude3 varchar2(16) := '19.9.100.2';
begin
  if ( sys_context('userenv','ip_address')
    IN (c_exclude1,c_exclude2,c_exclude3 )
  then
    raise_application_error(-20001,'connection refused');
  end if;
end;

It performs exactly the same thing your trigger does, but doesn't it look much more compact?

Now I don't thing the triger is what is causing your troubles, the trigger is just the manifestation of the real problem you have in your database. Because I'm almost certain the real reason for your troubles lies in the table SYS.DUAL!

What does this query return:
SELECT COUNT(*) FROM dual;

I bet it returns 2 or more. Someone must have inserted you aditional rows in your table DUAL. Delete all rows but one from there and you'll be fine.

[Giani]

Thanks a lot jmodic!
You are absolutely right, my problem was on dual table.

Regards,