Oracle - Null or Blank parameter value in function

Question

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?

How many records this function returns ? The function uses BULK COLLECT and put all records into a collection, which is created in memory. If there are a lot of records, then it can run out of memory, and it swaps the memory table on disk- this is always slow. I am not sure what this function is supossed to do, why do you want to select from the function instead of directly from the table using simple SELECT, this must be slow. — krokodilko, Jun 18 '18 at 21:22
I'm not clear what the `null` vs `''` issue has to do with the question. There is no difference for `varchar2` parameters, which your function has. What is the question exactly? — William Robertson, Jun 18 '18 at 22:17
The query returns a set of 7k records, more or less. I'm doing this because I need to process the columns which it returns and I thought it was the best way. Also thought it was the null parameter because if run passing all valid values, it runs perfectly. Firebird can process this amount of data, i can't believe that oracle is not able to handle. — Pablo F. Wachsmann, Jun 19 '18 at 01:49
Of course Oracle can handle vast amounts of data. However, it is easy to write a badly performing query, especially when new to Oracle. You're asking us for optimization tips without showing us any details of your implementation. So we can't give you any actual advice. What we can say is, almost certainly the poor performance you're experiencing is due to the way you've written your code. If you want to post the actual program, query and data structures then maybe we'll be able to be more helpful. — APC, Jun 19 '18 at 06:22
I questioned about the variables because of the different behavior when I pass or no values. But if the use of the bulk collection combined with the slow query may be causing the problem, I can try to optimize it. — Pablo F. Wachsmann, Jun 19 '18 at 12:45

Pablo F. Wachsmann · Accepted Answer · 2018-06-20T14:11:12.533

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:

https://dba.stackexchange.com/questions/198443/does-oracle-database-suffer-from-parameter-sniffing-issue

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!

Oracle - Null or Blank parameter value in function

1 Answers1