dbms_lob conversion

hi,
am interested in porting some of my oracle quries to sqlserver. Here is a sample oracle procedure:
create procedure sp1(ac_clob clob)
as
n number;
position number;
begin
position := 4;
n := dbms_lob.instr(ac_clob,'test', position); --returns the location of the substring
n: = dbms_lob.getlength(ac_clob); --returns the length of the clob object
end;
As the clob object comes as a parameter let me know if it can be converted in sql server. Also I some other functions to dbms_lob packages like: dbms_lob.copy, dbms_lob.write, dbms_lob.trim,Hi,

SQL Server does not have the exact same functionality as the DBMS_LOB package. For example, there is no direct I/O between files and blob variables, and blob variables are not allowed as local variables. You need to use BULK INSERT and/or chunk-mode reads and writes from TSQL. ADO as a Stream class that makes this much easier from a client app.

In SQL2K we do support some functions on text columns (like CLOBs in Oracle) from the TSQL level, including DATALENGTH(), PATINDEX(), and SUBSTRING().

See:
Managing ntext, text, and image Data
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_13_8orl.asp

In SQL 2005, we've introduced a new feature called "large-value types", which allow you to treat CLOBS just like normal string values. You can declare local variables, use them in expressions, pass them as parameters, return them from functions, cast to/from XML, and so on. They have become a first-class data type in the TSQL language. Their declarations look like

varchar(max), nvarchar(max), varbinary(max)

It's really a great SQL 2005 feature, and it makes life *much* easier when dealing with CLOBS and BLOBs from within TSQL.

Regards,
Clifford Dibble
Program Manager, SQL Server|||Thanks for your suggestions|||In addition to what Clifford says, you can take a look at the CHARINDEX, PATINDEX and SUBSTRING functions which give you the functionality you have in your sample code. These should work in both SQL 2000 and SQL 2005.

- Christian