CODEWITHSUNDEEP
sqloracle-databaseplsqldynamic-sql
Thiru
Thiru

Reputation: 1

Getting error while using dynamic SQL

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.

Upvotes: 0

Views: 60

Answers (1)

APC
APC

Reputation: 146349

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.

Upvotes: 1

Related Questions