Link to home
Start Free TrialLog in
Avatar of Kamal Agnihotri
Kamal AgnihotriFlag for United States of America

asked on

Fix ORA_29902: error in execution

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.
Avatar of johnsone
johnsone
Flag of United States of America image

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.
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..."
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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.
@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"
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.
Avatar of Kamal Agnihotri

ASKER

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.
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).
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.
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.
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.
>>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.
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.
>>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.
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.
>>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?
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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial