Oracle SQL: variables used in place of table names

user_897740 0
Viewed 42754

I am converting a MSSQL script to Oracle, and I haven't been able to figure out the syntax to use a variable in place of a table name or column.

Here is a simple example that I've been try to make work in Oracle SQL Developer so I can better understand the syntax:

 set serveroutput on format wrapped;
declare 
  VR_TABLE VARCHAR2(256);
  VR_UPDATE VARCHAR2(256);
begin
  VR_TABLE :='SYSTEM_STATUS';
  EXECUTE IMMEDIATE 'select UPDATE_VERSION INTO VR_UPDATE from ' || VR_TABLE || 'where rownum < 2 ;'
end;

Where VR_TABLE is the variable table name that will get changed each iteration of the loop. Can somebody point out what I'm doing wrong, or link me to a site that would be useful for me to read? I've read a few tutorials on this, but I haven't had any luck thus far.

plsql oracle sql
2 Answers

INTO part of the query should not be directly included in the query string.

Syntax

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]

The above syntax shows EXECUTE IMMEDIATE command.

Clause INTO is optional and used only if the dynamic SQL contains a select statement that fetches values. The variable type should match with the variable type of the select statement.

Clause USING is optional and used only if the dynamic SQL contains any bind variable.

https://www.guru99.com/dynamic-sql-pl-sql.html#2

You can visit this site for a better understanding of Dynamic SQL.

user_135553370
Related