CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Data-Base
Data-Base

Reputation: 8598

Create Table As within PL/SQL?

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;

Upvotes: 6

Views: 60039

Answers (2)

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40489

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;

Upvotes: 9

Florin Ghita
Florin Ghita

Reputation: 17643

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

Upvotes: 1

Related Questions