0

I have a SQL Server function and I want to convert to Oracle function.

This is my function:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SUB_FOLDERS]( @GRUP_GRUP NVARCHAR(30),@STATU NVARCHAR(30)) 
RETURNS @Strings TABLE (GRUP_KODU nvarchar(100)) 
AS 
BEGIN 
DECLARE @sonuc nvarchar(4000) 
DECLARE merge_cursor CURSOR FAST_FORWARD FOR WITH temptab(id)
 AS 
( SELECT root.GRUP_KODU FROM GRUP_TABLE  root WHERE GRUP_KODU=@GRUP_GRUP AND DURUM=@STATU 
UNION ALL 
SELECT sub.GRUP_KODU FROM GRUP_TABLE sub, temptab super WHERE sub.GRUP_GRUP = super.id ) 
SELECT * FROM temptab OPEN merge_cursor WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @sonuc='' 
FETCH NEXT FROM merge_cursor INTO @sonuc if (@sonuc<>'') 
INSERT INTO @Strings VALUES (@sonuc) 
END 
RETURN 
END
GO

When I run it on SQL Server it works properly but on Oracle, it gives error.

Error report:
SQL Command: functıon SUB_FOLDERS
Failed: ORA-24344: success with compilation error
24344. 00000 -  "success with compilation error"
*Cause:    A sql/plsql compilation error occurred.
*Action:   Return OCI_SUCCESS_WITH_INFO along with the error code

Thanks in advance.

burhan akgül
  • 9
  • 1
  • 2
    Did you read Oracle's PL/SQL [language reference](https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/index.html)? –  Feb 02 '21 at 07:01
  • Using cursors is a bad idea. Doing so in a cursor is far, far worse.*Fix* the T-SQL query by removing the cursor and the conversion will be a lot easier. Even the `@Strings` table seems pointless. Why not just return the results of the query???????? – Panagiotis Kanavos Feb 02 '21 at 07:10
  • The original function could be replaced with `create function Sub_Folders (...) RETURNS TABLE AS RETURN (SELECT root.GRUP_KODU FROM... UNION ALL SELECT sub.GRUP_KODU ...)`. This isn't only simpler, it's also *faster* because the server can merge this query with the outer query and optimize it – Panagiotis Kanavos Feb 02 '21 at 07:25
  • Was the original query converted *from PL/SQL* perhaps? SQL Server doesn't need a cursor to return query results in stored procedures and functions. Oracle does [as this probably duplicate question shows](https://stackoverflow.com/questions/46993559/return-a-table-in-a-pl-sql-function). This query won't return all nested. groups though, only the first level. You need a [recursive CTE](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15#d-using-a-recursive-common-table-expression-to-display-multiple-levels-of-recursion) – Panagiotis Kanavos Feb 02 '21 at 07:32
  • So what is your question? You obviously have to convert it to run on Oracle - what have you tried? Where did you get stuck? – Dale K Feb 02 '21 at 09:35
  • @PanagiotisKanavos fwiw, using cursors in Oracle isn't a bad thing at all (unless you're misusing them by doing row-by-row processing when you should be doing set-based processing, of course!). In fact, I'd be suggesting the OP uses a ref cursor in their Oracle function, and change the calling code to handle the cursor directly. – Boneist Feb 02 '21 at 09:54
  • @Boneist it is in this case, because it prevents the database from inlining the function and coming up with a good execution plan for the entire query. An inline table valued function is essentially a parameterized view. – Panagiotis Kanavos Feb 02 '21 at 10:05
  • @PanagiotisKanavos Can I return the table that created the transaction directly without creating a table type type? – burhan akgül Feb 03 '21 at 07:46

0 Answers0