CODEWITHSUNDEEP
oracle-databaseplsql
Rahul Parit
Rahul Parit

Reputation: 331

Getting Error-ORA-00604, while creating new procedure in oracle sql developer

I am getting error while creating procedure and package. Please anyone help me.

My procedure code-

CREATE OR REPLACE PROCEDURE IPROC(CID IN NUMBER, CNAME IN VARCHAR2, CON IN NUMBER, A_NO IN NUMBER, BAL IN NUMBER, TTYPE VARCHAR2)
AS
BEGIN 
INSERT INTO CUSTOMER_TBL VALUES(CID,CNAME,CON,A_NO,BAL,TTYPE);
END;

Error-

Error report -
ORA-00604: error occurred at recursive SQL level 1

ORA-01653: unable to extend table SYS.PLSCOPE_ACTION$ by 128 in tablespace SYSAUX

00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

Upvotes: 2

Views: 5077

Answers (3)

Aitor
Aitor

Reputation: 3419

If you're experiencing this error only in Sql Developer, try to change, Tools > Preferences > Database > PL/SQL Compiler, the option PLSCope to None. PLSCOPE_SETTINGS controls the compile time collection, cross reference, and storage of PL/SQL source code identifier data.

  • NONE: Disables collection of identifier data.
  • ALL: Enables the collection of all source code identifier data. This is the default in SqlDeveloper.

SqlDeveloper default for PLScope is All, and maybe is causing this error.

Upvotes: 5

anudeepks
anudeepks

Reputation: 1132

If you have acces to the dba_data_files table , This query will show you how much space you have and how much you have utilized. If it is full add another file to the tablespace or ask dba to add another file to it.

SQL> select file_name, sum(bytes)/1024/1024/1024 "current_gb",sum(maxbytes)/1024/1024/1024 "total_gb" from dba_data_files where tablespace_name='SYSAUX' group by file_name;

FILE_NAME                                                    current_gb   total_gb
------------------------------------------------------------ ---------- ----------
C:\AKS\AKDB\ORADATA\RESEARCH\SYSAUX01.DBF                       .546875 31.9999847

To add datafile

Alter tablespace SYSAUX ADD DATAFILE 'C:\AKS\AKDB\ORADATA\RESEARCH\SYSAUX02.DBF' size 100m autoextend of maxsize 2g;

Upvotes: 0

Janis Baiza
Janis Baiza

Reputation: 961

You have no space left in tablespace SYSAUX Either you also have no space left or your HDD or max limits of SYSAUX are reached. Also look at script mentioned in https://dba.stackexchange.com/questions/33645/sysaux-tablespace-is-98, maybe it will help you.

Upvotes: 0

Related Questions