"Select * into table internal_table from database_table" in native SQL

[Former Member]

Dear All,

Is it possible to insert into internal table from database table in native sql instead of open sql? Regarding single values it's clear, just to use ":variable_name". But how to map internal table? RDBMS Oracle version > 9. If it's possible and you know how to do it, please, give some code example.

Thank you,

Pavel

[Former Member]

Dear All,

Is it possible to insert into internal table from database table in native sql instead of open sql? Regarding single values it's clear, just to use ":variable_name". But how to map internal table? RDBMS Oracle version > 9. If it's possible and you know how to do it, please, give some code example.

Thank you,

Pavel

[Former Member]

Hi ,

Here is the code snippet for the native sql .

 select * from zwf_role into table i_wfroles." where role_id ='CAO'.
  loop at i_wfroles into wa_wfroles.
    EXEC sql.
      insert into   ROLES_MASTER_SAP
             values (:wa_wfroles-ROLE_ID,:wa_wfroles-ROLE_NM,
                    :wa_wfroles-ROLE_DESC,:wa_wfroles-PAR_ROLE_ID,
                    :wa_wfroles-WF_ROLE_ORDER,:wa_wfroles-ROLE_TYPE )
    ENDEXEC.
    if sy-subrc = 0 .
      suc_rec_cnt = suc_rec_cnt + 1.
    else.
      fail_rec_cnt = fail_rec_cnt + 1.
    endif.

*              ( ROLE_ID,ROLE_NAME, ROLE_DESC,PARENT_ROLE_ID,ROLE_ORDER,
*                  ROLE_TYPE )
  endloop.

zwf_role database table has the same files

Regards

Girish

[Former Member]

Open SQL supports internal tables because it is an SAP construct. Oracle knows nothing about ABAP.

So you can fetch data using a cursor and native SQL and put the results into an internal table one row at a time.

Rob

[Former Member]

Thank you for reply.

Cursor can be, and probably the only solution. May be I expect a lot, but it would be good to have something like BULK COLLECT in PL/SQL:

exec sql.
select * 
bulk collect into :sap_internal_tab
from oracle_tab ....

endexec.

instead, we have something like this:

data: var1 type string,
i_tab type table of var1.
exec sql.
for cur in select * from tab1 loop
:var1:=cur.a;
endexec.

append var1 to i_tab.

exec sql.

end loop;

end exec.

[Former Member]

select * from d.b table into itab[ ] where stmt and with end select

thanking you

[matt]

select * from d.b table into itab[ ] where stmt and with  end select 
> thanking you

?????

Do you know what native SQL is?

[Former Member]

correct me sir ...........

[Former Member]

Tank you everyone for replyings.

I found in sap help this example:

EXEC SQL
  OPEN c1 FOR
    SELECT client, arg1, arg2 FROM table_001
     WHERE client = '000' AND arg2 = :arg2
ENDEXEC.
DO.
  EXEC SQL.
    FETCH NEXT c1 INTO :wa-client, :wa-arg1, :wa-arg2
  ENDEXEC.
  IF sy-subrc <> 0.
    EXIT.
  ELSE.
    <verarbeite Daten>
  ENDIF.
ENDDO.
EXEC SQL.
  CLOSE c1
ENDEXEC.

(http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm)

This is almost the thing I expected.

Problem solved.

[Former Member]

After the fetch, you will need to append the wa to the itab.

Rob