mardi 27 février 2018

Oracle - updating table with single special character

I have table where I need to update one column with single random special character. So far I have tried two ways:

1) using dbms_random.string with 'P'

BEGIN
FOR I IN 1..100
  LOOP
    UPDATE
        my_schema.data_table
      SET
        special_char =
        (
          SELECT
              SUBSTR(regexp_replace (str,'[A-Za-z0-9<>"&]',''),1,1)
            FROM
              (
                SELECT
                    dbms_random.string('P', 20) str
                  FROM
                    dual
              )
        );
END LOOP;
COMMIT;
END;

2) creating a simple table with few rows which contain set of special character (1st row - !, second row - _ etc) and running this script:

DECLARE
  v_special_char VARCHAR2(5);
BEGIN
  FOR i IN 1..100
  LOOP
    SELECT
        special_char
      INTO
        v_special_char
      FROM
        (
          SELECT
              special_char
            FROM
              my_schema.char_table
            ORDER BY
              dbms_random.value
        )
      WHERE
        rownum = 1;
    UPDATE
        my_schema.data_table
      SET
        special_char = v_special_char;
    COMMIT;
  END LOOP;
END; 

But none of these works properly - each time column gets updated with the same character - ! or _

I'd be grateful for any help!




Aucun commentaire:

Enregistrer un commentaire