<p>I want to create a stored procedure with one argument which will return different sets of records depending on the argument. What is the way to do this? Can I call it from plain SQL?</p>

I think you want to return a REFCURSOR:
```
```
create function test_cursor 
            return sys_refcursor
            is
                    c_result sys_refcursor;
            begin
                    open c_result for
                    select * from dual;
                    return c_result;
            end;
```
```
Update
: If you need to call this from SQL, use a table function like @Tony Andrews suggested.