Oracle SQL: Variables Used in Place of Table Names

Oracle SQL: variables used in place of table names

1. You need to have a space between the table name and the subsequent WHERE clause
2. The INTO needs to be part of the EXECUTE IMMEDIATE, not part of the dynamic SQL statement.
3. The dynamic SQL statement should not have a trailing semicolon
4. 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?

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.

Unable to use PL/SQL table name as a variable in PL/SQL procedure?

I solve it without using EXECUTE IMMEDIATE, I don't know if this solution considered dynamic SQL or not, the hardest part was concatenating this big complex query into string variable.

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
--FIRST DAY YEAR
v_SG_FYEAR VARCHAR(50);
--FIRST DAY MONTH
v_SG_FMNTH VARCHAR(50);
--FIRST DAY START DAY
v_SG_FDAY VARCHAR(50);

--LAST DAY MONTH
v_SG_LYEAR VARCHAR(50);
--LAST DAY MONTH
v_SG_LMNTH VARCHAR(50);
--LAST DAY START DAY
v_SG_LDAY VARCHAR(50);

v_JOBID number(20);
v_PRM_TABLE1 varchar(200);
v_PRM_TABLE2 varchar(200);
--craeting a data type
type t_rec is record(SGSN_DATE VARCHAR2(200),
                 SGSN_MB VARCHAR2(200),
                 PRM_MNTH VARCHAR2(200),
                 PRM_ACT_MB VARCHAR2(200),
                 PRM_RND_MB VARCHAR2(200),
                 PRM_CHRG_USD VARCHAR2(200));
v_REC t_rec;
C1 sys_refcursor;
v_sql varchar2(32767);
BEGIN
/*Getting last JOB_ID*/
SELECT MAX(JOB_ID) INTO v_JOBID from ON_SGSN_TAPOUT;
case when v_JOBID IS NULL then v_JOBID:=1;
 when v_JOBID IS NOT NULL THEN 
      SELECT MAX(JOB_ID)+1 INTO v_JOBID FROM ON_SGSN_TAPOUT;
 END CASE;
v_SG_FYEAR:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYY');
v_SG_FMNTH:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'MM');
v_SG_FDAY:='01';
v_SG_LYEAR:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYY');
v_SG_LMNTH:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'MM');
v_SG_LDAY:=TO_CHAR(TRUNC(SYSDATE,'MM')-1,'DD');
v_PRM_TABLE1:='prmdb.CDR_TAPOUT_'||v_SG_FYEAR||v_SG_FMNTH||'@prmdb';
v_PRM_TABLE2:='prmdb.CDR_TAPOUT_'||TO_CHAR(SYSDATE,'YYYYMM')||'@prmdb';

/*putting the SQL query into a variable*/
v_sql:='select * from('||
   'select * from ('||
   'SELECT TO_CHAR(START_TIME,''YYYYMMDD'') AS "SGSN_DATE",'||
   'SUM(SUM_UP_DOWN/1024/1024) SGSN_MB'||
   ' FROM OPS$DEBIT.FACT_HUA_SGSN_INBOUND@RADB'|| 
   ' WHERE START_TIME BETWEEN 
TO_DATE('''||v_SG_FMNTH||'/'||v_SG_FDAY||'/'||v_SG_FYEAR||' 
'||'00:00:00'||''','||'''mm/dd/yyyy hh24:mi:ss'||''')'||
' AND  TO_DATE('''||v_SG_LMNTH||'/'||v_SG_LDAY||'/'||v_SG_LYEAR||' 
'||'23:59:59'||''','||'''mm/dd/yyyy hh24:mi:ss'||''')'||
' GROUP BY TO_CHAR(START_TIME,''YYYYMMDD'')) A '||
'left outer join '|| 
'('||
'select PRM_MNTH,PRM_ACT_MB,PRM_RND_MB,PRM_CHRG_USD from ('||
'select PRM_MNTH,SUM(PRM_ACT_MB) PRM_ACT_MB,SUM(PRM_RND_MB) 
 PRM_RND_MB,SUM(PRM_CHRG_USD) PRM_CHRG_USD  from ('||
 'select substr(START_TIME,1,8) PRM_MNTH,sum(TOTAL_BYTE)/1024/1024 PRM_ACT_MB, 
 sum(charged_units)/1024/1024 PRM_RND_MB, sum(tap_fee_usd) PRM_CHRG_USD'||
 ' from '|| v_PRM_TABLE1 ||
 ' where start_time between '''||v_SG_FYEAR||v_SG_FMNTH||v_SG_FDAY||'000000'' and 
 '''||v_SG_LYEAR||v_SG_LMNTH||v_SG_LDAY||'235959'''|| 
 ' and CDR_TYPE in (''GPRSO'') '||
 ' group by substr(START_TIME,1,8) '||
 ' UNION ALL '||
 ' select substr(START_TIME,1,8) PRM_MNTH,sum(TOTAL_BYTE)/1024/1024 PRM_ACT_MB, 
 sum(charged_units)/1024/1024 PRM_RND_MB, sum(tap_fee_usd) PRM_CHRG_USD '||
 ' from '||v_PRM_TABLE2||
 ' where start_time between '''||v_SG_FYEAR||v_SG_FMNTH||v_SG_FDAY||'000000'' and 
 '''||v_SG_LYEAR||v_SG_LMNTH||v_SG_LDAY||'235959'''|| 
 ' and CDR_TYPE in (''GPRSO'')'||
 ' group by substr(START_TIME,1,8))'||
 ' group by'||
 ' PRM_MNTH)'||
 ') B'||
 ' ON A.SGSN_DATE=B.PRM_MNTH)';  
   OPEN C1 FOR v_sql;
   LOOP
   FETCH C1 INTO v_REC;
   EXIT WHEN C1%NOTFOUND;
   dbms_output.put_line(v_REC.SGSN_DATE||' '||v_REC.SGSN_MB||' '||v_REC.PRM_MNTH||' 
   '||v_REC.PRM_ACT_MB||' '||v_REC.PRM_RND_MB||' '||v_REC.PRM_CHRG_USD||' '||SYSDATE);
   END LOOP;
 END;
 /

---

Related Topics