Kamal Agnihotri
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(((******Intentionall y 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((((((******Intention ally 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.
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_
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
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(((******Intentionall
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((((((******Intention
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.
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..."
"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..."
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.
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"
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.
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.
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).
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).
ASKER
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.
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.
The difference in timing is likely due to the delete rather than the truncate.
ASKER
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(((------Intentionall y 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(((------Intentionall y 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(((------Intentionall y 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.
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(((------Intentionall
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(((------Intentionall
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(((------Intentionall
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.
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.
ASKER
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.
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.
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.
ASKER
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. 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?
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?
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.