none
BizTalk 2010 - WCF custom with Oracle RRS feed

  • Question

  • Hello,

    What is the correct syntax to put in postpollstatement for Oracle? I know BizTalk does not take Oracle procedure. The polling went on correctly if I issue this 1 statement in postpollstatement:

    DELETE FROM TestTable WHERE ROWNUM=1

    But BizTalk will complaint when I put in 2 statements:

    insert into TestTable2 ( 
          SELECT
          "Field1" ,
          "Field2" , 
          "Field3"
          from TestTable WHERE ROWNUM=1
        );

    DELETE FROM TestTable WHERE ROWNUM=1;

    With semi colon, it will complaints invalid character, without it, it will complaint something else.

    What is the correct syntax to use 2 statements? I remember I did it once and it worked but it is now no longer working.

    Please help?

    Thursday, July 7, 2011 7:33 PM

Answers

  • Hi Frank,

    You can call stored procedure in Oracle using WCF-Oracle binding and then poll Oracle and delete record in orchestration. See my post on Table operations Oracle. Through Consume Adapter Service you create multiple schema's for calling stored procedure, polling and deleting a record. Use these schema's in subsequent calls to Oracle. So you will have a process that does this for you. You could combine actions in stored procedure inside Oracle, and perhaps you can ask for assistance by an Oracle DBA/Developer. For reference see also this post.

    HTH

    Regards,

    Steef-Jan Wiggers
    MVP & MCTS BizTalk Server 2010
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly

     


    BizTalk
    • Marked as answer by FrankTo Monday, July 11, 2011 1:37 PM
    Friday, July 8, 2011 4:54 PM
    Moderator
  • Hi Frank,

    Yes that seems a good approach and are correct steps. It is good that those posts helped. I created a new post on polling Oracle 11g, which you can read to if you are interested.

    HTH

    Regards,

    Steef-Jan Wiggers
    MVP & MCTS BizTalk Server 2010
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    • Marked as answer by FrankTo Monday, July 11, 2011 1:37 PM
    Monday, July 11, 2011 1:35 PM
    Moderator

All replies

  • As per this post

    Multiple statements do not work with Oracle (and other databases might prevent this as well). Oracle prevents what is called SQL injection, a mechanism that a hacker would use to get the database to run code within the database.

    MS-SQL is one database that allows many statements.


    Mark As Answer or Vote As Helpful if My Reply Does, Regards, -Rohit
    Friday, July 8, 2011 11:00 AM
    Moderator
  • Hmm.. interesting. I remember I had 2 Oracle statements in the past in the same postpollstatement since I found out the Oracle does not store procedure. I would like to save the record in another table before polling and delete the original one. Any way we could do that in the orchestration?
    Friday, July 8, 2011 11:42 AM
  • Hi Frank,

    You can call stored procedure in Oracle using WCF-Oracle binding and then poll Oracle and delete record in orchestration. See my post on Table operations Oracle. Through Consume Adapter Service you create multiple schema's for calling stored procedure, polling and deleting a record. Use these schema's in subsequent calls to Oracle. So you will have a process that does this for you. You could combine actions in stored procedure inside Oracle, and perhaps you can ask for assistance by an Oracle DBA/Developer. For reference see also this post.

    HTH

    Regards,

    Steef-Jan Wiggers
    MVP & MCTS BizTalk Server 2010
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly

     


    BizTalk
    • Marked as answer by FrankTo Monday, July 11, 2011 1:37 PM
    Friday, July 8, 2011 4:54 PM
    Moderator
  • Steef,

    That was an excellent post and that is what I am looking for!!! Thank you Steef!

    So, to make sure I can apply what you said in your post to my situation, for me, I would want to:

    1) Poll the record into orchestration for processing

    2) Create another auto generated outbound schema that will take the record from step 1) and copy it to another table in Oracle for archiving.

    Am I getting it right?

    Thanks,

     

    Monday, July 11, 2011 12:39 PM
  • Hi Frank,

    Yes that seems a good approach and are correct steps. It is good that those posts helped. I created a new post on polling Oracle 11g, which you can read to if you are interested.

    HTH

    Regards,

    Steef-Jan Wiggers
    MVP & MCTS BizTalk Server 2010
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    • Marked as answer by FrankTo Monday, July 11, 2011 1:37 PM
    Monday, July 11, 2011 1:35 PM
    Moderator
  • Yep...They do help, thanks a lot!
    Monday, July 11, 2011 1:37 PM