Oracle query - Stored proc

Question

I am running a stored proc :

CREATE OR REPLACE MYSP (runDate Date)
BEGIN 
EXECUTE IMMEDIATE 'CREATE TABLE MYTABLE AS (SELECT * FROM DATATABLE WHERE DATADATE = :1' USING runDate); 

END MYSP;

I get the error -

ORA-01027: bind variables not allowed for data definitions operations 
ORA-06512: "Database.MYSP", line 4

Can someone help where am I going wrong.

I think you need a close parenthesis ) after the **DATADATE = :1** — mtwaddell, May 17 '13 at 18:46
@Monica :Also u missed CREATE OR REPLACE PROCEDURE MYSP --procedure keyword — Gaurav Soni, May 17 '13 at 18:57
@Monica Sorry, I should have put a little more thought into my earlier comment. I did a little looking around and agree with Justin's answer. Oracle won't allow it. — mtwaddell, May 17 '13 at 19:03
Why do you want to create a table in a stored procedure? It's almost always the wrong design decision. http://stackoverflow.com/a/1193443/146325 — APC, May 17 '13 at 21:10
[Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?](http://stackoverflow.com/q/25489002/1461424) — sampathsris, Jul 15 '15 at 03:36

score 10 · Accepted Answer · answered May 17 '13 at 18:55

10

The error is pretty self-explanatory. You cannot use bind variables in a DDL statement.

It is incredibly, incredibly unlikely that you really want to create a table in Oracle dynamically. I would strongly suggest taking a step back and finding a different way to design the system so that you're not trying to create tables at runtime.

If you are absolutely convinced that you have an extraordinary need that requires creating a table at runtime, you can't use bind variables and you don't want to have mismatched parenthesis (you have an open parenthesis but no close parenthesis). Assuming that you are ignoring the time component, something like

EXECUTE IMMEDIATE 'CREATE TABLE MYTABLE ' ||
                  ' AS ' ||
                  ' SELECT * FROM DATATABLE WHERE DATADATE = to_date( ' || 
                  to_char( runDate, 'YYYY-MM-DD' ) ||
                  ', ''YYYY-MM-DD'')';

should work.

answered May 17 '13 at 18:55

Justin Cave

227,342
24
367
384

Thanks. But now I get the error: ORA-01847: day of month must be between 1 and last day of month – Monica May 17 '13 at 19:08
Its funny since I am passing 30-Apr-2013 – Monica May 17 '13 at 19:10
3

@Monica - Are you actually passing a `DATE`? Or are you passing a string that represents a date? Are you sure that the format mask in your `to_char` matches the format mask in your `to_date`? – Justin Cave May 17 '13 at 19:12
If you're passing a date in a string as '30-APR-2013' the date format would need to be 'DD-MON-YYYY'. – Bob Jarvis - Слава Україні May 18 '13 at 15:59

Oracle query - Stored proc

1 Answers1