Re: Convert stored procedure

I'm not a SQL-Server (yuck! *grin*) person but looking at the code, it seems quite easy to convert to an Oracle Stored procedure.

However, i'm unsure of the significance of the '_at_'s. Also the code below the 'Get Tabs list' doesn't put its values into any variables so again doesn't make sense to me.

The SQL code can be put into cursors and referred to without any problem.
Also, does 'bit' mean 'boolean' for Oracle?

I'm not gonna write the whole procedure for you since i'm not converese with some of the logic and the SQL Server stuff so maybe some kind soul can finish it for you.

Mark.

ps. For my efforts, my payment is a bottle of wine!

CREATE OR REPLACE PROCEDURE GetPortalSettings (

    in_PortalAlias           IN  varchar2,
    in_TabID                 IN  number,
    out_PortalID             OUT number,
    out_PortalName           IN  varchar2,
    out_AlwaysShowEditButton OUT BOOLEAN,       -- ??????
    out_TabName              OUT varchar2,
    out_TabOrder             OUT number,
    out_MobileTabName        OUT varchar2,
    out_AuthRoles            OUT varchar2,
    out_ShowMobile           OUT BOOLEAN        -- ??????

   CURSOR c_tab_0 IS

     SELECT 
         Portals.PortalID,
         Portals.PortalName,
         Portals.AlwaysShowEditButton,
         Tabs.TabID,
         Tabs.TabOrder,
         Tabs.TabName,
         Tabs.MobileTabName,
         Tabs.AuthorizedRoles,
         Tabs.ShowMobile
     FROM
         Tabs, Portals         
     WHERE
         Tabs.PortalID = Portals.PortalID
     AND Portal.PortalAlias= in_PortalAlias 
     ORDER BY
         Tabs.TabOrder;

   CURSOR c_tab_not_0 IS
     SELECT
         Portals.PortalID,
         Portals.PortalName,
         Portals.AlwaysShowEditButton,
         Tabs.TabName,
         Tabs.TabOrder,
         Tabs.MobileTabName,
         Tabs.AuthorizedRoles,
         Tabs.ShowMobile
     FROM
         Tabs, Portals
     WHERE
         Tabs.PortalID = Portals.PortalID         
     AND Tabs.TabID = in_TabID;
 

BEGIN   /* First, get Out Params */
  /* Use cursor to only obtain first record (could use other methods)

  IF in_TabID = 0 THEN

     FOR x IN c_tab_0 LOOP

         out_PortalID      = x.PortalID;
         out_PortalName    = x.PortalName;
         out_AlwaysShowEditButton = x.AlwaysShowEditButton;
         out_TabID         = x.TabID;
         out_TabOrder      = x.TabOrder;
         out_TabName       = x.TabName;
         out_MobileTabName = x.MobileTabName;
         out_AuthRoles     = x.AuthorizedRoles;
         out_ShowMobile    = x.ShowMobile;

         -- Only want first record, works since cursor is
         -- ordered by what we want, exit loop
         EXIT;

     END LOOP;

  ELSE
     
     -- I assume that the SQL will only return 1 row, if not amend
     -- Also shows alternative method of opening a cursor.

     OPEN c_tab_not_0;
     FETCH c_tab_not_0 INTO 
         out_PortalID,
         out_PortalName,
         out_AlwaysShowEditButton,
         out_TabName,
         out_TabOrder,
         out_MobileTabName,
         out_AuthRoles,
         out_ShowMobile;
     CLOSE c_tab_not_0;         

  END IF;

• You'll have to code the rest since logic from here looks strange

END GetPortalSettings;

boah123_at_hotmail.com (Boah) wrote in message news:<8202c755.0111061128.53c93fb8_at_posting.google.com>...
> Hello,
>
> I have a stored procedure in my microsoft sql server 2000.
> This is the code:
>
> CREATE PROCEDURE GetPortalSettings
> (
> _at_PortalAlias nvarchar(50),
> _at_TabID int,
> _at_PortalID int OUTPUT,
> _at_PortalName nvarchar(128) OUTPUT,
> _at_AlwaysShowEditButton bit OUTPUT,
> _at_TabName nvarchar (50) OUTPUT,
> _at_TabOrder int OUTPUT,
> _at_MobileTabName nvarchar (50) OUTPUT,
> _at_AuthRoles nvarchar (256) OUTPUT,
> _at_ShowMobile bit OUTPUT
> )
> AS
>
> /* First, get Out Params */
> IF _at_TabID = 0
> SELECT TOP 1
> _at_PortalID = Portals.PortalID,
> _at_PortalName = Portals.PortalName,
> _at_AlwaysShowEditButton = Portals.AlwaysShowEditButton,
> _at_TabID = Tabs.TabID,
> _at_TabOrder = Tabs.TabOrder,
> _at_TabName = Tabs.TabName,
> _at_MobileTabName = Tabs.MobileTabName,
> _at_AuthRoles = Tabs.AuthorizedRoles,
> _at_ShowMobile = Tabs.ShowMobile
>
> FROM
> Tabs
> INNER JOIN
> Portals ON Tabs.PortalID = Portals.PortalID
>
> WHERE
> PortalAlias=_at_PortalAlias
>
> ORDER BY
> TabOrder
>
> ELSE
> SELECT
> _at_PortalID = Portals.PortalID,
> _at_PortalName = Portals.PortalName,
> _at_AlwaysShowEditButton = Portals.AlwaysShowEditButton,
> _at_TabName = Tabs.TabName,
> _at_TabOrder = Tabs.TabOrder,
> _at_MobileTabName = Tabs.MobileTabName,
> _at_AuthRoles = Tabs.AuthorizedRoles,
> _at_ShowMobile = Tabs.ShowMobile
>
> FROM
> Tabs
> INNER JOIN
> Portals ON Tabs.PortalID = Portals.PortalID
>
> WHERE
> TabID=_at_TabID
>
> /* Get Tabs list */
> SELECT
> TabName,
> AuthorizedRoles,
> TabID,
> TabOrder
>
> FROM
> Tabs
>
> WHERE
> PortalID = _at_PortalID
>
> ORDER BY
> TabOrder
>
> /* Get Mobile Tabs list */
> SELECT
> MobileTabName,
> AuthorizedRoles,
> TabID,
> ShowMobile
>
> FROM
> Tabs
>
> WHERE
> PortalID = _at_PortalID
> AND
> ShowMobile = 1
>
> ORDER BY
> TabOrder
>
> /* Then, get the DataTable of module info */
> SELECT
> *
>
> FROM
> Modules
> INNER JOIN
> ModuleDefinitions ON Modules.ModuleDefID = ModuleDefinitions.ModuleDefID
>
> WHERE
> TabID = _at_TabID
>
> ORDER BY
> ModuleOrder
>
> GO
>
>
> Now I want to convert this to an oracle stored procedure.
> I do not have any experience with this.
>
> First question: Is it possible to convert this stored procedure?
>
> If yes, could somebody do this for me? (Offcourse, only this one)
>
> Many Thanks!
>
> Boah
Received on Wed Nov 07 2001 - 14:26:28 CET