Prepare filters to be passed to the REST data source (if supported by the data source)Ĭreate a variable of APEX_EXEC.T_FILTERS typeĪdd bind values with APEX_EXEC.ADD_FILTER Prepare REST data source parameters variables with įinally ALWAYS close the query context - IMPORTANT Retrieve column values for each column by positionĬall APEX_EXEC.GET_VARCHAR2, APEX_EXEC.GET_NUMBER, APEX_EXEC.GET_DATE. Get column indexes for result column names The typical call sequence depends on the used data source.Ĭreate a variable of APEX_EXEC.T_PARAMETERS typeĪdd bind values with APEX_EXEC.ADD_PARAMETER In a pure SQL or PL/SQL context, use the APEX_SESSION package to initialize a new session. It can be used for plug-in development and procedural PL/SQL processing in applications or within packages and procedures.Īll APEX_EXEC procedures require an existing Application Express session to function.
APEX_EXEC contains procedures and functions to execute queries or procedural calls on local and remote data sources as well as REST Data Sources. Select 'X' user_name, 0 total_amount from dual),Īnd, i think, your advise to use nvl for v_list this may produce same result as my union all did, will check it too.The APEX_EXEC package encapsulates data processing and querying capabilities and provides an abstraction from the data source to Application Express components and plug-ins. I test with union all in with clause in my query where i am creating v_list and it returns 'x' (0 will not show) but it is not appropriate too. apex_ssage('Region pivot query: ', sql_query) Sum(totals) for custname in ( ' ||V_LIST|| ' ))' SELECT user_name as custname, Sum(total_amount) as totals FROM (SELECT user_name, total_amount from customers_orders where zone_id = nvl((select c001 from apex_collections where collection_name=''ZONE_ADMIN''), zone_id) and order_date between to_date(sysdate)-7 and to_date(sysdate) ) group by user_name order by 2 desc) SELECT LISTAGG(''''||user_name||'''',',') WITHIN GROUP (ORDER BY user_name) Where zone_id = nvl((select c001 from apex_collections where collection_name='ZONE_ADMIN'), zone_id) -NVL(:SESSION_ZONE, zone_id)Īnd order_date between to_date(sysdate)-7 and to_date(sysdate)ĭc as (Select distinct user_name from dt) With dt as (select user_name, total_amountįrom (Select user_name, sum(total_amount) total_amount Sql statement (PL/SQL Function Body returning SQL Query): DECLARE ORA-30732: table contains no user-visible columns 1.7K Training / Learning / Certification.165.3K Java EE (Java Enterprise Edition).7.8K Oracle Database Express Edition (XE).3.7K Java and JavaScript in the Database.
0 kommentar(er)
