I'm trying to get the result from a function that makes a query, processes the fields and returns a collection.
If I take the query of the function and execute separately, it returns in about 10 minutes, depending on the parameters that I put. If I pass the same parameters to the function, it keeps processing and I can't get any result after 45 minutes.
After the query, I only have a few if's which check's for zero values or values that higher than others.
I think the problem is that I'm passing some parameters null or blank and it makes the query crash. Here is my problem:
I have a type:
CREATE OR REPLACE TYPE TypeForFunction is OBJECT (
-- all my fields here
)
/
Then a make a collection:
CREATE OR REPLACE TYPE TypeForFunctionTable AS
TABLE OF TypeForFunction
/
Then my function goes like this:
CREATE OR REPLACE FUNCTION MyFunction
(
/* here I have five parameters and in the case that the query crashes,
two of them I'm trying to pass blank or null */
COL in varchar2, -- This I pass a valid value
INDEX in number, -- same here
REF in varchar2, -- This one I'm trying to pass Blank ('') or Null and i
get no result no matter which one I pass.
P in varchar2,
BLOQ in varchar2 -- Same null or blank here
) RETURN TypeForFunctionTable
IS
result_table TypeForFunctionTable;
i integer := 0;
begin
select
TypeForFunction(
/* Here I have some subquerys that I use the parameters null which
I use the same way as parameter REF. Like: */
and (MyTable.FieldP = P or P is null)
and (MyTable.FielBloq = BLOQ or BLOQ is null)
) BULK COLLECT into result_table
from
myTables
where
-- here I have a clause like
(MyTable.FieldREF = REF or REF is null)
;
For i in 1..result_table.count loop
/* Here I have some if's, but nothing to crash the query like it happens.
Things like: */
if MyVar > 0 then
COL = REF;
INDEX = INDEX + 100;
end loop;
return result_table;
end MyFunction;
/
To call the function I try:
select * from table(MyFunction('59', 1, '', 'IV18', ''));
Also try:
select * from table(MyFunction('59', 1, Null, 'IV18', Null));
Anyway I get the same result, function don't return or give any error after 45 minutes.
Is there any better way to work with parameters that I may or may not pass value?
I couldn' make the query faster. It turns out this query was already optimized a while ago, and it returns the bet of production for the next season, based on the previous season for the company I work for (a women's clothing factory in Brazil), so it's heavy.
But then I wrote a procedure to make three simple updates and ran into the same issue, it kept hanging and does'nt give me any result when running the procedure. The update queries run separately worked fine.
I started to search and found this answer:
Stored procedure hangs seemingly without explanation
Which is an SQL Server anwser, but then I started to search if the problem affects also Oracle and I came across this post:
So, I declared local variables in both my function and procedure, and these variables received the parameters.
Now my function looks like this:
CREATE OR REPLACE FUNCTION MyFunction
(
/* here i have five parameters and in the case that the query crashes,
two of them i'm trying to pass blank or null */
COL in varchar2, -- This I pass a valid value
INDEX in number, -- same here
REF in varchar2, -- This one I'm trying to pass Blank ('') or Null and i
get no result no matter wich one I pass.
P in varchar2,
BLOQ in varchar2 -- Same null or blank here
) RETURN TypeForFunctionTable
IS
result_table TypeForFunctionTable;
i integer := 0;
LOCAL_COL varchar2(4) := COL;
LOCAL_REF varchar2(15) := REF;
LOCAL_P varchar2(6) := P;
LOCAL_BLOQ varchar2(1) :=;
And I used the "LOCAL" variables in all queries and it worked fine. Solved the problem. I couldn't thank the guy who commented on the original post because of my reputation, but i'm very thankfull.
Also thankfull for the replies!