How to Use a Variable in Oracle Script for The Table Name

Oracle SQL: variables used in place of table names

You need to have a space between the table name and the subsequent WHERE clause
The INTO needs to be part of the EXECUTE IMMEDIATE, not part of the dynamic SQL statement.
The dynamic SQL statement should not have a trailing semicolon
The EXECUTE IMMEDIATE statement should end with a semicolon

Putting those together, something like this should work

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

Of course, since you're not doing anything with VR_UPDATE, nothing will be displayed when this anonymous block is executed.

Using variables in oracle as table names for insert command oracle

This is the right syntax, but not recommended for such a simple operation

testes:= 'INSERT INTO ' || n_tab2 ||' SELECT * FROM ' || n_tab;
EXECUTE IMMEDIATE testes;

Because It is preferable(and safer) to explicitly specify the column names in an insert, for which you need extra blocks if you want to do it dynamically.

INSERT INTO tab2(col1,col2,col3)  SELECT col1,col2,col3 FROM tab;

By the way, any reason why you've put table names in variable instead of doing a direct insert?

Using a Variable in a Table Creation Script

For reasons that aren't immediately clear to me, the problem is that the variable you're using is nvarchar2. It works if you declare it as varchar2:

  curTableName VARCHAR2(50);

Validated via SQL Fiddle; that fiddle fails if you just change the declaration of the table name variable to nvarchar2.

As @jonearles points out, the documentation does state that the command string's 'type must be either CHAR, VARCHAR2, or CLOB'. And although the documentation for the concatenation operator doesn't refer to it, the related concat function does state that 'if one of the arguments is a national data type, then the returned value is a national data type' - so your command string is nvarchar2 because the variable you're using in the concatenation is nvarchar2, making the argument to the execute immediate statement illegal.

It's not a great idea to create tables on the fly though. Schema objects should normally be created once. You could have a table - possibly a global temporay table - with a month column, or at a push twelve tables that you empty and populate as needed.

Also, all your select from dual statements could be simplified:

  curMonthChar := TO_CHAR(ADD_MONTHS(sysdate,-1),'fmMONTH');
  curTableName := 'QP17414_'||curMonthChar);

etc., even in your subquery:

... WHERE OWNER = user AND ...

... and I have no idea why you're using so much concatenation in your pivot clause.

Dynamic SQL with table name as a parameter

You can only use bind variables (denoted by colons) for values, not for parts of the structure. You will have to concatenate the table and column names into the query:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where REF_D = ' || column2 into ATTR_AVG;

Which implies REF_D is a fixed column name that can appear in any table you'll call this for; in a previous question that seems to be a variable. If it is actually a string variable then you'd need to bind and set that:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where ' || column2 || ' = :REF_D' into ATTR_AVG using REF_D;

If it's supposed to be a date you should make sure the local variable is the right type, or explicitly convert it.

passing in table name as plsql parameter

You can use dynamic SQL:

create or replace function get_table_count (table_name IN varchar2)
  return number
is
  table_count number;
begin
  execute immediate 'select count(*) from ' || table_name into table_count;
  dbms_output.put_line(table_count);
  return table_count;
end;

There is also an indirect way to get number of rows (using system views):

create or replace function get_table_count (table_name IN varchar2)
  return number
is
  table_count number;
begin
  select num_rows
    into table_count
    from user_tables
   where table_name = table_name;

  return table_count;
end;

The second way works only if you had gathered statistics on table before invoking this function.

PL/SQL Use table variable as query table

Yes ... but not how you are doing it, for two reasons:

Firstly, you do not have a collection (what you are calling a table variable) as you have used INDEX BY PLS_INTEGER so what you have is an associative array.
Secondly, you can only use collections in SQL queries where the data type has been declared in the SQL scope (and yours is declared in PL/SQL).

So, first you need to create the type:

CREATE TYPE t_name IS TABLE OF VARCHAR2(50);

Then you can run the PL/SQL block:

DECLARE
  v_names t_name;
  v_name  VARCHAR2(50);
BEGIN
  SELECT name
  BULK COLLECT INTO v_names
  FROM my_table;

  SELECT COLUMN_VALUE
  INTO   v_name
  FROM   TABLE(v_names)
  WHERE  COLUMN_VALUE = 'Max';
  
  DBMS_OUTPUT.PUT_LINE( v_name );
END;
/

(Note: the table collection expression in the second query has the pseudo-column COLUMN_VALUE rather than any particular identifier from a table.)

db<>fiddle here

How to declare variable and use it in the same Oracle SQL script?

There are a several ways of declaring variables in SQL*Plus scripts.

The first is to use VAR, to declare a bind variable. The mechanism for assigning values to a VAR is with an EXEC call:

SQL> var name varchar2(20)
SQL> exec :name := 'SALES'

PL/SQL procedure successfully completed.

SQL> select * from dept
  2  where dname = :name
  3  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL>

A VAR is particularly useful when we want to call a stored procedure which has OUT parameters or a function.

Alternatively we can use substitution variables. These are good for interactive mode:

SQL> accept p_dno prompt "Please enter Department number: " default 10
Please enter Department number: 20
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 20

ENAME             SAL
---------- ----------
CLARKE            800
ROBERTSON        2975
RIGBY            3000
KULASH           1100
GASPAROTTO       3000

SQL>

When we're writing a script which calls other scripts it can be useful to DEFine the variables upfront. This snippet runs without prompting me to enter a value:

SQL> def p_dno = 40
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 40

no rows selected

SQL>

Finally there's the anonymous PL/SQL block. As you see, we can still assign values to declared variables interactively:

SQL> set serveroutput on size unlimited
SQL> declare
  2      n pls_integer;
  3      l_sal number := 3500;
  4      l_dno number := &dno;
  5  begin
  6      select count(*)
  7      into n
  8      from emp
  9      where sal > l_sal
 10      and deptno = l_dno;
 11      dbms_output.put_line('top earners = '||to_char(n));
 12  end;
 13  /
Enter value for dno: 10
old   4:     l_dno number := &dno;
new   4:     l_dno number := 10;
top earners = 1

PL/SQL procedure successfully completed.

SQL>