Hi, hope someone can help me....I've been wasting time on this for several days, so here's the thing:- I'm configuring a new OutSystems environment and migrating apps from another to this new (OS 10)
- So, of course, the code is exactly the same in both environments but they don't share the same db....
- In this new environment there are some sql functions that don't work (ex: Hextoraw(), Translate() ), if I test the queries in the advanced query they don't work, but if I remove the functions from the queries, it will work....
- If I test those queries in sqldeveloper (with the functions) it will work!
- Oracle versions and client are the same in both db and machines.....
Can someone have some idea of what can it be?
Can you share a bit more information, like the query and the error message? and perhaps the testdata.
is it an external Oracle database, or just the OutSystems Oracle database?
regards Hans
The query uses both an external Oracle database (via dblink) and the Outsystems oracle database...I can't share the query sorry....but I can share the error with the hextoraw function query:
ORA-01465: Invalid Hex Number
Hi João Coelho,
have you test the query with same data and input parameters which you have used in sql developer?
Regards,
Manish Jawla
Yes...and in Outsystems advanced query tool, and it works perfectly in one environment, but in the new one, it doesn't work...
are the database settings the same for the 2 databases, e.g. are they using the name NLS settings etc. And is the database connection the same? (you can see that in Service Center > Administration)
The NLS settings are the same in both database.
Using a dblink, called through internal sql in db, do I need to configure any database connection in Service Center??
Still didn't find a solution for this problem, anyone else with a clue?
can you show the query with renaming names? And could it be that the insert gives an error and the select doesn't?
you're talking about an internal databaselink. So OutSystems is connected to a database (Oracle? ) and that has a databaselink to another oracle database? And can you execute the query from the first database using the databaselink in sql developer?
It is a damn long query, sorry, but the exactly same query works fine in another environment.
The query basically is a select to a Oracle database that has a dblink to another oracle database.
It uses some oracle functions like translate, trim, hextoraw and like I said it works perfectly in another environment.
If I execute the same query from the first database, using the dblink, it works fine as well....
Could it be some missing or bad configuration within the Outsystems server?? (since this is a pretty new environment)
it could be that there is a bad configuration created in the Configuration Tool, or in the Database Section in Service Center > Administration.
After a new test, in which I raised the timeout to 60000 seconds....the query finally had a result on screen...but it took between 5 to 10 minutes to finish... (in other environment it takes about 10 seconds max)So, it isn't a problem with the functions....
you need to compare Executions Plans of this queries on old and new server.
May be on old database, DBA did some optimizations with queries, so they work fast.
Also, when you execute query via dblink, Plan could be created on main DB or remote, and it could be bad.