Solved

Fix ORA_29902: error in execution

Posted on 2015-02-16
17
183 Views
Last Modified: 2015-03-03
Hi Experts,

Below  procedure compiles successfully but on execution throws ORA-29902: error in execution.

--###################################
Upon compiling th eprocedure below I get:
Procedure created.

SQL> Execute Near_Sars_Of_Interest_01;

Upon execution I get:
PL/SQL procedure successfully completed.

SQL>

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

The Procedure is:          

CREATE OR REPLACE PROCEDURE Near_Sars_Of_Interest_01
 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(4000);
  v_Search_Date Date;
  v_Error_Message Varchar2(100);
  Type sSearch_Term IS TABLE OF  Varchar2 (4000);
  Type sDoc_Id IS TABLE OF  Number (12);
  Type sFilg_Dt IS Table OF Date;
  Type sLST_UPD_TS IS Table OF Date;
  l_sSearch_Term sSearch_Term;
  l_sFilg_Dt sFilg_Dt;
  l_sDOC_Id   sDoc_Id;
  l_sLST_UPD_TS   sLST_UPD_TS;
  e_no_records_found   Exception;      
     
   Begin
   -- Empty the Destination Table
     Execute Immediate ('Truncate Table Sars_Of_Interest_01');    
     v_my_loops := 0;
   -- If the source table (Query_origin) has no records, Raise exeception,
   -- Enter "No Record Found" in Temp_Sars_Of_Interest  
        Select count (*) into v_row_count FROM QUERY_ORIGIN_01 ;
        If     v_row_count = 0 Then
           RAISE e_no_records_found;
   -- Create a cursor to hold values from table QUERY_ORIGIN_01          
    Else
       DECLARE
         CURSOR c1_cur
    IS
       SELECT NAME_IN, Search_Date
       INTO v_Name_In, v_Search_Date FROM QUERY_ORIGIN_01 ;
     BEGIN
        For c1_rec IN c1_cur
    -- Loop through QUERY_ORIGIN to find matching records in Tables
    -- BASE.NRTV, BASE.DOC And populate table SARS_OF_INTEREST_01
      LOOP
       SELECT
          c1_rec.NAME_IN SEARCH_TERM, N.DOC_ID DOC_ID, D.FILG_DT Filing_date, D.LST_UPD_TS LAST_UPDATE    
          BULK COLLECT INTO  l_sSearch_Term,  l_sDoc_Id, l_sFilg_Dt, l_sLST_UPD_TS
          FROM BASE.NRTV N, BASE.DOC D
          WHERE CONTAINS (N.NRTV_TXT, C1_REC.NAME_IN)>0
          AND D.LST_UPD_TS > v_Search_Date;        
          FORALL i IN 1..l_sSearch_Term.count
          INSERT INTO SARS_OF_INTEREST_01 VALUES (l_sSearch_Term(i),l_sDoc_Id(i), l_sFilg_Dt(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_01 (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_01 (code, message, info, Record_Time)
         Values (v_Error_Code, v_Error_Message, 'Oracle error occured', sysdate);        
Commit;
End Near_Sars_Of_Interest_01;
/

--#########################################################################
However, when I execute the queries, I get the expected rows.

SQL> SELECT N.DOC_ID DOC_ID , D.FILG_DT FILING_Date, D.LST_UPD_TS LAST_UPDATE
  2  FROM AABASE.NRTV N,  AABASE.DOC D
  3  WHERE CONTAINS (N.NRTV_TXT, 'NEAR(((******Intentionally Removed, query returns results shown below)),10)')>
  4  AND N.DOC_ID=D.DOC_ID
  5  AND D.LST_UPD_TS > '01-JAN-2015';

    DOC_ID FILING_DA LAST_UPDATE
---------- --------- ---------------------------------------------------------------------------
1.0053E+10 21-NOV-12 12-JAN-15 09.26.02.000000 AM

SQL>
SQL> SELECT N.DOC_ID DOC_ID , D.FILG_DT, D.LST_UPD_TS
  2  FROM AABASE.NRTV N,  AABASE.DOC D
  3  WHERE CONTAINS (N.NRTV_TXT, 'NEAR((((((******Intentionally Removed, query returns results shown below),15)
  4  NOT ((((******Intentionally Removed, query returns results shown below)')>0
  5  AND N.DOC_ID=D.DOC_ID
  6  AND D.LST_UPD_TS > '01-JAN-2015';

    DOC_ID FILG_DT   LST_UPD_TS
---------- --------- ---------------------------------------------------------------------------
1.0053E+10 23-NOV-12 12-JAN-15 09.26.02.000000 AM
1.0026E+10 10-MAY-13 20-JAN-15 03.43.40.000000 PM
1.0027E+10 17-MAY-13 20-JAN-15 03.43.40.000000 PM
1.0027E+10 21-MAY-13 20-JAN-15 03.43.42.000000 PM
1.0021E+10 08-FEB-13 20-JAN-15 03.43.41.000000 PM
1.0034E+10 02-OCT-13 20-JAN-15 03.43.33.000000 PM
1.0038E+10 31-DEC-13 20-JAN-15 03.43.36.000000 PM
1.0040E+10 31-JAN-14 20-JAN-15 03.43.34.000000 PM
1.0042E+10 04-MAR-14 20-JAN-15 03.43.51.000000 PM
1.0045E+10 30-APR-14 20-JAN-15 03.43.35.000000 PM
1.0043E+10 30-MAR-14 20-JAN-15 03.43.35.000000 PM
1.0047E+10 30-MAY-14 20-JAN-15 03.43.39.000000 PM
1.0047E+10 04-JUN-14 20-JAN-15 03.43.54.000000 PM
1.0045E+10 02-MAY-14 20-JAN-15 03.43.53.000000 PM
1.0050E+10 10-JUL-14 20-JAN-15 03.43.54.000000 PM
1.0052E+10 27-AUG-14 20-JAN-15 03.43.52.000000 PM

16 rows selected.

Thanks for your help.
0
Comment
Question by:KamalAgnihotri
  • 6
  • 5
  • 3
  • +2
17 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 40613029
Here is the text for that error number:

ORA-29902: error in executing ODCIIndexStart() routine
Cause: The execution of ODCIIndexStart routine caused an error.
Action: Examine the error messages produced by the indextype code and take appropriate action.

There should be an error stack that comes with it.  There should be more information telling you what is going on.  It has to do with an index, typically created via Oracle Text.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40613048
You have this DDL command near the top of the procedure:
"Execute Immediate ('Truncate Table Sars_Of_Interest_01');"

Then, later in the procedure you attempt to insert records into this same table.  I suspect that combination causes the error you see.

Try changing this line:
"Execute Immediate ('Truncate Table Sars_Of_Interest_01');"
to
"Delete Sars_Of_Interest_01;"
and see if that gets you past the error.  Yes, I understand that a "truncate" is usually much more efficient than a delete, but doing DDL commands in PL\SQL adds some limitations plus some performance penalties (and possible security problems).

Also, this word order in your question: "Below  procedure..." is distinctively Indian (possibly Hindi-influenced).  English speakers from England, North America, Australia, New Zeeland, etc. would write that as:
"The procedure below..."
or:
"The following procedure..."
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40613085
I use Oracle Text A LOT.  These types of errors are typically due to one of two things:
Bad syntax in the query
or
A bug.

I would use dbms_output.put_line to write out ALL of the values used in the CONTAINS query then execute each one individually.

My bet is one of them will generate the error and you will be able to decide if it is bad syntax or a bug.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40613141
@markgeer

Hooray for the grammar comment above!

However using below in the wrong position is falling into such common use I think we may be on the losing team.

------------------------
"the red shirt"
"the" refers to the noun ("shirt") and "red" is an adjective which qualifies the noun so this is OK

"the below item"
"the" refers to the noun ("item") but the word "belong" is NOT an adjective so it should not be placed between "the" and "item"
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40613328
The only "common" use of the word "below" preceding a noun that I have seen is by non-native speakers of English from India.  I realize that the number of English-speakers in India may be about as high as the combined number of native English speakers in the UK, Canada, the US, Australia and New Zeeland.  But, as far as I know, most people in India are not native speakers of English and/or they did not learn English from native speakers.  If we native speakers of English do not speak up when we hear or see what we consider mistakes in English, those changes will become accepted.
0
 

Author Comment

by:KamalAgnihotri
ID: 40614300
Hi all, Thanks for your valuable comments. We are buried in a foot of snow and all offices are closed. I will work on this tomorrow. (most likely).

To Markgeer, the Truncate table is working fine in a very similar PL/SQL procedure. Hence, I believe, the root cause of my procedure not working may not be the Truncate command. Please help me fix the procedure.  

To Markgeer and PortletPaul, Thanks for your remarks on my English. I am not making an excuse, I made a mistake and I will make an attempt to fix it.  Please realize that right below where I made the mistake you rightly pointed out, I also wrote the following:

Upon compiling the procedure below I get:
 
Which is the correct diction. The only explanation, I can give is that this is a "technical" forum and  as a programmer, I am in a different mindset. The concentration is on making the code work and ensure that the code generates the expected output and not necessarily on strictly following the rules of English grammar. Yes, I am not "Native English Speaker", and  recognize that I do not have the right to "butcher" the language.   I am sure you will be equally appalled by the frequent misuse of  double negatives by  vast number of Americans.  The classic example is , "I don't know nothing" .  I am not attempting to "cover-up". This is just a polite dialogue with some intelligent persons. Thanks for your comments. I will ensure my diction is proper. If you have further suggestions to fix my code, that will be greatly appreciated.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40614405
Can you test this procedure with a simple delete instead of the truncate to see if that avoids the error?

Usually, I don't like seeing "truncate" commands in a PL\SQL procedure because this means that the procedure cannot be used concurrently by multiple users.

I have not written or developed Oracle Text queries and/or indexes, but I have encountered them occasionally.  They do add some restrictions (and maybe some bugs).
0
 

Author Comment

by:KamalAgnihotri
ID: 40614802
Hi slightwv, markgeer,

I could not keep myself away, even though there is no one in office due to snow.

I removed the Truncate statement with delete. The procedure compiled successfully. Upon execution, it took 12Min54Sec to complete. It appears unusually long time. I still get ORA-29902: error in execution. It appears that there certainly is an issue with Oracle Text queries. I will keep trying. Any suggestions are welocme.

Thanks a lot.
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.

 
LVL 34

Expert Comment

by:johnsone
ID: 40614887
There is more to the error than just that one message.  I would try removing the exception handling all together so that you can get the full message.

The difference in timing is likely due to the delete rather than the truncate.
0
 

Author Comment

by:KamalAgnihotri
ID: 40615216
Hi Experts.  Let me restate the objective:

The Select statement below, executes successfully with actual value after the "(((" and returns the result in 9 seconds.

SELECT N.DOC_ID DOC_ID , D.FILG_DT FILING_Date, D.LST_UPD_TS LAST_UPDATE
FROM BASE.NRTV N,  BASE.DOC D
WHERE CONTAINS (N.NRTV_TXT, 'NEAR(((------Intentionally Removed)),10)')>0
AND N.DOC_ID=D.DOC_ID
AND D.LST_UPD_TS > '01-JAN-2015';

I want to create a procedure where the value, 'NEAR(((------Intentionally Removed)),10)')>0  
and the date value  in parameter AND D.LST_UPD_TS > '01-JAN-2015' is read from a two column table. This table would have a bunch of statements like 'NEAR(((------Intentionally Removed)),10)')>0   and corresponding dates like 01-JAN-2015. The procedure using a cursor would loop through the two column table and extract corresponding values from two other tables in the FROM clause. This is what I tried to do in the code I posted. The code compiles and upon execution throws ORA_29902: error in execution. If you can suggest another approach, to get the objective, that is welcome as well.

Any help will be greatly apprecaited.

Thanks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40615378
>>I want to create a procedure where the value,

I understand that.  You are basically wanting to re-invent Stored Query Expressions (SQE).

That is why I posted what I did:
The query you are building is likely causing a syntax issue or is causing you to hit a bug.

You need to figure out exactly which combination of 'variables' is causing this.

I would probably dbms_output.put_line to output all the variables (queries) in the loop.  when the procedure dies, the last displayed variables should be the ones that caused the error.

You should then be able to take those and manually execute the query and it should reproduce the error.
0
 

Author Comment

by:KamalAgnihotri
ID: 40617491
Hi SlightWV,

If you can point me to some examples of Stored Query Expressions (SQE) reading from a table, that will be great. I did stumble on an idea of creating a function and then calling the function from   the where clause as shown below.

select POLICY_NO from insurance
      where contains (TEXTCOL, '@ctxsys.french(right)')>0;

The problem with this approach is that the example above returns ONE value, while in my case, I can have multiple values. It is like creating a function to get the area_Of_Circle, where the value of the variable (radius) is being read from a table where each row has a different value. The output would be:

Radius                    Area
A                                1
B                                2
C                                3

etc.

Your Ideas/Comments. Thanks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40617502
>>examples of Stored Query Expressions (SQE) reading from a table

The online docs is always where I start:
http://docs.oracle.com/cd/E11882_01/text.112/e24435/query.htm#CCAPP9182

>>I did stumble on an idea of creating a function
>>It is like creating a function to get the area_Of_Circle, where the value ...

Sorry but neither of these posts helps me understand your requirements any better than I think I do.

Here is what I think I understand:
you have a table that contains a list of 'search terms'.  You want to loop through ALL those terms and find ALL the docs with those terms.

While you are testing this, you are getting an ORA_29902.

Either the search terms are causing a incorrect query of some type.
or
You are hitting a bug.
0
 

Author Comment

by:KamalAgnihotri
ID: 40617814
Hi SlightWV,

I will open an SR with Oracle and see what they have to say. I will keep this Question open for a few more days.

Thanks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40617889
>>I will open an SR with Oracle and see what they have to say.

While you wait:
Since they will likely ask you for the same sort of information...
At a minimum, they will have you trace the entire process and upload the results.  This will have the exact SQL statement that is failing.

You can probably solve this yourself if you do a simple step to figure out EXACTLY what values are generating the error.

Did you add dbms_output.put_line statements above the select to see what the actual select that is failing looks like?

Can it be reproduced manually using the SAME parameters?
0
 

Author Comment

by:KamalAgnihotri
ID: 40642900
This question can now be closed. I found the solution myself. How ever, I would like to give credit to Slightwv as he poited me in the right direction.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40643019
If you can, post the solution and accept multiple solutions and this will allow you to provide assist points.
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

Suggested Solutions

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…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

746 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

13 Experts available now in Live!

Get 1:1 Help Now