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

Question

I'm unable to use table name as a variable in my PL/SQL procedure, the table here change every month, in my code, there are two table name variable from ' || v_PRM_TABLE1 || ' and from ' || v_PRM_TABLE2 || ' I tried to use the syntax that was mentioned in this question but not working for me

Oracle SQL: variables used in place of table names

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);
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';

FOR REC IN (
        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||'01000000' 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||'01000000' 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)
        )
        LOOP
        INSERT INTO ON_SGSN_TAPOUT values(v_JOBID,REC.SGSN_DATE,REC.SGSN_MB,REC.PRM_MNTH,REC.PRM_ACT_MB,REC.PRM_RND_MB,REC.PRM_CHRG_USD,REC.PRM_ACT_MB-REC.SGSN_MB,SYSDATE);
        commit;
        END LOOP;  
END;

You're still using static sql, please read linked question carefully again. — 0xdb
– 0xdb, Commented Apr 4, 2020 at 11:36
Static SQL: select 1 into varResult from dual; Dynamic SQL: execute immediate 'select 1 from '||varTableName into varResult;. — 0xdb
– 0xdb, Commented Apr 4, 2020 at 11:49
I recommend the Oracle documentation. I find it very clear and complete. Refer to PL/SQL Dynamic SQL — Abra
– Abra, Commented Apr 4, 2020 at 11:57
Looks like the table name is the only thing that requires dynamic sql. If so then there is a much easier, and more efficient way. Create a pseudo table name use that name as synonym for the actual table. Write static SQL, referring to the synonym. Now write a script that runs monthly to update the synonym. — Belayer
– Belayer, Commented Apr 4, 2020 at 17:30

Osama Al-Banna · Accepted Answer · 2020-04-06 23:18:01Z

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;
 /

Yep, sub-optimal solution and a maintenance nightmare. it is not a continuous running update job, scheduled once a month. But I don't have to maintain it. Good Luck.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related