Home » Developer & Programmer » Warehouse Builder » OWB not considering the constant whil execution (OWB 11g)
OWB not considering the constant whil execution [message #595205] Mon, 09 September 2013 05:58 Go to next message
srinivas.k2005
Messages: 299
Registered: August 2006
Senior Member
Hi,

In OWB i have assigned a constant variable as below
Constant : Expression : PACKG1.GET_DATE
and linked the constant to a target table column

When i generate code, i am getting as below:


CREATE OR REPLACE PACKAGE "M_TEST" AS
"CO_0_PR" DATE := PACKG1.GET_DATE;
END "M_TEST" ;
/


CREATE OR REPLACE PACKAGE BODY AS "M_TEST" AS

FUNCTION "F_FUN" 
 RETURN BOOLEAN IS

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

  BEGIN
    INSERT    
    INTO
      "TARGET_TAB" 
      (
      ID,
      DATE
       )
      (SELECT
       ID,
       PACKG1.GET_DATE
       FROM "SOURCE_TAB"     
       );
   END;
END "F_FUN";
END "M_TEST" ;
/
 



My question :
Why is it taking PACKG1.GET_DATE in select statement instead of the constant "CO_0_PR".
How to set in OWB to take constant value.
If i have 10million records the PACKG1.GET_DATE is executing 10 million times.

Please let me know the solution if any, to take the constant value





Re: OWB not considering the constant whil execution [message #595206 is a reply to message #595205] Mon, 09 September 2013 06:02 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why? Because you told it so. I suppose you meant to say
insert into target_tab (id, date)
  (select id, m_test.co_0_pr from source_tab)
Re: OWB not considering the constant whil execution [message #595230 is a reply to message #595206] Mon, 09 September 2013 09:12 Go to previous messageGo to next message
srinivas.k2005
Messages: 299
Registered: August 2006
Senior Member
Hi,

Sorry if i had confused.

The package i sent was generated automatically by OWB internally.

OWB generated package:

CREATE OR REPLACE PACKAGE "M_TEST" AS
"CO_0_PR" DATE := PACKG1.GET_DATE;
END "M_TEST" ;
/


CREATE OR REPLACE PACKAGE BODY AS "M_TEST" AS

FUNCTION "F_FUN" 
 RETURN BOOLEAN IS

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

  BEGIN
    INSERT    
    INTO
      "TARGET_TAB" 
      (
      ID,
      DATE
       )
      (SELECT
       ID,
       PACKG1.GET_DATE
       FROM "SOURCE_TAB"     
       );
   END;
END "F_FUN";
END "M_TEST" ;
/



My Question is what setting should we do so that the OWB package generate code as below:

CREATE OR REPLACE PACKAGE "M_TEST" AS
"CO_0_PR" DATE := PACKG1.GET_DATE;
END "M_TEST" ;
/


CREATE OR REPLACE PACKAGE BODY AS "M_TEST" AS

FUNCTION "F_FUN" 
 RETURN BOOLEAN IS

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

  BEGIN
    INSERT    
    INTO
      "TARGET_TAB" 
      (
      ID,
      DATE
       )
      (SELECT
       ID,
       CO_0_PR
       FROM "SOURCE_TAB"     
       );
   END;
END "F_FUN";
END "M_TEST" ;
/
Re: OWB not considering the constant whil execution [message #595294 is a reply to message #595230] Mon, 09 September 2013 23:59 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
My answer is: I have no idea, sorry.
Re: OWB not considering the constant whil execution [message #595307 is a reply to message #595230] Tue, 10 September 2013 02:12 Go to previous message
Lalit Kumar B
Messages: 2405
Registered: May 2013
Location: World Wide on the Web
Senior Member
srinivas.k2005 wrote on Mon, 09 September 2013 19:42
    SELECT
       ID,
       PACKG1.GET_DATE
       FROM "SOURCE_TAB"


How do you generate this select statement? I know your answer would be OWB internally generates this, but again, how do you make OWB generate this?
Previous Topic: OWB MAP TO VISIO DIAGRAM
Next Topic: Create Mapping As a Flat File Source
Goto Forum:
  


Current Time: Tue Sep 30 08:54:42 CDT 2014

Total time taken to generate the page: 0.05654 seconds