ATTN: SPENGLERR Error when DBMS_LOB.fileopen

[borisch]

Thanks for your help but I still have a problem. I am trying to execute package/Procedure and I am getting an error:
ORA-00604: error occurred at recursive SQL level 1
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "SYS.DBMS_LOB", line 370
ORA-06512: at "SMKT.INS_SESSION", line 19
ORA-06512: at line 1
It happens when I am trying to open file.
Line: DBMS_LOB.fileopen(theBFile,DBMS_LOB.FILE_READONLY);

Can you tell me what is wrong pleaseeeeeeeeee
Here is a procedure:
CREATE OR REPLACE PROCEDURE SMKT.ins_session(p_logid in varchar2,p_sessionid in varchar2, dir in varchar2, file in varchar2, p_userid in varchar2)
IS
theBFile BFILE;
theClob CLOB;

BEGIN
-- (1) insert a new row into xml_documents with an empty CLOB and
-- (2) Retrieve the empty CLOB into a variable

INSERT INTO INT_SESSIONLOG (LogID, SessionID, datecreated, PageXML, XHTML, UserID)
VALUES (p_logid,p_sessionid,sysdate,empty_clob(),empty_clob(),p_userid)
RETURNING PageXML INTO theClob;


-- (3) Get a BFile handle to the external file
theBFile := BFileName(dir,file);

-- (4) Open the file
DBMS_LOB.fileopen(theBFile,DBMS_LOB.FILE_READONLY);


-- (5) Copy the contents of the BFile into the empty CLOB
DBMS_LOB.loadFromFile (dest_lob => theCLOB, src_lob => theBFile,amount => DBMS_LOB.GETLENGTH(theBFile) );
DBMS_LOB.fileClose(theBFile);

end ins_session;

Thanks a lot

 

[spenglerr]

Hi borisch,

actually you try to open a BFILE handle using a directory!
1.) The directory specified has to be on the DB-server
2.) The schema has to have CREATE ANY DIRECTORY-permissions
3.) The directory specified has to be created:
/*
|| Directory-Name for the XML-Files
*/
CREATE DIRECTORY xmlfiles AS 'D:\Baynet\XML_Files';
4.) even though the directory has been created using small letters, the name has to be given to your procedure in CAPITALS.

hope this helps

 

[borisch]

thanks a lot. It helped. thanks again.