3 Replies Latest reply: May 22, 2013 9:13 AM by ljames RSS

    How to write below function in Oracle?

    879152
      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?
          Pnauduri-Oracle
          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
            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?
              ljames
              Just curious

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