Solved

Fix a procedure

Posted on 2015-02-02
19
164 Views
Last Modified: 2015-02-03
I have created the procedure 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_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 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 
     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 (Name_In Varchar2)
    IS 
      SELECT 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);      
-- Commit;
      my_loops := my_loops +1;
      If mod(c1_cur%rowcount, c_row_processed) =0 Then
      --Commit;
      End If;
  End Loop;
  End;
-- Commit;
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;
/

--###################################

Open in new window


Upon compiling, I am getting:

Warning: Procedure created with compilation errors.

Errors for PROCEDURE SARS_OF_INTEREST_03:

LINE/COL ERROR
-------- -----------------------------------------------------------------
43/7     PLS-00103: Encountered the symbol "END" when expecting one of the
         following:
         ( begin case declare exit for goto if loop mod null pragma
         raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         continue close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe purge

47/1     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         ( begin case declare end exit for goto if loop mod null
         pragma raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         continue close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe purge

57/24    PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         end not pragma final instantiable order overriding static
         member constructor map
--##################################

Please help me fix this procedure.
0
Comment
Question by:KamalAgnihotri
  • 11
  • 7
19 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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_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 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
    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(NAME_IN VARCHAR2)
            IS
                SELECT 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);

                -- Commit;
                my_loops := my_loops + 1;

                IF MOD(c1_cur%ROWCOUNT, c_row_processed) = 0
                THEN
                    --Commit;
                    NULL;   /******** I added this, you can't have an empty if block *************/
                END IF;
            END LOOP;
        END; -- Commit;
    END IF;   /********  I added this END IF also ***************/
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
 

Author Comment

by:KamalAgnihotri
Comment Utility
Hi Sdstuber;

Based on your input, the errors are now limited to:


Warning: Procedure created with compilation errors.

Errors for PROCEDURE SARS_OF_INTEREST_03:

LINE/COL ERROR
-------- -----------------------------------------------------------------
28/5     PL/SQL: Statement ignored
28/19    PLS-00306: wrong number or types of arguments in call to 'C1_CUR'


SQL>

--######################################

Please help me fix this one. Thanks a lot.
0
 

Author Comment

by:KamalAgnihotri
Comment Utility
Hi Sdstuber;

I fixed the procedure and it compied successfully.  

Else
    DECLARE
    CURSOR c1_cur
    IS

--##############################

Procedure created.

No errors.

no rows selected


no rows selected


no rows selected
--###############################################
But it does not showany rows in the destination table.

Question, how to get rid of the three "no rows selected"

Thanks a lot.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
This line is the problem

 FOR c1_rec IN c1_cur


 FOR c1_rec IN c1_cur(you_need_to_supply_a_parameter_here)

or, you need to change your cursor to not require a parameter.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>> Question, how to get rid of the three "no rows selected"

whatever script you are running has 3 queries after your procedure

remove those
0
 

Author Comment

by:KamalAgnihotri
Comment Utility
Ihave tried two ways:

1) when I put : For c1_rec IN c1_cur (Name_In varchar2)

I get the errro below.

SQL> show errors;
Errors for PROCEDURE SARS_OF_INTEREST_03:

LINE/COL ERROR
-------- -----------------------------------------------------------------
28/35    PLS-00103: Encountered the symbol "VARCHAR2" when expecting one
         of the following:
         . ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
         <an exponent (**)> <> or != or ~= >= <= <> and or like like2
         like4 likec as between from using || multiset member
         submultiset
         The symbol "." was substituted for "VARCHAR2" to continue.

--###################################################
When I put:  For c1_rec IN c1_cur (Name_In)

I get the error below.

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE SARS_OF_INTEREST_03:

LINE/COL ERROR
-------- ---------------------------------------------------------
28/5     PL/SQL: Statement ignored
28/27    PLS-00201: identifier 'NAME_IN' must be declared
SQL>
--#############################

I guess I just a few minutes away from the solution. Plesae help me.

Thanks.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
post the full code you are using.

NAME_IN looks like it's supposed to be a parameter to your query.
You can't simply remove it.

If you do, you must your adjust the query so it doesn't need it anymore.
0
 

Author Comment

by:KamalAgnihotri
Comment Utility
Here is the full code. And it compiles successfully.

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);  --************** I added this one --****************
  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 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
     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 ;   --************** I added this one --****************
     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);      
-- Commit;
      v_my_loops := v_my_loops +1;
      If mod(c1_cur%rowcount, c_row_processed) =0 Then
      --Commit;
      NULL;
      End If;
  End Loop;
  End;
  End If;
-- Commit;
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;
/

--#######################################################
Here is the output after creating the procedure.

Procedure created.

SQL>
--#######################################
When I execute the procedure, I get:

SQL> Execute Sars_Of_Interest_03;

PL/SQL procedure successfully completed.

--###########################################

After the successful compilation of of the Procedure,
and Upon executing the select below, I expect to see about 15 rows. However,  I get

SQL> Select * from SARS_OF_INTEREST;

no rows selected

--################################################################
When I execute this code..........This works fine and I get the 15 rows in the destination table as expected.

DECLARE
CURSOR c1_cur
IS
    SELECT NAME_IN FROM QUERY_ORIGIN ;            
c_rowprocessed CONSTANT number(4) := 1000;
my_loops pls_integer;

BEGIN
   my_loops := 0;
   FOR c1_rec IN c1_cur
   LOOP

INSERT
   INTO SARS_OF_INTEREST
       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
          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);
commit;
   my_loops := my_loops +1;
      IF mod(c1_cur%rowcount, c_rowprocessed) = 0 then
      commit;
      END IF;
   END LOOP;
COMMIT;
END;
/
--##############################################
I get

PL/SQL procedure successfully completed.


SEARCH_TERM      DOC_ID FILG_DT   LGL_RAW_PRTY_FULL_NM
------------ ---------- --------- ----------------------------------------
{UCALSA}     1.0037E+10 05-DEC-13 DEUTSCHE BANK TRUST COMPANY AMERICAS
UCALSA       1.0037E+10 05-DEC-13 DEUTSCHE BANK TRUST COMPANY AMERICAS
JUAN QUIROS    30654222 26-JUN-06 WESTERN UNION FINANCIAL SERVICES
JUAN QUIROS    31016261 27-OCT-06 WESTERN UNION FINANCIAL SERVICES
JUAN QUIROS    30583048 01-JUN-06 WESTERN UNION FINANCIAL SERVICES
JUAN QUIROS    33019728 11-JUN-08 KROGER CO
JUAN QUIROS    28292333 12-SEP-03
JUAN QUIROS    28613588 22-MAR-04 WESTERN UNION FINANCIAL SERVICES
JUAN QUIROS    28562435 26-FEB-04 WESTERN UNION FINANCIAL SERVICES
JUAN QUIROS    29189885 07-JAN-05 WESTERN UNION FINANCIAL SERVICES
JUAN QUIROS    33335038 12-SEP-08 WESTERN UNION FINANCIAL SERVICES
JUAN QUIROS    36182734 15-NOV-10 FLAGSTAR BK
JUAN QUIROS    30576077 31-MAY-06 WESTERN UNION FINANCIAL SERVICES
JUAN QUIROS    31127340 30-NOV-06 WESTERN UNION FINANCIAL SERVICES
JUAN QUIROS    31268060 23-JAN-07 WESTERN UNION FINANCIAL SERVICES

15 rows selected.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
your procedure doesn't insert data to that table.

why would you expect the query to return anything?
0
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.

 

Author Comment

by:KamalAgnihotri
Comment Utility
The destination table SARS_OF_INTEREST has 4 columns, which are:

l_sSearch_Term,  l_sDoc_Id, l_sFilg_Dt, l_sLGL_RAW_PRTY_FULL_NM

The code  BULK COLLECT  INTO, takes values from the cursor c1_cur and loops through the Table Query_origin and upon finding matching records in tables,
 
AABASE.NRTV N,  AABASE.FILG_NSTUTN F, AABASE.DOC D  (specified in From Clause) should enter those values in Table SARS_OF_INTEREST.

I can the issue, as I am referencing the columns to be pouplated, but not the table. We are almost there. I don't think you entertain, phone calls, if you do, please let me know the number.

Thanks.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>>  I don't think you entertain, phone calls, if you do, please let me know the number.

even if I did, it would not be appropriate to take the question outside the EE framework.

to do a bulk insert add this after your bulk collect


forall i in 1..  l_sSearch_Term.count
 insert into  SARS_OF_INTEREST
      l_sSearch_Term(i), l_sDoc_Id(i), l_sFilg_Dt(i), l_sLGL_RAW_PRTY_FULL_NM(i);
0
 

Author Comment

by:KamalAgnihotri
Comment Utility
hi sdstuber, I am leaving for the day, and can not test the procedure remotely. Hence appriciate your help to fix this tomorrow.

Once again, thanks a lot.
0
 

Author Comment

by:KamalAgnihotri
Comment Utility
This is just sample data. Nothing to worry.
0
 

Author Comment

by:KamalAgnihotri
Comment Utility
Hi sdstuber,

Thanks a lot. The procedure works and populates the Destination Table.

I would to delete all records from the destination table and like to add "Truncate Table Sars_Of_Interest;"
WITH IN the procedure so that the destination table has no records BEFORE the procedure starts compiling. This way, I am sure there are no existing records in  destination table.

I have tried to put a commit at various palces in the code but need your help to place the commit where the records, after being inserted into the destination table are commited.

Thanks a lot.
0
 

Author Comment

by:KamalAgnihotri
Comment Utility
After the procedure has comiled successfully, When I issue the statement, Truncate Table Sars_Of_interest;
I get error shown below.

--##########################################

Procedure created.

SQL> Truncate Table Sars_Of_interest;
Truncate Table Sars_Of_interest
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL>
--################################################

The successfully compiled procedure looks like this.

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);
  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
     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;
   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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
please stick to a single question per question thread.

This thread is about the procedure.
Does the procedure compile and do what you intended?  If not, what else do you need help with?


Open a new question about truncates, selects or other problems that happen outside of the procedure
0
 

Author Comment

by:KamalAgnihotri
Comment Utility
Yes, the procedure compiles.

I will open another thread. If you can pick that up, that be really great.
thanks a lot.

This question can now be closed.
0
 

Author Closing Comment

by:KamalAgnihotri
Comment Utility
Excellent help from ststuber.
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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 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

9 Experts available now in Live!

Get 1:1 Help Now