I am running 12c, and I have been tasked out with cloning a select list of users over to a separate database.
I'm still a newbie at pl/sql for the most part, and I'm struggling here. I only need the users and their objects' structure. I don't need any of the data.
I found the package for dbms_metadata.get_granted_ddl, not sure if it's the right way to go, but it sounded like it.
I'm trying to put together a script that will use a cursor to build the user list, then pass it to the package, which will output all the DDL to the screen for creating the user (including encrypted password), as well as grants, and any object created under that user/schema.
So far this is what I have.
declare
a_u_name varchar2(1000);
b_u_name varchar2(1000);
cursor US_CUR is
select username from dba_users
where username like '%IAU%'
or username like '%OPSS'
or username like '%STB';
begin
for b_u_name in US_CUR
LOOP
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',b_u_name.username)
FROM DUAL;
END LOOP;
END;
/
And the error:
Error report -
ORA-06550: line 15, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I'm not sure how to put the into clause in there properly. It makes no sense to me.
Can someone help, or offer up a better method?