ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Getting the current sequence number from DUAL in Oracle

Post new topic  Reply to topic
 Getting the current sequence number from DUAL in Oracle « View previous topic :: View next topic » 
Author Message
MQDummy
PostPosted: Sun Jun 16, 2002 9:31 pm    Post subject: Getting the current sequence number from DUAL in Oracle Reply with quote

Novice

Joined: 11 Jun 2002
Posts: 23

Hi,

How do i write it in eSQL, so that i can retrieve the current sequence number, in the DUAL table in Oracle?

Thanks!!
Back to top
View user's profile Send private message
TorpedoSTu
PostPosted: Mon Jun 17, 2002 5:01 am    Post subject: Reply with quote

Acolyte

Joined: 14 Nov 2001
Posts: 73

Hiya

Three things to do ..

1. Ensure your running WMQI 2.1 CSD02 - otherwise not possible.
2. Create a stored procedure in Oracle to to the select on dual with definitions to match below ...
3. In Esql ...

DECLARE LV_OUTPUT_PARM INTEGER;
DECLARE LV_DUAL INTEGER;

CALL GetDualFromMyTable(LV_OUTPUT_PARM);
SET LV_DUAL = LV_OUTPUT_PARM;

-- This is the definition of the stored procedure to get unique sequence number for MYTABLE :
CREATE PROCEDURE GetDualFromMyTable (OUT newseq INTEGER) EXTERNAL NAME SEQ_MYTABLE;

That should do the trick

Regs

Stu
Back to top
View user's profile Send private message Send e-mail
smurthy
PostPosted: Mon Jun 17, 2002 9:35 am    Post subject: Select From DUAL Reply with quote

Newbie

Joined: 21 May 2001
Posts: 3
Location: New York

Use the following code:

Set OutputLocalEnvironment.ScratchPad[] = PASSTHRU('Select SEQ_ID.CurrVal From DUAL;');

Remember for this to work you have to create the sequence in SQL:
create sequence SEQ_ID;
Back to top
View user's profile Send private message
MQDummy
PostPosted: Mon Jun 17, 2002 5:04 pm    Post subject: Thanks! Reply with quote

Novice

Joined: 11 Jun 2002
Posts: 23

Thanks for your replies!
Back to top
View user's profile Send private message
MQDummy
PostPosted: Mon Jun 17, 2002 7:12 pm    Post subject: Reply with quote

Novice

Joined: 11 Jun 2002
Posts: 23

I got the following error after using the eSQL above :

Quote:
BIP2402E: (8, 1) : Syntax error : 'integer CALL'.

The token caused a syntax error.

Correct the syntax of your expression and redeploy the message flow.


Can anyone help???
Back to top
View user's profile Send private message
TorpedoSTu
PostPosted: Mon Jun 17, 2002 11:53 pm    Post subject: Reply with quote

Acolyte

Joined: 14 Nov 2001
Posts: 73

Looks like your missing a ';' off the end of the previous statement to me

Regs

Stu
Back to top
View user's profile Send private message Send e-mail
smurthy
PostPosted: Tue Jun 18, 2002 6:11 am    Post subject: Reply with quote

Newbie

Joined: 21 May 2001
Posts: 3
Location: New York

You do not need to use a stored procedure just to get the sequence number. It seems like alot of work to do something very simple.

Cheers,
Suntosh
Back to top
View user's profile Send private message
MQDummy
PostPosted: Tue Jun 18, 2002 6:13 am    Post subject: Reply with quote

Novice

Joined: 11 Jun 2002
Posts: 23

How do you make it work without using stored procedure? I've used ways and means and cant do it
Back to top
View user's profile Send private message
smurthy
PostPosted: Tue Jun 18, 2002 6:36 am    Post subject: ESQL For Compute Node to Select Sequence Number from DUAL Reply with quote

Newbie

Joined: 21 May 2001
Posts: 3
Location: New York

Here is my exact code:
On the Compute Node you are using, set The Adavanced tab to Message and Local Environment. This is a PASSTHRU statement do it should deploy properly. Also, remember SEQ_CorrelID must be created on the DB that you are trying to retreive the sequence number.

SET OutputLocalEnvironment.ScratchPad[] = PASSTHRU ('SELECT SEQ_CorrelID.CURRVAL FROM DUAL;');

Declare C INT;
Set C = OutputLocalEnvironment.ScratchPad.CURRVAL;

The error seemed to be some syntax problem. Send me your exact ESQL, maybe I can help.


Cheers,
Suntosh
Back to top
View user's profile Send private message
MQDummy
PostPosted: Mon Jun 24, 2002 7:02 pm    Post subject: Reply with quote

Novice

Joined: 11 Jun 2002
Posts: 23

Hi,

I've used the scratchpad to do this :

----
SET OutputLocalEnvironment.ScratchPad[] =
PASSTHRU('SELECT Partner_ID, Business_Name FROM Partner
WHERE Business_Name = ?', InputBody.Line.CustomerName);

SET IsExistsPartner = Cardinality(OutputLocalEnvironment.ScratchPad[]);

IF (IsExistsPartner > 0) THEN
SET IsExistsPartner_ID =
OutputLocalEnvironment.ScratchPad[1].Partner_ID;
END IF;
----

Is this the correct way use scratchpad if i want to extract "partner_id"
for example? I tried it and it always gives me NULL for partner_id even though there are records in the table

Is there a more 'correct' way to get column values from the table?

best regards
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Getting the current sequence number from DUAL in Oracle
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.