Talend community forum / [resolved] Schema & tOracleMap

nSmolyniec · 2009-03-23 14:41:00

Hi,

I'm trying to reprodruce an PL/SQL interface with Talend 3.0.3.
I created a new connection with tOracleConnection, with a specified schema.
When I configured the tELTOracleMap component for this connection, I encountered one problem :
"Exception in component tELTOracleOutput_1
java.sql.SQLException: ORA-00942: table or view does not exist".
The problem does not come from the table name, but from the schema.
When I unselected "use an existing connection" to configure it manually, I had the surprise not to see the possibility of using a schema.
Could I have done something wrong ? Is there another way to proceed ? Or have you got some tips ?
I know I can use an ETL with tMap, but it won't be as quick as the ELT version with thousand and thousand rows...

I know I don't have the last version, but when I read the changelog, it seems there is no modification about this stuff.

Thank you in advance.

Regards,

Nicolas

Last edited by nSmolyniec (2009-06-05 14:23:09)

shong · 2009-03-23 15:28:40

Hello Nicolas

Can you upload some screenshots of your job?

Best regards

shong

nSmolyniec · 2009-03-23 19:22:13

Hi,

Thanks for your answer.
I made a simpler interface. Here is what I have :

-------------------
Inserting with :
INSERT INTO ESSAI_TMPR_DUPL_SUP_METI2 (SELECT t.ID , t.FOU_CDFO , t.FOU_NM , t.FOU_NVOI , t.FOU_BTQ , t.FOU_TVOI , t.FOU_LVOI , t.FOU_CVOI , t.FOU_CPOS2 , t.FOU_DIST , t.FOU_CDPAYS , t.FOU_TLPH , t.FOU_FX , t.FOU_EMAI , t.FOU_CNUF , t.FOU_CDTYPF , t.FOU_NOADHER , t.FOU_CDBLFR , t.FOU_TTCOM1 , t.FOU_TTCOM2 , t.FOU_TTCOM3 , t.FOU_REPEDI FROM ESSAI_TMPC_MAN_SUP_METI t)

Exception in component tELTOracleOutput_1
java.sql.SQLException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3454)
at mosmart_interfaces.test_0_1.test.tELTOracleMap_1Process(test.java:360)
at mosmart_interfaces.test_0_1.test.tCreateTable_1Process(test.java:266)
at mosmart_interfaces.test_0_1.test.tOracleConnection_1Process(test.java:536)
at mosmart_interfaces.test_0_1.test.runJobInTOS(test.java:658)
at mosmart_interfaces.test_0_1.test.main(test.java:572)
-----------------

Best regards,

Nicolas

Uploaded Images

Last edited by nSmolyniec (2009-03-23 19:39:24)

shong · 2009-03-24 07:38:44

Hello nSmolyniec
From your pictures, I can't see any error on your job. I test these components on my local machine with TOS3.0.0/TOS3.0.3 and they all works fine.
1) About question' No schem in manual mode?' metioned on your picture, open the ETL oracle Map editor and define you schema.
2) Try to change 'OnComponentOk' to 'OnsubJobOK'.

Best regards

shong

nSmolyniec · 2009-03-24 10:13:36

Hi,

I tryied to change 'OnComponentOk' to 'OnsubJobOK', but i got the same.
Then, about opening the map editor to define my schema... I meaned the "Oracle schema". And I cannot see any place to define it. Furthermore, if I define its name in a new output name like "schema.mytable", the name becomes invalid.
However, manual mode was just a try to clearly define my Oracle schema (however, it seems to be impossible to do so), because, as you can see, there it is defined in my connection.
I have no problem using ETL components (tMap) and Oracle input or outputs components... The problem is only with my interface ELT version.

Any other idea ?

Thank you,

Nicolas

shong · 2009-03-24 10:26:04

Hello Nicolas

I meaned the "Oracle schema". And I cannot see any place to define it

see my screenshot

if I define its name in a new output name like "schema.mytable", the name becomes invalid.

The table name can't contain period characters.

Best regards

shong

Uploaded Images

nSmolyniec · 2009-03-24 19:03:42

Hi,

I think we do not talk about the same stuff.
What I'm doing it just creating a simple ELT job : import of table data into another table, as you can see here :

I agree that it works in normal use.
However, in my Oracle DB, i've got tables in different "Oracle Schemas".

> First, with the ELT stuff, I suppose I'm not able to import the table data of one schema/user into one table of another schema (with only one tOracleMap component)... Tell me if I'm wrong.

> Anyway, that is not the problem I encounter.
If I choose to write in the same schema as the user I'm connected with (like in the screenshot below), there is no problem.
However, if I choose to write in another schema, I can't and I get this message : "Exception in component tELTOracleOutput_1
java.sql.SQLException: ORA-00942: table or view does not exist", which is not surprising when you see the "Oracle schema" is not specified in the query :
"INSERT INTO ESSAI_TMPR_DUPL_SUP_METI2......."

(if you choose to configure your "tELTOracleMap" manually (without "using an existing connection"), you can see you cannot choose an Oracle schema either.

Hope you can understand my problem now
Thank you,

Nicolas

Uploaded Images

shong · 2009-03-25 07:31:42

Hello Nicolas
I understand you now, you are referring to the Oracle schema, not the talend schema which is to define fields.
Yes, you are right. There is not a text field for schema defined on tELTOracleMap. It is only work for the default schema.

Best regards

shong

nSmolyniec · 2009-03-25 09:38:23

Ok,
So you mean I'm obliged to create a connection for each Oracle schema ?
No other way ?
Will the possibility appear in a next feature ?

Thank you,

Nicolas

shong · 2009-03-25 10:57:32

Hello Nicolas

Will the possibility appear in a next feature ?

Yes, it should be reported as a feature on our bugtracker, would you like to do it?
Thanks for your support!

Best regards

shong

nSmolyniec · 2009-03-25 12:06:48

Hi Shong,

Ok, I'll do it .

Bye,

Nico

nSmolyniec · 2009-03-25 14:39:44

shong wrote:
Hello Nicolas
I understand you now, you are referring to the Oracle schema, not the talend schema which is to define fields.
Yes, you are right. There is not a text field for schema defined on tELTOracleMap. It is only work for the default schema.

Best regards

shong

Hi again,

Before reporting the feature, I read your answer one more time, and I want to say this is a more general problem :
> In manual configuration mode, you cannot choose the schema (you confirmed it).
> If you "use an existing connection" (in the basic settings), the component just ignore your schema specified into the connection, it will run the queries with the default Oracle Schema.
I think there is a link between each other... there may be no variable for the Oracle schema in the code of the component.

Regards,

Nicolas

Last edited by nSmolyniec (2009-03-25 14:49:35)

shong · 2009-03-25 15:00:35

Hello Nicolas

If you "use an existing connection" (in the basic settings), the component just ignore your schema specified into the connection, it will run the queries with the default Oracle Schema.

Before I ask you to report a feature, I have looked over the generate code and found this problem, and talked it to amaumont(one Talend developer who work on these components).

Best regards

shong

nSmolyniec · 2009-03-27 10:10:44

Hello,

As I can see, someone is already working on it, that's nice
Thanks for your help.

Regards,

Nicolas

Announcement

Unanswered posts

#1 2009-03-23 14:41:00

[resolved] Schema & tOracleMap

#2 2009-03-23 15:28:40

Re: [resolved] Schema & tOracleMap

#3 2009-03-23 19:22:13

Re: [resolved] Schema & tOracleMap

#4 2009-03-24 07:38:44

Re: [resolved] Schema & tOracleMap

#5 2009-03-24 10:13:36

Re: [resolved] Schema & tOracleMap

#6 2009-03-24 10:26:04

Re: [resolved] Schema & tOracleMap

#7 2009-03-24 19:03:42

Re: [resolved] Schema & tOracleMap

#8 2009-03-25 07:31:42

Re: [resolved] Schema & tOracleMap

#9 2009-03-25 09:38:23

Re: [resolved] Schema & tOracleMap

#10 2009-03-25 10:57:32

Re: [resolved] Schema & tOracleMap

#11 2009-03-25 12:06:48

Re: [resolved] Schema & tOracleMap

#12 2009-03-25 14:39:44

Re: [resolved] Schema & tOracleMap

shong wrote:

#13 2009-03-25 15:00:35

Re: [resolved] Schema & tOracleMap

#14 2009-03-27 10:10:44

Re: [resolved] Schema & tOracleMap

Board footer