I would like to make a Prodecure from the following PL/SQL.
Can someone help me with this?
<code>BEGIN
FOR c IN (SELECT id FROM test WHERE uuid IS NULL)
LOOP
UPDATE test
SET uuid=(
select lower(
trim(to_char(floor(dbms_random.value(0, power(16, 8))), '0XXXXXXX')) || '-' ||
trim(to_char(floor(dbms_random.value(0, power(16, 4))), '0XXX')) || '-' ||
trim(to_char(floor(dbms_random.value(0, power(16, 4))), '0XXX')) || '-' ||
case floor(dbms_random.value(0, 4))
when 0 then '8'
when 1 then '9'
when 2 then 'A'
when 3 then 'B'
end ||
trim(to_char(floor(dbms_random.value(0, power(16, 3))), '0XX')) || '-' ||
trim(to_char(floor(dbms_random.value(0, power(16, 12))), '0XXXXXXXXXXX'))
)
from dual
)
WHERE id=c.id;
END LOOP;
END;
/
</code>
<code>BEGIN
FOR c IN (SELECT id FROM test WHERE uuid IS NULL)
LOOP
UPDATE test
SET uuid=(
select lower(
trim(to_char(floor(dbms_random.value(0, power(16, 8))), '0XXXXXXX')) || '-' ||
trim(to_char(floor(dbms_random.value(0, power(16, 4))), '0XXX')) || '-' ||
trim(to_char(floor(dbms_random.value(0, power(16, 4))), '0XXX')) || '-' ||
case floor(dbms_random.value(0, 4))
when 0 then '8'
when 1 then '9'
when 2 then 'A'
when 3 then 'B'
end ||
trim(to_char(floor(dbms_random.value(0, power(16, 3))), '0XX')) || '-' ||
trim(to_char(floor(dbms_random.value(0, power(16, 12))), '0XXXXXXXXXXX'))
)
from dual
)
WHERE id=c.id;
END LOOP;
END;
/
</code>
BEGIN
FOR c IN (SELECT id FROM test WHERE uuid IS NULL)
LOOP
UPDATE test
SET uuid=(
select lower(
trim(to_char(floor(dbms_random.value(0, power(16, 8))), '0XXXXXXX')) || '-' ||
trim(to_char(floor(dbms_random.value(0, power(16, 4))), '0XXX')) || '-' ||
trim(to_char(floor(dbms_random.value(0, power(16, 4))), '0XXX')) || '-' ||
case floor(dbms_random.value(0, 4))
when 0 then '8'
when 1 then '9'
when 2 then 'A'
when 3 then 'B'
end ||
trim(to_char(floor(dbms_random.value(0, power(16, 3))), '0XX')) || '-' ||
trim(to_char(floor(dbms_random.value(0, power(16, 12))), '0XXXXXXXXXXX'))
)
from dual
)
WHERE id=c.id;
END LOOP;
END;
/
<code>ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'RANDOM_UUID_1'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
</code>
<code>ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'RANDOM_UUID_1'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
</code>
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'RANDOM_UUID_1'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
New contributor
Samir Araar Akalay is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
It is easy to make it a stored procedure; just add appropriate command at the beginning:
<code>CREATE OR REPLACE PROCEDURE p_my_proc
AS
BEGIN
FOR c IN (SELECT id
FROM test
WHERE uuid IS NULL)
LOOP
UPDATE test
SET uuid =
(SELECT LOWER (
TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 8))),
'0XXXXXXX'))
|| '-'
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 4))),
'0XXX'))
|| '-'
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 4))),
'0XXX'))
|| '-'
|| CASE FLOOR (DBMS_RANDOM.VALUE (0, 4))
WHEN 0 THEN '8'
WHEN 1 THEN '9'
WHEN 2 THEN 'A'
WHEN 3 THEN 'B'
END
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 3))),
'0XX'))
|| '-'
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 12))),
'0XXXXXXXXXXX')))
FROM DUAL)
WHERE id = c.id;
END LOOP;
END;
/
</code>
<code>CREATE OR REPLACE PROCEDURE p_my_proc
AS
BEGIN
FOR c IN (SELECT id
FROM test
WHERE uuid IS NULL)
LOOP
UPDATE test
SET uuid =
(SELECT LOWER (
TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 8))),
'0XXXXXXX'))
|| '-'
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 4))),
'0XXX'))
|| '-'
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 4))),
'0XXX'))
|| '-'
|| CASE FLOOR (DBMS_RANDOM.VALUE (0, 4))
WHEN 0 THEN '8'
WHEN 1 THEN '9'
WHEN 2 THEN 'A'
WHEN 3 THEN 'B'
END
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 3))),
'0XX'))
|| '-'
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 12))),
'0XXXXXXXXXXX')))
FROM DUAL)
WHERE id = c.id;
END LOOP;
END;
/
</code>
CREATE OR REPLACE PROCEDURE p_my_proc
AS
BEGIN
FOR c IN (SELECT id
FROM test
WHERE uuid IS NULL)
LOOP
UPDATE test
SET uuid =
(SELECT LOWER (
TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 8))),
'0XXXXXXX'))
|| '-'
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 4))),
'0XXX'))
|| '-'
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 4))),
'0XXX'))
|| '-'
|| CASE FLOOR (DBMS_RANDOM.VALUE (0, 4))
WHEN 0 THEN '8'
WHEN 1 THEN '9'
WHEN 2 THEN 'A'
WHEN 3 THEN 'B'
END
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 3))),
'0XX'))
|| '-'
|| TRIM (
TO_CHAR (FLOOR (DBMS_RANDOM.VALUE (0, POWER (16, 12))),
'0XXXXXXXXXXX')))
FROM DUAL)
WHERE id = c.id;
END LOOP;
END;
/
Though, that’s not your main problem. You reported that Oracle complains about wrong number or datatype of arguments while calling RANDOM_UUID_1
function (or procedure), but – there’s no such call in code you posted.