ORA-06550 and PLS-00905 error while compiling PLSQL for flat file creation
839046Feb 11 2011 — edited Feb 11 2011I am trying to create a PLSQL to write to a flat file on using Oracle Object Directory and UTL_FILE. Following is process and errors I am getting. Please help.
First I created an entry for the directory in Oracle
CREATE DIRECTORY TEST_Rptg_Dir AS 'C:\TEMP\TEST_RPTG';
Then created the procedure -
CREATE OR REPLACE PROCEDURE TESTDummy
AS
CURSOR C1 IS
SELECT field1, field2, field3 from CMP_TABLE; -- replace with actual dump SQL
col1 CMP_TABLE.field1%TYPE;
col2 CMP_TABLE.field2%TYPE;
col3 CMP_TABLE.field3%TYPE;
OutputRecord VARCHAR2(255);
OutputFile utl_file.FILE_TYPE;
BEGIN
OPEN C1;
FETCH C1 INTO col1, col2, col3;
OutputFile := utl_file.fopen(UPPER('TEST_Rptg_Dir'), 'Test1.csv', 'w', 32000); -- w = write, 32000 = line size in bytes
WHILE C1%FOUND LOOP
OutputRecord := col1 || ',' || col2 || ',' || col3 ;
utl_file.put (OutputFile, OutputRecord);
utl_file.new_line (OutputFile);
FETCH c1 INTO col1, col2, col3;
END LOOP;
CLOSE C1;
utl_file.fclose(OutputFile);
END;
Upon executing the procedure I am getting the following error
BEGIN TESTDummy; END;
Error at line 1
ORA-06550: line 1, column 7:
PLS-00905: object ADM.TESTDUMMY is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Appreciate all the help in this regards.
Thanks.