Solved

Tracate a Table inside a procedure.

Posted on 2015-02-03
5
166 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
  • 2
5 Comments
 
LVL 76

Expert Comment

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

That is the correct way.

Define "does not work"?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks. For your help, Sdstuber. The Procedure is working as expected.
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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

728 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

8 Experts available now in Live!

Get 1:1 Help Now