Блог

Я пытаюсь реализовать простую процедуру, которая вычисляет соотношение различных значений данного поля и сохраняет результат во вспомогательной таблице.

   CREATE TABLE TEST_DATA_QTY_AND
  (

  TABLE_ID VARCHAR2(30),
  FLD_ID VARCHAR2(30),
  MEASURE NUMBER(1, 2),
  DATA_T DATE DEFAULT SYSDATE,
  NOTES VARCHAR2(255)

  );


CREATE OR REPLACE PROCEDURE DATA_QTY_AND(pTable IN VARCHAR2, pField IN VARCHAR2)

 IS


v_sql varchar2(2000);


v_sql := 'INSERT INTO TEST_DATA_QTY_AND (TABLE_ID, FLD_ID, MEASURE)'||
         'VALUES('||
         pTable||', '||pField||', ('||
         'SELECT SUM(CASE WHEN '||pField||' <> 0 THEN 1 END) /COUNT(*) FROM'||
         pTable||'));';

EXECUTE IMMEDIATE(v_sql);

COMMIT;

   EXCEPTION
   ...
   END;
  

Однако я продолжаю получать ОШИБКУ- ORA-00923: из ключевого слова, не найденного там, где ожидалось.
Приветствуется любая помощь.

Спасибо

Ваше объяснение не очень понятно, я пытаюсь прояснить, что я понял (напрягая свое воображение), со следующим примером / дальнейшим описанием

Допустим, у вас есть две основные таблицы

 create table tbl_1(
    id          int not null primary_key,
    value_1     int 
);
create table tbl_2(
    id          int not null primary_key,
    value_2     int 
);
  

В случае, если вы передаете tbl_1, value_1 в качестве параметров вашей процедуре, ваш insert SQL должен быть

 INSERT INTO TEST_DATA_QTY_AND (TABLE_ID, FLD_ID, MEASURE) values ('tbl_1', 'value_1', (select sum(case when value_1 <> 0 then 1 else 0 end) from tbl_1))
  

Когда вы передаете tbl_2, value_2 в качестве параметров вашей процедуре, ваш insert SQL должен быть

 INSERT INTO TEST_DATA_QTY_AND (TABLE_ID, FLD_ID, MEASURE) values ('tbl_2', 'value_2', (select sum(case when value_2 <> 0 then 1 else 0 end) from tbl_2))
  

Если это понимание верно, то должно сработать следующее

 CREATE OR REPLACE PROCEDURE DATA_QTY_AND(pTable IN VARCHAR2, pField IN VARCHAR2)
IS
  v_sql varchar2(2000);
BEGIN

  v_sql :=  utl_lms.format_message(
        'INSERT INTO TEST_DATA_QTY_AND (TABLE_ID, FLD_ID, MEASURE) VALUES('''%s''', '''%s''', '
      ||'(SELECT SUM(CASE WHEN %s <> 0 THEN 1 ELSE 0 END)/ COUNT(*) FROM %s))',  
      pTable, pField, pField, pTable);  

EXECUTE IMMEDIATE(v_sql);
END;

  

Обратите внимание, что я оставил обработку транзакций и исключений для вас в качестве упражнения. Также обратите внимание, на момент написания этого у меня нет доступа к Oracle DB, следовательно, не удалось создать / скомпилировать, чтобы устранить любую ошибку / проблему. Вам следует попытаться устранить проблемы (если таковые имеются). В случае, если вы не можете, оставьте комментарий, я сделаю это, когда у меня будет доступ к экземпляру базы данных.

ОДНАКО все это только тогда, когда мое расширение вашей проблемы правильное.

Вам нужно заключить строковые значения вашего литерала в одинарные кавычки, а одинарные кавычки, используемые для хранения динамической строки SQL, не учитываются. Мы можем попробовать использовать две одинарные кавычки для представления одной буквальной одинарной кавычки:

 v_sql := 'INSERT INTO TEST_DATA_QTY_AND (TABLE_ID, FLD_ID, MEASURE) ' ||
         'VALUES(' ||
         '''' || pTable || ''', ''' || pField || ''', (' ||
         'SELECT SUM(CASE WHEN ' || pField || ' <> 0 THEN 1 END) / COUNT(*) FROM ' ||
         pTable || '))';
  

Обратите внимание, что в подзапросе вы сравниваете pField текстовую переменную с 0 без кавычек, то есть целое число. Это не имеет смысла, поэтому в приведенном выше фрагменте я сравниваю с '0' вместо числа.

ORA-00923: из ключевого слова, не найденного там, где ожидалось.

Ваша строка выглядит следующим образом:

 FROM'||
pTable||'));';
  

После литерала нет пробела FROM , поэтому собранный код объединит параметр table name с параметром FROM, чтобы получить строку типа:

 SEELCT COLUMN_1 FROMTABLE_23
  

Отсюда ошибка.

Динамический SQL сложен, потому что он превращает ошибки компиляции в ошибки времени выполнения. Вы избавите себя от многих проблем, если добавите какую-нибудь отладку. Простой dbms_output.put_line(v_sql); позволил бы вам увидеть собранный код: вы, вероятно, сразу же заметили бы свой bloomer.

Вы можете использовать execute immediate с переменными после using ключевого слова

 create or replace procedure data_qty_and ( pTable varchar2, pField varchar2 ) is
 v_sql varchar2(2000);
begin
  v_sql := 'insert into test_data_qty_and( table_id, fld_id, measure  )
            select :1, :2, sum(case when :2 <> 0 then 1 end ) /count(*) from '||pTable;
  dbms_output.put_line(v_sql);
  execute immediate v_sql using pTable, pField, pField;

  commit;
 exception when others then dbms_output.put_line(sqlerrm);
end;
  

использование переменных привязки всегда намного лучше, чем конкатенация строк, которая может быть уязвима для инъекционных атак, в блоке кода.