Kamal Agnihotri
asked on
Tracate a Table inside a procedure.
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;
/
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_
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_
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
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
Values (v_Error_Code, v_Error_Message, 'Oracle error occured');
Commit;
End Sars_Of_Interest_03;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The procedure is working as expected. Thanks to the valuable tips from sdstuber and slightwv.
This question cqan now be closed.
This question cqan now be closed.
ASKER
Thanks. For your help, Sdstuber. The Procedure is working as expected.
That is the correct way.
Define "does not work"?