Solved

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

Posted on 2004-03-30
11
1,199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 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