Solved

Tracate a Table inside a procedure.

Posted on 2015-02-03
5
172 Views
Last Modified: 2015-02-03
I would like a table to be trucated inside the procedure listed below. The table (Sars_of_interest) is the destination table.

I would like a commit on the destination table (Please see the comment below.)

CREATE OR REPLACE PROCEDURE Sars_Of_Interest_03
 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_Error_Message Varchar2(100);
  --v_Destination_Table Varchar2(100) := 'SARS_OF_INTEREST';
  Type sSearch_Term IS TABLE OF  Varchar2 (50);
  Type sDoc_Id IS TABLE OF Number (12);
  Type sFilg_Dt 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_sLGL_RAW_PRTY_FULL_NM   sLGL_RAW_PRTY_FULL_NM;  
  e_no_records_found   Exception;      
     
   Begin
     Execute Immediate ('Truncate Table Sars_Of_interest');   --******* I tried this but it does not work******-----
     v_my_loops := 0;
        Select count (*) into v_row_count FROM QUERY_ORIGIN ;
        If     v_row_count = 0 Then
           RAISE e_no_records_found;
    Else
       DECLARE
         CURSOR c1_cur
    IS
       SELECT NAME_IN INTO v_Name_In FROM QUERY_ORIGIN ;
     BEGIN
        For c1_rec IN c1_cur
      LOOP
       SELECT /*+ PARALLEL(64) */
          c1_rec.NAME_IN SEARCH_TERM, N.DOC_ID,D.FILG_DT,
          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_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
          GROUP BY c1_rec.NAME_IN, n.doc_id, D.FILG_DT, UPPER(f.lgl_raw_prty_full_nm);
          FORALL i IN 1..l_sSearch_Term.count
          INSERT INTO SARS_OF_INTEREST VALUES (l_sSearch_Term(i), l_sDoc_Id(i), l_sFilg_Dt(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;  --------**************THis commit should work, but does not appear to be working.
   End;
  End If;
EXCEPTION
    When e_no_records_found Then
        Insert Into Temp_Sars_Of_Interest (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 (code, message, info)
         Values (v_Error_Code, v_Error_Message, 'Oracle error occured');        
Commit;  
End Sars_Of_Interest_03;
/
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
  • 2
5 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40586313
>>--******* I tried this but it does not work******-----

That is the correct way.

Define "does not work"?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40586348
if someone is using the table when you try to truncate it then you will get an error

by using I mean  inserting, updating, deleting, altering or doing anything else that will hold a lock on the table or any rows within the table.
0
 

Author Comment

by:KamalAgnihotri
ID: 40586878
The procedure is working as expected. Thanks to the valuable tips from sdstuber and slightwv.

This question cqan now be closed.
0
 

Author Closing Comment

by:KamalAgnihotri
ID: 40587146
Thanks. For your help, Sdstuber. The Procedure is working as expected.
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…
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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

710 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