Oracle Table field TYPE declaration issue

Question

I have a procedure where I am using %TYPE to declare my variable. As of now its working for only one table i.e. EMPLOYEES:

CREATE OR REPLACE PROCEDURE MYPROCEDURE
DECLARE
filed2 EMPLOYEES.name%TYPE;
END;

Now I want to extent it for others table which have same few common tables. So I will pass the table name as parameter. Here how can use the TYPE syntax because table name is in variable. If I am trying this:

CREATE OR REPLACE PROCEDURE MYPROCEDURE (myTable IN VARCHAR2)
DECLARE
filed2 myTable.name%TYPE;
END;

I am getting an error. How can I achieve it?

Patrick Hofman · Accepted Answer · 2014-08-13 14:49:12Z

1

No, you can't. Type declaration is static. It can be inferred using %type, but that will still compile to a static type definition.

In your case, you try to reference a 'property' from a varchar2, which is not possible (Oracle doesn't have properties on varchar2).

answered Aug 13, 2014 at 14:49

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Hofman Over a year ago

@CrazyC: need more help?

Collectives™ on Stack Overflow

Oracle Table field TYPE declaration issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related