Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    25

    Unanswered: Substituting Variables In DML Statements

    I am Using PL/SQL for
    Oracle9i Enterprise Edition Release 9.2.0.8.0

    I am having trouble updating values is all schema tables. The application is MAXIMO.
    I am using the following code;

    SET VERIFY OFF
    SET SERVEROUTPUT ON
    ACCEPT V_COLNAME PROMPT 'Enter the column you want to change: '
    ACCEPT V_OLD_VALUE PROMPT 'Enter the existing value in that column: '
    ACCEPT V_NEW_VALUE PROMPT 'Enter the new value for that column: '
    DECLARE
    V_SQL VARCHAR2(254);
    V_CURSOR INTEGER;
    V_RESULT INTEGER;
    v_COUNT INTEGER;
    CURSOR TBL IS
    SELECT TBNAME, NAME
    FROM MAXSYSCOLUMNS
    WHERE SAMEASCOLUMN = '&V_COLNAME'
    OR NAME = '&V_COLNAME';
    BEGIN
    V_COUNT:=0;
    V_CURSOR:=DBMS_SQL.OPEN_CURSOR;
    FOR REC IN TBL LOOP
    V_SQL:= 'UPDATE '||REC.TBNAME||' SET '||REC.NAME||' = '||'&V_NEW_VALUE'||' WHERE '||REC.NAME||' = '||'&V_OLD_VALUE';
    DBMS_SQL.PARSE(V_CURSOR,V_SQL,1);
    V_RESULT := DBMS_SQL.EXECUTE(V_CURSOR);
    V_COUNT:=V_COUNT+1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('VALUE CHANGED IN '||V_COUNT||' TABLES');
    END;
    /

    I get the following error;
    DECLARE
    *
    ERROR at line 1:
    ORA-00904: "TZ6W0R": invalid identifier
    ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
    ORA-06512: at "SYS.DBMS_SQL", line 39
    ORA-06512: at line 18

    Maxsyscolumns is the table which identifies the application table and column names.

    Any help you could provide would be greatly appreciated.
    Last edited by aldankirk; 08-21-07 at 12:57.
    Kirk Wahl
    GM Powertrain SMCO

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    You need to embed quotes around your substitution variables into the sql string you are building

    You want the result to include quotes around the value, something like this:

    Code:
    Ora10>define MYVAR=somevalue
    Ora10>select '''&MYVAR''' from dual;
    old   1: select '''&MYVAR''' from dual
    new   1: select '''somevalue''' from dual
    
    '''SOMEVALU
    -----------
    'somevalue'

  3. #3
    Join Date
    Aug 2007
    Posts
    25
    Thank you very much. I knew I was doing something stupid, I just couldn't see it.
    Kirk Wahl
    GM Powertrain SMCO

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you are constructing SQL on the fly, it is always a GOOD THING(tm) to construct the statement using a VARCHAR2 variable.
    Prior to executing the new SQL, print is out so that when it fails you can CUT & PASTE it back into SQL*Plus to see exactly where it fails.
    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
  •