PL/SQL (multiple row queries :( ).

[Starburn]

I have an SQL statement that returns multiple rows.. (two columns)..

and I can't seem to figure out how to use it within a PL/SQL procedure using two parameters from the procedure.

I've tried using records/tables (until I discovered PL/SQL tables can only be one column), and currently "EXECUTE IMMEDIATELY".. which works (i.e. compiles/executes) but the output from the SQL statement is not displayed.

CREATE OR REPLACE PROCEDURE testList
(title VARCHAR2,
 type VARCHAR2)
AS
  getList VARCHAR2(500);
BEGIN
  getList := '<the SQL statement>';
  EXECUTE IMMEDIATE getList USING title, type;
END testList;

/

[Starburn]

ah I've figured out you use a dynamic cursor..

now I'm just having fun trying to fetch the values from the cursor :/
"ORA-00932: inconsistent datatypes: expected - got -"

[GAteKeeper]

If you are using 9i v2 onwards you can use multi-dimensional tables

type my_table as table of real_table%rowtype;
var_table my_table;

or somet like that.

but Y not use early binding?

CREATE OR REPLACE PROCEDURE testList
(title VARCHAR2,
 type VARCHAR2)
AS
BEGIN
     Update my_table 
       set my_column = type
       where my_title = title;
END testList;

its good practice to name your procedural variables with a pre-fix such as var_ or int_ or str_ so that there is less chance of a naming mix up with table column names.

GAteKeeper