Convert T-SQL function to PL/SQL
- by llasarov
can you help me convert following T-SQL funcntion into Oracle. The function converts a string like
service|nvretail;language|de;yyyy|2011;
to a table.
The main problem I have is the usage of the temp table. I could not find any equivalent to it in Oracle.
CREATE FUNCTION [dbo].[TF_ConvertPara]
(
@parastringNVARCHAR(max)
)
RETURNS @para TABLE
(
[Key] varchar(max),
[Value] varchar(max)
)
begin
DECLARE @NextString NVARCHAR(40)
DECLARE @Pos1 INT
DECLARE @Pos2 INT
DECLARE @NextPos INT
DECLARE @Delimiter1 NCHAR=';'
DECLARE @Delimiter2 NCHAR='|'
if substring(@paraString, len(@paraString) - 1, 1) <> @Delimiter1
SET @paraString = @paraString + @Delimiter1
SET @Pos1 = charindex(@Delimiter1, @paraString)
WHILE (@pos1 <> 0)
BEGIN
SET @NextString = substring(@paraString, 1, @Pos1 - 1)
SET @paraString = substring(@paraString, @pos1 + 1, len(@paraString))
SET @pos1 = charindex(@Delimiter1, @paraString)
SET @Pos2 = charindex(@Delimiter2, @NextString)
if (@Pos2 > 0)
begin
insert into @para
values
(substring(@NextString, 1, @Pos2 - 1),
substring(@NextString, @Pos2 + 1, len(@NextString)))
end
END
return;
end
Thank you in advance.
