I’m working on a stored procedure that would to some nice things, where I need to execute native some SQL kitchen code because using top and ordernot yet fully supported on SqlServer driver of Invantive by Query Tool. I have hard time to make a execute native into a execute immediate
Let’s take an example:
declare
l_stmt varchar2;
source_table varchar2 := 'my_table_name';
last_job_start datetime;
begin
l_stmt := 'execute native ''select top 1 job_start from sync_data where job_table_name like '
|| source_table
|| ' order by id DESC'' into last_job_start datacontainer '''
|| d_cont
|| ''''
;
execute immediate l_stmt;
end
This one won’t work. Query Tool says:
Error: missing ‘;’ at ‘<EOF>’.
Whereas this statement work :
declare
l_stmt varchar2;
source_table varchar2 := 'my_table_name';
last_job_start datetime;
begin
execute native 'select top 1 job_start from sync_data where job_table_name like '''
|| source_table
|| ''' order by id DESC'
into last_job_start
datacontainer 'sage_sql2019_Vega_replication'
;
end
The first statement will not work; it is not correct.
A working solution is for instance:
declare
l_stmt varchar2;
source_table varchar2 := 'my_table_name';
last_job_start datetime;
begin
execute native 'select top 1 job_start'
|| ' from (select getdate() job_start, ''my_table_name'' job_table_name, 1 id ) t '
|| ' where job_table_name like '''
|| source_table
|| ''' order by id DESC'
into last_job_start
datacontainer 'sqlserver'
;
dbms_output.put_line('Value retrieved is: ' || to_char(last_job_start) || '.');
end;