3

I am trying to run something similar to this within a stored procedure in Oracle.

pWhereCLAUSE is generic which I set depending on the table I am working on. The following code runs just if I have the proper defined where clause, but If I try to use this variable, I keep getting errors. I have tried to use ||, single quotes, but nothing has worked. I am not an expert at stored procedures. But any help would be appreciated. I am just trying to figure how I can keep this generic and have this code work with pWhereCLAUSE working

INSERT INTO ABC
    SELECT * 
    FROM ABCD 
    pWhereCLAUSE to_char(dateOne, 'DD-MON-YYYY') and ROWNUM <= variable1;
commit;

I have also tried:

BEGIN    
    sql_stmt := 'INSERT INTO TABLS_AB SELECT * FROM TABLE_C' || pWhereClause || to_char(offsetDate, 'DD-MON-YYYY');  
END;
EXECUTE IMMEDIATE sql_stmt;

The procedure compiles just fine, and this never executes. Please assist.

Improve this question
1
  • 1
    If what you're showing is the outermost PL/SQL block, move the execute immediate up above the END; line. It's a PL/SQL command, not a SQL command. Commented Sep 3 at 17:41

1 Answer 1

Reset to default
3

You can’t concatenate a WHERE clause into static SQL in PL/SQL.

Static SQL is parsed at compile time, but your condition only exists at runtime. The fix is to build the statement dynamically and run it with EXECUTE IMMEDIATE.

Your procedure could look like this:

CREATE OR REPLACE PROCEDURE insert_data (
    pCondition IN VARCHAR2,
    pMaxRows   IN NUMBER
) AS
    sql_stmt VARCHAR2(4000);
BEGIN
    sql_stmt := 'INSERT INTO abc ' ||
                'SELECT * FROM (SELECT * FROM abcd';

    IF pCondition IS NOT NULL AND TRIM(pCondition) <> '' THEN
        sql_stmt := sql_stmt || ' WHERE ' || pCondition || ' ';
    END IF;

    sql_stmt := sql_stmt || ') WHERE ROWNUM <= :v1';

    EXECUTE IMMEDIATE sql_stmt USING pMaxRows;
    COMMIT;
END;
/

This works because the entire statement is built dynamically. You pass only the condition (id < 4) - or skip it if you don't need a condition at all - instead of a full WHERE ... string, and the procedure takes care of adding WHERE and the ROWNUM filter.

Some sample calls:

BEGIN
    -- Insert up to 5 rows where id < 4
    insert_data('id < 4', 5);

    -- Insert up to 2 rows for 2025-09-01 (index-friendly)
    insert_data('dateone >= DATE ''2025-09-01'' AND dateone < DATE ''2025-09-02''', 2);

    -- Insert up to 3 rows with no condition (all rows)
    insert_data(NULL, 3);
END;
/

See this db<>fiddle with sample input data, some calls of the procedure and the results after each of those calls.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks.. somehow when I capture the query getting formed in stored procedure, It gets RECORD_IN_DATETIME <05-JUN-2025 when I use TO_CHAR(offsetDate, 'DD-MON-YYYY'); If I use to_date, my sql never gets formed. I think it gets errored out . I get no error, but I am writing this sql statement in a table, and nothing gets recorded in this case. I have tried several things, with quotes. Can you please assist. What I expect is for the sql to have this: RECORD_IN_DATETIME < to_char('05-JUN-2025', 'DD-MON-YYYY')
Sorry, I have no idea what you talk about. Could you please provide a fiddle (for example use the one in my answer, edit it and share the new url) that shows the issue? Maybe create a new question for a better overview.
Never compare dates as strings and don't use TO_CHAR on a value that is already a string. Compare dates as dates like RECORD_IN_DATETIME < DATE '2025-06-05' (which uses a date literal and will not perform any implicit conversions between dates and strings or vice versa). Or TRUNC(dateone) = DATE '2025-09-01' or, if you want Oracle to use an index on the date column then, compare on a range dateone >= DATE '2025-09-01' AND dateone < DATE '2025-09-02'.
Absolutely true. Maybe one of my sample function calls was misleading, so I improved that now.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.