Skip to Content
avatar image
Former Member

Use a variable as a table name with NATIVE SQL

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 21, 2011 at 06:39 PM

    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,

    m.

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 21, 2011 at 03:48 PM

    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 😊

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 22, 2011 at 03:43 AM

    Have a look at program adbc_demo. Its good.

    Keshav

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 21, 2011 at 03:16 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 21, 2011 at 03:19 PM

    Hi

    Can you try something like this

    data: lv_table type string value 'theTable'.

    select * from :lv_table

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 22, 2011 at 10:35 AM

    Thank you all, I solved the problem !!

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 04, 2013 at 10:42 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded