Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unanswered: sort data in a ref cursor

    Hello,

    I would like to know if the following is possible:

    I'm getting data from different sources in a ref cursor. Is it possible to sort this ref cursor on a specific column in the ref cursor? If so ... any suggestions how?

    Thanks
    Karuna

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I think you'll have to add an ORDER BY clause on the column you want to be sorted in the SELECT query that is the source of your REF CURSOR. AFAIK, a REF CURSOR cannot be sorted in itself.

    Regards,

    RBARAER

  3. #3
    Join Date
    Dec 2004
    Posts
    3
    My problem is ...
    I take the ref cursor, and have added some more data in there, based on a few conditions from different tables. So my ref cursor actually has more relevant data as compared to its source. I now need to find a way to sort that info. Is there a way around this?

    Thanks
    Karuna

  4. #4
    Join Date
    Nov 2004
    Posts
    8
    Use Dynamic Statements, and based on conditions concatenate those Dynamic Statements, and finally return to ref cursor. like example

    selectQuery_1 := Ref Cursor data string(I think select stement)

    selectQuery_2 := 'select ----
    from tables
    where <conditions>
    order by'; --- you are going to add conditions

    selectQuery := selectQuery_1||selectQuery_2.

    open ref_cursor for selectQuery;


    Thanks,

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I take the ref cursor, and have added some more data in there
    What do you mean ? You can't add data through a cursor (or it is something I am not aware of), you have to insert data into your table(s) with INSERT statements or update data with UPDATE statements on your table(s), but not directly on the cursor. AFAIK, cursors in Oracle are read-only... Furthermore, Your cursor is related to a SELECT query that reads data as they were at the point in time the execution of the query began (this is part of the read-consistency mechanism of Oracle), so AFAIK you can only read these "old data" with your cursor unless you re-execute your query after your inserts or updates. BTW, maybe you only need insert or update queries in a first time (no select), and when you're done, select the new data. What do you think ?

    To sankarg :

    Your solution would not work because of what I said above : karuna would still have to re-execute the query after the updates or inserts. Furthermore, be cautious about dynamic SQL without bind variables :

    1) in PL/SQL, dynamic SQL in general is less optimized than static SQL

    2) using concatenations like 'select ... from ... where '||where_clause is prone to sql injection (you can search the web for "sql injection" to learn more about that)

    3) instead of using ||, use bind variables when you can, search on asktom about "bind variables" to learn more about their necessity in PLSQL (when using dynamic SQL) and even more in L3G like C, C++, VB, Java... And look at the proper doc (depending on your language) for more info on how to use them.

    HTH and Regards,

    RBARAER

Posting Permissions

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