Error message: 'ORA-00926:missing VALUES keyword

[Former Member]

Hi Friends,

I am trying to update a database table in SAPR3.ORACLE database from CRM via an ABAP program.

I did required BDCON setup. But I get the error message : ORA-00926:missing VALUES keyword.

Looking forward your help to solve this problem. Anyone came across such issue?

below is my code and BDCON setup

DB Connection DB_CONNECT

DBMS ORA

User Name test_db

DB password

Conn. info sapde9db00a

Connection Limit 10

Optimum Conns 5

TABLES: BUT000.

DATA: EXC_REF TYPE REF TO CX_SY_NATIVE_SQL_ERROR,

ERROR_TEXT TYPE STRING.

DATA W_PARTNER TYPE BUT000-PARTNER.

TYPES: BEGIN OF TYP_PARTNER,

MANDT TYPE SY-MANDT,

PARTNER_NUMBER TYPE BU_PARTNER,

END OF TYP_PARTNER.

DATA: ZZ_TEST TYPE STANDARD TABLE OF TYP_PARTNER WITH HEADER LINE.

DATA: DBTYPE TYPE DBCON_DBMS,

DBCUR TYPE CURSOR,

T_BUT000 LIKE BUT000 OCCURS 0 WITH HEADER LINE.

SELECT * INTO CORRESPONDING FIELDS OF TABLE T_BUT000 FROM BUT000 ORDER BY PRIMARY KEY.

LOOP AT T_BUT000.

ZZ_TEST-MANDT = SY-MANDT.

ZZ_TEST-PARTNER_NUMBER = T_BUT000-PARTNER.

APPEND ZZ_TEST.

ENDLOOP.

TRY.

EXEC SQL.

SET CONNECTION 'DB_CONNECT'

ENDEXEC.

EXEC SQL.

CONNECT TO 'DB_CONNECT'

ENDEXEC.

• LOOP AT ZZ_TEST.

EXEC SQL.

INSERT INTO SAPR3."ZZTEST_DB":

(mandt, partner_number) VALUES('220', '0000000253');

ENDEXEC.

IF SY-SUBRC <> 0.

ENDIF.

• ENDLOOP.

CATCH CX_SY_NATIVE_SQL_ERROR INTO EXC_REF.

ERROR_TEXT = EXC_REF->GET_TEXT( ).

MESSAGE ERROR_TEXT TYPE 'I'.

ENDTRY.

EXEC SQL.

SET CONNECTION DEFAULT

ENDEXEC.

Thanks in advance for you help,

regards

DJ

[Former Member]

Hi,

INSERT INTO SAPR3."ZZTEST_DB":

(mandt, partner_number) VALUES('220', '0000000253');

Give a space between VALUES and parenthesis.

Best regards,

[Former Member]

Hi,

Sorry, I have tried but it gives the same error message.

[Former Member]

INSERT INTO SAPR3.ZZTEST_DB (mandt, partner_number) VALUES ('220', '0000000253');

Try it as it is

[Former Member]

Hi,

If I just use your code as it is, I get the following error.

An SQL error has occurred: ORA-00911: invalid character

but, when I modify your statement like as :

INSERT INTO SAPR3.ZZTEST_DB: (mandt, partner_number) VALUES ('220', '0000000253');

I get the error like : An SQL error has occurred: ORA-00926: missing VALUES keyword

in the database the description of the table is SAPR3."ZZTEST_DB";

SQL> desc SAPR3."ZZTEST_DB";

MANDT NOT NULL VARCHAR2(3)

PARTNER_NUMBER NOT NULL VARCHAR2(10)

The Oracle version 10.2.0.2.0

please help me .

DJ

Edited by: Mourougane DJEARAMANE on Mar 31, 2008 12:58 PM

[Former Member]

rambarat saroj showed what do do;

Leave away the : from SAPR3.ZZTEST_DB:

[Former Member]

HI,

If I remove the : from my statement,

I get the following error:

An SQL error has occurred: ORA-00911: invalid character

regards

[Former Member]

ok, what is your current statement?

It seems you have to remove still more ...

[Former Member]

my current statement is

EXEC SQL.

INSERT INTO SAPR3.ZZTEST_DB (mandt, partner_number) VALUES ('220', '0000000253');

ENDEXEC.

[Former Member]

Hi,

See my complete code to update values using internal table

TABLES: BUT000.

DATA: EXC_REF TYPE REF TO CX_SY_NATIVE_SQL_ERROR,

ERROR_TEXT TYPE STRING.

DATA W_PARTNER TYPE BUT000-PARTNER.

TYPES: BEGIN OF TYP_PARTNER,

MANDT TYPE SY-MANDT,

PARTNER_NUMBER TYPE BU_PARTNER,

END OF TYP_PARTNER.

DATA: ZZ_TEST TYPE STANDARD TABLE OF TYP_PARTNER WITH HEADER LINE.

DATA: DBTYPE TYPE DBCON_DBMS,

DBCUR TYPE CURSOR,

T_BUT000 LIKE BUT000 OCCURS 0 WITH HEADER LINE.

SELECT * INTO CORRESPONDING FIELDS OF TABLE T_BUT000 FROM BUT000 ORDER BY PRIMARY KEY.

LOOP AT T_BUT000.

ZZ_TEST-MANDT = SY-MANDT.

ZZ_TEST-PARTNER_NUMBER = T_BUT000-PARTNER.

APPEND ZZ_TEST.

ENDLOOP.

TRY.

EXEC SQL.

SET CONNECTION 'DB_CONNECT'

ENDEXEC.

EXEC SQL.

CONNECT TO 'DB_CONNECT'

ENDEXEC.

LOOP AT ZZ_TEST.

EXEC SQL.

INSERT INTO SAPR3.ZZTEST_DB (mandt, partner_number) VALUES (ZZ_TEST-MANDT, ZZ_TEST-PARTNER_NUMBER);

ENDEXEC.

IF SY-SUBRC <> 0.

ENDIF.

ENDLOOP.

CATCH CX_SY_NATIVE_SQL_ERROR INTO EXC_REF.

ERROR_TEXT = EXC_REF->GET_TEXT( ).

MESSAGE ERROR_TEXT TYPE 'I'.

ENDTRY.

EXEC SQL.

SET CONNECTION DEFAULT

ENDEXEC.

Edited by: Mourougane DJEARAMANE on Mar 31, 2008 2:11 PM

[Former Member]

and without the ; ?

[Former Member]

If I remove ;

then I get new error as follows:

You tried to work with the name of a table or view

that does not exist in the database

[Former Member]

Hello,

that's a quite different problem now. Something seems to be wrong with your database connection,and you seem to be looking in the wrong place for table ZZTEST_DB. Not sure about it, but I think you have to use 'CONNECT TO' first, followed by 'SET CONNECTION'.

Might be worth a try;

otherwise I can't help more, I'm afraid.

[Former Member]

Hi Joe,

I have changed the configuration as you said, but I get the error as follows:

An SQL error has occurred: ORA-00911: invalid character

see my new latest code:

TRY.

EXEC SQL.

CONNECT TO 'DB_CONNECT'

ENDEXEC.

EXEC SQL.

SET CONNECTION 'DB_CONNECT'

ENDEXEC.

LOOP AT ZZ_TEST.

EXEC SQL.

INSERT INTO SAPR3.ZZTEST_DB (mandt, partner_number) VALUES (ZZ_TEST-MANDT, ZZ_TEST-PARTNER_NUMBER);

ENDEXEC.

IF SY-SUBRC <> 0.

ENDIF.

ENDLOOP.

CATCH CX_SY_NATIVE_SQL_ERROR INTO EXC_REF.

ERROR_TEXT = EXC_REF->GET_TEXT( ).

MESSAGE ERROR_TEXT TYPE 'I'.

ENDTRY.

[Former Member]

You have this wrong ; once again ...

[Former Member]

If I remove ;

I get error as: " You tried to work with the name of a table or view that does not exist in the database"

[Former Member]

ok, so order does not matter in this case; sorry.

One thing that should be clarified:

If I understand correctly, there is a table SAPR3.ZZTEST_DB in your remote database, i.e. table name is ZZTEST_DB and schema name (aka user name) is SAPR3. Correct?

But in you definition of DB_CONNECT you have 'User Name test_db'. So shouldn't it be 'SAPR3' instead of 'testdb'? Or does user testdb have authorisation to read user SAPR3's tables?

[Former Member]

Hi Joe,

the Schema is SAPR3.

And the user test_db has all rights on this table.

[Former Member]

Hello,

Any body can help me on this request?

[Former Member]

Hi

Change it to this....and let us know if it works

>

> TRY.

>

> EXEC SQL.

>

> CONNECT TO 'DB_CONNECT'

>

> ENDEXEC.

>

>

> EXEC SQL.

>

> SET CONNECTION 'DB_CONNECT'

>

> ENDEXEC.

>

>

> LOOP AT ZZ_TEST.

> EXEC SQL.

>

> INSERT INTO SAPR3.ZZTEST_DB (mandt, partner_number) VALUES (:ZZ_TEST-MANDT, :ZZ_TEST-PARTNER_NUMBER)

>

> ENDEXEC.

>

> IF SY-SUBRC <> 0.

>

> ENDIF.

>

> ENDLOOP.

>

> CATCH CX_SY_NATIVE_SQL_ERROR INTO EXC_REF.

>

> ERROR_TEXT = EXC_REF->GET_TEXT( ).

>

> MESSAGE ERROR_TEXT TYPE 'I'.

>

> ENDTRY.

[Former Member]

Hi Ravi,

If i use your statement, i get the error message

" You tried to work with the name of a table

that does not exist in the database "

I hope the connexion is not successful, what is your opinion?

could you please check if the DBCON entry is correct for ORACLE

DB Connection : DB_CONNECT

DBMS : ORA

User Name : test_etl

DB password /

Conn. info : sapde9db00a

Connection Limit : 10

Optimum Conns : 5