Find rows containing multi-byte character

select *
from
you_table
where length(your_column) <> lengthb(your_column)

Passing a select as Cursor to a Function

create or replace function CursorSum(cur SYS_REFCURSOR) return number
AS
ln_int NUMBER;
ln_sum NUMBER := 0;
BEGIN
loop
fetch cur into ln_int;
exit when cur%NOTFOUND;
ln_sum := ln_sum + ln_int;
end loop;
return ln_sum;
END;
/

select CursorSum(Cursor(select 10 from dual union all select 20 from dual)) as sum
from dual

SUM
-----
30

Select from a function

The following steps allow you select from a function return value just like a normal table.

1. Create a record type

Create TYPE MyRecType AS OBJECT
(c1 number, c2 varchar2(5));
/

2. Create a table type

Create Type MyTabType AS Table of MyRecType;
/

3. Create a function returning the Table Type

Create or Replace function MyFunc return MyTabType
AS
mytab MyTabType := MyTabType();
BEGIN
mytab.extend(2);
mytab(1) := MyRecType(1, 'abc');
mytab(2) := myRecType(2, 'efg');
return mytab;
END;
/

4. Do you select

SQL> select * from TABLE(MyFunc);

C1 C2
---------- --------------------
1 abc
2 efg

** Note **
You can make the function as PIPELINE (refer to Oracle Docs for details)

get one line randomly from a group

SQL> select c1, c2
2 from x;

C1 C2
----- ------------
1 a
1 b
1 c
2 a
2 c
3 f
4 c
5 a

SQL> select
2 c1,
3 substr(max(dbms_random.string('a', 1) || c2), 2) random_c2
4 from x
5 group by c1
6 /

C1 RANDOM_C2
----- --------------------
1 b
3 f
5 a
2 a
4 c

Or you can do it with a fancy way
1 select c1,
2 min(c2) keep (dense_rank first order by dbms_random.value) random_c2
3 from x
4 group by c1
5* order by 1

C1 RANDO
-------------------- -----
1 a
2 c
3 f
4 c
5 a