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 builds a statement at runtime. It is necessary when the shape of the query genuinely varies, and it is where injection vulnerabilities come from.

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

The common EBS case: optional report parameters.

    l_sql := 'SELECT * FROM xx_orders WHERE 1=1';
    IF p_customer IS NOT NULL THEN
        l_sql := l_sql || ' AND customer_id = :b_cust';
    END IF;
    IF p_from_date IS NOT NULL THEN
        l_sql := l_sql || ' AND order_date >= :b_from';
    END IF;

    OPEN l_cursor FOR l_sql USING p_customer, p_from_date;

Only the predicates the user supplied are added. A static query cannot do this without NVL tricks that defeat the indexes.

NVL

Bind variables, always — and here is what happens without them.

    -- vulnerable
    l_sql := 'SELECT * FROM xx_orders WHERE customer_id = ' || p_input;

    p_input = '1 OR 1=1'          returns every row
    p_input = '1; DROP TABLE ...' in some contexts, worse

    -- safe
    l_sql := 'SELECT * FROM xx_orders WHERE customer_id = :b_cust';
    OPEN l_cursor FOR l_sql USING p_input;

A bind variable is a value, never parsed as SQL. Concatenation makes the input part of the statement, which is the entire vulnerability.

Binds also help performance. Concatenated values produce a different statement text per execution, so every call is a hard parse and the shared pool fills with near-identical statements. One statement with binds is parsed once.

The one thing you cannot bind is an identifier — a table or column name. If those must vary, validate them against a known list rather than accepting input:

    IF p_column NOT IN ('ORDER_DATE','SHIP_DATE') THEN
        RAISE invalid_column;
    END IF;

Ref cursors return a result set without the caller knowing the query:

    TYPE ref_cur IS REF CURSOR;

    PROCEDURE get_orders(p_customer IN NUMBER, x_cursor OUT ref_cur) IS
    BEGIN
        OPEN x_cursor FOR SELECT ... WHERE customer_id = p_customer;
    END;

This is how BI Publisher data models and OAF pages take data from a package: the procedure opens the cursor, the consumer fetches from it.

Strong or weak. A strong ref cursor declares its return type, so the compiler checks the fetch. A weak one (SYS_REFCURSOR) accepts any query, which is what dynamic SQL needs and what gives up that checking. Use strong where the shape is fixed.

SYS_REFCURSOR

What the interviewer is probing.1. "When is dynamic SQL justified?" Probing: judgement. Stalls: "Whenever convenient." Moves up: when the structure genuinely varies — optional predicates, a table name from configuration — not merely to avoid writing branches.

2. "What is the injection risk?" Probing: the security point. Stalls: "None." Moves up: concatenating parameter values into the statement; bind variables with USING remove it and let the cursor be shared.

3. "Why do bind variables matter for performance here?" Probing: the depth. Stalls: "They do not." Moves up: literals produce a new hard parse per value, flooding the shared pool — binds let one plan be reused.

4. "What is a ref cursor used for?" Probing: the capability. Stalls: "A loop." Moves up: returning a result set to a caller, including BI Publisher data templates and Java or web service consumers.