Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56

    Unanswered: PL/SQL in DB2 9.7 mixing

    We are migrating an Oracle 10gR2 platform to DB2 express LUW 9.7

    DB2 9.7 has PL/SQL compatibility now.

    However, we have a situation where our client is doing an Oracle command, "execute immediate" often within these objects.
    Which executes other objects and statements outside the procedure, etc.

    I don't believe this command supported directly for DB2.

    So the crust of it is...
    Can you mix PL/SQL and Oracle within the same object?

    Or will we be forced to wrap a procedure within another, etc, and other workaround potentials?...

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rogue49 View Post

    I don't believe this command supported directly for DB2.
    Your belief is incorrect.

    EXECUTE IMMEDIATE - IBM DB2 9.7 for Linux, UNIX, and Windows

    Quote Originally Posted by rogue49 View Post
    Can you mix PL/SQL and Oracle within the same object?
    You mean PL/SQL and SQL PL? Then the answer is "no".

  3. #3
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    Well, let me be a bit more specific. I'm sorry if I wasn't more clear.

    DB2 supports "execute immediate" but it was not clear if it is supported how we use it: IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    Example -

    begin
    select a.* into thisEmbCont from embedded_content a where a.fiscal_years_id = yearId
    and a.lookup_name = lookupName and a.status = 'ACTIVE' and a.embedded_content_id is null;

    bfm_emb_cont.insertedContent := '';

    codeblock := REPLACE (thisEmbCont.code, CHR (13) || CHR (10), CHR (10));
    begin
    execute immediate codeblock;

    end;


    An execute immediate codeblock example (full PL/SQL) -

    declare
    tempClob clob;
    userName varchar2(80) := owa_custom.getUser;
    orgAbbr varchar2(40);
    startPos number := 1;
    year number := 0;
    pNames varchar2(32000);
    pValues varchar2(32000);

    begin
    userName := owa_custom.getUser;
    DBMS_LOB.CREATETEMPORARY(tempClob, TRUE, DBMS_LOB.CALL);

    select fiscal_year into year from fiscal_years where id = bfm_emb_cont.yearId;

    pNames :='orgId~setId~year~userSessionId';
    pValues :=Bfm_emb_cont.orgId|| '~' ||bfm_emb_cont.setId|| '~'||year||'~DocEng';

    tempClob:= FW_UTILITIES****njavareportbyname(
    'gov.treas.bfm.rtf.doeReports.EnergyPMMReport',
    'runReport',
    pNames,
    pValues
    );


    startPos := inStr(tempClob, '\par', startPos);

    -- if startPos <> 0 then
    if 2>1 then
    Bfm_emb_cont.insertedContent := replace(substr(tempClob, startPos + 4, length(tempClob) - startPos - 4), '\clcbpat1', '');
    Bfm_emb_cont.insertedContent := replace(Bfm_emb_cont.insertedContent, '\clcbpat2', '\clcbpat' || Bfm_emb_cont.colors('SKY_BLUE'));
    Bfm_emb_cont.insertedContent := replace(Bfm_emb_cont.insertedContent, '\f3', '\f1');
    else
    Bfm_emb_cont.insertedContent := '\cf6\b ERROR: malformed document';
    end if;


    exception when others then

    Bfm_emb_cont.insertedContent := '\cf6\b ERROR: ' || sqlerrm;

    end;

    ----------------

    Does this seem doable within this context for DB2??

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 supports Compound SQL (compiled) and Compound SQL (inlined) in an execute immediate statement.

    So, if codeblock was constructed according to the syntax of Compound SQL,
    DB2 would execute innediate codeblock properly.

    Compound SQL must be parenthesized in "BEGIN" and "END".
    Last edited by tonkuma; 03-28-11 at 19:16.

  5. #5
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    thanks for your input, I do appreciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •