How to execute immediate an `execute native stmt` in a stored procedure?

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 order not 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

Thanks for your help!

Small correction, my first statement is:

declare
  l_stmt varchar2;
  source_table varchar2 := ‘my_table_name’;
  last_job_start datetime;
  d_cont varchar2 := ‘sage_sql2019_Vega_replication’ -- container
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

The following error:

indicates that the (closing) end-of-statement character ‘;’ is missing at the end of the file.

This can be solved by adding ; to the last end statement.

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;

Result:

Thanks for your reply.

If understand well, I cannot put my container alias ‘sqlserver’ into a variable that could be called by the execute native like :

declare
  container_alias varchar2 := 'sqlserver';
  result varchar2;
begin
  execute native 'my stmt'
  into result
  datacontainer container_alias;
end;

Am I correct?

Yes, that is correct. With current release 20.2.86 it is not yet possible to use an expression or variable as datacontainer.

See the grammar on Invantive SQL Grammar v22.1