Create table error

Mathhew

unread,

Nov 14, 2002, 11:45:28 AM11/14/02

to

Hi,

I have come across this error when ever we try to create a table:

create table test ( test1 varchar(1))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-29540: class oracle/CDC/PublishApi does not exist
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 0
ORA-06512: at line 4

This has just started to happened, as the database had been working fine for
quite some time. I have tried to find posts related to this but could not
find any.

Does anybody know what this error is about and what could have caused this?

Thanks,

Mat

Daniel Morgan

unread,

Nov 14, 2002, 12:05:59 PM11/14/02

to

Mathhew wrote:

What version and edition of Oracle?
What platform and operating system?

Daniel Morgan

Mathhew

unread,

Nov 15, 2002, 5:34:27 AM11/15/02

to

Sorry, here are the versions & platform:

Oracle:

Oracle 9i - Vers: 9.0.1.0.0

System:

Intel P4 - Redhat Linux 7.2

Thanks,

Mat

"Daniel Morgan" <dmo...@exesolutions.com> wrote in message
news:3DD3D7F3...@exesolutions.com...

Bert Bear

unread,

Nov 15, 2002, 11:32:18 PM11/15/02

to

Mat,

1) I found two references to ORA-00604 and ORA-29540 on Goggle. The second
one might offer some insight:

http://lists.suse.com/archive/suse-oracle/2001-Sep/0237.html

http://orafaq.net/msgboard/server/messages/9999.htm

If _SYSTEM_TRIG_ENABLED is not set to FALSE then you may encounter
[BUG:1362374] which can cause ORA-604 , ORA-6553 , PLS-213 in package
STANDARD errors when opening the database under Oracle8i/9i.

There are many potential problem scenarios if you have system triggers in
place when performing dictionary maintenance operations. Some of the more
common symptoms are described below. In most cases setting
_SYSTEM_TRIG_ENABLED=FALSE and re-performing the operation will allow you to
proceed.
I also found some more on _SYSTEM_TRIG_ENABLED at the URL:
http://www.orafaq.com/parms/parm727.htm NOTE: This is an internal Oracle
parameter. Do NOT use it unless instructed to do so by Oracle Support.
Playing with this parameter may be harmful.

I also found:
http://216.239.53.100/search?q=cache:tJaCHjhV-wUC:download.oracle.com/docs/p
df/A87386_02.pdf+SYSTEM_TRIG_ENABLED&hl=en&lr=lang_en&ie=UTF-8

Known Upgrade Issues

Oracle8 Release 3 (8.1.7) for Linux Intel must be installed in a new
ORACLE_HOME. If you try to upgrade older versions of Oracle8 to Release 3
(8.1.7) in the same ORACLE_HOME, you will get irreparable errors. This
functionality is not supported in Release 3 (8.1.7) for Linux Intel. I
Before performing either a manual database upgrade or an automated database
upgrade using Oracle Data Migration Assistant, make sure that _
SYSTEM_TRIG_ENABLED = FALSE in the initialization parameter file. After
completing the database portion of the upgrade, you must either remove the
_SYSTEM_TRIG_ENABLED from the initialization parameter file or set it to
TRUE before attempting any other upgrade steps, such as upgrading Oracle
JServer. Refer to the Oracle8i Migration Release 3 (8.1.7) guide for more
information about upgrade and migration.

----------

2) It worked (new installation) on 9.2.0.1.0 at Windows/XP Professional SR
1 using the SCOTT username. Of course, you're on 9.0.1.0.0 and Linux, so
there are obvious BIG differences!

C:\Documents and Settings\Bertram Moshier>sqlplus scott

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 15 21:51:49 2002

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> create table test (test1 varchar(1));

Table created.

SQL>

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"Mathhew" <mathhew....@tma.co.uk> wrote in message
news:3dd3d2b9$0$82563$7b0f...@reader.news.newnet.co.uk...

Patrick de Jong

unread,

Dec 1, 2002, 4:23:42 PM12/1/02

to

You have to use varchar2(1) or char(1) in Oracle.
If you have used DB2 or SQl server the use varchar. Oracle uses varchar2()
and char()
Good luck with it.

Greetz
Patrick

"Mathhew" <mathhew....@tma.co.uk> schreef in bericht
news:3dd3d2b9$0$82563$7b0f...@reader.news.newnet.co.uk...

Wanderley M Ceschim

unread,

Dec 1, 2002, 9:02:10 PM12/1/02

to

VARCHAR still exists. From the "Oracle9i SQL Reference":

"VARCHAR Datatype

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype.
Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the
future, VARCHAR might be defined as a separate datatype used for
variable-length character strings compared with different comparison
semantics."

The package is part of Oracle9i's change data capture API (CDC). As to
why it's failing, I have no idea. But the VARCHAR datatype shouldn't be
a problem for Oracle.