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