Devendrakumar Rajendran - A Blog to Oracle Financials World

You might implement a database application as several PL/SQL packages—one package that provides the application programming interface (API) and helper packages to do the work. Ideally, only the API is accessible to clients. Also, you might create a utility package to provide services to only some other PL/SQL units in the same schema. Ideally, the utility package is accessible only to the intended PL/SQL units. Before Oracle Database 12c, PL/SQL could not prevent clients from using items exposed in helper packages. To isolate these items, you had to use relational database management system (RDBMS) security features. Some application deployment schemes made RDBMS security features hard to use. As of Oracle Database 12c, each of these statements has an optional ACCESSIBLE BY clause that lets you specify a white list of PL/SQL units that can access the PL/SQL unit that you are creating or altering

DECLARE  emp_salary NUMBER(8,2);  PROCEDURE adjust_salary (  emp NUMBER,                              sal IN OUT NUMBER,                              adjustment NUMBER  ) IS  BEGIN  sal := sal + adjustment;  END; BEGIN  select amount_due_original INTO emp_salary  from ar_payment_schedules_all  where trx_number = 'XXXXX'  and payment_schedule_id = XXXX;  DBMS_OUTPUT.PUT_LINE ('Before invoking procedure, emp_salary: ' || emp_salary);  adjust_salary (100, emp_salary, 1000);  DBMS_OUTPUT.PUT_LINE  ('After invoking procedure, emp_salary: ' || emp_salary); END;

Setup tables Tax Regimes: ZX_REGIMES_B Taxes: ZX_TAXES_B Tax Status: ZX_STATUS_B Tax Rates: ZX_RATES_B Tax Jurisdictions: ZX_JURISDICTIONS_B Tax Rules: ZX_RULES_B SQL References SELECT * FROM zx_regimes_b WHERE tax_regime_code = :tax_regime_code; SELECT * FROM zx_taxes_b WHERE DECODE(:tax_name,null,'xxx',tax) = nvl(:tax_name,'xxx') AND tax_regime_code = :tax_regime_code; SELECT * FROM zx_status_b WHERE tax = :tax_name AND tax_regime_code = :tax_regime_code; SELECT * FROM zx_rates_b WHERE tax = :tax_name AND tax_regime_code = :tax_regime_code; SELECT * FROM zx_jurisdictions_b WHERE DECODE(:tax_name,null,'xxx',tax) = nvl(:tax_name,'xxx') AND tax_regime_code = :tax_regime_code; SELECT * FROM zx_rules_b WHERE tax = :tax_name AND tax_regime_code = :tax_regime_code;

You can define any number of functions as PLSQL inline and can be called in SQL statements Procedure can be defined but cant be invoked from SQL for results However procedure results can be retrieved through functions and same function should be called from SQL statements for results Procedures and functions defined in WITH clause  can't be iterated through for loop cursor However dynamic sql allows you to work around this restriction The whole reason for defining the PL/SQL code inline is to improve performance than the regular function Example WITH   FUNCTION get_name(p_name IN VARCHAR2) RETURN VARCHAR2 IS   BEGIN     RETURN 'Hello! '||p_name||'..'||' Oracle 12c database welcomes you :-)';   END;   FUNCTION add_numbers(p_n1 IN NUMBER, p_n2 IN NUMBER) RETURN NUMBER IS   BEGIN     RETURN NVL(p_n1,0) + NVL(p_n2,0);   END;     PROCEDURE calcuations (p_mode IN VARCHAR2, p_n1 IN NUMBER, p_n2 IN NUMBER,p_out OUT NUMBER) IS   l_calc NUMB

The new and optimized SQL function, APPROX_COUNT_DISTINCT(), provides approximate count distinct aggregation. Processing of large volumes of data is significantly faster than the exact aggregation, especially for data sets with a large number of distinct values, with negligible deviation from the exact result. The need to count distinct values is a common operation in today's data analysis. Optimizing the processing time and resource consumption by orders of magnitude while providing almost exact results speeds up any existing processing and enables new levels of analytical insight. APPROX_COUNT_DISTINCT returns the approximate number of rows that contain distinct values of expr. This function provides an alternative to the COUNT (DISTINCT expr) function, which returns the exact number of rows that contain distinct values of expr. APPROX_COUNT_DISTINCT processes large amounts of data significantly faster than COUNT, with negligible deviation from the exact result. For

Purpose RATIO_TO_REPORT is an analytic function. It computes the ratio of a value to the sum of a set of values. If expr evaluates to null, then the ratio-to-report value also evaluates to null. Query SELECT qry.party_name, qry.revenue, RATIO_TO_REPORT (revenue) OVER () AS rr    FROM (select (select party_name from hz_parties p, hz_cust_accounts ca where ca.party_id = p.party_id and cust_account_id = ps.customer_id) party_name , sum(amount_due_original) revenue from ar_payment_schedules_all ps where class = 'INV' group by ps.customer_id) qry

Query select qry.party_name, qry.revenue, dense_rank() over (order by qry.revenue desc) as rank from (select (select party_name from hz_parties p, hz_cust_accounts ca where ca.party_id = p.party_id and cust_account_id = ps.customer_id) party_name , sum(amount_due_original) revenue from ar_payment_schedules_all ps where class = 'INV' group by ps.customer_id) qry