Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE
EXECUTE IMMEDIATE [message #415384] Mon, 27 July 2009 09:17 Go to next message
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 #415389 is a reply to message #415384] Mon, 27 July 2009 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

The standard advice for EXECUTE IMMEDIATE is to assign the SQL to a VARCHAR2 variable & "print" the SQL prior to invoking it.
This allows you to via CUT & PASTE to confirm the result set.
Re: EXECUTE IMMEDIATE [message #415390 is a reply to message #415384] Mon, 27 July 2009 09:40 Go to previous message
cookiemonster
Messages: 12420
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.
Previous Topic: Help on query (merged 2) 10.2.0.1
Next Topic: Query Help
Goto Forum:
  


Current Time: Thu Dec 08 06:37:58 CST 2016

Total time taken to generate the page: 0.08183 seconds