1

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

vikky
  • 171
  • 1
  • 1
  • 16
  • You might be able to do something with `execute immediate` with the create table as part of the string - not an advocate of this - but this is discussed here... https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:11670337038966 – JGFMK Oct 31 '18 at 11:55

0 Answers0