Solved

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

Posted on 2015-02-19
6
1,146 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 76

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 76

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

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

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

706 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

17 Experts available now in Live!

Get 1:1 Help Now