[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2015-02-19
6
Medium Priority
?
3,757 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 Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

656 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