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

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.

maybe you should post your code ?

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.

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

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';

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;

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.

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;

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.

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.