1

I have the below code, which works fine.

SET SERVEROUTPUT ON;

DECLARE
    cols CLOB;
BEGIN
    SELECT
        LISTAGG('"'
                || column_name
                || '"', ',') WITHIN GROUP(
            ORDER BY
                column_name
        )
    INTO cols
    FROM
        all_tab_columns
    WHERE
        lower(table_name) = 'd_dialler_brut'
        AND column_name LIKE 'REASON%';

    dbms_output.put_line(cols);
END;

However, if I try the below code, it throws the below error

Error report - ORA-00905: missing keyword ORA-06512: at line 20 00905. 00000 - "missing keyword"

SET SERVEROUTPUT ON;

DECLARE
    cols        CLOB;
    vstr_cols   CLOB;
BEGIN
    vstr_cols := q'[
    SELECT
        LISTAGG('"'
                || column_name
                || '"', ',') WITHIN GROUP(
            ORDER BY
                column_name
        )
    INTO cols
    FROM
        all_tab_columns
    WHERE
        lower(table_name) = 'd_dialler_brut'
        AND column_name LIKE 'REASON%']'
    ;
    EXECUTE IMMEDIATE ( vstr_cols );
    dbms_output.put_line(cols);
END;

What's wrong in the second code? How can I avoid the error?

Improve this question
0

1 Answer 1

Reset to default
4

Remove the INTO clause from the SELECT statement you are building and add it to the EXECUTE IMMEDIATE statement

vstr_cols := q'[
SELECT
    LISTAGG('"'
            || column_name
            || '"', ',') WITHIN GROUP(
        ORDER BY
            column_name
    )
FROM
    all_tab_columns
WHERE
    lower(table_name) = 'd_dialler_brut'
    AND column_name LIKE 'REASON%']'
;

EXECUTE IMMEDIATE vstr_cols
             INTO cols;

Of course, I'd question the appropriateness of using dynamic SQL in the first place here. If you can do something using static SQL, you really ought to use static SQL.

I'd also strongly suggest that you log the SQL statement or write it out using dbms_output before executing it. Otherwise, you'll make it much harder on yourself to debug.

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

8 Comments

Thanks. Not sure what do you mean by logging the SQL statement before executing it. Also, what are the benefits of static SQL vs dynamic in this case?
@jeiv - Logging meaning writing it to a table or whatever logging mechanism your application implements so that when you get an error, you can see exactly what query was dynamically built. Lots of benefits to keeping things as static SQL-- you get compilation errors at compile time rather than at runtime, you get much more specific errors when there are syntax errors, Oracle keeps track of dependencies for you, you don't have to worry about things like SQL injection attacks, your code is much easier to read, write, debug, and maintain.
@jeiv - Yes, if you want to create a table, you'd need to use dynamic SQL. It is, however, exceedingly rare that you would want to create a table in a stored procedure. Tables in Oracle are created once, when the application is installed, not at runtime. I've personally been writing PL/SQL code for, gosh, north of 20 years and have yet to encounter a situation where I really wanted to create a table in a stored procedure. I've seen a couple of third party systems that created tables at runtime periodically and they were generally a royal pain to manage.
@jeiv - It really depends on exactly what you are doing. If you're doing a data warehouse load and writing to staging tables via a complete reload, it's not unreasonable to do a truncate and reload. It's generally faster to do an incremental load via a merge statement but that may not always be practical. I'm not sure what problem you're trying to solve with a dynamic pivot into a newly created table so I'm not sure what to suggest. But given that other code can't consume data from a dynamically created table unless it is also dynamic, I'd be wary.
@jeiv - Better than dropping and recreating the table? Yes. Identifying the delta and loading that through a merge would likely be the most efficient.
|