Marco
When you said NAME_OF_ENTITY, what do you mean?, database connection name or something like that?
Also, I did it and got error: "Error in advanced query ExecuteSequence: The multi-part identifier "MY_SEQUENCE.nextval" could not be bound"
Also, the line that you define in comments is needed?
Carlos A. Aponte Roa wrote:
Hi Carlos, by "NAME_OF_ENTITY" i mean name of some object (table,view, etc) that exists in that database. This command, that seams to be a comment, allows to set in runtime the catalog that will be needed in the advanced query. This is mainly important when you have multiple catalogs in database.
The error that you are getting is normal. I also got this error if i test my query. This will only works in runtime.
I suggest to you to create a simple screen where you show in a label or feedback message the result of this query to validate the behaviour.
Notice the "name_of_one_entity" cannot be set with an input parameter (maybe because is in a "comment").
In my case i have the the following advanced query :
/* %LogicalDatabase%=GetLogicalDatabase({CAMBIOS}) */ select @Schema.@Sequence.NEXTVAL from DUAL
Let me explain my situation. We have an external database in Oracle and in there we have a set of sequences and triggers. We need from Outsystems to execute a sequence
select sequence_name.nextval from dual
using an advanced query and then get the number and use it to create a record in another table.
Problem is that during test in advanced query I got next error: "Error in advanced query ExecuteSequence: The "multi-part identifier.nextval" could not be bound"
Yes. I figure it out with some testing. Thanks a lot for explanation and time.
I used in advance query:
/*%LogicalDatabase%=GetLogicalDatabase({ONE_TABLE_IN_DATABASE})*/
select SCHEMA.SEQUENCE_NAME.nextval from DUAL
But is important to note that if I used test button inside advanced query it fails always but in run time works perfectly.
Hi all - despite this post is old - I like to post my solution to the topic
"select <YourSequence>.nextval from external OracleDB via OutSystems"
Since OutSystems only integrates Views or Tables in its Database-Extensions (connector for external databases) it is not possible to integrate the sequence itself in the DB-Extension.
If you choose an SQL-Widget and try "select <YourSequence>.nextval from dual" in the widget
it wont work - because OutSystems selects DUAL from its own platform Database. Here the sequence does not exist.
So what is the solution:
1. Create the sequence on your external Oracle DB and set the needed grants
2. Create a function that returns <YourSequence>.nextval on your external Oracle DB
3. Create a view that does
create or replace view <YourView>
as
select <YourFunctionname>() as <yourAlias> from dual;
4.Integrate <YourView> into your OutSystems DB-Extension via IntegrationStudio.
Ready :)
regards
Christine