msimons4
asked on
Metadata
I'm trying to write the metadata results from DBMS_METADATA.get_dependen t_ddl to a file, but am getting errors.
declare
v_file_handle UTL_FILE.file_type;
indexes_metadata VARCHAR2(50);
begin
v_file_handle := UTL_FILE.fopen('DATA_MODEL ', 'DDL_' || indexes_metadata || '.sql', 'w', 32767);
FOR cur_rep IN
(SELECT DBMS_METADATA.get_dependen t_ddl ('INDEX', 'TABLE_NAME', 'SCHEMA_NAME') output from dual
)
LOOP
UTL_FILE.put_line(v_file_h andle, cur_rep.output);
END LOOP;
UTL_FILE.fclose(v_file_han dle);
end;
/
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at line 9
declare
v_file_handle UTL_FILE.file_type;
indexes_metadata VARCHAR2(50);
begin
v_file_handle := UTL_FILE.fopen('DATA_MODEL
FOR cur_rep IN
(SELECT DBMS_METADATA.get_dependen
)
LOOP
UTL_FILE.put_line(v_file_h
END LOOP;
UTL_FILE.fclose(v_file_han
end;
/
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at line 9
Try an upper case 'W' to open for Write.
ASKER
I did, but same results. Any other ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Apologizes. I thought the mode was case sensitive.
The problem might be that get_dependent_ddl returns a CLOB and put_line accepts a varchar2.
I'm not sure what error utl_file will generate if it tires to write a buffer with over 4000 characters in it.
The problem might be that get_dependent_ddl returns a CLOB and put_line accepts a varchar2.
I'm not sure what error utl_file will generate if it tires to write a buffer with over 4000 characters in it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It should have write privileges since the /u01 mount point has:
chown -R oracle:oinstall /u01
chmod -R 775 /u01
chown -R oracle:oinstall /u01
chmod -R 775 /u01
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, DATA_MODEL is down a couple of directories in this mount point.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
you didn't need to accept my comment about how to close as part of the answer , because it clearly wasn't.
glad we could help,
but for future questions, it's best to just accept the posts that contribute to your answer
glad we could help,
but for future questions, it's best to just accept the posts that contribute to your answer