I want to read text file, store it in a variable and output the variable in pl/sql. I am using the following code but getting error:statement ignored.
create or replace PROCEDURE blah
AS
DECLARE
f UTL_FILE.FILE_TYPE;
s VARCHAR2(200);
BEGIN
f := UTL_FILE.FOPEN('C:\Users\s420105\Music','blahh.txt','R');
UTL_FILE.GET_LINE(f,s);
UTL_FILE.FCLOSE(f);
dbms_output.put_line(s);
END;
I am not sure about UTL permission but i suppose it will throw different error if it is due to UTL permission. The
Several issues:
DECLARE statement. The syntax is CREATE [OR REPLACE] PROCEDURE [schema.]name AS ... BEGIN ... END;This code should work:
CREATE DIRECTORY DIR__MUSIC AS 'C:/Users/s420105/Music';
/
CREATE PROCEDURE blah
AS
f UTL_FILE.FILE_TYPE;
s VARCHAR2(200);
BEGIN
f := UTL_FILE.FOPEN( 'DIR__MUSIC', 'blahh.txt', 'R', 200 );
UTL_FILE.GET_LINE( f, s );
UTL_FILE.FCLOSE( f );
DBMS_OUTPUT.PUT_LINE( s );
END;
/
SHOW ERRORS;
/
One simple way to read the entire file is:
CREATE DIRECTORY DIR__MUSIC AS 'C:/Users/s420105/Music';
/
CREATE PROCEDURE blah
AS
f CLOB;
BEGIN
f := DBMS_XSLPROCESSOR.READ2CLOB( 'DIR__MUSIC', 'blahh.txt' );
DBMS_OUTPUT.PUT_LINE( f );
END;
/
SHOW ERRORS;
/
ORA- number? Also, has the user (who is compiling/running the procedure) got read permissions on the directory? Try GRANT READ ON DIRECTORY dir__music TO username; (from an appropriately priviledged user).GRANT command). I would suggest searching online for ORA-29283 and going through things people suggest for fixing the permissions.