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 could not make the query any faster. Turns out this query was already otimized a while ago, and it returns the bet of production for the next season, based on the previous season in the company I work (It's a womens clothes factory in Brazil), so it's heavy.
But then I writed a procedure to make three simple updates and I haved the same issue, it kept hang and does'nt give me any result running from the procedure, but the updates queries runned separatedly 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 with this post:
So I declared local variables in both my function and procedure and did this variables receive 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 could not thank the guy whos comment on the original post because of my reputation, but i'm very thankfull.
Also thankfull for the replies!