Solved

PLS-00801: internal error [74402] .    Unknown error

Posted on 2004-03-30
11
1,191 Views
Last Modified: 2012-06-27
I am getting a the error - PLS-00801: internal error [74402]  - when creating a function. What does it mean and how do I get rid of it?

Thanks
0
Comment
Question by:mjsurma
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10712822
This is from Oracle documentation:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76999/pcmus.htm#1656

PLS-00801 internal error [string]

Cause: This is a generic internal error that might occur during compilation or execution. The first parameter is the internal error number.

Action: Report this error as a bug to Oracle Customer Support.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10712864
maybe you should post your code ?
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10713280
Since this is a generic error, we cannot be sure of the reason why you are getting this error. If you post your code, we might be able to help.
0
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 10713905
SQL> create or replace package body mypack as  
  2    function get_task (Atask IN OUT task) return integer is  
  3      val  integer default 0;  
  4    begin  
  5      if task%ISOPEN THEN  
  6        null;  
  7      else  
  8      open Atask for  
  9        select ename, empno from emp where ename='FORD'  
 10          order by empno;  
 11      END IF;  
 12      val := 0;  
 13      return (val);  
 14    EXCEPTION WHEN OTHERS THEN  
 15      val := SQLCODE;  
 16      return(val);  
 17    end get_task;  
 18  end mypack;  
 19  /  
 



If you mistakenly reference the cursor type instead of the cursor  
variable, you get PLS-00801: internal error [74303].  In the example  
given in the problem description, the statement  
  if task%ISOPEN THEN  
should be  
  if Atask%ISOPEN THEN      

0
 

Author Comment

by:mjsurma
ID: 10722962
My code. I apologize for the delayed response. Schwertner I think you are on to something.

FUNCTION F_PDMDCS_REQUEST_INSERT (p_USAGEID IN NUMBER,p_DOC_NUMBER IN VARCHAR2
                            ,P_REMARKS IN VARCHAR2,P_REVISION IN VARCHAR2
                            ,P_CAGE IN CHAR,P_NSN IN CHAR,P_REASON_TYPE IN VARCHAR2
                            ,P_SOLICITATIONNUM IN VARCHAR2,P_CLOSINGDATE IN DATE
                            ,P_CONTRACTNUM IN VARCHAR2,P_AWARDEECAGE IN VARCHAR2)
    RETURN NUMBER IS


--  Purpose: Briefly explain the functionality of the procedure
--
--  MODIFICATION HISTORY
--  Person      Date        Comments
--  ---------   ------      -------------------------------------------
--  MJS         3/26/2004   New

--  Declare program variables as shown above
    dmlstr                  VARCHAR2(1024);

--  Error handling variables
    load_step               VARCHAR2(80);
    startTime               VARCHAR2(20) := TO_CHAR(SYSDATE, 'DD MON RR HH24:MI:SS');
    endTime                 VARCHAR2(20);
    sys_code                NUMBER := 0;
    sys_msg                 VARCHAR2(256);
    log_msg                 VARCHAR2(256);
    seq_num                 SEQ_PDMDCS_ACTIVITY_LOG%TYPE;
    tname                   VARCHAR2(80) := 'PDMDCS_REQUEST_LOG';
      Input_Parm_err                  EXCEPTION;
    retval_requestid        NUMBER;
--  CONSTANTS
    PROC_NAME   CONSTANT VARCHAR2(30) := 'PDMDCS_F_REQUEST_INSERT';

PROCEDURE   INSERT_REQUEST_REC  (tname VARCHAR2
                            ,R_REQUESTID NUMBER
                            ,R_USAGEID NUMBER
                            ,R_DOC_NUMBER VARCHAR2
                            ,R_REVISION VARCHAR2
                            ,R_CAGE CHAR
                            ,R_NSN CHAR
                            ,R_REASON_TYPE VARCHAR2
                            ,R_REQUEST_SUBMITTED DATE
                            ,R_IMS_FILESIZE NUMBER
                            ,R_DISPOSITION VARCHAR2
                            ,R_SOLICITATIONNUM VARCHAR2
                            ,R_CLOSINGDATE VARCHAR2
                            ,R_CONTRACTNUM VARCHAR2
                            ,R_AWARDEECAGE VARCHAR2
                            ,R_REMARKS VARCHAR2
                            ,R_REQUEST_COMPLETE DATE)
                    IS
dmlstr    VARCHAR2(1024);
BEGIN
dmlstr :=  'Insert into  ' || tname || ' (R_REQUESTID,R_USAGEID,R_DOC_NUMBER,R_REVISION,R_CAGE
                                        ,R_NSN,R_REASON_TYPE,R_REQUEST_SUBMITTED,R_IMS_FILESIZE
                                        ,R_DISPOSITION,R_SOLICITATIONNUM,R_CLOSINGDATE
                                        ,R_CONTRACTNUM,R_AWARDEECAGE,R_REMARKS,R_REQUEST_COMPLETE)
                Values ('|| R_REQUESTID || ','|| R_USAGEID || ','|| '''' || R_DOC_NUMBER || '''' ||',
                        '|| R_REVISION || ','|| R_CAGE || ',' || R_NSN || ','|| R_REASON_TYPE || ',
                        '|| R_REQUEST_SUBMITTED || ',NULL,NULL,'|| R_SOLICITATIONNUM || ',
                        '|| R_CLOSINGDATE || ','|| R_CONTRACTNUM || ',' || R_AWARDEECAGE|| ',
                        '|| '''' || R_REMARKS || '''' ||',NULL)';
EXECUTE IMMEDIATE dmlstr;   -- Execute the command string
COMMIT;
END;
BEGIN
/*    load_step := 'Step 1: Processing Parameters ';
    IF p_usageid IS NULL OR p_disposition IS NULL THEN
        RAISE Input_Parm_err;
    END IF;*/

    load_step := 'Step 2: Get next sequence Number ';
    SELECT SEQ_PDMDCS_ACTIVITY_LOG.NextVal
      INTO retval_requestid
      FROM PDMDCS_REQUEST_LOG;

    load_step := 'Step 3: Build insert data ';
    INSERT_REQUEST_REC  (tname,retval_requestid,P_USAGEID,P_DOC_NUMBER,P_REVISION,P_CAGE,P_NSN,P_REASON_TYPE,
                        SYSDATE,NULL,NULL,P_SOLICITATIONNUM,P_CLOSINGDATE,P_CONTRACTNUM,
                        P_AWARDEECAGE,P_REMARKS,NULL);

    IF SQLCODE = 0 THEN
        RETURN(retval_requestid);
    END IF;

EXCEPTION
    WHEN no_data_found THEN
        ROLLBACK;
        endTime := TO_CHAR(SYSDATE, 'DD MON RR HH24:MI:SS');
        sys_code := TO_CHAR(SQLCODE);
        sys_msg := SUBSTR(SQLERRM, 1, 256);
        log_msg := 'PDMDCS SP ' || PROC_NAME || ' failed at  "' || load_step || '" ' ||
                   '.   Started: '  || startTime || '   Ended: '    || endTime;
        DBMS_OUTPUT.PUT_LINE('.');
        DBMS_OUTPUT.PUT_LINE(log_msg);
        DBMS_OUTPUT.PUT_LINE('.');
        INSERT INTO PDMDCS_ACTIVITY_LOG VALUES (SEQ_PDMDCS_ACTIVITY_LOG.NextVal, sys_code, sys_msg, SYSDATE, log_msg);
    WHEN Input_Parm_err THEN
        ROLLBACK;
        dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
        endTime := TO_CHAR(SYSDATE, 'DD MON RR HH24:MI:SS');
        sys_code := TO_CHAR(SQLCODE);
        sys_msg := SUBSTR(SQLERRM, 1, 256);
        log_msg := 'PDMDCS procedure ' || PROC_NAME || ' failed at  "' || load_step || '"' ||
                        '.   Started: '  || startTime || '   Ended: '    || endTime;
        DBMS_OUTPUT.PUT_LINE('.');
        DBMS_OUTPUT.PUT_LINE(log_msg);
        DBMS_OUTPUT.PUT_LINE('.');
        INSERT INTO PDMDCS_ACTIVITY_LOG VALUES (SEQ_PDMDCS_ACTIVITY_LOG.NextVal, sys_code, sys_msg, SYSDATE, log_msg);
    WHEN OTHERS THEN
        ROLLBACK;
        dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
        -- RAISE;
        endTime := TO_CHAR(SYSDATE, 'DD MON RR HH24:MI:SS');
        sys_code := TO_CHAR(SQLCODE);
        sys_msg := SUBSTR(SQLERRM, 1, 256);
        log_msg := 'PDMDCS SP ' || PROC_NAME || ' failed at  "' || load_step || '"' ||
                        '.   Started: '  || startTime || '   Ended: '    || endTime;
        DBMS_OUTPUT.PUT_LINE('.');
        DBMS_OUTPUT.PUT_LINE(log_msg);
        DBMS_OUTPUT.PUT_LINE('.');
        INSERT INTO PDMDCS_ACTIVITY_LOG VALUES (SEQ_PDMDCS_ACTIVITY_LOG.NextVal, sys_code, sys_msg, SYSDATE, log_msg);
    COMMIT;
END; -- Procedure
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:Helena Marková
ID: 10723025
This is strange - there is no return value and no END;

FUNCTION F_PDMDCS_REQUEST_INSERT (p_USAGEID IN NUMBER,p_DOC_NUMBER IN VARCHAR2
                            ,P_REMARKS IN VARCHAR2,P_REVISION IN VARCHAR2
                            ,P_CAGE IN CHAR,P_NSN IN CHAR,P_REASON_TYPE IN VARCHAR2
                            ,P_SOLICITATIONNUM IN VARCHAR2,P_CLOSINGDATE IN DATE
                            ,P_CONTRACTNUM IN VARCHAR2,P_AWARDEECAGE IN VARCHAR2)
    RETURN NUMBER IS


--  Purpose: Briefly explain the functionality of the procedure
--
--  MODIFICATION HISTORY
--  Person      Date        Comments
--  ---------   ------      -------------------------------------------
--  MJS         3/26/2004   New

--  Declare program variables as shown above
    dmlstr                  VARCHAR2(1024);

--  Error handling variables
    load_step               VARCHAR2(80);
    startTime               VARCHAR2(20) := TO_CHAR(SYSDATE, 'DD MON RR HH24:MI:SS');
    endTime                 VARCHAR2(20);
    sys_code                NUMBER := 0;
    sys_msg                 VARCHAR2(256);
    log_msg                 VARCHAR2(256);
    seq_num                 SEQ_PDMDCS_ACTIVITY_LOG%TYPE;
    tname                   VARCHAR2(80) := 'PDMDCS_REQUEST_LOG';
     Input_Parm_err               EXCEPTION;
    retval_requestid        NUMBER;
--  CONSTANTS
    PROC_NAME   CONSTANT VARCHAR2(30) := 'PDMDCS_F_REQUEST_INSERT';
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10723060
It would look like this:

FUNCTION F_PDMDCS_REQUEST_INSERT (p_USAGEID IN NUMBER,p_DOC_NUMBER IN VARCHAR2
                            ,P_REMARKS IN VARCHAR2,P_REVISION IN VARCHAR2
                            ,P_CAGE IN CHAR,P_NSN IN CHAR,P_REASON_TYPE IN VARCHAR2
                            ,P_SOLICITATIONNUM IN VARCHAR2,P_CLOSINGDATE IN DATE
                            ,P_CONTRACTNUM IN VARCHAR2,P_AWARDEECAGE IN VARCHAR2)
    RETURN NUMBER IS
--  Purpose: Briefly explain the functionality of the procedure
--
--  MODIFICATION HISTORY
--  Person      Date        Comments
--  ---------   ------      -------------------------------------------
--  MJS         3/26/2004   New

--  Declare program variables as shown above
    dmlstr                  VARCHAR2(1024);

--  Error handling variables
    load_step               VARCHAR2(80);
    startTime               VARCHAR2(20) := TO_CHAR(SYSDATE, 'DD MON RR HH24:MI:SS');
    endTime                 VARCHAR2(20);
    sys_code                NUMBER := 0;
    sys_msg                 VARCHAR2(256);
    log_msg                 VARCHAR2(256);
    seq_num                 SEQ_PDMDCS_ACTIVITY_LOG%TYPE;
    tname                   VARCHAR2(80) := 'PDMDCS_REQUEST_LOG';
     Input_Parm_err               EXCEPTION;
    retval_requestid        NUMBER;
--  CONSTANTS
    PROC_NAME   CONSTANT VARCHAR2(30) := 'PDMDCS_F_REQUEST_INSERT';
BEGIN
...
   RETURN retval_requestid;

END F_PDMDCS_REQUEST_INSERT;
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10723211
Well, @mjsurma:

talking about modularize your plsql code!!!

You cannot embed the implementation of a stored procedure inside the function....

split them into two steps

1. create the stored procedure INSERT_REQUEST_REC   first
2. then create the function F_PDMDCS_REQUEST_INSERT , and call the Stored procedure inside of it if needed.
0
 

Author Comment

by:mjsurma
ID: 10724192
I resolved this with a coworker. I know what caused it. I declared  seq_num                 SEQ_PDMDCS_ACTIVITY_LOG%TYPE;

I do not know why Oracle doesn't like it but it does like:
 seq_num                 number;
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10724231
glad you have resolved it.

FYI, in case you are still wondering why Oracle does not like this line:

seq SEQ_PDMDCS_ACTIVITY_LOG%TYPE; --because SEQ_PDMDCS_ACTIVITY_LOG is a table. you can only use %ROWTYPE after the table.

if you want to declare variable "seq" has the same data type as one of the columns in table SEQ_PDMDCS_ACTIVITY_LOG
you should use :


seq SEQ_PDMDCS_ACTIVITY_LOG.<column_name>%TYPE; --replace <column_name> with the real column name.
0
 
LVL 1

Expert Comment

by:Dazman
ID: 10752095
As a performance tip I would strongly suggest you alter:

dmlstr :=  'Insert into  ' || tname || ' (R_REQUESTID,R_USAGEID,R_DOC_NUMBER,R_REVISION,R_CAGE
                                        ,R_NSN,R_REASON_TYPE,R_REQUEST_SUBMITTED,R_IMS_FILESIZE
                                        ,R_DISPOSITION,R_SOLICITATIONNUM,R_CLOSINGDATE
                                        ,R_CONTRACTNUM,R_AWARDEECAGE,R_REMARKS,R_REQUEST_COMPLETE)
                Values ('|| R_REQUESTID || ','|| R_USAGEID || ','|| '''' || R_DOC_NUMBER || '''' ||',
                        '|| R_REVISION || ','|| R_CAGE || ',' || R_NSN || ','|| R_REASON_TYPE || ',
                        '|| R_REQUEST_SUBMITTED || ',NULL,NULL,'|| R_SOLICITATIONNUM || ',
                        '|| R_CLOSINGDATE || ','|| R_CONTRACTNUM || ',' || R_AWARDEECAGE|| ',
                        '|| '''' || R_REMARKS || '''' ||',NULL)';
EXECUTE IMMEDIATE dmlstr;   -- Execute the command string


to use bind variables or youll find your Oracle DBA getting a bit peeved when the database slows to a crawl and constantly gets shared pool issues ;-)

eg:
dmlstr :=  'Insert into  ' || tname || ' (R_REQUESTID,R_USAGEID,R_DOC_NUMBER,R_REVISION,R_CAGE
                                        ,R_NSN,R_REASON_TYPE,R_REQUEST_SUBMITTED,R_IMS_FILESIZE
                                        ,R_DISPOSITION,R_SOLICITATIONNUM,R_CLOSINGDATE
                                        ,R_CONTRACTNUM,R_AWARDEECAGE,R_REMARKS,R_REQUEST_COMPLETE)
                Values (:b1, :b2, :b3, b4, .............,NULL)';
EXECUTE IMMEDIATE dmlstr USING R_REQUESTID, R_USAGEID, R_REVISION, ............;   -- Execute the command string


and you sure you want that COMMIT in there? the when others does a rollback which will have no effect if your COMMIT gets run.

Regards,

Daz.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now