Median Number pl-sql procedure not executing

[Thiko]

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:

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;

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;

Many Thanks.

Thiko!

 

[sem]

You have to pass parameters to procedure. Just read its usage. Note: the line's wrapped