locked
convert sql function to Oracle function RRS feed

  • Question

  • User-1218295081 posted

    Can you please tell me how can i convert sql function to Oracle function

    create function [dbo].[Fn_CheckOrderStatus]
    (
    @OrderID bigint
    )
    returns bit
    as
    begin
    declare @Quantity int
    declare @ItemCount int
    declare @Result bit

    select @Quantity =sum(Quantity) from dbo.OrderDetails where OrderID = @OrderID

    SELECT @ItemCount=COUNT(dbo.ItemDetails.ItemDetailID)
    FROM dbo.OrderDetails
    INNER JOIN dbo.ItemDetails ON dbo.OrderDetails.OrderDetailID = dbo.ItemDetails.OrderDetailID
    where OrderID = @OrderID

    if(@Quantity = @ItemCount)
     set @Result = 1
    else
     set @Result = 0 

    return @Result
    end

     

    Thanks alot

    Sunday, October 23, 2011 1:24 PM

Answers

  • User269602965 posted

    You will have to convert the return NUMBER to a BOOLEAN (BIT) in the code calling the function.

    If calling the function from SQL, then BOOLEAN is not a supported datatype.

    CREATE OR REPLACE FUNCTION Fn_CheckOrderStatus (intOrderID IN NUMBER(32)) 
      RETURN NUMBER(1)
    IS
    DECLARE
      intQuantity  NUMBER(16);
      intItemCount NUMBER(16);
      intResult    NUMBER(1);
    
    BEGIN
     
    SELECT 
      SUM(Quantity) 
    INTO 
      intQuantity 
    FROM 
      OrderDetails 
    WHERE 
      OrderID = intOrderID
    GROUP BY 
      Quantity ;
     
    SELECT 
      COUNT(a.ItemDetailID) 
    INTO 
      intItemCount 
    FROM
      OrderDetails a
    INNER JOIN 
      ItemDetails  b 
    ON
      a.OrderDetailID = b.OrderDetailID
    WHERE 
      OrderID = intOrderID
    GROUP BY 
      a.ItemDetailID;
     
    IF intQuantity = intItemCount THEN
       intResult := 1;
    ELSE
       intResult := 0; 
    END IF;
    
    RETURN intResult;
    
    END;
    /
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 23, 2011 3:53 PM
  • User269602965 posted

    I like to populate a dataset directly using SQL statements in ODP.NET from code behind page,

    but sure, if you want to maintain code back at database in PL/SQL, you can call a cursor.

    you can call a PL/SQL package (group of procedures) and return a cursor.

    VB.NET code in ASP.NET application
    
        ' Get data from stored procedure '
        Try
          Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
          Using conn As New OracleConnection(connstr)
            Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcSelectBirdNames", conn)
              cmd.CommandType = CommandType.StoredProcedure
              cmd.Parameters.Clear()
              cmd.Parameters.Add("ListBirdNames", OracleDbType.RefCursor, ParameterDirection.Output)
              conn.Open()
              Using oda As New OracleDataAdapter(cmd)
                Dim ds As New DataSet()
                oda.Fill(ds)
                Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
              End Using
            End Using
          End Using
        Catch ex As Exception
        End Try
    
    Oracle PL/SQL code
        
    CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME} AS TYPE refCursor IS REF CURSOR;
      PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor);
    END {PACKAGENAME};
    /
    
    CREATE OR REPLACE PACKAGE BODY {PACKAGENAME} AS
    
    PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor)
    IS
    BEGIN
      OPEN ListBirdNames FOR
        SELECT BIRDNAME FROM {SCHEMANAME}.LKUP_BIRDNAME;
    END;
    
    END {PACKAGENAME};
    /
    
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 25, 2011 7:39 PM

All replies

  • User269602965 posted

    You will have to convert the return NUMBER to a BOOLEAN (BIT) in the code calling the function.

    If calling the function from SQL, then BOOLEAN is not a supported datatype.

    CREATE OR REPLACE FUNCTION Fn_CheckOrderStatus (intOrderID IN NUMBER(32)) 
      RETURN NUMBER(1)
    IS
    DECLARE
      intQuantity  NUMBER(16);
      intItemCount NUMBER(16);
      intResult    NUMBER(1);
    
    BEGIN
     
    SELECT 
      SUM(Quantity) 
    INTO 
      intQuantity 
    FROM 
      OrderDetails 
    WHERE 
      OrderID = intOrderID
    GROUP BY 
      Quantity ;
     
    SELECT 
      COUNT(a.ItemDetailID) 
    INTO 
      intItemCount 
    FROM
      OrderDetails a
    INNER JOIN 
      ItemDetails  b 
    ON
      a.OrderDetailID = b.OrderDetailID
    WHERE 
      OrderID = intOrderID
    GROUP BY 
      a.ItemDetailID;
     
    IF intQuantity = intItemCount THEN
       intResult := 1;
    ELSE
       intResult := 0; 
    END IF;
    
    RETURN intResult;
    
    END;
    /
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 23, 2011 3:53 PM
  • User-1218295081 posted

    Thank you Sir for your help

    but i want to know if there is any tool to convert sql query to oracle query

    Thank you again ... good luck

    Monday, October 24, 2011 5:07 AM
  • User269602965 posted

    I have never seen a tool to reliably convert stored procedures between various databases.

    So it ends up being an in-house or sub-contracted programming effort by someone familiar with both databases.

    Although, MSSQL and Oracle have their roots in ANSI-SQL, there are differences in datatypes, punctuation, lengths allowed in database object names, etc.

    as you have seen in the sample code I presented in last post.

    Sometimes, it gets very confusing going back and forth between the two.

    http://www.amazon.com/Oracle-Database-Programming-Osborne-ORACLE/dp/0071494456/ref=sr_1_5?ie=UTF8&qid=1319464046&sr=8-5

    Monday, October 24, 2011 9:48 AM
  • User-1218295081 posted

    what if i have multiple row should i use curser??

    Tuesday, October 25, 2011 6:25 PM
  • User269602965 posted

    I like to populate a dataset directly using SQL statements in ODP.NET from code behind page,

    but sure, if you want to maintain code back at database in PL/SQL, you can call a cursor.

    you can call a PL/SQL package (group of procedures) and return a cursor.

    VB.NET code in ASP.NET application
    
        ' Get data from stored procedure '
        Try
          Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
          Using conn As New OracleConnection(connstr)
            Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcSelectBirdNames", conn)
              cmd.CommandType = CommandType.StoredProcedure
              cmd.Parameters.Clear()
              cmd.Parameters.Add("ListBirdNames", OracleDbType.RefCursor, ParameterDirection.Output)
              conn.Open()
              Using oda As New OracleDataAdapter(cmd)
                Dim ds As New DataSet()
                oda.Fill(ds)
                Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
              End Using
            End Using
          End Using
        Catch ex As Exception
        End Try
    
    Oracle PL/SQL code
        
    CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME} AS TYPE refCursor IS REF CURSOR;
      PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor);
    END {PACKAGENAME};
    /
    
    CREATE OR REPLACE PACKAGE BODY {PACKAGENAME} AS
    
    PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor)
    IS
    BEGIN
      OPEN ListBirdNames FOR
        SELECT BIRDNAME FROM {SCHEMANAME}.LKUP_BIRDNAME;
    END;
    
    END {PACKAGENAME};
    /
    
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 25, 2011 7:39 PM