DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: "Execute immediate" problem

  1. #1
    Join Date
    Nov 2004
    Posts
    2

    "Execute immediate" problem

    Can anyone tell what is the error in this statement.

    execute immediate 'Update '||tabs.table_name||' set ID_CTLGRP = REPLACE(ID_CTLGRP,'''','_') WHERE ID_CTLGRP LIKE '%''%'';

    I am replacing apostrophes in a field by underscore for a number of tables.

    Thanks you

  2. #2
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    why dont you write it as
    PHP Code:
    stmt1 varchar2(8000)
          := 
    'Update tabs.table_name set ID_CTLGRP = REPLACE(ID_CTLGRP,'''','_') WHERE ID_CTLGRP LIKE '%''%'';

    EXECUTE IMMEDIATE STMT1

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: "Execute immediate" problem

    Originally posted by locus87
    Can anyone tell what is the error in this statement.

    execute immediate 'Update '||tabs.table_name||' set ID_CTLGRP = REPLACE(ID_CTLGRP,'''','_') WHERE ID_CTLGRP LIKE '%''%'';

    I am replacing apostrophes in a field by underscore for a number of tables.

    Thanks you
    Try this.

    Code:
    execute immediate 'Update '       || tabs.table_name ||
    ' set ID_CTLGRP = REPLACE(ID_CTLGRP,chr(39),''_'')'  ||
    ' WHERE ID_CTLGRP LIKE ''%''%'''

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by thomasp
    why dont you write it as
    PHP Code:
    stmt1 varchar2(8000)
          := 
    'Update tabs.table_name set ID_CTLGRP = REPLACE(ID_CTLGRP,'''','_') WHERE ID_CTLGRP LIKE '%''%'';

    EXECUTE IMMEDIATE STMT1
    He is writing dynamic sql.
    The tablename will change each time he runs this.

  5. #5
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    How can tabs.table_name change in this case? where is he reading that from?

  6. #6
    Join Date
    Nov 2004
    Posts
    2

    "execute immediate" problem

    Hi,

    Thanks for your help so far. I have tried your suggestion. However it does not work. I still get errors.

    My code is below:


    set serveroutput on size 1000000;

    declare
    cursor c1 is
    select table_name from cols where column_name = 'ID_CTLGRP' and table_name like 'TUA%';
    begin

    --loop through tables with field CTLGRP


    For tabs in c1 loop

    dbms_output.put_line ('Processing Table:'||tabs.table_name);

    execute immediate 'Update '|| tabs.table_name ||' set ID_CTLGRP = REPLACE(ID_CTLGRP,chr(39),''_'')'||' WHERE ID_CTLGRP LIKE ''%''%''';



    End loop; --tables
    dbms_output.put_line ('Job Complete');
    end;

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    It would help if you specified WHAT ERROR YOU ARE GETTING!!!
    Jeff Hunter

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: "execute immediate" problem

    Does this work?

    Code:
    SET serveroutput on size 1000000;
    
    DECLARE
       CURSOR c1
       IS SELECT table_name
          FROM   cols
          WHERE  column_name = 'ID_CTLGRP' AND
                 table_name LIKE 'TUA%';
    BEGIN
       DBMS_OUTPUT.ENABLE(1000000);
    --loop through tables with field CTLGRP
       FOR v_tabs IN c1
       LOOP
          DBMS_OUTPUT.put_line( 'Processing Table:' || v_tabs.table_name );
    
          EXECUTE IMMEDIATE 
             'Update ' || v_tabs.table_name ||
             ' SET ID_CTLGRP = REPLACE(ID_CTLGRP,chr(39),''_'')' ||
             ' WHERE ID_CTLGRP LIKE ''%''%''';
          COMMIT;
       END LOOP;
    
       DBMS_OUTPUT.PUT_LINE( 'Job Complete' );
    END;
    /

  9. #9
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    what is the error message that you're getting?
    type
    sql> show errors

    and what do you get?

    For one you havent opened the cursor c1.
    why dont you just do a
    begin
    for x in (select table_name from cols where column_name = 'ID_CTLGRP' and table_name like 'TUA%') loop

    dbms_output.put_line ('Processing Table:'||tabs.table_name);

    execute immediate 'Update '|| tabs.table_name ||' set ID_CTLGRP = REPLACE(ID_CTLGRP,chr(39),''_'')'||' WHERE ID_CTLGRP LIKE ''%''%''';

    End loop; --tables
    dbms_output.put_line ('Job Complete');
    end;

  10. #10
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    PHP Code:
    declare
    .....
    begin
    for x in (select table_name from cols where column_name 'ID_CTLGRP' and table_name like 'TUA%'loop

    dbms_output
    .put_line ('Processing Table:'||tabs.table_name);

    execute immediate 'Update '|| x.table_name ||' set ID_CTLGRP = REPLACE(ID_CTLGRP,chr(39),''_'')'||' WHERE ID_CTLGRP LIKE ''%''%''';

    End loop; --tables
    dbms_output
    .put_line ('Job Complete');
    end

Posting Permissions

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


Click Here to Expand Forum to Full Width