Hi ABAPxxxGoddess!
Whoa, what a name!
Your best bet in this case would be to sit with an OracleXXXGod and work it
out. It's what I did - here's a possible solution, and some of the lessons
I learnt:
For starters - if you insist on doing this from an internal table to the
remote DB, this not the solution for you. I couldn't manage to get that
working. *Let me know if you do!*
This solution is for array-inserting into a remote DB from an SAP table.
You'll have to somehow create a unique key on the SAP-table, even if the
Oracle table has no key. This unique key could consist of fields not found
on the remote DB.
Function Module CL_SQL_CONNECTION is simply a wrapper for the old EXEC SQL
statement, which allows you to call Oracle (or other native SQL) directly
from ABAP. But, it turns out, this is a plugin that Oracle provides, and it
is up to SAP to define what's allowed. And SAP doesn't allow everything
that Oracle allows. In particular, you can't do PL/SQL, or at least, I
haven't been able to. Nor can you use internal tables as parameters, like I
tried for array inserts (if you manage it, please give me a shout);
Ground rules, to save you time.
Rule 0: Be patient. It's hard. Don't give up. It's possible.
Rule 1: your ABAP syntax check doesn't work within EXEC SQL, so test your
SQL in a separate Oracle session first. Really. You'll only know why I say
this if you *don't* do it. ^_^
Rule 2: ABAP sometimes throws errors for legal SQL, e.g. the end-of-command
semicolon (;). If you need to use it, leave a space before. And if you get
"invalid character" during SQL, make another plan (see Rule 3.
Rule 3: If you have to string two Oracle commands together, sometimes it's
best to have two EXEC SQL / ENDEXEC statements, to get around the
semicolon. This was with Oracle 10.x and SAP ECC6.
I'm going to have to assume that you (or someone who likes you) know enough
Oracle SQL to do a bulk insert. But if you don't, bounce it back, we'll see
what we can do.
Option 1: create a stored procedure on Oracle, and call that. Read the help
for EXEC SQL to find out more. You can even create it on the fly through
ABAP (see documentation for EXEC SQL). Though you might have problems with
passing internal tables as parameters. I didn't use this solution, though I
created a working prototype.
Option 2: Set up a DB-link from the SAP DB to the other DB. Test it
separately, so you're sure it works. I repeat, test separately. This is
hard enough without having to fight setup-problems. (This is usually done
by your local grumpy Oracle DBA. They respond well to kindness, and
respectful grovelling. Force, threats, desperate grovelling, promise of
punishment are treatments that are known to be counterproductive. Expect to
have to argue. Keep your temper at all times.)
Naming convention in the examples below:
Your SAP-table is called ZSAP_TABLE - this is a DB-table;
Your Oracle table is called ORACLE_TABLE;
Your DB-link is called ABAP_GODDESS_LINK, though you might struggle to get
your grumpy Oracle DBA to set up that name for your real link. ^_^
EXEC SQL.
insert into ORACLE_TABLE@ABAP_GODDESS_LINK
select * from ZSAP_TABLE
ENDEXEC.
The Oracle guys will tell you there should be a semicolon at the end.
Noooooo! There mustn't! Preciousssss. We hates it, we hates it! If you
put that semicolon there, you will pass all syntax checks, and then you will
spend hours and hours trying to get the SQL to work. Eventually, you will
give up in despair, and tell people that Mark from Scotland is an idiot.
No semicolon. No semicolon. No semicolon. (Surprisingly enough, it works
elsewhere, in other EXEC SQL).
Option 2 (dates and timestamps): SAP uses NUMC8 for saving dates on the DB
(I think), whereas Oracle uses a sexy date+time+millions-of-milliseconds
thing. So, if you have any dates in your table, read up on Oracle command
ALTER SESSION, and then do as follows:
Before your insert-statement:
EXEC SQL.
ALTER SESSION SET NLS_DATE_FORMAT 'YYYYMMDD'
ENDEXEC.
No semicolon. See rant above.
If you have timestamps on Oracle, define the SAP field as NUMC14, and fill
with DATE(8)+TIME(6). Then ALTER SESSION SET NLS_TIMESTAMP_FORMAT
'YYYYMMDDHH24MISS'
No semicolon. See rant above. And reference to rant above, above. You get
the idea. They're not wanted.
Obviously, your fields have to match in Oracle and in SAP. The sequence as
well as the data definition should match.
Option 2 (variants):
If you have fields in SAP that you don't want in Oracle, there's a
solution. Given below.
If you have blank dates in SAP, there's a solution. You can't pass
00000000-dates to Oracle, though you can pass null dates. But SAP doesn't
do null dates! Whaaaa!
1. Define an ABAP constant gc_blank_date type d value '00000000' - because
it's the right thing to do.
2. Read up on Oracle statement DECODE;
3. Change the EXEC SQL array insert as follows:
EXEC SQL.
insert into ORACLE_TABLE@ABAP_GODDESS_LINK
select
field1
field2
field3
decode(DATEFIELD4,:gc_blank_date,NULL,DATEFIELD4)
field5
from ZSAP_TABLE
ENDEXEC.
No semicolon.
Right. It's late, and we're going live tomorrow. If you actually read this
far, good luck. ^_^
Mark
Scotland
On Sat, Sep 25, 2010 at 6:05 PM, ABAPxxxGoddess via sap-dev <
sap-dev@groups.ittoolbox.com> wrote:
>
>
> Hi,
>
> I need to do performance tuning for an export program that transfer data
> from SAP to Oracle. It uses the class CL_SQL_CONNECTION to do this. In the
> insert data SQL command part, it does the insertion row by row which taking
> up so much time.
>
> My question is, is there a way to do bulk insert? I tried to make it into
> statement INSERT INTO oracle_table SELECT * FROM sap_internal_table, making
> it inserting the whole contents of the table one time, passing the table
> itself.
> But this gives an error message 'table not exist'. If I make the SAP
> internal table as a custom table, the problem would be saving of data into
> this custom table. Other data will be omitted because of the primary key
> concern.
>
> Please help me on how to do this. Thanks.
| __.____._ Copyright © 2010 Toolbox.com and message author.
Toolbox.com 4343 N. Scottsdale Road Suite 280, Scottsdale, AZ 85251
| |  _.____.__ |
Mark as helpful
