Solved

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

Posted on 2004-03-30
11
1,190 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

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.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

758 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

23 Experts available now in Live!

Get 1:1 Help Now