how to define types dynamically in plsql

Interesting question. As APC noted, Oracle doesn't have reflection per say like other languages, so passing in the name (varchar2) doesn't help Oracle much (esp at compile time). What I believe you are saying here is that you have a set of tables that all share certain characteristics (1 or more columns of the same type used in the same way), so you want to make a generic function that will work for any of them.

You can do this, but you'll have to define an object type that defines the common columns and types that your various tables share. Say this is the following 2 columns:

create or replace type my_obj as object (name varchar2(100), num number);

Now your function (or procedure) would accept this type as a param:

create or replace function my_fn(obj my_obj) return varchar2 is
  begin
    -- do something with object
    return obj.name || ',' || obj.num;
  end;

And you'd call it like:

declare
    obj my_obj;
    rv varchar2(1000);
  begin
    for rec in (select * from sometable)
    loop
      obj := my_obj(rec.some_varchar_col, rec.some_number_col);
      select my_fn(obj) into rv from dual;
      dbms_output.put_line(rv);
    end loop;
  end;

The only other way that I can think of is to accept a weakly typed sys_refcursor and then force calling procs to send in a correct cursor (risky due to potential runtime exceptions and not very clear). I prefer the above approach if coding a "generic" function.

EDIT To be complete, I'll throw in the sys_refcursor example I mentioned above:

create or replace procedure my_proc(cur sys_refcursor) is
  v_name varchar2(100);
  v_num number;
begin
  loop
    fetch cur into v_name, v_num;
    exit when cur%notfound;

    -- do something with our common fields
    dbms_output.put_line(v_name || ',' || v_num);
  end loop;
end;

And call it like:

declare
  v_cur sys_refcursor;
begin
  open v_cur for select my_name, my_num from some_table;
  my_proc(v_cur);
  close v_cur;
end;

NOTE This seems overly trivial with just 2 columns (why not just set params as varchar2 and number), but you may have dozens of columns that you want to work on in the function, and the input object can have any number of them populated.

Cheers

The syntax is this:

declare
    col1  t23.col1%type;
begin
    null;
end;
/ 

The Oracle documentation set is online. Find it here. The information about %TYPE is in the PL/SQL User's Guide.

" In your example t23 is a table. Not a parameter with a table name."

Fair point. I was assuming that every declaration of COL1 was the same datatype, hence declaring it using one fixed table name would work for all tables. If this is not the case then I agree my solution won't work.

The issue with passing a table name is the usual one: PL/SQL doesn't support it. It has not anything like reflection in Java.

There is always dynamic SQL but that may well be overkill. I think the OP needs to provide more details about what they are trying to achieve.

Have a look at all the data types used by COL_ID in all the tables you might pass to the procedure. Chances are they all could fit in one of the basic data types - i.e. NUMBER, VARCHAR2(4000).

Create a separate procedure for each data type, call the appropriate version of the procedure (or, work out which variable to use with logic within the procedure) and don't worry about anchoring the type.

You can achieve this using EXECUTE IMMEDIATE.

The trick is to put all your code in a VARCHAR2 variable, and use utl_lms.format_message to replace the table name with your parameter. See below -

create or replace procedure pname (tn varchar2) is
stmt_ VARCHAR2(32000) := q'[
DECLARE
  col1 %s.COL_ID;
BEGIN
  -- Perform operations
  col1 := 'foo';
END;
]';
BEGIN
  EXECUTE IMMEDIATE
    utl_lms.format_message(stmt_, tn);
END;

Thanks.