Issue with 'Upload an XML/CSV Transmission'

[sgshah]

Hi,

I am having issues updating a custom table using 'Upload an XML/CSV Transmission' in OTM.

The initial load of the table is fine; however, following update to the table are erroring out.

Table Definition:
----------
CREATETABLE CUSTOM_TABLE (
DC VARCHAR2(100)NOTNULL,
STATE VARCHAR2(100)NOTNULL,
LAST_PICK_RELEASE VARCHAR2(100)NOTNULL,
SHIPMENT_DATE VARCHAR2(100)NOTNULL,
DELIVERY_DATE VARCHAR2(100)NOTNULL,
DOMAIN_NAME VARCHAR2(50)NOTNULL,
INSERT_USER VARCHAR2(128)NOTNULL,
INSERT_DATE DATENOTNULL,
UPDATE_USER VARCHAR2(128),
UPDATE_DATE DATE,
PRIMARYKEY( DC, STATE )
----------

I have provided grants to APP_USER and EXT_USER. As well as created similar Triggers found in other OTM tables (to update UPDATE_DATE & UPDATE_USER etc.)

The initial load CSV is something like this (Sample):
----------
GLOGOWNER.CUSTOM_TABLE
DC,STATE,LAST_PICK_RELEASE,SHIPMENT_DATE,DELIVERY_ DATE,DOMAIN_NAME
ONT,AK,Monday,Wednesday,Friday,TESTDOMAIN
ONT,HI,Monday,Wednesday,Friday,TESTDOMAIN
ONT,DE,Monday,Wednesday,Tuesday,TESTDOMAIN
--------

And the update CSV is something like this:
--------
GLOGOWNER.CUSTOM_TABLE
DC,STATE,LAST_PICK_RELEASE,SHIPMENT_DATE,DELIVERY_ DATE,DOMAIN_NAME
ONT,AK,Monday,Wednesday,Monday,TESTDOMAIN
ONT,HI,Monday,Wednesday,Monday,TESTDOMAIN
ONT,DE,Monday,Wednesday,Monday,TESTDOMAIN
--------

When I try to upload the update into OTM via CSV (using IU - Insert Update), all records fail with the following error: ORA-00936: missing expression. The Key to the table is STATE-DC, and thus I would assume that any changes to the non-key fields would be updated, however, no changes to the table are seen, as they all error out.

Thanks,
Samir

[PIYUSH]

Re: Issue with 'Upload an XML/CSV Transmission'

Hi ,

I have created a custom Table with relevant constraints and triggers(insert user and date) .

I have also give select/update/insert/delete privileges to app_user and ext_user .

Yet I am not able to select the table from front end nor am I able to upload data .

While uploading using "Upload XML/CSV transmission"
it shows error "ORA-00942: table or view does not exist"

Can anybody suggest as to what is to be done to upload data to custom table using "Upload XML/CSV transmission" feature ??

Regards,
Piyush

[josephliang]

Re: Issue with 'Upload an XML/CSV Transmission'

Try to create Public Synonym for your custom tables.

[PIYUSH]

Re: Issue with 'Upload an XML/CSV Transmission'

Thanks Joseph . Issue resolved !!!

Piyush

[tabish.nafay]

Re: Issue with 'Upload an XML/CSV Transmission'

Dear sgshah and Piyush,

I am a new BEE in OTM, This thread is really helpfull, but for same thread i have a query, can u guys please tell me in detail for this query.

I have created a custom table say "container" , have moved some data in it and make a report, then register this report in OTM, but when I run report, I have received the Error that "table or view does not exist". I have given Select rights to APP_USER, APP_USER_SELECT, EXT_USER and grantable for select for REPORTOWNER user. But the error is same. please help me in this, how can i use this table from front end. How to create Public Synonym for my custom tables and how the issue is resolved.

Thanks in Advanced.

MTN

[Pinkesh Jain]

Re: Issue with 'Upload an XML/CSV Transmission'

Hi sgshah,
have you got the solution for ORA-00936: missing expression , I am also facing same problem while uploading CSV in iu mode in custom table.

[PIYUSH]

Re: Issue with 'Upload an XML/CSV Transmission'

grant select, insert, update, delete on CUSTOM_TABLE to app_user ;
grant select, insert, update, delete on CUSTOM_TABLE to ext_user ;
grant select, insert, update, delete on CUSTOM_TABLE to reportowner ;

CREATE PUBLIC SYNONYM CUSTOM_TABLE FOR CUSTOM_TABLE;

* Give the grant priviledges to Custom Table according to requirment .

[parirani]

Re: Issue with 'Upload an XML/CSV Transmission'

[QUOTE=PIYUSH;15360]

Any update on above issue. I also facing the same issue
ORA-00936: missing expression , I am also facing same problem while uploading CSV in iu mode in custom table.

Please reply asap .Thanks in advance.

QUOTE]

[Rajesh Venati]

Re: Issue with 'Upload an XML/CSV Transmission'

Hi Parirani,

Please add the Primary Key to any unique data columns and try, it will work. I had same issue, upon adding PK it worked.

Thanks!
Rajesh Venati