tonydba
asked on
ddl error
I have an error here
SQL>select dbms_metadata.get_ddl( 'USER','PERF_ENGINEERING_R SAARC' ) from dual
2 UNION ALL
3 select dbms_metadata.get_granted_ ddl('SYSTE M_GRANT',' PERF_ENGIN EERING_RSA ARC') from dual
4 UNION ALL
5 select dbms_metadata.get_granted_ ddl('OBJEC T_GRANT',' PERF_ENGIN EERING_RSA ARC') from dual
UNION ALL
6 7 select dbms_metadata.get_granted_ ddl('ROLE_ GRANT','PE RF_ENGINEE RING_RSAAR C') from dual;
DBMS_METADATA.GET_DDL('USE R','PERF_E NGINEERING _RSAARC')
-------------------------- ---------- ---------- ---------- ---------- ---------- ----
CREATE USER "PERF_ENGINEERING_RSAARC" IDENTIFIED BY VALUES 'S:EECA10D0E816B7
GRANT CREATE TRIGGER TO "PERF_ENGINEERING_RSAARC"
GRANT CREATE PROCEDURE TO
ERROR:
ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7737
ORA-06512: at line 1
SQL>select dbms_metadata.get_ddl( 'USER','PERF_ENGINEERING_R
2 UNION ALL
3 select dbms_metadata.get_granted_
4 UNION ALL
5 select dbms_metadata.get_granted_
UNION ALL
6 7 select dbms_metadata.get_granted_
DBMS_METADATA.GET_DDL('USE
--------------------------
CREATE USER "PERF_ENGINEERING_RSAARC" IDENTIFIED BY VALUES 'S:EECA10D0E816B7
GRANT CREATE TRIGGER TO "PERF_ENGINEERING_RSAARC"
GRANT CREATE PROCEDURE TO
ERROR:
ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7737
ORA-06512: at line 1
ASKER
>select dbms_metadata.get_ddl( 'USER','PERF_ENGINEERING_R SAARC' ) || ';' from dual
2 UNION ALL
3 select dbms_metadata.get_granted_ ddl('SYSTE M_GRANT',' PERF_ENGIN EERING_RSA ARC') || ';' from dual
4 UNION ALL
5 select dbms_metadata.get_granted_ ddl('OBJEC T_GRANT',' PERF_ENGIN EERING_RSA ARC') || ';' from dual
6 UNION ALL
7 select dbms_metadata.get_granted_ ddl('ROLE_ GRANT','PE RF_ENGINEE RING_RSAAR C') || ';' from dual;
DBMS_METADATA.GET_DDL('USE R','PERF_E NGINEERING _RSAARC')| |';'
-------------------------- ---------- ---------- ---------- ---------- ---------- ----
CREATE USER "PERF_ENGINEERING_RSAARC" IDENTIFIED BY VALUES 'S:EECA10D0E816B7
GRANT CREATE TRIGGER TO "PERF_ENGINEERING_RSAARC"
GRANT CREATE PROCEDURE TO
ERROR:
ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7737
ORA-06512: at line 1
SYS@TSSUDALM SQL>
2 UNION ALL
3 select dbms_metadata.get_granted_
4 UNION ALL
5 select dbms_metadata.get_granted_
6 UNION ALL
7 select dbms_metadata.get_granted_
DBMS_METADATA.GET_DDL('USE
--------------------------
CREATE USER "PERF_ENGINEERING_RSAARC" IDENTIFIED BY VALUES 'S:EECA10D0E816B7
GRANT CREATE TRIGGER TO "PERF_ENGINEERING_RSAARC"
GRANT CREATE PROCEDURE TO
ERROR:
ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7737
ORA-06512: at line 1
SYS@TSSUDALM SQL>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
I think you'll find that dbms_metadata.get_ddl doesn't add the semi-colon to the end of the command so more than one command will not be 'valid' sql for a script.
You can try concatenating:
select dbms_metadata.get_ddl( 'USER','PERF_ENGINEERING_R
UNION ALL
...
But you might run into trouble if the generated SQL exceeds 4000 characters.