Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2007
    Posts
    21

    Unanswered: Searching Function

    I have a strange function to create.

    In our system, we need a "search all" function that will search a different table based on the users input.

    All the tables have the same columns but represent different data (and slightly different other structures)

    so the pseduo code for the function would be like this:

    Code:
    Create Function MySearch
      If User Chose option 1 then
        insert from table 1 into a temp table or cursor
      end
      If User Chose option 2 then
        insert from table 2 into a temp table or cursor
      end
    
      select * from temp table or cursor
    End Function
    Does this make sense?

    I would like users opinions on temp tables in oracle and if cursors are the best way to go, how to select from them.

    Thanks heaps!!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Alternatively fire the previous "data architect" & redesign the application.
    "same columns" represent "different data" How bizzare!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2007
    Posts
    21
    I knew someone was going to say that!

    Sadly we dont have that luxury

    The app (like many) was designed to be very small, and grew over a fwe years, many different developers had their hands on it, its a bit of a mess.

    Its not too bad, the similar tables do have their differences and such.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Its not too bad, the similar tables do have their differences and such.
    Others can NOT rewrite history.
    When you find yourself in a hole the best cousre of action is to stop digging.
    You're On Your Own.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2007
    Posts
    21
    Quote Originally Posted by anacedent
    Others can NOT rewrite history.
    I am not asking you (or anyone else) to re-write/design anything. In what world do time lines and business costs NOT come into play? I cant re-design it. its FAR too big to do that.

    All I want to know is the best way to write a searching procedure or function in Oracle, given that a user can choose different tables on the web form. Surely this is done all around the world on a daily basis.

    It boils down to needing to know how to use multiple tables in a query, or cursors.

    Can it be done? is there any articles out there that can help me?

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You shouldnt need temp tables as you should be able to use dynamic sql to select from whichever table the user requires.

    Check out the plsql manual on OPEN-FOR cursors.

    Also to cut out a great big IF THEN ELSIF... clause (if you have a lot of options) then put the user selected option ID and the table to select from in a table. So when a new option comes along you dont alter the procedure just the table.

    Alan

  7. #7
    Join Date
    Jun 2007
    Posts
    21
    Thats a good idea, I will look into that.

    I didn't think I would need temp tables either, apparently Oracle's cursors are a lot better then the MSSQL ones!

    So basically you think that I should build up by SQL dynamically?

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you use dynamic sql you cut out all the hard coded queries, especially if the tablenames are stored in a table themselves.

    Also what do you do with the selected data, if it needs to be passed back to a calling app you may need to use a REF cursor (where the calling app executes a cursor passed back from the procedure)?

    Alan

  9. #9
    Join Date
    Jun 2007
    Posts
    21
    Well thats one of the questions that I did have as well.

    Currently I have a Function written that returns a ref cursor back to the Java app (but its not working for some strange reason, but thats for another thread).

    I will need the data in my app to work with. So I guess I need to pass back a ref cursor. Is it best to use a ref cursor as a return type from a function or as an output parameter in a procedure?

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It doesnt really matter wether its a function or a procedure whichever is more consistent with your existing style of coding.

    If you google oracle dynamic ref cursor you wil find some good examples.

    Alan

  11. #11
    Join Date
    Jun 2007
    Posts
    21
    Thanks Alan, I guess I will have more of a look around on Google as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •