Create Table As within PL/SQL?

Question

I'm trying to create a table within PL/SQL

how I can achieve that?

keep getting

Error report:

ORA-00933: "SQL command not properly ended"

here is the code that I have error with

DECLARE
  station_id_ms1  NUMBER :=10347;
  realtime_start  DATE   :=to_date('2012-01-01 00:00:00','YYYY-DD-MM HH24:MI:SS');
  realtime_end    DATE   :=to_date('2012-07-01 00:00:00','YYYY-DD-MM HH24:MI:SS');
BEGIN
  EXECUTE IMMEDIATE ('
  CREATE TABLE new_table_name
  AS
  SELECT
  ((realtime - to_date(''01-JAN-1970'',''DD-MON-YYYY'')) * (86400)) AS realtime_ms1,
  magnetic_ms_id,
  ADC_value_pp_2_mgntc_fld_amp(ch2_value,ch2_gain_value,magnetic_ms_id,2) AS B_x_ms1,
  ADC_value_pp_2_mgntc_fld_amp(ch1_value,ch1_gain_value,magnetic_ms_id,1) AS B_y_ms1,
  real_nanosecs2*4/3*360/20e6 AS phase_x_ms1,
  real_nanosecs1*4/3*360/20e6 AS phase_y_ms1
  FROM
      raw_mag
  WHERE
    magnetic_ms_id    = '||station_id_ms1||'
  AND realtime        > '||realtime_start||'
  AND realtime        < '||realtime_end||'
  AND ch1_tune_value  = 0
  AND realtime        < pkg_timezone.change_timezone(gettime,''CET'',''UTC'')
  ');  
END;

@vulkanino: because you can't execute DDL statements inside PL/SQL without EXECUTE IMMEDIATE. — Benoit, Mar 06 '12 at 15:49
@Benoit - that just begs the question. Why PL/SQL? Why not just create the table? There are scenarios where it is legitimate but they are vastly outnumbered by the cases where this approach is misguided or just plain wrong. — APC, Mar 07 '12 at 12:25
@APC - What do you mean? "create table" seems not working under PL/SQL! do you have an example that shows it work? .... thanks — Data-Base, Mar 07 '12 at 14:29
@Data-Base - what I mean is, database schemas should be stable. We wouldf expect things like tables to be slowly changing, and hence created with DDL scripts, preferably scripts under source control. A requirement to create tables in PL/SQL doesn't fit that model. Now perhaps there is a genuine need to create tables dynamically, in a background job. But more usually it's because the developer misunderstands the requirement and/or doesn't know how to write a proper Oracle database application. — APC, Mar 07 '12 at 15:20
I use create table statement in a procedure only if it's a temporary table. requirement: Not an important table. Someone might drop it, I just need it when that procedure is running to insert into. — Nicolas de Fontenay, Aug 20 '13 at 17:56

René Nyffenegger · Accepted Answer · 2012-03-06T16:25:49.337

You should do the char-to-date conversion within the plsql-string that you excecute immediate.

The date you declared will be "back-cast" to a varchar2 in the concatenation and "re-cast" into a date again for the execution of the create table statement. And "all sorts of things" can happen in these two casts, so you want to make sure you're in control how the character-string is interpreted when cast to a date.

DECLARE
  station_id_ms1  NUMBER :=10347;
  realtime_start  VARCHAR2(100)   :='2012-01-01 00:00:00';
  realtime_end    VARCHAR2(100)   :='2012-07-01 00:00:00';
BEGIN
  EXECUTE IMMEDIATE ('
  CREATE TABLE new_table_name
  AS
  SELECT
  ((realtime - to_date(''01-JAN-1970'',''DD-MON-YYYY'')) * (86400)) AS realtime_ms1,
  magnetic_ms_id,
  ADC_value_pp_2_mgntc_fld_amp(ch2_value,ch2_gain_value,magnetic_ms_id,2) AS B_x_ms1,
  ADC_value_pp_2_mgntc_fld_amp(ch1_value,ch1_gain_value,magnetic_ms_id,1) AS B_y_ms1,
  real_nanosecs2*4/3*360/20e6 AS phase_x_ms1,
  real_nanosecs1*4/3*360/20e6 AS phase_y_ms1
  FROM
      raw_mag
  WHERE
    magnetic_ms_id    = '||station_id_ms1||'
  AND realtime        > to_date(''' || realtime_start || ''', ''YYYY-DD-MM HH24:MI:SS'')
  AND realtime        < to_date(''' || realtime_end   || ''', ''YYYY-DD-MM HH24:MI:SS'')
  AND ch1_tune_value  = 0
  AND realtime        < pkg_timezone.change_timezone(gettime,''CET'',''UTC'')
  ');  
END;

score 1 · Answer 2 · answered Mar 06 '12 at 15:57

1

I would use binds for station_id_ms1, realtime_start, realtime_end:

EXECUTE IMMEDIATE '
 ...
 WHERE
 magnetic_ms_id    = :station_id_ms1
  AND realtime        > :realtime_start
  AND realtime        < :realtime_end
 ...
 ' USING IN station_id_ms1, realtime_start, realtime_end

answered Mar 06 '12 at 15:57

Florin Ghita

17,525
6
57
76

1

you mean USING IN station_id_ms1, realtime_start, realtime_end ??? if so, then I get this error ORA-00936: "missing expression" – Data-Base Mar 06 '12 at 16:00
Yes, binds. this value from query, :station_id_ms1, will be replaced naturally with value written in USING IN station_id_ms1. – Florin Ghita Mar 06 '12 at 16:02
I got this ORA-01027: "bind variables not allowed for data definition operations" – Data-Base Mar 06 '12 at 16:07
found this "Bind variables are allowed in DML statements (SELECT, UPDATE, DELETE) or inside programs, but not inside DDLs (the Data Definition Language statements)" – Data-Base Mar 06 '12 at 16:09
1

argh, in this case you should be more carefully with date variables. In the query should appear to_date(variable, format). The statement is a text. Date variable will be put as varchar2 (implicit cast) – Florin Ghita Mar 06 '12 at 16:10

Create Table As within PL/SQL?

2 Answers2

Linked