EXECUTE IMMEDIATE [message #415384] |
Mon, 27 July 2009 09:17  |
p.sree
Messages: 1 Registered: July 2009 Location: INDIA
|
Junior Member |
|
|
HI,
AM USING EXECUTE IMMEDIATE INSIDE MY FUNCTION.
IT GOES SUMTHING LIKE THIS -
create or replace
FUNCTION f_prescript (Dim_Table in varchar2, stg_table in varchar2) RETURN date is
Dim_Table_name varchar2(50);
Stg_Table_name varchar2(50);
Load_Start_time date;
v_Load_End_time date;
Records_processed number(5);
Begin
Dim_Table_name:= Dim_Table;
Stg_Table_name:= stg_table;
Load_Start_time:= sysdate();
Execute immediate 'insert into DATA_LOAD_CONTROL (DATA_LOAD_TABLE_NAME, LOAD_START_TIME, LOAD_END_TIME, RECORDS_PROCESSED, RECORDS_INSERTED, RECORDS_UPDATED, BALANCE_CONTROL_STAUS, DATA_LOAD_STATUS) values (:1 ,:2 , null, null, null, null, null, null)'
using Stg_Table_name, Load_Start_time;
Execute immediate 'select max(to_char(LOAD_END_TIME, "YYYY.MM.DD HH24:MI:SS")) from DATA_LOAD_CONTROL where DATA_LOAD_TABLE_NAME = :1 group by DATA_LOAD_TABLE_NAME '
into v_Load_End_time
USING stg_table;
IF TO_DATE(v_Load_End_time,'YYYY.MM.DD HH24:MI:SS') = null then
v_Load_End_time := to_date('1900.01.01 12:12:12', 'YYYY.MM.DD HH24:MI:SS');
End If;
return (v_Load_End_time);
END;
I CALL IT FROM BO-DI JOB - DATAFLOW
THE FUNCTION GETS COMPILED BUT WHEN I CALL IT FROM MY JOB IT DOESNT RETURN ME THE CORRECT DATE... IT RETURNS NULL.. please HELP!
|
|
|
|
Re: EXECUTE IMMEDIATE [message #415390 is a reply to message #415384] |
Mon, 27 July 2009 09:40  |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
1) When posting code can you please use code tags - see the orafaq forum guide if you're not sure how.
2) It always helps to copy and paste the exact code, which presumably you haven't done.
3) None of your dynamic SQL appears to actually be dynamic - so don't use dynamic.
4) Inserts in functions generally aren't a good idea.
5) = NULL is never true.
6) Don't to_date dates.
|
|
|