Unable to run script

[jjeffman]

Hello,

I am using ODAC version 9.7.26 on C++Builder 6.0 Professional edition.

I am attempting to run the script bellow which I have added it first to a TOraSQL component using parameters and second to a TOraScript using macros, but none of the alternatives were successful.

Code: Select all

"DECLARE \
 -- data a excluir  \
 day DATE := null; \
 nrec     INTEGER := 0;  \
 -- Cursor para identificar o rowid (endereco unico) a ser limpo na tabela  \
 -- Rowid seria um endereco unico que localiza o registro na instancia dados  \
 CURSOR CV_LIMPEZA IS \
     SELECT ROWID      \
     FROM SAM.HST_HOL_15MIN \
     WHERE DATAHORA >= day \
     AND   DATAHORA <= day +1 ; \
 -- Estou criando uma especie de array aqui  \
 TYPE TP_ROWID IS TABLE OF CHAR(18);  \
 RY_ROWID      TP_ROWID; \
BEGIN  \
  day := TO_DATE(&DTDEL , \'YYYY-MM-DD\'); \
  -- Abrindo cursor \
  OPEN CV_LIMPEZA; \
  -- Loop de limpeza principal  \
  LOOP \
     -- Carregando no array 50000 registros (enderecos rowid) \
     FETCH  CV_LIMPEZA BULK COLLECT INTO RY_ROWID LIMIT 50000; \
     -- Se chegou ao final do cursor, sai do LOOP    \
     EXIT WHEN CV_LIMPEZA%NOTFOUND;  \
     -- Comando FORALL para limpeza massiva dos registros  \
	 BEGIN  \
	   FORALL I IN 1..RY_ROWID.COUNT \
			 DELETE FROM SAM.HST_HOL_15MIN WHERE ROWID = RY_ROWID(I); \
		nrec := nrec + SQL%ROWCOUNT;  \
	 EXCEPTION   \
	   WHEN OTHERS THEN \
		   ROLLBACK;  \
		   RAISE;  \
	 END;  \
     COMMIT; \
   END LOOP; \
   -- Fechando o cursor \
   CLOSE CV_LIMPEZA; \
   --DBMS_OUTPUT.PUT_LINE(nrec|| ' registros excluidos.'); \
END;"

Where is the correct position to place a macro or a parameter to run a Script on ODAC components, using SQLDeveloper I can run it.

Thank you very much.

[MaximG]

When executing the query you are interested in, you can either use the macro :

Code: Select all

...
OraScript -> MacroByName("DTDEL") -> Value = "2017-10-18";
OraScript -> Execute();
...
OraSQL -> MacroByName("DTDEL") -> Value = "2017-10-18";
OraSQL -> Execute();

or in the query text, pass the DTDEL value as the :DTDEL parameter

Code: Select all

...
OraSQL -> ParamByName("DTDEL") -> Value = "2017-10-18";
OraSQL -> Execute();

[jjeffman]

Hello Maxim,

Thank you very much for answering me.

As I was in a hurry I ended up building a stored procedure instead of placing the code on an ODAC component. I think the problem is linked to "--" comments style which it seems ODAC do not get along with. I was getting the macro or parameter not found exception when using the "ByName" properties.

I will try the code without comments to check if it works.

Best regards.

Jayme Jeffman

[MaximG]

We will be waiting for the results of the final test

[jjeffman]

I have just done it.

I have just done it. I have removed all comments but I kept the dbms_output commands and everything works fine.

Thank you very much.

Best regards.

Jayme Jeffman

[MaximG]

We are glad that you found a necessary solution. Please don't hesitate to contact us with questions concerning ODAC usage.