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

Questions
1

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!

2

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
3

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.

4

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:

dbms_output
dbms_output831×405 16 KB

5

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?

7

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

execute native statement
execute native statement835×173 27.9 KB