Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41

    Unanswered: pl/sql: adding rows to a cursor multiple times

    Hi,

    I need to get a date field from multiple tables and find out which one is the latest. There could be zero or more records in any single table that matches my condition.

    My approach is select all the dates from each table and then merge them together, and then find out what one is the newest. ugly, but that's how much I know of PL/SQL.

    I am wondering if there is a better approach, i.e., appending the values to a cursor?

    Thanks in advance!

    -mongo

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > appending the values to a cursor?
    What does this mean in English?

    Is the number of the tables being queried fixed & known?
    Are the names of the tables with the dates fixed & known?

    You might want to read up on the GREATEST function:
    http://download-west.oracle.com/docs...s49a.htm#77475
    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
    Apr 2004
    Location
    USA
    Posts
    33
    select max(md) from (
    select mydate md from t1
    union
    select mydate from t2
    union
    select mydate from t3);
    Do unto others as you would have others do unto you !!!

  4. #4
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41
    thanks, i still would like to know more about pl/sql. seems i can never pass the usual "select into".

    -mongo

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > i still would like to know more about pl/sql.
    Then why don't you take the time to RTFM -
    http://download-west.oracle.com/docs...a96624/toc.htm

    >seems i can never pass the usual "select into".
    In PL/SQL there is no need to ever do so.
    Besides this construct is limited to returning a single row.
    Why do you ever want to limit the code in this manner?
    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.

  6. #6
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41
    easy man. i did RTFM. but what I was looking for was a better way of doing it in PL/SQL instead of using collections or cursors. I wish I had the ability to write procedures in Java, but then your database has to support java...

    peace and love

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but then your database has to support java
    What makes you erroneously conclude that Oracle does not support Java?
    http://download-west.oracle.com/docs...a96656/toc.htm

    http://download-west.oracle.com/docs...a96659/toc.htm
    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.

  8. #8
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41
    Sorry, I didn't explain clearly. i know oracle supports java. the thing I don't like about using it is the extra jvm and the average pool_size used.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >the thing I don't like about using it is the extra jvm
    "extra jvm"????
    The JVM inside the database is NOT "extra".
    It is necessary to run Java code within the DB.
    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.

  10. #10
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Mongo is quite right, the Oracle engine instantiates a fairly large JVM per connection (regardless of your shared/dedicated server settings). It can be quite RAM intensive.

    Some of us choose to not use or install Java support on the database for this very reason. In this sense, the term 'Extra' (while grammatically incorrect) is from a technical standpoint quite meaningful ... ie I know what Mongo means. I guess when I turn into a nitpicking arrogant poster I'll just give out RTFM's instead.

    Your RTFM advice is good, maybe you should try it.

    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  11. #11
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi mongo,

    Incidentally, referring back to arvindrams earlier, helpful, useful and constructive advice... you may the find following less resource hungry...

    Code:
    select max(md) from (
    select max(mydate) md from t1
    union
    select max(mydate) from t2
    union
    select max(mydate) from t3);
    Hth
    Bill - trying to be helpful, useful and constructive.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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