Oracle Apex Get Table Name From Page Variable

0
votes

Oracle 11g

APex 4.2.6.00.03

I have 15 views each holds 1 months data. from -3 months to forward 12 months. I have set a dynamic action which sets a val from a LOV dropdown list of :P_Matrix_Var give the view name.

Code below gets all data but need to use the dynamic table name :P_Matrix_Var. But all i get back is

Query cannot be parsed, please check the syntax of your query. (ORA-00903: invalid table name).

is this even possible.

Yet if I set the page name manually it works fine

SELECT DISTINCT MD.eng_id, 
            E."name"        AS f_name, 
            E.surname       AS l_name, 
            E.post_code 
            || ' ' 
            || E.tel_number Eng_d, 
            R.role, 
            RE.shortn       AS Reg, 
            md."1"          AS "1d", 
            l1.matrix_data  AS "1", 
            md."2"          AS "2d", 
            l2.matrix_data  AS "2", 
            md."3"          AS "3d", 
            l3.matrix_data  AS "3", 
            md."4"          AS "4d", 
            l4.matrix_data  AS "4", 
            md."5"          AS "5d", 
            l5.matrix_data  AS "5", 
            md."6"          AS "6d", 
            l6.matrix_data  AS "6", 
            md."7"          AS "7d", 
            l7.matrix_data  AS "7", 
            md."8"          AS "8d", 
            l8.matrix_data  AS "8", 
            md."9"          AS "9d", 
            l9.matrix_data  AS "9", 
            md."10"         AS "10d", 
            l10.matrix_data AS "10", 
            md."11"         AS "11d", 
            l11.matrix_data AS "11", 
            md."12"         AS "12d", 
            l12.matrix_data AS "12", 
            md."13"         AS "13d", 
            l13.matrix_data AS "13", 
            md."14"         AS "14d", 
            l14.matrix_data AS "14", 
            md."15"         AS "15d", 
            l15.matrix_data AS "15", 
            md."16"         AS "16d", 
            l16.matrix_data AS "16", 
            md."17"         AS "17d", 
            l17.matrix_data AS "17", 
            md."18"         AS "18d", 
            l18.matrix_data AS "18", 
            md."19"         AS "19d", 
            l19.matrix_data AS "19", 
            md."20"         AS "20d", 
            l20.matrix_data AS "20", 
            md."21"         AS "21d", 
            l21.matrix_data AS "21", 
            md."22"         AS "22d", 
            l22.matrix_data AS "22", 
            md."23"         AS "23d", 
            l23.matrix_data AS "23", 
            md."24"         AS "24d", 
            l24.matrix_data AS "24", 
            md."25"         AS "25d", 
            l25.matrix_data AS "25", 
            md."26"         AS "26d", 
            l26.matrix_data AS "26", 
            md."27"         AS "27d", 
            l27.matrix_data AS "27", 
            md."28"         AS "28d", 
            l28.matrix_data AS "28", 
            md."29"         AS "29d", 
            l29.matrix_data AS "29", 
            md."30"         AS "30d", 
            l30.matrix_data AS "30", 
            md."31"         AS "31d", 
            l31.matrix_data AS "31" 
      FROM   :P_Matrix_Var MD 
   LEFT JOIN man_engineers E 
          ON E.eng_id = MD.eng_id 
   LEFT JOIN role_lookup R 
          ON R.role_id = md."roles" 
   LEFT JOIN md_region_area re 
          ON RE.id = MD."region" 
   LEFT JOIN matrix_trip_lov l1 
          ON l1.md_id = md."1" 
   LEFT JOIN matrix_trip_lov l2 
          ON l2.md_id = md."2" 
   LEFT JOIN matrix_trip_lov l3 
          ON l3.md_id = md."3" 
   LEFT JOIN matrix_trip_lov l4 
          ON l4.md_id = md."4" 
   LEFT JOIN matrix_trip_lov l5 
          ON l5.md_id = md."5" 
   LEFT JOIN matrix_trip_lov l6 
          ON l6.md_id = md."6" 
   LEFT JOIN matrix_trip_lov l7 
          ON l7.md_id = md."7" 
   LEFT JOIN matrix_trip_lov l8 
          ON l8.md_id = md."8" 
   LEFT JOIN matrix_trip_lov l9 
          ON l9.md_id = md."9" 
   LEFT JOIN matrix_trip_lov l10 
          ON l10.md_id = md."10" 
   LEFT JOIN matrix_trip_lov l11 
          ON l11.md_id = md."11" 
   LEFT JOIN matrix_trip_lov l12 
          ON l12.md_id = md."12" 
   LEFT JOIN matrix_trip_lov l13 
          ON l13.md_id = md."13" 
   LEFT JOIN matrix_trip_lov l14 
          ON l14.md_id = md."14" 
   LEFT JOIN matrix_trip_lov l15 
          ON l15.md_id = md."15" 
   LEFT JOIN matrix_trip_lov l16 
          ON l16.md_id = md."16" 
   LEFT JOIN matrix_trip_lov l17 
          ON l17.md_id = md."17" 
   LEFT JOIN matrix_trip_lov l18 
          ON l18.md_id = md."18" 
   LEFT JOIN matrix_trip_lov l19 
          ON l19.md_id = md."19" 
   LEFT JOIN matrix_trip_lov l20 
          ON l20.md_id = md."20" 
   LEFT JOIN matrix_trip_lov l21 
          ON l21.md_id = md."21" 
   LEFT JOIN matrix_trip_lov l22 
          ON l22.md_id = md."22" 
   LEFT JOIN matrix_trip_lov l23 
          ON l23.md_id = md."23" 
   LEFT JOIN matrix_trip_lov l24 
          ON l24.md_id = md."24" 
   LEFT JOIN matrix_trip_lov l25 
          ON l25.md_id = md."25" 
   LEFT JOIN matrix_trip_lov l26 
          ON l26.md_id = md."26" 
   LEFT JOIN matrix_trip_lov l27 
          ON l27.md_id = md."27" 
   LEFT JOIN matrix_trip_lov l28 
          ON l28.md_id = md."28" 
   LEFT JOIN matrix_trip_lov l29 
          ON l29.md_id = md."29" 
   LEFT JOIN matrix_trip_lov l30 
          ON l30.md_id = md."30" 
   LEFT JOIN matrix_trip_lov l31 
          ON l31.md_id = md."31" 
   WHERE  E.active = 1;
2
oracleoracle11goracle-apex
Where are you putting this query - report region source? - Bacs
interactive report, region source - jase sykes

2 Answers

0
votes

If your trying to put it into dynamic report then it won't work.

But for regular report you may use a procedure that dynamically generates your report SQL query.

Also I don't think oracle allows to uses bind variables in referred table names.

0
votes

Oracle won't let you set the table name dynamically in a SQL query using a bind variable. If you were creating a classic report, you could set the source to something like this:

begin
 return 'select col1, col2 etc from '||:P_Matrix_Var||' joins, where etc';
end;

If it has to be an interactive report, you can't use PL/SQL for the source. Do you really need to use the views? If they aren't materialised , there won't be a performance benefit from them, so you may as well just include the base tables in your query and use page items to set the date range. If they are materialised and you want to use them, you may have to resign yourself to having 15 reports, and the page number rather than the view name conditional on a page item.