Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Tracate a Table inside a procedure.

Posted on 2015-02-03
5
Medium Priority
?
185 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 78

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 2000 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

886 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