Re: Running a function

Vinay Gidwani wrote:
>
> I have a simple function
>
> create or replcae function show_date return number
> is
> begin
> select * from table;
> return 1;
> end;
>
> I need to display all the contents of the table into the screen, one
> screenful at a time.
> How do I execute functions from sql? I have this saved in a file, and
> I did get the function to compile. How do I get this to display on
> the screen?

Stored procedures/functions in Oracle cannot return a result set as they may do in others DBMS's such as Sybase. What you have done in your function is define and open an implicit cursor. The example as shown should not compile as you do not have an INTO clause, which is required with an implicit cursor. However, even with an INTO clause, you will probably run into run-time problems. An implicit cursor must return one *and only one* row in the result set. If no rows are returned, you will get a data_not_found exception. Should it return more than one row, you will get a too_many_rows exception.

To do what you want to do, you can try a form of the following:

  create or replace
  procedure show_date
  is
    cursor dateX is
      select * from table_a;
    rowCount integer := 0;
  begin
    for dateRec in dateX loop
      dbms_output.put_line( dateRec.string_col || to_char( dateRec.number_col) || ... );

      rowCount := rowCount + 1;
    end loop;
    dbms_output.put_line( to_char( rowCount ) || ' row(s) returned.' );   end;

I made it a procedure because you did not seem to need a function.     

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"