Solved

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

Posted on 2015-02-19
6
2,369 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 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.

739 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