Results 1 to 9 of 9

Thread: Simple Question

  1. #1
    Join Date
    Jun 2007
    Posts
    21

    Unanswered: Simple Question

    Hello everyone, i am quite new to Oracle but not new to databases, I have been working with MSSQL for a long time and now need some help with Oracle.

    I am using TOAD and I have created a Function for searching of data in my database.

    I have created a simple package for cursor types:

    Code:
    CREATE OR REPLACE PACKAGE types AS
      TYPE ref_cursor IS REF CURSOR;
    END types;
    /
    Then created the Function:

    Code:
    CREATE OR REPLACE FUNCTION SEARCH_WORK_ITEMS 
    	   RETURN types.ref_cursor
    as
    work_item_cursor types.ref_cursor;
    
    BEGIN
    	OPEN work_item_cursor FOR 
       	SELECT name FROM customer c
    	;
    
    	RETURN work_item_cursor;
    
       EXCEPTION
         WHEN NO_DATA_FOUND THEN
           NULL;
         WHEN OTHERS THEN
           -- Consider logging the error and then re-raise
           RAISE;
    
    END SEARCH_WORK_ITEMS;
    /
    Rather simple.

    But I CANNOT for the life of me figure out the best way to test this function in the SQL browser. how do I run it to see its values?

    Please help, this really is hurting my head!!!

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But I CANNOT for the life of me figure out the best way to test this function in the SQL browser.

    Please step back & THINK about various entities.

    Both SQL & PL/SQL are "programming" languages; but SQL <> PL/SQL.

    What is valid in 1 language is NOT necessarily valid in the other.

    Simply put a REF CURSOR is NOT a valid data type in SQL.

    So the answer you can NOT test your function in a "SQL Browser"; what ever that is.

    Your function is only valid within PL/SQL.
    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
    Thanks for the fast reply,

    What I should have said, was in TOAD I create a Function, then I want to test that functions in TOAD as well.

    I want to test it in the SQL Editor (not SQL Browser sorry)

    How do I do this?

    Thanks!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You create another block of PL/SQL that invokes your function.
    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
    Exactly, thats the problem, I cant seem to get it to work.

    How do you write a PL/SQL statement to run a function which returns a cursor, then see whats in the cursor?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It appears you forgot the advice in the #1 STICKY post at the top of this forum.

    http://asktom.oracle.com has many, many fine coding examples.
    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.

  7. #7
    Join Date
    Jun 2007
    Posts
    21
    Yeah I did actually read them, but it seems that I just cant find any examples on how to do this.

    If you could find one a link to it, or just show me the PL/SQL I need, that would be greatly appreciated

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Thanks for the question regarding "problem with FOR LOOP with PL/SQL Function that returns a ref cursor", version 10g

    http://asktom.oracle.com/pls/asktom/...66892450922230

    May I suggest remedial SEARCH training.
    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.

  9. #9
    Join Date
    Jun 2007
    Posts
    21
    actually I did find one:
    Code:
    declare
       c cursor_types.ref_cursor;
       l_name varchar(150);
    begin
       c := SEARCH_WORK_ITEMS(1, 15);
       
       loop
           fetch c into l_name;
           exit when c%notfound;
    	   
    	   dbms_output.put_line(l_name);
    	   
       end loop;
       close c;
    end;
    seems to work fine

Posting Permissions

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