Hi all,
I tried to translate T-SQL to PL/SQL using scratch editor in SQL Developer. The T-SQL consists of CTE on its query.
T-SQL
CREATE FUNCTION [dbo].[Get_ChannelHierarchy]
(@ChannelID int)
RETURNS @ChannelHierarchy TABLE
([ChannelID] int Primary Key,
[ParentChannelID] int,
RowID int)
AS
BEGIN
WITH [ChildChannelTree] ([ChannelID],[ParentChannelID],[RowID]) AS
(
SELECT
[ChannelID]
,[ParentChannelID]
,1 AS [RowID]
FROM [dbo].[Channels] WITH (NOLOCK)
WHERE ChannelID = @ChannelID
UNION ALL
SELECT
[c].[ChannelID]
,[c].[ParentChannelID]
,[cct].[RowID] + 1
FROM [dbo].[Channels] AS [c] WITH (NOLOCK)
INNER JOIN [ChildChannelTree] AS [cct] ON [c].[ChannelID] = [cct].ParentChannelID
WHERE [c].[ChannelID] <> 0
),
-- CTE Channel superset
[ChannelTree] ([ChannelID],[ParentChannelID],[RowID]) AS
(
SELECT
[ChannelID]
,[ParentChannelID]
, [RowID]
FROM [ChildChannelTree] AS [cct] WITH (NOLOCK)
UNION ALL
SELECT
[c].[ChannelID]
,[c].[ParentChannelID]
,[cct].[RowID] + 1
FROM [dbo].[Channels] AS [c] WITH (NOLOCK)
INNER JOIN [ChildChannelTree] AS [cct] WITH (NOLOCK) ON [c].[ChannelID] = [cct].ParentChannelID
WHERE [c].[ChannelID] = 0 -- Core Channel only
AND [cct].[ChannelID] <> 0
)
INSERT INTO @ChannelHierarchy
SELECT * FROM [ChannelTree]
OPTION (MAXRECURSION 20);
RETURN;
END
Output, translated by scratch editor.
SQL> CREATE OR REPLACE FUNCTION Get_ChannelHierarchy(
2 v_ChannelID IN NUMBER )
3 RETURN Get_ChannelHierarchy_pkg.tt_v_ChannelHierarchy_type PIPELINED
4 AS
5 v_temp SYS_REFCURSOR;
6 v_temp_1 TT_V_CHANNELHIERARCHY%ROWTYPE;
7 BEGIN
8 WITH "ChildChannelTree" ( "ChannelID", "ParentChannelID", /*"ChannelID", "ParentChannelID",*/ "Row_ID" ) AS
9 (SELECT ChannelID ,
10 ParentChannelID ,
11 1 Row_ID
12 FROM Channels
13 WHERE ChannelID = v_ChannelID
14 UNION ALL
15 SELECT c.ChannelID ,
16 c.ParentChannelID ,
17 cct.Row_ID + 1
18 FROM Channels c
19 JOIN ChildChannelTree cct
20 ON c.ChannelID = cct.ParentChannelID
21 WHERE c.ChannelID <> 0 -- exclude Core Channel due to infite loop w/ParentChannelID
22 ),
23 -- CTE Channel superset
24 "ChannelTree" ( "ChannelID", "ParentChannelID", /*"ChannelID", "ParentChannelID",*/ "Row_ID" ) AS
25 ( SELECT ChannelID , ParentChannelID , Row_ID FROM ChildChannelTree cct
26 UNION ALL
27 SELECT c.ChannelID ,
28 c.ParentChannelID ,
29 cct.Row_ID + 1
30 FROM Channels c
31 JOIN ChildChannelTree cct
32 ON c.ChannelID = cct.ParentChannelID
33 WHERE c.ChannelID = 0 -- Core Channel only
34 AND cct.ChannelID <> 0
35 )
36 select * from ChannelTree;
37 INSERT INTO tt_v_ChannelHierarchy
38 ( SELECT * FROM ChannelTree
39 ) ;
40 OPEN v_temp FOR SELECT * FROM tt_v_ChannelHierarchy;
41 LOOP
42 FETCH v_temp INTO v_temp_1;
43 EXIT
44 WHEN v_temp%NOTFOUND;
45 PIPE ROW ( v_temp_1 );
46 END LOOP;
47 END;
Execution of this PL/SQL has following errors:
8/1 PL/SQL: SQL Statement ignored
19/7 PL/SQL: ORA-00942: table or view does not exist
37/2 PL/SQL: SQL Statement ignored
38/19 PL/SQL: ORA-00942: table or view does not exist
Should table "ChannelTree" need to be created first? I've crosscheck in the source db (MSSQL), no user table "ChannelTree" exist.
Looking forward to get your advise on this as I'm pretty much new to PL/SQL.
Thanks!!