Variables

[chris.b]

Hi, apologies if this has been asked before. I did search but couldn't find anything.
We are using PLSQL v13 having previously been using a older version.

I want to use a variable in a where clause and also as a table name. In 9 we could define it once and use it in both. In 13 it is giving an error. The first select works but the second fails.

Note this is not the actual query i am using but one created simply to demonstrate the issue.

Am hoping there is a really simple fix that i am just not seeing.

Thanks in advance.

Chris

SELECT
& AS "my_Table"
FROM DUAL;

select column_name from sys.all_tab_columns where table_name = & and owner=user;

Select * from &;

 

[Marco Kalter]

You have declared the variable as a string:

TYPE = "String"

As a result the variable value will be placed in quotes. This will not work for the 3rd statement. If you remove the TYPE="String" property, it will work correctly for the 3rd statement, but the 1st and 2nd will fail. You will need to add the quotes for these 2 statements. For example:

SELECT '&<DEFAULT = "select table_name from user_tables"
          HINT = "Select Table to extract (REQUIRED)"
          LIST = "select table_name from user_tables"
          NAME = "my_Table"
          REQUIRED = "Yes"
          RESTRICTED = "Yes"> AS "my_Table"'
FROM DUAL;

select column_name
from sys.all_tab_columns
where table_name = '&<NAME = "my_Table">'
and owner=user;

Select * from &<NAME = "my_Table">