topic Re: unable to insert rows into ORACLE database using ABAP code in Application Development and Automation Discussions

unable to insert rows into ORACLE database using ABAP code

Former Member — Mon, 19 Nov 2007 05:29:29 GMT

Hai,

I am facing problem while creating a table in Oracle database with 15 attributes in a table. To create a table I am using the classes:

cl_sql_connection -

> to create the connection

cl_sql_statement -

> to execute the query

This I used by reffering the SAP program ADBC_DEMO. Without any trouble I am able to create a Table with 6 attributes by following the same procedure in ABCD_DEMO program but the same is not working for the table with 15 attributes .

Please help me.

Regards,

Swetha

Re: unable to insert rows into ORACLE database using ABAP code

Former Member — Mon, 19 Nov 2007 14:20:23 GMT

Hi Swetha,

maybe you have an error in your sql command, can you send your coding?

Regards

Andreas

Re: unable to insert rows into ORACLE database using ABAP code

Former Member — Tue, 20 Nov 2007 06:37:33 GMT

Hai,

here is my code.

DATA: V_con_name TYPE dbcon-con_name,

con_ref TYPE REF TO cl_sql_connection,

sqlerr_ref TYPE REF TO cx_sql_exception,

c_tabname TYPE string VALUE `TO_DETAILS`,

c_coldefs TYPE string.

DATA: IT_ORA LIKE ZVOP_X_ORA_UPDATE OCCURS 0 WITH HEADER LINE.

V_CON_NAME = 'TVL-DSS-01'.

concatenate '(LGNUM CHAR(3) primary key,'

'TANUM VARCHAR2(10),'

'FLAG CHAR(1),'

'BDATU timestamp(3),'

'TAPOS VARCHAR2(4),'

'MATNR CHAR(18))'

'VLQNR VARCHAR2(10),'

'VLPLA CHAR(10),'

'VLBER CHAR(3),'

'NLPLA CHAR(10),'

'NLBER CHAR(3),'

'VDATE DATE(3) ,'

'BNAME CHAR(12),'

'VTIME DATE(4),'

'PROTYPEFLAG CHAR(1),'

'PROCOMFLAG CHAR(1))'

into c_coldefs separated by space .

TRY.

PERFORM: CONNECT USING V_CON_NAME CON_REF,

CREATE_TABLE USING con_ref c_tabname c_coldefs.

CATCH cx_sql_exception INTO sqlerr_ref.

TB_ERROR-MESSAGE = SQLERR_REF->SQL_MESSAGE.

APPEND TB_ERROR. CLEAR TB_ERROR.

ENDTRY.

form create

form CONNECT using p_con_name TYPE dbcon-con_name

p_con_ref TYPE REF TO cl_sql_connection

RAISING cx_sql_exception.

p_con_ref = cl_sql_connection=>get_connection( p_con_name ).

endform. " CONNECT

form create

form CREATE_TABLE USING p_con_ref TYPE REF TO cl_sql_connection

p_tabname TYPE string

p_coldefs TYPE string

RAISING cx_sql_exception.

DATA:

l_sqlerr_ref TYPE REF TO cx_sql_exception,

l_stmt TYPE string,

l_stmt_ref TYPE REF TO cl_sql_statement.

create a statement object

l_stmt_ref = p_con_ref->create_statement( ).

create the statement string

CONCATENATE

'create table' p_tabname p_coldefs

INTO l_stmt SEPARATED BY space.

execute the DDL command; catch the exception in order to handle the
case if the table already exists

TRY.

l_stmt_ref->execute_ddl( l_stmt ).

CATCH cx_sql_exception INTO l_sqlerr_ref.

IF l_sqlerr_ref->dbobject_exists = 'X'

OR l_sqlerr_ref->internal_error = 1024.

table already exists => drop it and try it again

PERFORM:

drop_table USING p_con_ref p_tabname,

create_table USING p_con_ref p_tabname p_coldefs.

ELSE.

RAISE EXCEPTION l_sqlerr_ref.

ENDIF.

ENDTRY.

endform. " CREATE_TABLE

please do help me

Regards,

Swetha

Re: unable to insert rows into ORACLE database using ABAP code

Former Member — Tue, 20 Nov 2007 08:19:09 GMT

You should replace the last bracket with a comma in this row:

'MATNR CHAR(18))'

to:

'MATNR CHAR(18),'

this should work. Please reward if helpful.

Andreas

Re: unable to insert rows into ORACLE database using ABAP code

Former Member — Tue, 20 Nov 2007 10:45:38 GMT

Hai,

I have done it.It was mistake at time of pasting the code . The actual code was

concatenate '(LGNUM CHAR(3) primary key,'

'TANUM VARCHAR2(10),'

'FLAG CHAR(1),'

'BDATU timestamp(3),'

'TAPOS VARCHAR2(4),'

'MATNR CHAR(18),'

'VLQNR VARCHAR2(10),'

'VLPLA CHAR(10),'

'VLBER CHAR(3),'

'NLPLA CHAR(10),'

'NLBER CHAR(3),'

'VDATE DATE(3) ,'

'BNAME CHAR(12),'

'VTIME DATE(4),'

'PROTYPEFLAG CHAR(1),'

'PROCOMFLAG CHAR(1))'

into c_coldefs separated by space

However the problem is c_coldefs is not taking the whole string while creating.

Regards,

Swetha

Re: unable to insert rows into ORACLE database using ABAP code

Former Member — Tue, 20 Nov 2007 12:00:56 GMT

Hmmm, it works perfectly on my computer. There is one space after VDATE dispensable, but I do not think this is the problem. Sorry.