Solved

Help in fixing ORA-04044: procedure, function, package, or type is not allowed here

Posted on 2015-02-19
6
2,108 Views
Last Modified: 2015-02-19
My procedure is throwing error ORA-04044: procedure, function, package, or type is not allowed here.

Here is the procedure:  

CREATE OR REPLACE PROCEDURE Sars_Of_Interest_04
 IS
  c_row_processed CONSTANT number(4) := 1000;
  v_my_loops  pls_integer;
  v_row_count pls_integer;
  v_Error_Code  Number;
  v_Name_In   Varchar2(100);
  v_Search_Date Date;
  v_Error_Message Varchar2(100);
  Type sSearch_Term IS TABLE OF  Varchar2 (50);
  Type sDoc_Id IS TABLE OF Number (12);
  Type sFilg_Dt IS Table OF Date;
  Type sLST_UPD_TS IS TABLE OF Date;
  Type sLGL_RAW_PRTY_FULL_NM is TABLE OF Varchar2 (200);
  l_sSearch_Term sSearch_Term;
  l_sDoc_Id sDoc_Id;
  l_sFilg_Dt sFilg_Dt;
  l_sLST_UPD_TS sLST_UPD_TS;
  l_sLGL_RAW_PRTY_FULL_NM   sLGL_RAW_PRTY_FULL_NM;  
  e_no_records_found   Exception;      
     
   Begin
   -- Empty the Destination Table
     Execute Immediate ('Truncate Table Sars_Of_Interest_04');    
     v_my_loops := 0;
   -- If the source table (Query_origin) has no records, Raise exeception,
   -- Enter "No Record Found" in Temp_Sars_Of_Interest_04  
        Select count (*) into v_row_count FROM QUERY_ORIGIN_04 ;
        If     v_row_count = 0 Then
           RAISE e_no_records_found;
   -- Create a cursor to hold values from table Query_Origin_04          
    Else
       DECLARE
         CURSOR c1_cur
    IS
       SELECT Name_In, Search_Date
       INTO v_Name_In, v_Search_Date
       FROM QUERY_ORIGIN_04 ;
     BEGIN
        For c1_rec IN c1_cur
    -- Loop through QUERY_ORIGIN_04 to find matching records in Tables
    -- AABASE.NRTV ,  AABASE.FILG_NSTUTN , AABASE.DOC And populate table OUT_SARS_OF_INTEREST_04
      LOOP
       SELECT /*+ PARALLEL(64) */
          c1_rec.NAME_IN SEARCH_TERM, N.DOC_ID DOC_ID, D.FILG_DT FILING_DATE, D.LST_UPD_TS LAST_UPDATE,
          upper(F.LGL_RAW_PRTY_FULL_NM) LGL_RAW_PRTY_FULL_NM
          BULK COLLECT INTO  l_sSearch_Term,  l_sDoc_Id, l_sFilg_Dt, l_sLST_UPD_TS, l_sLGL_RAW_PRTY_FULL_NM
          FROM AABASE.NRTV N,  AABASE.FILG_NSTUTN F, AABASE.DOC D
          WHERE CONTAINS (N.NRTV_TXT, C1_REC.NAME_IN)>0
          AND N.DOC_ID=F.DOC_ID
          AND N.DOC_ID=D.DOC_ID
          AND D.LST_UPD_TS > C1_REC.Search_Date
          GROUP BY c1_rec.NAME_IN, n.doc_id, D.FILG_DT, D.LST_UPD_TS, UPPER(f.lgl_raw_prty_full_nm);
          FORALL i IN 1..l_sSearch_Term.count
          INSERT INTO OUT_SARS_OF_INTEREST_04 VALUES (l_sSearch_Term(i), l_sDoc_Id(i), l_sFilg_Dt(i), l_sLST_UPD_TS(i), l_sLGL_RAW_PRTY_FULL_NM(i));      
      v_my_loops := v_my_loops +1;
      If mod(c1_cur%rowcount, c_row_processed) =0 Then
         NULL;
      End If;
    End Loop;
    Commit;
   End;
  End If;
EXCEPTION
    When e_no_records_found Then
        Insert Into Temp_Sars_Of_Interest_04 (out_Put_SARS, Record_Time) VALUES ('Input_Table has no records to process', sysdate);
        Commit;
    When Others then
       v_Error_Code := SQLCODE;
       v_Error_Message := Substr (SQLERRM, 1, 25);
       Insert Into Log_Table_Sars_Of_Interest_04 (code, message, info, Record_Time)
         Values (v_Error_Code, v_Error_Message, 'Oracle error occured', sysdate);        
Commit;
End Sars_Of_Interest_04;
/
0
Comment
Question by:KamalAgnihotri
  • 3
  • 3
6 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40619146
Either post the table descriptions involved or provide the line number that is generating the error.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40619190
I found it:
Your procedure name is Sars_Of_Interest_04 which is the same as the table name you are trying to truncate in:
Execute Immediate ('Truncate Table Sars_Of_Interest_04');    

So, you are trying to truncate the procedure.
0
 

Author Comment

by:KamalAgnihotri
ID: 40619197
Hi Slightwv,

The procedure compiles successfully. Upon execution, I get ORA-04044  error.



SQL> desc OUT_SARS_OF_INTEREST_04;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------
 SEARCH_TERM                                            VARCHAR2(512 CHAR)
 DOC_ID                                                         NUMBER(38)
 FILG_DT                                                        DATE
 LAST_UPDATE                                              DATE
 LGL_RAW_PRTY_FULL_NM                        VARCHAR2(512 CHAR)

SQL>
SQL> desc Query_origin_04;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------
 NAME_IN                                                        VARCHAR2(4000)
 SEARCH_DATE                                               DATE

SQL>
0
Technology Partners: 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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40619202
>> The procedure compiles successfully. Upon execution, I get ORA-04044  error.

See my post above: http:#a40619190
0
 

Author Comment

by:KamalAgnihotri
ID: 40619263
Hi SlightWV,


Yes, I changed the truncate Table statement to: Truncate Table Out_Sars_Of_Interest_04 and it worked like "charm".

You are the BEST of the Very Best, from East Coast to West Coast.  I guess, at times I need another pair of eyes.

I will close this question. And you get the well deserved points.

Thanks a lot.
0
 

Author Closing Comment

by:KamalAgnihotri
ID: 40619273
Thanks Slightwv.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

685 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