I have tried to use DBMS_SQLDIAG such as in this tutorial (www.dba-oracle.com/t_advanced_sql_repair_advisor.htm) but my report returns "No SQL patch was found to resolve the problem".
So, I cannot perform code to accept recommendations and corrections:
exec dbms_sqldiag.accept_sql_patch (task_name => 'test_task1',task_owner => 'SYS');
The use of disabling/enabling error correction also does not help (I do not know how to understand which number to indicate in place 4728348 - but it is taken from a similar example of the book by Sam Alapati): ALTER SESSION SET "_FIX_CONTROL"='4728348:OFF'.
Please, help me figure out how to use the example of this DELETE command (or any other command that cause critical error) to get recommendations and apply corrections.
I would be overly grateful for your help and advice.
My code is below:
ALTER SESSION SET "_FIX_CONTROL"='4728348:OFF';
DELETE FROM hr.employees WHERE ROWID <> (SELECT MAX(ROWID) FROM hr.employees GROUP by employee_id);
DECLARE
report_out clob;
task_id varchar2(50);
BEGIN
task_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK(
sql_text => 'DELETE FROM hr.employees WHERE ROWID <> (SELECT MAX(ROWID) FROM hr.employees GROUP by employee_id)',
task_name=>'test_task1',
problem_type=>dbms_sqldiag.problem_type_compilation_error);
END;
/
exec dbms_sqldiag.execute_diagnosis_task('test_task1');
DECLARE
rep_out CLOB;
BEGIN
rep_out := dbms_sqldiag.report_diagnosis_task('test_task1', dbms_sqldiag.type_text);
dbms_output.put_line('Report : ' || rep_out);
END;
/
The output for command
exec dbms_sqldiag.accept_sql_patch (task_name => 'test_task1',task_owner => 'SYS');
is:
ORA-13786: missing SQL text of statement object "1" for tuning task
"test_task1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16255
ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 31
ORA-06512: at "SYS.DBMS_SQLDIAG", line 1132
ORA-06512: at "SYS.DBMS_SQLDIAG", line 1158