[Oracle Connector P9] Call sequence / trigger

[Oracle Connector P9] Call sequence / trigger

  
Forge Component
(3)
Published on 2015-03-13 by Grazina
3 votes
Published on 2015-03-13 by Grazina
Hi

Is a way to call a sequence or a trigger from oracle database using this extension?
If you know other way to to that please contact me.

Thanks

Matheos Polydorou
Hi,

I am a bit confused why you want to explicitly "call" a trigger?
or even a sequence.
Those are not supposed to call explicitly?


Hi

I am using a sequence for set auto number when i am create a record in a table.
So, when i call the create from the Outsystems, i want to get the sequence to increase the autonumber field.

Thanks

Matheos
True,

but if it works when you insert a record with toad for example.
it should work when using an advance query or even the generated create-action.



It does not working, because Outsystems don't know what sequence to use to get the nextval.

When you insert a table from oracle using integration studio and mark the serial_number as an autonumber, that means for outystems do not send value for the field serial_number.

The generated create-action does not working for sure. I did not try the advance query 
erm, can you explain?

do you use outsystems-entities?
or do you use an external databse and include them in your project with integration studio?

the first it should be created by outsystems platform itself.
the latter, well, you still have to link the sequence to the insert-trigger yourself.
no need to use outsystems for that.

then simply in integration-studio set the identifier to the correct column...



Hi,

There is a way to call a sequence in an advanced query?
The current table has a trigger that increment the id when his value is null, but reaches with "0".  The solution that i was thinking is to get the next value with an advanced query, set it and send it back.

Best Regards,

Marco Rodrigues
The solution that we found to solved this issue is using an advanced query with the following struture:

/* %LogicalDatabase%=GetLogicalDatabase({NAME_OF_ONE_ENTITY}) */
select SEQUENCE_NAME.nextval from DUAL

Best Regards,

Marco Rodrigues

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?



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"

Carlos A. Aponte Roa wrote:

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?



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

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.