Bfiles

Hello,

I just want to get some chars from a textfile to be shown via SQL+. My intention is to demonstrate the random access possibility to a LOB in Oracle.
Therefore I put a BFILE into a table and try to read and output a small part from the referenced textfile to the screen.

Here is my code:

CREATE TABLE PLSQL(
text_id NUMBER(5) PRIMARY KEY,
text_file BFILE)

CREATE OR REPLACE directory SAMPLES as 'C:\samples'

INSERT INTO PLSQL(text_id,text_file)
values(1,BFILENAME('samples','erle.txt'))

declare
locator_var BFILE:=BFILENAME('SAMPLES','erle.txt');
amount_var INTEGER;
offset_var INTEGER;
output_var VARCHAR2(10);
begin
amount_var:= 10;
offset_var:= 1;
select text_file into locator_var from PLSQL
where text_id = 1;
DBMS_LOB.OPEN(locator_var, DBMS_LOB.LOB_READONLY);
DBMS_LOB.READ(locator_var, amount_var, offset_var, output_var);
DBMS_OUTPUT.PUT_LINE('Begin of Erle: ' || output_var);
end;
/

COMMIT;

when I run the skript I get the following error message:
"SQL> start demo_plsql;
declare
*
FEHLER in Zeile 1:
ORA-22285: Verzeichnis oder Datei fr FILEOPEN-Vorgang ist nicht vorhanden
ORA-06512: in "SYS.DBMS_LOB", Zeile 672
ORA-06512: in Zeile 11"

The directory exists and the file is also there.
So where could be the problem?

In hope for any suggestions.

MaxSorry, can't read German... yet :)

However, it looks like your problem is here:

INSERT INTO PLSQL(text_id,text_file)
values(1,BFILENAME('samples','erle.txt'))

Input into BFILENAME first parameter needs to be upper case in order to match your specification in the CREATE DIRECTORY command.

JoeB

Best way to search

I have a stored procedure declared (shown below) The intent of the stored
proc is to return all records where the field values match the criteria
specified in the stored proc parameters. I want to specify some or all of
the parameter values. What I have written works, but I don't think it is
very efficient, any ideas?
CREATE PROCEDURE dbo.pSearch
@.strFirstName varchar(50) = NULL,
@.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
@.iYear int = null
SELECT TOP 50
p.[ID],
np.[Name] as Prefix,
p.[FirstName],
p.[MiddleName],
p.[LastName],
p.[DateOfBirth]
FROM
[Patient] p
JOIN
[NamePrefix] np ON p.NamePrefixID = np.[ID]
WHERE
(@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
(@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
(@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
(@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
(@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
ORDER BY
p.[LastName],
p.[MiddleName],
p.[FirstName]
GOHave a look at
http://www.sommarskog.se/dyn-search.html
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Jeremy Chapman" <nospam@.please.com> wrote in message
news:eSzgjdfhGHA.3424@.TK2MSFTNGP05.phx.gbl...
>I have a stored procedure declared (shown below) The intent of the stored
>proc is to return all records where the field values match the criteria
>specified in the stored proc parameters. I want to specify some or all of
>the parameter values. What I have written works, but I don't think it is
>very efficient, any ideas?
> CREATE PROCEDURE dbo.pSearch
> @.strFirstName varchar(50) = NULL,
> @.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
> @.iYear int = null
>
> SELECT TOP 50
> p.[ID],
> np.[Name] as Prefix,
> p.[FirstName],
> p.[MiddleName],
> p.[LastName],
> p.[DateOfBirth]
> FROM
> [Patient] p
> JOIN
> [NamePrefix] np ON p.NamePrefixID = np.[ID]
> WHERE
> (@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
> (@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
> (@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
> (@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
> (@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
> ORDER BY
> p.[LastName],
> p.[MiddleName],
> p.[FirstName]
> GO
>