This discussion is archived
3 Replies Latest reply: May 22, 2013 7:13 AM by 976250 RSS

How to write below function in Oracle?

879152 Newbie
Currently Being Moderated
Hi,
How to write below function in Oracle with same no of Signature and
also how to execute with same way as sql Server?

In SQL Server:

CREATE FUNCTION [dbo].[wsp_user_RootMenu_200] (
@userlevel int,
@islamicYN char(1),
@user_code int
)
RETURNS TABLE
--wsp_user_RootMenu 2,'N', 10 
AS
RETURN
SELECT TOP 100 PERCENT MenuID,(case when @islamicyn='Y' then MenuNameIslamic else MenuName end) MenuName,
(case when @islamicyn='Y' then ToolTipIslamic else ToolTip end) ToolTip
FROM wv_MenuWeb_T a WHERE ParentID='ROOT'
--and UserLevel like '%1%'   
order by Depth, MenuSequence
GO


To execute:
select * from wsp_user_RootMenu_200(2,'N', 10)

Please reply.

Thanks
Solaiman
  • 1. Re: How to write below function in Oracle?
    84959 Explorer
    Currently Being Moderated
    SQLDeveloper 3.2.2.X with some new patches converts it as follows:

    /*Global Temporary Tables:1 *//* Translation Extracted DDL For Required Objects*/
    CREATE GLOBAL TEMPORARY TABLE tt_wsp_user_RootMenu_200
    AS (
    SELECT MenuID ,
    (CASE
    WHEN v_islamicyn = 'Y' THEN MenuNameIslamic
    ELSE MenuName
    END) MenuName ,
    (CASE
    WHEN v_islamicyn = 'Y' THEN ToolTipIslamic
    ELSE ToolTip
    END) ToolTip
    FROM wv_MenuWeb_T a

    );
    /
    CREATE OR REPLACE PACKAGE wsp_user_RootMenu_200_pkg
    AS
    TYPE tt_wsp_user_RootMenu_200_type IS TABLE OF tt_wsp_user_RootMenu_200%ROWTYPE;
    END;
    /


    CREATE OR REPLACE FUNCTION wsp_user_RootMenu_200
    (
    v_userlevel IN NUMBER,
    v_islamicYN IN CHAR,
    v_user_code IN NUMBER
    )
    RETURN wsp_user_RootMenu_200_pkg.tt_wsp_user_RootMenu_200_type PIPELINED
    AS
    v_temp SYS_REFCURSOR;
    v_temp_1 TT_WSP_USER_ROOTMENU_200%ROWTYPE;

    BEGIN
    --wsp_user_RootMenu 2,'N', 10
    TRUNCATE TABLE tt_wsp_user_RootMenu_200;

    INSERT INTO tt_wsp_user_RootMenu_200
    SELECT MenuID ,
    (CASE
    WHEN v_islamicyn = 'Y' THEN MenuNameIslamic
    ELSE MenuName
    END) MenuName ,
    (CASE
    WHEN v_islamicyn = 'Y' THEN ToolTipIslamic
    ELSE ToolTip
    END) ToolTip
    FROM wv_MenuWeb_T a
    WHERE ParentID = 'ROOT'
    ORDER BY
    --and UserLevel like '%1%'
    Depth,
    MenuSequence;

    OPEN v_temp FOR
    SELECT *
    FROM tt_wsp_user_RootMenu_200;

    LOOP
    FETCH v_temp INTO v_temp_1;
    EXIT WHEN v_temp%NOTFOUND;
    PIPE ROW ( v_temp_1 );
    END LOOP;
    END;


    Give it a try. You might run into some errors but generally this is one solution. You can also define the temp table directly with proper columns instead of using a SELECT statement.

    Regards

    Prakash
  • 2. Re: How to write below function in Oracle?
    879152 Newbie
    Currently Being Moderated
    Thanks for reply. I already know it. I am trying to find any other way.


    Thanks
    Solaiman
  • 3. Re: How to write below function in Oracle?
    976250 Newbie
    Currently Being Moderated
    Just curious

    Why you are trying to find another way? The conversion are not working for you using SQL-Developer?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points