topic Re: Use a variable as a table name with NATIVE SQL in Application Development and Automation Discussions

Use a variable as a table name with NATIVE SQL

Former Member — Wed, 21 Sep 2011 14:35:20 GMT

Hi all,

I am trying to execute a SELECT statement in order to fetch data from an external Oracle DB table to SAP with the following instructions:

EXEC SQL.

SELECT cityfrom, cityto

INTO STRUCTURE :wa

FROM spfli

WHERE mandt = :sy-mandt AND

carrid = :p_carrid AND connid = :p_connid

ENDEXEC.

However, I need to indicate the external table name from a variable instead of the solution above. That is, declaring a variable and store the name of the table (e.q. spfli) in it. The resulting ABAP code would be something like:

EXEC SQL.

SELECT cityfrom, cityto

INTO STRUCTURE :wa

FROM <VARIABLE>

WHERE mandt = :sy-mandt AND

carrid = :p_carrid AND connid = :p_connid

ENDEXEC.

Does anybody know if is possible to do that?

If not, is there any other solution?

Thank you in advance

Re: Use a variable as a table name with NATIVE SQL

former_member226519 — Wed, 21 Sep 2011 15:16:29 GMT

you could try to create a FORM dynamically at runtime with your SQL statement and perform that FORM.

Re: Use a variable as a table name with NATIVE SQL

darren_bambrick2 — Wed, 21 Sep 2011 15:19:03 GMT

Can you try something like this

data: lv_table type string value 'theTable'.

select * from :lv_table

Re: Use a variable as a table name with NATIVE SQL

SuhaSaha — Wed, 21 Sep 2011 15:48:09 GMT

Hello,

AFAIR you cannot use dynamic tokens in Native SQL. In order to cater to your requirement you have to use [ADBC|http://help.sap.com/abapdocu_702/en/abenadbc.htm] classes.

BR,

Suhas

PS: IMHO ADBC classes are easier to use & maintain

Re: Use a variable as a table name with NATIVE SQL

Former Member — Wed, 21 Sep 2011 18:39:10 GMT

Yes, as Suhas said, you could use the ADBC API and his class CL_SQL_CONNECTION to achieve this...

Here is a small example:

 
PARAMETERS: p_carrid TYPE spfli-carrid,
                           p_connid TYPE spfli-connid.

DATA:
  l_con_ref      TYPE REF TO cl_sql_connection,
  l_stmt         TYPE string,
  l_stmt_ref     TYPE REF TO cl_sql_statement,
  l_dref         TYPE REF TO data,
  l_res_ref      TYPE REF TO cl_sql_result_set,
  l_col1         TYPE spfli-carrid,
  l_col2         TYPE spfli-connid,
  l_wa           TYPE spfli.

CONSTANTS:
  c_tabname  TYPE string VALUE 'SPFLI'.


* Create the connecction object
CREATE OBJECT l_con_ref.

* Create the SQL statement object
CONCATENATE 'select * from' c_tabname 'where carrid = ? and connid = ?'
       INTO l_stmt SEPARATED BY space.                           "#EC NOTEXT

l_stmt_ref = l_con_ref->create_statement( ).

* Bind input variables
GET REFERENCE OF l_col1 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
GET REFERENCE OF l_col2 INTO l_dref.
l_stmt_ref->set_param( l_dref ).

* Set the input value and execute the query
l_col1 = p_carrid.
l_col2 = p_connid.

l_res_ref = l_stmt_ref->execute_query( l_stmt ).

* Set output structure
GET REFERENCE OF l_wa INTO l_dref.
l_res_ref->set_param_struct( l_dref ).

* Show result
WHILE l_res_ref->next( ) > 0.
  WRITE: / 'Result:', l_wa-carrid, l_wa-connid.
ENDWHILE.

* Close the result set object
l_res_ref->close( ).

Otherwise you can also use the FM DB_EXECUTE_SQL...

Kr,

Re: Use a variable as a table name with NATIVE SQL

kesavadas_thekkillath — Thu, 22 Sep 2011 03:43:30 GMT

Have a look at program adbc_demo. Its good.

Keshav

Re: Use a variable as a table name with NATIVE SQL

Former Member — Thu, 22 Sep 2011 10:35:10 GMT

Thank you all, I solved the problem !!

Re: Use a variable as a table name with NATIVE SQL

former_member212148 — Wed, 04 Sep 2013 10:42:34 GMT

Dear Manu,

Your code is working fine.

My requirement is little bit different.

I want to get data from two table into internal table.

I have written code using inner join but i am getting dump

The exception 'CX_SQL_EXCEPTION' was raised,

Could you give me solution.

Thanks

Ranjit K.

Re: Use a variable as a table name with NATIVE SQL

SuhaSaha — Wed, 04 Sep 2013 11:45:23 GMT

Hello Ranjit,

Please create a new thread for and provide relevant & appropriate data to get better responses.

BR,

Suhas

Re: Use a variable as a table name with NATIVE SQL

former_member212148 — Thu, 05 Sep 2013 04:09:35 GMT

Thanks Suhas,

I will keep it mind for future.

Re: Use a variable as a table name with NATIVE SQL

merrill_schilling — Wed, 08 Jul 2020 10:20:31 GMT

how did you solve it?

BR Merrill