none
Convert Oracle Type to SQL RRS feed

  • Question

  • Hi,

    I am trying to figure out how to change the Oracle statement to SQL

    TYPE hsfo_type IS RECORD
      (asat_date          DATE,
      mod_date            DATE,
      valid               CHAR(1)
    );

    TYPE cur_hsfo_type IS REF CURSOR RETURN hsfo_type;

    Any idea how to convert on this 1?

    Thursday, May 21, 2009 2:46 AM

Answers

  • Which Version of sql server you have? in 2008 you can create table-valued parameter can be used


    CREATE TYPE hsfo_type AS TABLE (
    asat_date          DATE,
      mod_date            DATE,
      valid               CHAR(1)
    )


    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    • Marked as answer by leotheram Thursday, May 21, 2009 4:19 AM
    Thursday, May 21, 2009 2:57 AM
    Moderator

All replies

  • Which Version of sql server you have? in 2008 you can create table-valued parameter can be used


    CREATE TYPE hsfo_type AS TABLE (
    asat_date          DATE,
      mod_date            DATE,
      valid               CHAR(1)
    )


    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    • Marked as answer by leotheram Thursday, May 21, 2009 4:19 AM
    Thursday, May 21, 2009 2:57 AM
    Moderator
  • It's 2008! .. Thanks.. How about this cursor?

    TYPE cur_hsfo_type IS REF CURSOR RETURN hsfo_type;
    Thursday, May 21, 2009 4:07 AM
  • for last three months i have been working in a Oracle project and with that knowlege (may be i am wrong) i would say REF_CURSOR equivalent is Temptable/tablevar in SQL Server. There is fundamental architecture difference in the way how these types are inplemented in Oracle. In SQL Server you can create a temprorary table inside a procedure , push the data into in (like BULKCOLLECT what you have in oracle) and you can do a Select *From thattemptable as a last statment which will retrun all the column in the temp table as recordset to the data consumer.

    There are table variable and Temprorary table in sql server and you can find lot of information about when to go for each.  If you DONOT WANT to reuse this type (hsfo_type) , and since it is a RECORD (that means only one row)   you dont need anything to declare you can directly return a record set in the sp like

    create proc spTest
    @someinputparm int
    as
    Begin

    Select asat_date         ,  mod_date            ,  valid    from sometable where somecolumn=@someinputparm

    End

    when you call this sp  EXEC spText 1

    You will get a records set (it can be sigle row or multipe).

    Madhu


    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Thursday, May 21, 2009 4:17 AM
    Moderator
  • Hey Madhu, this is a bit outside my area, but would an OUTPUT cursor variable be at all similar?

    Paul
    Thursday, May 21, 2009 6:12 AM
  • Hey Madhu, this is a bit outside my area, but would an OUTPUT cursor variable be at all similar?

    Paul

    Hi Paul,

    u taught me a new thing today. I never used OUTPUT cursor (in SQL Server from the day one we hear that CURSOR is bad so somehow i overlooked this option).  I think you are correct and it looks like OUTPUT cursor fits here if the type do not needs to reuse (may be we are wrong). As you said this area is even new for me but i can co-relate things in SQL Server and oracle i hope.

    One thing i have seen in oracle , as we say Cursors are bad in sql server, in oracle they do not believe that. They use cursors extensively (at least the project i have seen  (which is in 8i we are migrating to 10g ). But in 10g there are other options avaliable.

    Anyhow, thanks for that info

    Edit : forgot to post the link :Output cursor ? see here.  http://msdn.microsoft.com/en-us/library/ms175498(SQL.90).aspx
    Madhu

    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Thursday, May 21, 2009 6:23 AM
    Moderator
  • Hey that's cool Madhu - I seem to come across at least one new thing in SQL Server everyday too - I guess I will start to worry when that stops...!

    I'm not sure that the OUTPUT cursor is a great solution, at least in SQL Server, but what you said about Oracle rang a faint bell.  I think I must have had this debate with one of the Oracle guys when I first came across Oracle (8i) and saw how extensively cursors were used.  My guess is that cursors are implemented very differently in Oracle - they seem to be the optimal or only solution in many cases, whereas we usually strive t avoid them in SQL Server.

    Thanks again for taking the time to reply, and providing the reference I missed.

    Paul
    Thursday, May 21, 2009 8:11 AM