Getting error while using dynamic SQL

Question

I'm getting an error when I try to execute this statement

EXECUTE IMMEDIATE
               (   'CREATE TABLE DP.SD_NEW_TEST TABLESPACE DP_TS_TX_DATA INITRANS   10
                          PARTITION  BY RANGE (SALES_DATE)(
                          PARTITION  '''
                || Partition_name
                || ''' VALUES LESS THAN '''
                || MAX_DATET
                || ''' LOGGING
                        NOCOMPRESS 
                        TABLESPACE DP_TS_TX_DATA INITRANS   10) AS SELECT * FROM DP.SALES_DATA WHERE 1=2'

Here Partition_name,MAX_DATET values I'm fetching through other queries as part my procedure . Please suggest if i'm doing anything wrong here.

"An error"? Any chance you could be more specific than that? — Tony Andrews
– Tony Andrews, Commented Dec 7, 2017 at 10:38
Put your generated SQL into a variable and output it with dbms_output.put_line. This output will help you to understand where you are wrong — Dmitriy
– Dmitriy, Commented Dec 7, 2017 at 10:42

APC · Accepted Answer · 2017-12-07 13:47:38Z

1

I'm going to guess that the value of MAX_DATET is not formatted in an acceptable way. Basically you need to provide dates as a string with an appropriate data conversion.

The easier thing would be if MAX_DATET fitted the ANSI format, yyyy-mm-dd. Then all you would need is:

            || ''' VALUES LESS THAN date '''
            || MAX_DATET
            || ''' LOGGING

Otherwise you'll need to code a to_date() call and provide the appropriate mask.

Dynamic SQL is hard, because it turns compilation errors into runtime errors. As the commenters say, sensible practice here would be to assemble the statement in a variable, which can be displayed through DBMS_OUTPUT. Debugging is always easier when we can see the code which is failing.

answered Dec 7, 2017 at 13:47

APC

147k20 gold badges180 silver badges289 bronze badges

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

Collectives™ on Stack Overflow

Getting error while using dynamic SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related