Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Bad bind variable error -- please help!

    I'm writing a procedure to purge data older than x days from a tableY based on columnZ. So it's like

    create or replace sp_purge (table_name, col_name, DaysToKeep)
    v_StartPurgeDate date; -- most likely be the date the purge starts
    v_EndPurgeDate date; -- v_StartPurgeDate - DaysToKeep
    ...

    -- find v_StartPurgeDate
    -- syntax is not correct here, but you know what I mean
    select max(columnZ) into v_StartPurgeDate from TableY;

    -- Find v_EndPurgeDate
    v_EndPurgeDate := v_StartPurgeDate - DaysToKeep;

    While v_StartPurgeDate > v_EndPurgeDate LOOP
    v_StartPurgeDate := v_StartPurgeDate -1;
    execute immediate 'delete ' || i_tableName || ' where '|| i_purgeColName || ' > '''tartPurgeDate'''' using v_StartPurgeDate; ; -- Keep getting error here
    END LOOP;
    ...
    EXCEPTION
    ..
    END;
    /

    It compiles fine, but I keep getting error when trying to run it --

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    50/85 PLS-00049: bad bind variable 'STARTPURGEDATE'
    50/85 PLS-00103: Encountered the symbol "" when expecting one of the
    following:
    . ( * @ % & = - + ; < / > at in mod not rem return returning
    <> or != or ~= >= <= <> and or like
    between into using is null is not || is dangling
    The symbol "*" was substituted for "" to continue.

    I think it's complaining the quotes I put around the bind variable. But I do need to put the quotes there as this column is a date column, so I need to put single quote around it(e.g. '04-Apr-03') when put it in the where clause.

    Anybody can shed some light on this? Millions of thanks!!!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Bad bind variable error -- please help!

    Actually, you don't need and shouldn't put the quotes there - it is a variable not a literal:

    execute immediate 'delete ' || i_tableName || ' where '|| i_purgeColName || ' > tartPurgeDate' using v_StartPurgeDate;

  3. #3
    Join Date
    Jan 2004
    Posts
    2

    Re: Bad bind variable error -- please help!

    You are right -- the procedure is working fine now after I removed the quotes. Thanks!

    Originally posted by andrewst
    Actually, you don't need and shouldn't put the quotes there - it is a variable not a literal:

    execute immediate 'delete ' || i_tableName || ' where '|| i_purgeColName || ' > tartPurgeDate' using v_StartPurgeDate;

Posting Permissions

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