@?/rdbms/admin/utlrp.sql How to create procedure code to run the utlrp.sql in each pluggable database of oracle. I have created the below one but it has failed. please advise. I have created the below one but it has failed. it should run in all PDB and exit once done. please advise.
DECLARE
v_pdb_name VARCHAR2(30);
v_pdb_status VARCHAR2(30);
v_sql VARCHAR2(200);
BEGIN
-- Loop through all PDBs in the container database (CDB)
FOR c IN (select Name , OPEN_MODE from v$pdbs where OPEN_MODE='READ WRITE';) LOOP
v_pdb_name := c.Name;
v_pdb_status := c.OPEN_MODE;
-- Check if the PDB is in READ WRITE mode
IF v_pdb_status = 'READ WRITE' THEN
-- Generate and execute the script for each READ WRITE PDB
v_sql := 'ALTER SESSION SET CONTAINER=' || v_pdb_name;
EXECUTE IMMEDIATE v_sql;
-- Run utlrp.sql script in the current PDB
@?/rdbms/admin/utlrp.sql;
-- Switch back to the CDB root container
EXECUTE IMMEDIATE 'ALTER SESSION SET CONTAINER=CDB$ROOT';
ELSE
DBMS_OUTPUT.PUT_LINE('Skipped PDB ' || v_pdb_name || ' (Status: ' || v_pdb_status || ')');
END IF;
END LOOP;
END;
/
