Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Anand.

Asked: March 04, 2019 - 6:03 pm UTC

Last updated: October 01, 2020 - 12:15 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hello,

I have a stored procedure which takes 2 input paramaters - owner and table_name. I would like to create a TYPE variable based on what is passed. This is the code:

create or replace PROCEDURE proc (
  in_owner   IN         VARCHAR2,
  in_table   IN         VARCHAR2
) 
AS

...
...

TYPE tab_type IS TABLE OF in_owner.in_table%ROWTYPE;
tab_typ_finl tab_type;

TYPE ref_cur IS REF CURSOR;
plcur ref_cur;

...
...


As you can see I would like to declare "type" tab_type based on the table_name passed. I guess PL/SQL doesn't support this currently. Would you be able to suggest a workaround or some hack to get through this?

Thank you very much in advance.

and Chris said...

Correct. You can't define dynamic types like this in PL/SQL. The name of the identifier must match the name of an underlying database object. You can't use a variable which holds the object name.

Presumably you're passing a table name and want a dynamic type because you're running dynamic SQL later in your code.

To suggest a meaningful workaround we'd need to understand why you're doing this. In the vast majority of cases static SQL is the Right Way to write your code. Dynamic SQL is harder to code. And opens up SQL injection risks.

So: why are you trying to this? What's the reason you can't use static SQL?

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Faced similar issue and can understand situation

Vikash Kumar Sharma, October 01, 2020 - 10:59 am UTC

Table is to be passed as input to the store proc, so before defining proc, I have declared table type in proc.
But as table input to proc is variable, so table type declared in package should be dynamic too because every time when proc is called there can be different table type or structure.
That is the necessity to have dynamic table type here.

Chris Saxon
October 01, 2020 - 12:15 pm UTC

How exactly are you using this table type?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library