Link to home
Start Free TrialLog in
Avatar of Maliki Hassani
Maliki HassaniFlag for United States of America

asked on

Oracle Function: How to compile on execution

Experts,

I am using CRD a tool used to automate/send crystal reports.  I have a a report that fails to run a few times a month.  The report is a function that collects a list of values and stores them in a table.  

Issue:

The function fails to send when folks makes updates to fields, which requires me to compile the function and then rerun the automation.

I am looking for an execution statement that would compile the function before it runs.
Any ideas on how to get this work?

Thanks
Avatar of paquicuba
paquicuba
Flag of United States of America image

Is the function getting into an invalid state?

You can recompile all invalid objects in the schema:

DBMS_UTILITY.compile_schema(schema => 'your_schema', compile_all => FALSE);
Avatar of Maliki Hassani

ASKER

Yes, that is correct.
does DBMS_UTILITY.compile_schema(schema => 'your_schema', compile_all => FALSE);  go in the function itself?
ASKER CERTIFIED SOLUTION
Avatar of paquicuba
paquicuba
Flag of United States of America image

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
I have a ttached the function.  All I know is that when it fails to run, I see from oracle that the fuction needs to be compiled, then it works.
Function.txt
Avatar of awking00
You might consider creating an after insert, update, or delete trigger on the tables that the "folks" modify that checks the status of the function and, if invalid, issues an alter function compile command.
So, you're saying that everytime a developer adds or drop a column from RPT_NOC_KIR_DATA , the function goes invalid -that's fine, but it should not fail the next time it gets called. Oracle will implicitly recompile it for you.

Can you post how you're calling the function and if possible the error you're getting?
Well I just talked my boss and he says that we dont have the managment rights to use that command you provided.

What is happening is when the developers add or modify the fieds size of the temporary tables it causes this issue.  He says that monthly he manually compiles any functions.
Awking00,
Do you have an an example of what query is for this?  Seems like that could help reduce the occurances.
What awking00 is telling you is to execute the following command inside PL/SQL:

execute immediate 'alter function your_function_name_here compile';


In you case, it will have to be done in a "AFTER DDL ON SCHEMA" trigger
Can you post your function or its pseudo-code? I still don't understand how the function goes invalid when changing fields of a table the function uses and it gets fixed just by recompilation.  --> I am behind this as i second paquicuba that the recompilation should be done automatically when we try to execute it. The only thing what we can observe a few more milliseconds when it tries to recompile and execute.
I see what you you all are saying is that when the function is executed, it will trigger the function to compile.

So what I need to do is create a trigger.  Does this look correct?

create or replace
TRIGGER "NOCREPORTS"."RPT_NOC_KIR_DATA_COMPILER"
AFTER INSERT OR UPDATE
      ON NOCREPORTS.RPT_NOC_KIR_DATA
DECLARE
      
BEGIN
      execute immediate 'alter SF_RPT_NOC_KIR_DATA compile';

END;


Attached is the function from earlier.
Function.txt
SOLUTION
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
The original question indicated the function was becoming invalid because of updates to tables, which is why I suggested an after insert, update, or delete trigger on those tables. Subsequently, we have discovered that the cause for the function is due to adding or modifying columns in the tables. As such, a ddl trigger is what is needed as paquicuba stated. I'm not sure that "after ddl" is necessary since it would compile the function after any ddl event (including things like analyze, grant, revoke, etc.). It might be sufficient to just use "after alter on schema" since that appears to be the only culprit.
awking00, you're correct, DDL trigger can fire for a bunch of different events. However, you can always use ora_sysevent function to control the events and keep all DDL events in the same trigger.
Great, thanks!  I will give this a try and let you know.
still reviewing...