Manually calling sequences, adhocSQL

Posts   
 
    
trevorg
User
Posts: 104
Joined: 15-Nov-2007
# Posted on: 06-Mar-2008 23:21:31   

I am trying to figure out the best way to get the next sequence value from an Oracle database (because of a strange database design, I must call it via code rather than configuring in the designer). I searched all the forums and it seems the recommended way is by defining a stored proc and calling via ActionProcedures, ie:

CREATE OR REPLACE PROCEDURE TLMINT.SEQ_EVENT_ID_NEXTVAL( p_nextval OUT NUMBER) IS BEGIN select EVENT_ID_SEQ.NEXTVAL into p_nextval from dual; END SEQ_EVENT_ID_NEXTVAL;

Then.... Dim NextVal As Decimal ActionProcedures.SEQ_EVENT_ID_NEXTVAL(NextVal)

So this works ok, but, do I have to define a stored procedure for every sequence? Or is there a way that a person can call any arbitrary SQL, that is basically just passed straight through to the database, something like: Dim NextVal As Decimal NextVal = SomeLLBObject.ExecuteSQL("select {0}.NextVal FROM DUAL","EVENT_ID_SEQ")

Or, even, some other adhoc SQL: SomeLLBObject.ExecuteSQL("delete from MY_TABLE")

Obviously this is poor application design, but wondering if it is possible to do this.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 07-Mar-2008 10:33:31