My Oracle Support Banner

Create Stored Procedure For Logon Trigger with user SYSTEM gives ORA-00942 (Doc ID 730793.1)

Last updated on NOVEMBER 29, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

Creating a audit table and Stored Procedure for LOGON Trigger as SYSTEM

connect system/<PASSWORD>
CREATE TABLE logonaudittable
(
event VARCHAR2(10),
sid NUMBER,
serial# NUMBER,
timestamp DATE,
username VARCHAR2(30),
osuserid VARCHAR2(30),
machinename VARCHAR2(64)
)
tablespace QC_DATA

Table created.

SQL>create or replace procedure sp1
2 is
3 machinename VARCHAR2(64);
4 osuserid VARCHAR2(30);
5 v_sid NUMBER(10);
6 v_serial NUMBER(10);
7 CURSOR c1 IS
8 SELECT sid, serial#, osuser, machine
9 FROM v$session WHERE audsid = userenv('sessionid');
10 BEGIN
11 OPEN c1;
12 FETCH c1 INTO v_sid, v_serial, osuserid, machinename;
13 INSERT INTO logonaudittable VALUES ( 'LOGON', v_sid, v_serial, sysdate,
14 user, osuserid, machinename );
15 CLOSE c1;
16 END;
17 /

Warning: Procedure created with compilation errors.

SQL>show errors
Errors for PROCEDURE SP1:

LINE/COL ERROR
-------- --------------------------------------------------
8/1 PL/SQL: SQL Statement ignored

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.