ABAP internal table insert to remote Oracle database table problem

[Former Member]

Hello.

In an ABAP function, I can do a one on one insert in a table stored outside SAP in an Oracle database (Loop on the internal table). This piece of code works perfect .The problem is performance..

SO...

Based on the literature, it says that I can insert an internal table (mass insert) with the following statement:

INSERT <remote database table>

FROM TABLE :internal_table.

When I execute this statement (and I tried a lot of variants..) I always get this error:

Database error text........: "ORA-00925: missing INTO keyword#

Triggering SQL statement...: "INSERT SAP.ZBSCASH FROM TABLE ?

FYI:

(SAP is the remote database schema name and ZBSCASH is the tble name

Any hint on how to solve it ? I searched SDN and various sites but no solution yet.

Thanks

YB

Edited by: Yves Baril on Aug 25, 2008 10:29 PM

[Former Member]

Yves,

Can you please post more of your code?

E.g. are you using 'EXEC SQL ... ENDEXEC' in addition to your 'INSERT' command?

The syntax (and error message) you've posted suggests that you're using 'open' sql in your ABAP program, and not 'native' (e.g. Oracle specific) sql syntax.

Duane

[Former Member]

Hello Duane

Here is the source code:

EXEC SQL.

INSERT SAP.ZBSCASH FROM TABLE :E_T_DATA

ENDEXEC.

E_T_DATA is an internal table.

Tx

Yves

[Former Member]

And a trace form ST05 shows this:

INSERT SAP.ZBSCASH FROM :A0

So :A0 can not be translated to an actual table value.

Insertin in a loop with INSERT INTO ...VALUES works well.

I also tried the same code but inserting into a R3 table and it works perfect.

The other Oracle DB version is 9.2

Tx

[Former Member]

Anyone watching ? I really need quick help on this.

Thanks for all your inputs.

YB

[Former Member]

Yves,

I think you are mixing 'open' SQL (used with database tables that are defined in the ABAP dictionary) syntax with 'native' SQL (used with SQL tables defined externally, e.g. Oracle SQL tables) syntax.

The EXEC SQL ... ENDEXEC indicates you're using 'native' SQL syntax, but the INSERT statement itself (INSERT ... FROM itab) is 'open' SQL syntax. I do not believe that you can do an Oracle SQL INSERT from an 'internal' table. I suggest putting your INSERT within a loop, inserting one record at a time.

Hope this helps ...

Duane

[christine_evans]

I don't think you can do that. The statement INSERT SAP.ZBSCASH FROM TABLE :E_T_DATA where :E_T_DATA is a SAP internal table is not a valid insert statement in Oracle, and, as far as I know (ie I could be wrong here) any SQL statements that you use within an EXEC....ENDEXEC must be valid Oracle SQL statements.

In Oracle, bulk inserts are usually done from database table to database table ie instead of a itab you collect together all your data in a database table and do your insert from that. Something like this might work for you.

TRY.
   EXEC SQL.
     INSERT INTO zcetest21
        (mandt, ebeln, ebelp)
     SELECT
        :sy-mandt, ebeln, ebelp
     FROM ekpo
     WHERE ebeln LIKE '0007003%'
     AND rownum < 10
   ENDEXEC.
   CATCH cx_sy_native_sql_error INTO ex_check_rs.
   ex_text = ex_check_rs->get_text( ).
   error_text = ex_check_rs->sqlmsg.
   EXIT.
ENDTRY.

[Former Member]

Hello Duane

Using a loop and insert works well but it is far from being performing.. And my problem is that I have a window of 30 minutes to transfer over 100K records. I read in the SAP help and other forums that the table insert also works with native SQL.

ANy more insights ?

Thank you for your help DUane.

[Former Member]

Hello Christine

Let me try this when our DB server will back up... and I will get back to you. Thanks a lot for your suggestion.

[Former Member]

Christine

ZBSCASH_TMP is the temp R3 table that I fill and SAP.ZBSCASH is the remote oracle database table in a schema named SAP.

When I put the : in front of the sub selection field names, it says that it cannot find the field name.

The follwing code is executed and returns the following SQL error:

EXEC SQL.

INSERT INTO SAP.ZBSCASH

( SEQ,

OCOMP_CODE,

ZSDSTYPE,

ZBILLTYPE,

OAC_DOC_NO,

OFISCYEAR,

OFISCPER3,

OAMOUNT,

OCURRENCY,

ODOC_TYPE,

OCUSTOMER,

ONAME,

ONAME2,

ONAME3,

OCOUNTRY,

ODOC_NUMBER,

OS_ORD_ITEM,

ZBOM_TYPE,

ZMODEL,

ZREL_NO,

ZSERIAL,

OMATERIAL,

OWBS_ELEMT,

OCON_TYPE,

OAMOUNT,

OAMOUNT_CURR,

OREGION,

ZREGDSCR)

VALUES

(SELECT SEQ,

COMP_CODE,

SALES_TYPE,

BILLING_TYPE,

BILLING_DOC,

BILLING_YEAR,

BILLING_PERIOD,

NET_VALUE,

KOEI1,

SALES_ORDER_TYPE,

CUSTOMER,

NAME1,

NAME2,

COUNTRY,

COUNTRY_CODE,

SALES_DOC,

SALES_ITEM,

BOM_TYPE,

MODEL,

RELEASE_NO,

SERIAL,

MATNR,

WBS,

COND_TYPE,

AMOUNT,

AMOUNT_CURR,

REGION,

REGION_DESC FROM ZBSCASH_TMP )

ENDEXEC.

ERROR: Database error text........: "ORA-00936: missing expression#"

Triggering SQL statement...: "INSERT INTO SAP.ZBSCASH ( SEQ, OCOMP_CODE,

ZSDSTYPE, ZBILLTYPE, OAC_DOC_NO, OFISCYEAR, OFISCPER3, OAMOUNT, OCURRENCY,

A traqce from ST05 outputs:

INSERT INTO

SAP.ZBSCASH

(

SEQ, OCOMP_CODE, ZSDSTYPE, ZBILLTYPE, OAC_DOC_NO, OFISCYEAR, OFISCPER3,

OAMOUNT, OCURRENCY, ODOC_TYPE, OCUSTOMER, ONAME, ONAME2, ONAME3, OCOUNTRY,

ODOC_NUMBER, OS_ORD_ITEM, ZBOM_TYPE, ZMODEL, ZREL_NO, ZSERIAL, OMATERIAL,

OWBS_ELEMT, OCON_TYPE, OAMOUNT, OAMOUNT_CURR, OREGION, ZREGDSCR

)

VALUES

(

SELECT SEQ, COMP_CODE, SALES_TYPE, BILLING_TYPE, BILLING_DOC, BILLING_YEAR,

BILLING_PERIOD, NET_VALUE, KOEI1, SALES_ORDER_TYPE, CUSTOMER, NAME1, NAME2,

COUNTRY, COUNTRY_CODE, SALES_DOC, SALES_ITEM, BOM_TYPE, MODEL, RELEASE_NO,

SERIAL, MATNR, WBS, COND_TYPE, AMOUNT, AMOUNT_CURR, REGION, REGION_DESC FROM

ZBSCASH_TMP

)

Thanks for your help.

[christine_evans]

You need to select your values from table ZBSCASH_TMP (as I did in my example) and not use the VALUES keyword; you only use VALUES if you already have a set of variables that you want to insert into the table so then you don't use the SELECT....FROM.

You prefix a fieldname with : only if it is a variable. Here what you are doing is selecting fields directly from one database table and inserting directly into another database table - no variables involved - so you need to specify the exact table fieldname (which presumably doesn't start with a colon).

[Former Member]

Hello Christine

Forgot to telly ou yesterday I tried different variant of your example and copied the last one ( which was not the one you told me to do in your example).

Here is the error I got originally with your code:

Database error text........: "ORA-00957: duplicate column name#"

riggering SQL statement...: "INSERT INTO SAP.ZBSCASH ( SEQ, OCOMP_CODE,

ZSDSTYPE, ZBILLTYPE, OAC_DOC_NO, OFISCYEAR, OFISCPER3, OAMOUNT, OCURRENCY,

ODOC_TYPE, OCUSTOMER, ONAME, ONAME2, ONAME3, OCOUNTRY, ODOC_NUMBER,

OS_ORD_ITEM, ZBOM_TYPE, ZMODEL, ZREL_NO, ZSERIAL, OMATERIAL, OWBS_ELE

nternal call code.........: "[DBDS/NEW DSQL]"

EXEC SQL.

INSERT INTO SAP.ZBSCASH

(SEQ,

OCOMP_CODE,

ZSDSTYPE,

ZBILLTYPE,

OAC_DOC_NO,

OFISCYEAR,

OFISCPER3,

OAMOUNT,

OCURRENCY,

ODOC_TYPE,

OCUSTOMER,

ONAME,

ONAME2,

ONAME3,

OCOUNTRY,

ODOC_NUMBER,

OS_ORD_ITEM,

ZBOM_TYPE,

ZMODEL,

ZREL_NO,

ZSERIAL,

OMATERIAL,

OWBS_ELEMT,

OCON_TYPE,

OAMOUNT,

OAMOUNT_CURR,

OREGION,

ZREGDSCR)

SELECT SEQ,

COMP_CODE,

SALES_TYPE,

BILLING_TYPE,

BILLING_DOC,

BILLING_YEAR,

BILLING_PERIOD,

NET_VALUE,

KOEI1,

SALES_ORDER_TYPE,

CUSTOMER,

NAME1,

NAME2,

COUNTRY,

COUNTRY_CODE,

SALES_DOC,

SALES_ITEM,

BOM_TYPE,

MODEL,

RELEASE_NO,

SERIAL,

MATNR,

WBS,

COND_TYPE,

AMOUNT,

AMOUNT_CURR,

REGION,

REGION_DESC FROM ZBSCASH_TMP

ENDEXEC.

ANd the external database layout:

CREATE TABLE zbscash

(seq NUMBER(15,2),

ocomp_code VARCHAR2(4),

zsdstype VARCHAR2(2),

zbilltype VARCHAR2(4),

oac_doc_no VARCHAR2(10),

ofiscyear NUMBER(4,0),

ofiscper3 NUMBER(2,0),

net_value NUMBER(15,2),

koei1 VARCHAR2(5),

odoc_type VARCHAR2(4),

ocustomer VARCHAR2(10),

oname VARCHAR2(35),

oname2 VARCHAR2(35),

oname3 VARCHAR2(15),

ocountry VARCHAR2(3),

odoc_number VARCHAR2(10),

os_ord_item VARCHAR2(6),

zbom_type VARCHAR2(1),

zmodel VARCHAR2(8),

zrel_no VARCHAR2(20),

zserial VARCHAR2(35),

omaterial VARCHAR2(18),

owbs_elemt VARCHAR2(24),

ocon_type VARCHAR2(4),

oamount NUMBER(15,2),

ocurrency VARCHAR2(5),

oregion VARCHAR2(3),

zregdscr VARCHAR2(20))

Thank you..

[christine_evans]

Ok. Read the error message and it will tell you exactly what is wrong. One of your field names is listed twice and this is not allowed.

By the way, when posting code it will make it much more readable if you include in within code tags.

[Former Member]

Christine

Unless I am blind, I don't see the duplicate field name. ANd this is what I've been trying to fix yesterday via different means. I've also renamed SEQ to SEQNUM in the insert statement and changed the external DB accordingly and it still gives me this error. I am lost...

[christine_evans]

Look more carefully. Or do what I did and copy the fieldnames into a spreadsheet and sort it. OAMOUNT is specified twice in the fieldlist.

[Former Member]

Christine.

I don't know what to say... I was blind....and tired of looking at the code I guess.. Too many bad copy and paste to try things.

Now the message I get is that it does not find the table name.

I suspect it is the R3 database table. Do you know how (or a tcode) I can find the schema name for a table ? We have very limited database admin privileges .

Thanks for all your help.

[christine_evans]

If you were maintaining the remote table successfully when you were using a LOOP, as you say in your first post, then you must be accessing the remote table correctly. If you are filling your temp R/3 table successfully before you call this bit of code, then you must be accessing the local table correctly. Put them both together.....and it should work. What is the Oracle error code and message that you are getting?

I'm starting to think that perhaps you should go back to your original method of updating the remote table record by record in a LOOP, which you say was working. Even if you get this direct table-to-table update to work, there is no guarantee (there is never any guarantee) that it will be much quicker, especially over a remote link. I've not done this sort of thing in SAP before, but I've previously used DBLINK to link two Oracle databases and just the existence of the remote connection seemed sometimes to affect access paths for the worse.

[Former Member]

Hello Christine

Thank you for your help. You have been very kind in trying to help me solve the problem. Indeed I can not spend more time on this. I will revert back to the loop insert. Very slow but no choice.

Have a good day.

YB