Oracle DB Version - 19c (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -)
I have to create over 150+ views based on oracle tables and want to automate the script.
For security reason I can not use UTL_FILE to generate individual view files
I have created a database package.procedure to fetch data from All_tab_source and generated Create_View script.
Is there any other way instead of UTL_FILE as I can't call SPOOL file with in PLSQL
--- Package.Procedure -- This generates Create or replace view along with Grants, Comments etc.
AUTOGENERATE_DDL_SCRIPTS.CREATE_VIEW (
I_SCHEMA => l_I_SCHEMA,
I_OBJECT_NAME => l_I_OBJECT_NAME,
I_OBJECT_TYPE => l_I_OBJECT_TYPE,
i_grantee_l => l_i_grantee_l,
i_grantee_2 => l_i_grantee_2,
i_grantee_3 => l_i_grantee_3,
i_grantee_4 => l_i_grantee_4,
i_grantee_5 => l_i_grantee_5,
i_grantee_6 => l_i_grantee_6,
O_RET_CODE => l_O_RET_CODE,
O_RET_MESG => l_O_RET_MESG);
-- In the package.procedure I am using DBMS_OUTPUT.PUT_LINE as below.
l_dynamic_create_script :=
'CREATE OR REPLACE FORCE VIEW '
|| i_schema
|| '.'
|| l_object_name
|| ' AS '
|| CHR(10)
|| ' Select '
|| l_columns_withCommas
|| CHR(10)
|| ' From '
|| i_object_name --- l_tables_withCommas
|| l_where_clause
|| CHR(10)
|| CHR(10)
|| 'COMMENT ON TABLE '
|| l_schema
|| '.'
|| l_object_name
|| ' IS '
|| CHR(10)
|| ''' [Purpose] - '
|| l_purpose||'.'
|| CHR(10)
|| ' [Criteria] - '
|| CHR(10)
|| ' [Source Systems] - '
|| CHR(10)
|| ' [Unique Identifier] - '
|| l_columns_identifier_with_commas
|| CHR(10)
|| ' [Usage] - Created for EDW System'''||';'
|| CHR(10)
|| CHR(10);
dbms_output.put_line(l_dynamic_create_script);
dbms_output.put_line(CHR(10) ||CHR(10)||l_dynamic_grants_script);
--- I have tested it works and generates DBMS_OUTPUT perfectly for all the views in the Cursor I am passing. However, its a manual work to split the view script into individual files.
Any Recommendations / Suggestions would be appreciated.
Regards, Rajat