populate only if not populated
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: populate only if not populated

  1. #1
    Join Date
    Dec 2002
    Posts
    28

    populate only if not populated

    I use the following script to populate all uppercase fields as shown below. However once this script is run once and populates U## fields. If run the second time it will do the same. How can I change the script so that it ignores existing populated U## fields but only populates new U## field names.

    cursor get_obj is
    select table_name,column_name from user_tab_columns
    where column_name like 'U##%';

    BEGIN

    cursor_id:=dbms_sql.open_cursor;

    FOR obj_rec in get_obj LOOP

    u_sql := 'UPDATE '||obj_rec.table_name||'SET '||obj_rec.column_name||' = UPPER(' || SUBSTR(obj_rec.column_name, 4) || ')';
    dbms_sql.parse(cursor_id,u_sql,1);
    dbms_output.put_line(u_sql);
    result := dbms_sql.execute(cursor_id);

    END LOOP;

    dbms_sql.close_cursor(cursor_id);

    END;
    /
    Last edited by dsd7038; 09-19-2003 at 07:08 AM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: populate only if not populated

    Code:
    Begin
    For   c
    In    (select table_name tab,column_name col
          from user_tab_columns
          where  column_name like 'U##%')
    Loop
       Execute Immediate
          'Update '||c.tab||
          ' Set '||c.col||'=Upper('||SubStr(c.col, 4)||')'||
          'Where '||c.col||'!=Upper('|| SubStr(c.col, 4)||')';
    End Loop;
    End;
    /
    I also dumped the explicit cursor and the DBMS_SQL, which IMHO make it more difficult to read.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2002
    Posts
    28

    dml_locks exceeded

    I receive error dml_locks exceeded when I run the following. I know I can increase the dml_locks parameter in the ini file, BUT is there a way whereby the select statement will only select column names where the value in U## does not equal the value in the SUBSTR obj_rec.column_name, 4.

    Please advise

    SQL Statement


    cursor get_obj is
    select table_name,column_name from user_tab_columns
    where column_name like 'U##%';

    BEGIN

    cursor_id:=dbms_sql.open_cursor;

    FOR obj_rec in get_obj LOOP

    u_sql := 'UPDATE '||obj_rec.table_name||' SET '||obj_rec.column_name||
    ' = UPPER(' || SUBSTR(obj_rec.column_name, 4) || ')'||
    'Where '||obj_rec.column_name||'!=Upper('|| SubStr(obj_rec.column_name, 4)||')';
    dbms_sql.parse(cursor_id,u_sql,1);
    dbms_output.put_line(u_sql);
    result := dbms_sql.execute(cursor_id);

    END LOOP;

    dbms_sql.close_cursor(cursor_id);

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