Median Number pl-sql procedure not executing

From: streamline <small_bore_at_hotmail.com>
Date: 24 Oct 2001 04:20:49 -0700
Message-ID: <b1e9034f.0110240320.464d89c2_at_posting.google.com>

[Quoted] I have this pl-sql procedure to work out the median value of a set of id numbers from a table.

It created with no errors but when i execute in sqlplus i get:

[Quoted] SQL> execute median_value;
begin median_value; end;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MEDIAN_VALUE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Does any one have any ideas?

Rem Example of usage (note that "table_name" can be a view) Rem "variable median number;"
Rem "execute median_value('user_tab_columns','data_length',:median);" Rem
Rem Then:

Rem "print median;" OR (for example)
Rem "select min(data_length),max(data_length),:median
Rem "median(data_length)"

Rem from user_tab_columns;"

SET SERVEROUT ON SIZE 4096; [Quoted] create or replace procedure MEDIAN_VALUE

   (news varchar2, id varchar2, median OUT number) as    /*

      Note that this cannot be made a function callable from SQL,
      since it necessarily involves a SQL statement with an ORDER BY

   */

   S0 varchar2 (120) := 'select count('||id||') from '

                        ||users;

   S1  varchar2 (180) := 'select '|| id ||
                         ' from ' || users ||
                         ' where '|| id || ' is not null'||
                         ' order by '||id;

   dummy integer;
   C0    integer;
   X0    integer;
   odd   boolean;

   cnt integer;
   skip integer;
   val1 number;
   val2 number;

begin
/* Define a cursor to count (non-null) values of target column */

   C0 := dbms_sql.open_cursor;
   dbms_sql.parse(C0,S0,dbms_sql.v7);
   dbms_sql.define_column(C0,1,cnt);    

/* Execute it */

   X0 := dbms_sql.execute(C0);

   dummy := dbms_sql.fetch_rows(C0);	        -- fetch the (only) row;
   dbms_sql.column_value(C0,1,cnt);		-- count in variable cnt
   dbms_sql.close_cursor(C0);			-- so that we can re-use the

reference;    

/* Check whether the number of values is odd or even */

   if mod(cnt,2) = 1 then

      ODD := TRUE;
      skip := (cnt-1)/2;
   else
      ODD := FALSE;
      skip := cnt/2-1;

   end if;    

/* Re-use the cursor to select rows in order of target column */

   C0 := dbms_sql.open_cursor;
   dbms_sql.parse(C0,S1,dbms_sql.v7);
   dbms_sql.define_column(C0,1,val1);    

/* Execute it */

   X0 := dbms_sql.execute(C0);

/* Process rows */

   for i in 1 .. skip -- skip computed number of rows    loop

      dummy := dbms_sql.fetch_rows(C0);
   end loop;
   /*

     The next value fetched is:
     a)  the median if rows are ODD or
     b) the first of two values to be averaged if not

   */
   dummy := dbms_sql.fetch_rows(C0);
   dbms_sql.column_value(C0,1,val1);
   if ODD then

      median := val1;
   else

      dummy := dbms_sql.fetch_rows(C0);
      dbms_sql.column_value(C0,1,val2);
      median := (val1 + val2)/2;

   end if;
   dbms_sql.close_cursor(C0);
end MEDIAN_VALUE;
/

show errors; Received on Wed Oct 24 2001 - 13:20:49 CEST