Solved

Fix a procedure

Posted on 2015-02-02
19
173 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 74

Expert Comment

by:sdstuber
ID: 40584528
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
ID: 40584578
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
ID: 40584624
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 40584627
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 74

Expert Comment

by:sdstuber
ID: 40584629
>> 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
ID: 40584699
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 74

Expert Comment

by:sdstuber
ID: 40584711
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
ID: 40584768
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 74

Expert Comment

by:sdstuber
ID: 40584812
your procedure doesn't insert data to that table.

why would you expect the query to return anything?
0
 

Author Comment

by:KamalAgnihotri
ID: 40584866
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 74

Expert Comment

by:sdstuber
ID: 40584898
>>>  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
ID: 40584899
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
ID: 40585090
This is just sample data. Nothing to worry.
0
 

Author Comment

by:KamalAgnihotri
ID: 40586110
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
ID: 40586117
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 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40586208
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
ID: 40586275
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
ID: 40586283
Excellent help from ststuber.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle function to insert records? 15 62
setting local variables in a cursor block 3 28
SQL query to select row with MAX date 7 40
Oracle SQL Developer - SubString 2 23
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

713 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