Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-06550 and PLS-00905 error while compiling PLSQL for flat file creation

839046Feb 11 2011 — edited Feb 11 2011
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 11 2011
Added on Feb 11 2011
7 comments
8,072 views