| Author |
Message
|
| MQDummy |
 Posted: Sun Jun 16, 2002 9:31 pm Post subject: Getting the current sequence number from DUAL in Oracle |
 |
|
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 |
|
 |
| TorpedoSTu |
| Posted: Mon Jun 17, 2002 5:01 am Post subject: |
|
|
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 |
|
|
| smurthy |
| Posted: Mon Jun 17, 2002 9:35 am Post subject: Select From DUAL |
|
|
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 |
|
|
| MQDummy |
| Posted: Mon Jun 17, 2002 5:04 pm Post subject: Thanks! |
|
|
Novice
Joined: 11 Jun 2002
Posts: 23
|
|
| Back to top |
|
|
| MQDummy |
| Posted: Mon Jun 17, 2002 7:12 pm Post subject: |
|
|
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 |
|
|
| TorpedoSTu |
| Posted: Mon Jun 17, 2002 11:53 pm Post subject: |
|
|
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 |
|
|
| smurthy |
| Posted: Tue Jun 18, 2002 6:11 am Post subject: |
|
|
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 |
|
|
| MQDummy |
| Posted: Tue Jun 18, 2002 6:13 am Post subject: |
|
|
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 |
|
|
| smurthy |
| Posted: Tue Jun 18, 2002 6:36 am Post subject: ESQL For Compute Node to Select Sequence Number from DUAL |
|
|
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 |
|
|
| MQDummy |
| Posted: Mon Jun 24, 2002 7:02 pm Post subject: |
|
|
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 |
|
|
|
|
