Explain Dynamic SQL and Ref Cursors in EBS Extensions in Oracle Applications

20 / 60

PL/SQLadvanced

Dynamic SQL and Ref Cursors in EBS Extensions

Explain when and how to safely use dynamic SQL and ref cursors in custom EBS reports and extensions

Dynamic SQL (via EXECUTE IMMEDIATE or the older DBMS_SQL package) allows PL/SQL to construct and execute SQL statements whose text isn't fully known at compile time — essential for scenarios like flexible reporting extensions where filter criteria vary at runtime, or generic utilities that must operate across different tables. REF CURSORs (weak or strong typed) allow a query's result set to be returned from a function or passed between programs without the caller needing to know the exact SQL in advance, which is heavily used in BI Publisher data model extensions and custom OAF/ADF pages.

Building dynamic SQL safely is like a mail-merge letter template: you build the fixed wording once (the SQL skeleton) and drop distinct recipient details into placeholder slots (bind variables) rather than manually re-typing (concatenating) each recipient's name directly into a brand new letter every time — which would be both slower and more error/injection-prone.

Key Concepts

A very common EBS use case is a custom BI Publisher or Reports data source that must dynamically add WHERE clause predicates based on which parameters the user actually populated — building the SQL string conditionally and opening it as a ref cursor via OPEN l_cursor FOR l_sql_string USING l_bind1, l_bind2;.

OPEN l_cursor FOR l_sql_string USING l_bind1, l_bind2;

The primary risk with dynamic SQL is SQL injection if any part of the dynamically-built string incorporates unsanitized user input directly (string concatenation) rather than using bind variables. Oracle EBS's own coding standards mandate using bind variables (USING clause) for all user-supplied values in dynamic SQL, never concatenating raw parameter values into the SQL text.

SQL injectionUSING

Interviewers often present a snippet of dynamic SQL that concatenates a parameter directly into the query string and ask the candidate to identify and fix the injection vulnerability — a very common and practical assessment of secure coding awareness.