I am new to the database concepts. I currently have SQL Server code which i am trying to convert to Oracle.
CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(20) )
AS
BEGIN
DECLARE @String VARCHAR(20)
WHILE LEN(@StringInput) > 0
BEGIN
SET @String = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1,
-1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput),
0),
LEN(@StringInput)) + 1, LEN(@StringInput))
INSERT INTO @OutputTable ( [String] )
VALUES ( @String )
END
RETURN
END
I am unable to find any concept of table variable in oracle.
Can anybody help me to know how to replace the table variable in oracle.
EDIT: Posting my conversion to oracle below based on my understanding from the questions already asked on SO:
CREATE TYPE Output AS OBJECT
(
str varchar2(20)
);
CREATE TYPE OutputTable AS TABLE OF Output;
Create or replace function ufn_CSVToTable(
p_StringInput varchar2)
Return OutputTable
as
v_String VARCHAR2(20);
v_StringInput varchar2(8000);
BEGIN
WHILE LENGTH(RTRIM(p_StringInput)) > 0
LOOP
v_String := SUBSTR(p_StringInput, 1,
NVL(NULLIF(INSTR(p_StringInput, ',') - 1, -1),
LENGTH(RTRIM(p_StringInput))));
v_StringInput := SUBSTR(p_StringInput,
NVL(NULLIF(INSTR(p_StringInput, ','), 0),
LENGTH(RTRIM(p_StringInput))) + 1,
LENGTH(RTRIM(p_StringInput)));
INSERT INTO Output( str )
VALUES ( v_String );
END LOOP;
RETURN;
END;
But i am still getting the below errors: 1.[Error] ORA-04044 (25: 21): PL/SQL: ORA-04044: procedure, function, package, or type is not allowed here. 2.[Error] PLS-00503 (29: 5): PLS-00503: RETURN statement required for this return from function