?
Solved

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

Posted on 2015-02-19
6
Medium Priority
?
3,184 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
[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
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 2000 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

777 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