Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2004
    Posts
    8

    Thumbs up Unanswered: execute immediate

    I have a package with two procedure, one does insert using execute immediate and other does update using execute immediate. It works perfectly fine in 8i production database but gives ORA-22806: not an object or REF error. Anyone have came across this issue ?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105
    let's see the code...

  3. #3
    Join Date
    Aug 2004
    Posts
    8
    Below is the package body with one procedure and also the package defination.


    create or replace package body abc as
    procedure abc is
    upd_Stmt long;
    var1 number;
    var2 number;
    var3 date;
    var6 date;
    begin

    upd_stmt := 'UPDATE securitydbo.security_analytics SET update_date = :update_date ';
    upd_stmt := upd_stmt||chr(10)||
    ' WHERE SECURITY_ALIAS = :TABLE_VALUES.SECURITY_ALIAS'||chr(10)||
    ' AND SRC_INTFC_INST = :TABLE_VALUES.SRC_INTFC_INST'||chr(10)||
    ' AND TRUNC(EFFECTIVE_DATE) = :TABLE_VALUES.EFFECTIVE_DATE';


    var1 := 270670;
    var2 := 17;
    var3 := sysdate-13;
    var6 := sysdate;

    execute immediate upd_stmt using var6,var1, var2, var3;

    -- execute immediate upd_stmt using var1,var2,var1;
    end ;
    end ;


    Below is the table defination
    SECURITY_ALIAS NOT NULL NUMBER
    EFFECTIVE_DATE NOT NULL DATE
    SRC_INTFC_INST NOT NULL NUMBER
    CURRENCY CHAR(3)
    CONVEXITY NUMBER(28,12)
    MOD_DURATION NUMBER(28,12)
    YTM NUMBER(28,12)
    YTNC NUMBER(28,12)
    YTNP NUMBER(28,12)
    YTS NUMBER(28,12)
    EFF_YLD NUMBER(28,12)
    DUR_TO_WRST NUMBER(28,12)
    YTO NUMBER(28,12)
    CPR NUMBER(28,12)
    EFF_DUR NUMBER(28,12)
    PSA NUMBER(28,12)
    OAS NUMBER(28,12)
    OAD NUMBER(28,12)
    OAY NUMBER(28,12)
    WAL NUMBER(28,12)
    WAM NUMBER(28,12)
    WAC NUMBER(28,12)
    YTW NUMBER(28,12)
    MAT_YRS NUMBER(28,12)
    ACT_DAY_TO_ACCRUE NUMBER(28,12)
    CASH_FLW_DURATION NUMBER(28,12)
    PD3 NUMBER(28,12)
    PD4 NUMBER(28,12)
    EFF_SPREAD_DUR NUMBER(28,12)
    MTG_EFF_ELASTICITY NUMBER(28,12)
    MTG_THREAS_ELASTICITY NUMBER(28,12)
    MTG_TURNOVER_ELASTICITY NUMBER(28,12)
    SMM_RATE NUMBER(28,12)
    STRIPPED_YIELD NUMBER(28,12)
    UPDATE_DATE DATE
    UPDATE_SOURCE CHAR(12)
    TERM NUMBER(28,12)
    MACAULAY_DURATION NUMBER(28,12)
    YTNCONV NUMBER(28,12)
    DTM NUMBER(28,12)
    CASH_FLOW_YIELD NUMBER(28,12)
    ACCRUED_FACTOR NUMBER(28,12)
    EFF_SPREAD NUMBER(28,12)
    OAD2 NUMBER(28,12)
    OAD3 NUMBER(28,12)
    MOD_TO_WRST NUMBER(28,12)
    AVERAGE_LIFE_DATE DATE
    AVERAGE_MATURITY DATE
    DELTA NUMBER(28,12)
    GAMMA NUMBER(28,12)
    THETA NUMBER(28,12)
    VEGA NUMBER(28,12)
    R_DAY_CNT NUMBER
    P_DAY_CNT NUMBER
    BETA NUMBER(28,12)
    EFF_CONVEXITY NUMBER(28,12)
    PRICE NUMBER(28,12)
    MAPPING_CUSIP CHAR(12)

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Why are you using execute immediate? SImply do


    var1 := 270670;
    var2 := 17;
    var3 := sysdate-13;
    var6 := sysdate;

    UPDATE securitydbo.security_analytics
    SET update_date = sysdate
    WHERE SECURITY_ALIAS = var1
    AND SRC_INTFC_INST = var2
    AND EFFECTIVE_DATE between trunc(var3) and trunc(var3) + 1;

    If it isn't needed, why incure the overhead and typing of doing an execute immediate.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2004
    Posts
    8
    Found out the bug, making this changes it work, can anyone please explain why oracle works like this.

    upd_stmt := 'UPDATE securitydbo.security_analytics SET update_date = :update_date ';
    upd_stmt := upd_stmt || ' WHERE SECURITY_ALIAS = ECURITY_ALIAS'||chr(10)||
    ' AND SRC_INTFC_INST = RC_INTFC_INST'||chr(10)||
    ' AND TRUNC(EFFECTIVE_DATE) = trunc(:EFFECTIVE_DATE)';

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    you don't need the CHR(10) stuff.
    just hit a return for the line if you don't want to make one line.
    only enclose in one set of quotes
    PHP Code:
    upd_stmt := 'UPDATE securitydbo.security_analytics SET update_date = :update_date '
    upd_stmt := upd_stmt || ' WHERE SECURITY_ALIAS = :SECURITY_ALIAS
    AND SRC_INTFC_INST = :SRC_INTFC_INST
    AND TRUNC(EFFECTIVE_DATE) = trunc(:EFFECTIVE_DATE)'

    also, why set upd_stmt twice?
    PHP Code:
    upd_stmt := 'UPDATE securitydbo.security_analytics SET update_date = :update_date WHERE SECURITY_ALIAS = :SECURITY_ALIAS
    AND SRC_INTFC_INST = :SRC_INTFC_INST
    AND TRUNC(EFFECTIVE_DATE) = trunc(:EFFECTIVE_DATE)'

    I agree with beilstwh however. You don't even need the execute immediate.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Aug 2004
    Posts
    8
    Let me clarify, I just extracted the part of the problem and posted the code, actually I have other java codes calling this procedure and populating the variables.

    But the amazing thing is in the assignment, let say table values is of %rowtype and when you say

    security_alias = :table_values.security_alias it does not work in 9i, works in 8i

    but
    security_alias = :security_alias works works in both.


    Please explain.....

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Ellaborate more on that. What doesn't work? What errors do you get?

  9. #9
    Join Date
    Aug 2004
    Posts
    8
    Original code

    v temp%rowtype
    upd_stmt := 'INSERT INTO temp (col1, col2) values (:v.col1, :v.col2)';
    execute immediate upd_stmt using v.col1, v.col2;

    works in Oracle 8i, but in Oracle 9i I get ORA-22806: not an object or REF

    I have to change the binding variables from :v.col1 to :col1 and :v.col2 to :col2


    Thanks

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >works in Oracle 8i, but in Oracle 9i I get ORA-22806: not an object or REF
    Regardless of what worked in which version, what you are doing does not scale.
    There is no need for EXECUTE IMMEDIATE with what needs to be done.
    The current implementation forces a HARD PARSE for every INSERT issued.
    With only minor modification it could be changed to only use bind variables, which consumes many fewer resources.
    Of course if you do not care about performance or scalability, continue doing things inefficiently.

    HTH & YMMV
    HAND!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Aug 2004
    Posts
    8
    Its not a table with 2 columns, this is just an example of whats happenning with different version of Oracle, I have 50+ tables and all with minimum 40 columns being populated from 5 or more data sources. Dynamic SQL is the most efficient way to do. But it is just that now I have to go back and change every places where the record variable is being binded.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Dynamic SQL is the most efficient way to do.
    Using Dynamic SQL may be the easiset way to program the solution,
    but is definately NOT the most efficient from a SQL performance standpoint.
    Coding is a one time operation.
    Performance penalty is paid each & every time the code is run.
    I still contend this implementation scales very poorly.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •