Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Switzerland
    Posts
    3

    Unanswered: Stored Procedure - Multiple Recordset - Temporary Tables

    Hi there

    I hope someone can help me out of here. I have to migrate from MSSQL2000 to Oracle 9x. On MSSQL I have a Stored Procedure that returns one Recordset. The Procedure calls itself many times (recursive) and the results are stored in a temporary table. At the end the procedure returns only the temporary table. Since i wasn't realy familiar with Oracle i have now some problems to write the procedure on Oracle.
    The Problem is that the recursive call will not work (found symbole EXECUTE while an othe symbol was expectet).
    How have i to call the procedure from itself?

    Here the whole procedure:
    -------------------------------------------------------------

    (p_LeiEDV IN NUMBER, p_Lang IN NUMBER, p_rekStufe in number, p_lastRekStufe in number, p_patedv in number, p_rs OUT oraOLEDB.m_refcur)
    IS

    --DECLARE
    first NUMBER(10);
    next_r NUMBER(10);

    BEGIN --main

    IF p_rekstufe < 30 then
    BEGIN

    IF p_rekstufe = 0 THEN
    BEGIN
    -- generate the temporary table
    EXECUTE IMMEDIATE (
    'CREATE GLOBAL TEMPORARY TABLE t_VarPack
    (vpedvnr NUMBER(10),
    pvaredv NUMBER(10),
    cvaredv NUMBER(10),
    defanz NUMBER(3),
    vst NUMBER(1),
    hws NUMBER(1),
    lck NUMBER(1),
    usb NUMBER(1),
    opt NUMBER(1),
    ds NUMBER(3),
    varnr CHAR(10),
    varlang VARCHAR(200),
    varzeit NUMBER(10),
    katalog NUMBER(3),
    edvnr NUMBER(10),
    maxanzahl NUMBER(5),
    varset NUMBER(1),
    minchild NUMBER(5),
    extinp VARCHAR(250),
    rekstufe NUMBER(10)'
    );

    first := 0;

    insert into t_VarPack
    SELECT
    0, 0, edvnr, 0, 0, 0, 0, 0, 0, 0, varnr, varlang, varzeit, katalog, edvnr, maxanzahl, varset, 0, extinp, p_rekstufe
    FROM
    varstamm
    WHERE
    edvnr = p_LeiEDV);

    next_r := p_rekstufe + 1

    EXECUTE GETVARPACKALL p_LeiEDV, p_Lang, next_r, p_rekstufe, p_PatEDV

    END
    ELSE
    BEGIN

    first = :0

    INSERT INTO p_VarPack
    SELECT
    varpack.edvnr AS vpedvnr, varrpack.pvaredv, varpack.cvaredv, varpack.defanz, varpack.vst, varpack.hws, varpack.lck, varpack.usb, varpack.opt, varpack.ds
    varstamm.varnr, varstamm.varlang, varstamm.varzeit, varstamm.katalog, varstamm.edvnr, varstamm.maxanzahl, varstamm.varset, varstamm.minchild,
    varstamm.extinp, pLastRekStufe
    FROM
    varpack, varstamm
    WHERE
    varpack.cvaredv = varstamm.edvnr AND
    varpack.pvaredv IN (SELECT edvnr from t_VarPack WHERE rekstufe = p_LastRekStufe)

    INSERT INTO p_VarPack
    SELECT
    0, 0, varstamm.edvnr, 0,0,0,0,0,0,0, varnr, varlang, varzeit, katalog, varstamm.edvnr, maxanzahl, varset, 0, extinp, p_rekstufe
    FROM
    varstamm, dynset
    WHERE
    varstamm.edvnr = dynset.cvaredv AND
    dynset.pvaredv IN (SELECT edvnr FROM p_VarPack WHERE rekstufe = p_LastRekStufe AND ds > 0) AND
    dynset.patedv = p_PatEDV

    next_r := rekstufe + 1

    EXECUTE GETVARPACKALL p_LeiEDV, p_Lang, next_r, p_RekStufe, p_PatEDV

    END
    END IF

    IF first = 1 then

    BEGIN
    OPEN p_rs FOR
    SELECT * from t_VarPack
    DROP TABLE t_VarPack
    END

    END IF

    END
    END IF

    END getVarPackALL;
    --------------------------------------------------------------

    Thank in advance

    Daniel

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Stored Procedure - Multiple Recordset - Temporary Tables

    To call a procedure from PL/SQL, you don't use EXECUTE. Do it like this:

    GETVARPACKALL (p_LeiEDV, p_Lang, next_r, p_RekStufe, p_PatEDV);

    Also, the way Oracle uses temporary tables is different from SQL Server. Your procedure should NOT be creating and dropping the table - that is bad practise in Oracle, and fraught with problems. Create the global temporary table ONCE like any other table. Your procedure can then just insert data into it. Data will be deleted from the table automatically, either when you COMMIT or when your session terminates (depending on how you created the table: ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWS).

    Other issues with your code:

    1) In PL/SQL, EVERY statement must end with a semi colon; you keep leaving them off.

    2) You don't need all those BEGINs and ENDs. Just the main BEGIN and END are required. The others are legal but redundant - you would only add them if you wanted to put some EXCEPTION handling within the code.

    3) I don't understand what you are doing with the variable called "first". It is only ever set to NULL (initially) or 0, but then you test to see if it is 1 - it never will be.

  3. #3
    Join Date
    Jan 2003
    Location
    Switzerland
    Posts
    3

    Re: Stored Procedure - Multiple Recordset - Temporary Tables

    Hi andrews

    thank you for your answer.

    I've found some other topics about temporary tables and i have made it that way.
    The recursive call works too.
    The thing with the variable 'first' that was a mistake. In the first "If ... end" it should be set to '1' :-)

    It looks realy good now. Exept two little problems.

    Here the error-massages:

    - Line 66: Found the Symbol "IF" where the following is expected: ; The Symbol ";" replaces "IF", to conntinue.

    - Line 73: Found the Symbol "END" where the following is expected: ;

    Do the "If .. end if" need the ";" too? I dont thing so! Or what is wrong now??
    I'm a little bit confused now :-)

    ------------------------------GETVARPACKALL-------------------------------
    (p_LeiEDV IN NUMBER, p_Lang IN NUMBER, p_rekStufe in number, p_lastRekStufe in number, p_patedv in number, p_rs OUT oraOLEDB.m_refcur)
    IS

    --DECLARE
    first NUMBER(10);
    next_r NUMBER(10);

    BEGIN --main

    IF p_rekstufe < 30 then

    IF p_rekstufe = 0 THEN

    first := 1;

    INSERT INTO t_VarPack
    SELECT
    0, 0, edvnr, 0, 0, 0, 0, 0, 0, 0, varnr, varlang, varzeit, katalog, edvnr, maxanzahl, varset, 0, extinp, p_rekstufe
    FROM
    varstamm
    WHERE
    edvnr = p_LeiEDV;

    next_r := p_rekStufe + 1;

    GETVARPACKALL (p_LeiEDV, p_Lang, next_r, p_rekStufe, p_PatEDV);

    ELSE

    first := 0;

    INSERT INTO t_VarPack
    SELECT
    varpack.edvnr AS vpedvnr, varrpack.pvaredv, varpack.cvaredv, varpack.defanz, varpack.vst, varpack.hws, varpack.lck, varpack.usb, varpack.opt, varpack.ds,
    varstamm.varnr, varstamm.varlang, varstamm.varzeit, varstamm.katalog, varstamm.edvnr, varstamm.maxanzahl, varstamm.varset, varstamm.minchild,
    varstamm.extinp, pLastRekStufe
    FROM
    varpack, varstamm
    WHERE
    varpack.pvaredv IN (SELECT edvnr from t_VarPack WHERE rekstufe = p_LastRekStufe) AND
    varpack.cvaredv = varstamm.edvnr;

    INSERT INTO t_VarPack
    SELECT
    0, 0, varstamm.edvnr, 0,0,0,0,0,0,0, varnr, varlang, varzeit, katalog, varstamm.edvnr, maxanzahl, varset, 0, extinp, p_rekstufe
    FROM
    varstamm, dynset
    WHERE
    varstamm.edvnr = dynset.cvaredv AND
    dynset.pvaredv IN (SELECT edvnr FROM p_VarPack WHERE rekstufe = p_LastRekStufe AND ds > 0) AND
    dynset.patedv = p_PatEDV;

    next_r := p_rekStufe + 1;

    GETVARPACKALL (p_LeiEDV, p_Lang, next_r, p_rekStufe, p_PatEDV);

    END IF

    66 IF first = 1 then
    67
    68 OPEN p_rs FOR
    69 SELECT * from t_VarPack;
    70
    71 END IF
    72
    73 END IF

    END

    END getVarPackALL;
    -----------------------------------------------------------

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Stored Procedure - Multiple Recordset - Temporary Tables

    You need to put a semi-colon after each "END IF" and "END".

  5. #5
    Join Date
    Jan 2003
    Location
    Switzerland
    Posts
    3

    Re: Stored Procedure - Multiple Recordset - Temporary Tables

    Hi Tony

    Thank you very much for your input. At least i had to fix some other problems with the procedure, but now it works fine. Especialy the recursive call with the ref_cursour will not work inside the procedure, so I hade to make another procedure without the ref_coursor.

    For all other I will post here the final procedure(s). Perhaps someone out there could use it for it's one procedures.

    ---------------Temporary Table----------------------
    CREATE GLOBAL TEMPORARY TABLE t_VarPack
    (vpedvnr NUMBER(10),
    pvaredv NUMBER(10),
    cvaredv NUMBER(10),
    defanz NUMBER(3),
    vst NUMBER(1),
    hws NUMBER(1),
    lck NUMBER(1),
    usb NUMBER(1),
    opt NUMBER(1),
    ds NUMBER(3),
    varnr CHAR(10),
    varlang VARCHAR(200),
    varzeit NUMBER(10),
    katalog NUMBER(3),
    edvnr NUMBER(10),
    maxanzahl NUMBER(5),
    varset NUMBER(1),
    minchild NUMBER(5),
    extinp VARCHAR(250),
    rekstufe NUMBER(10),
    sort NUMBER(10),
    dsedv NUMBER(10))
    ON COMMIT PRESERVE ROWS;
    -------------------------------------------------------------------
    Once the temporary table is createt you dont have to create it again.

    -------------Main Stored Procedure (GETVARPACKALL)------
    (p_LeiEDV IN NUMBER, p_Lang IN NUMBER, p_rekStufe in number, p_lastRekStufe in number, p_patedv in number, p_rs OUT oraOLEDB.m_refcur)
    IS

    BEGIN --main

    --call the recursive function
    GETVARPACKALLREC (p_LeiEDV, p_Lang, p_rekStufe , p_rekStufe, p_PatEDV);

    --return the records
    OPEN p_rs FOR
    SELECT * from t_VarPack;

    --delete the records (only the records for the session will be deleted)
    delete from t_VarPack where vpedvnr <> -1;

    END getVarPackALL;
    ----------------------------------------------------------------------------
    -------------Sub Stored Procedure (GETVARPACKALLREC)----------
    (p_LeiEDV IN NUMBER, p_Lang IN NUMBER, p_rekStufe in number, p_lastRekStufe in number, p_patedv in number)
    IS

    --DECLARE
    next_r NUMBER(10);

    BEGIN --main

    --the recursive call goes up to a maximum of 30
    IF p_rekstufe < 30 THEN

    --first level, insert the parentobject
    IF p_rekstufe = 0 THEN

    INSERT INTO t_VarPack
    SELECT
    0, 0, edvnr, 0, 0, 0, 0, 0, 0, 0, varnr, varlang, varzeit, katalog, edvnr, maxanzahl, varset, 0, extinp, p_rekstufe, sort, 0
    FROM
    varstamm
    WHERE
    edvnr = p_LeiEDV;

    next_r := p_rekStufe + 1;

    --call the next level
    GETVARPACKALLREC (p_LeiEDV, p_Lang, next_r, p_rekStufe, p_PatEDV);

    ELSE

    --insert the next level from table varpack
    INSERT INTO t_VarPack
    SELECT
    varpack.edvnr AS vpedvnr, varpack.pvaredv, varpack.cvaredv, varpack.defanz, varpack.vst, varpack.hws, varpack.lck, varpack.usb, varpack.opt,
    varpack.ds, varstamm.varnr, varstamm.varlang, varstamm.varzeit, varstamm.katalog, varstamm.edvnr, varstamm.maxanzahl, varstamm.varset,
    varstamm.minchild, varstamm.extinp, p_rekStufe, varstamm.sort, 0
    FROM
    varpack, varstamm
    WHERE
    varpack.pvaredv IN (SELECT edvnr from t_VarPack WHERE rekstufe = p_LastRekStufe) AND
    varpack.cvaredv = varstamm.edvnr;

    --insert the next level from table dynset
    INSERT INTO t_VarPack
    SELECT
    0, 0, varstamm.edvnr, 0,0,0,0,0,0,0, varnr, varlang, varzeit, katalog, varstamm.edvnr, maxanzahl, varset, 0, extinp, p_rekstufe, varstamm.sort,
    dynset.edvnr
    FROM
    varstamm, dynset
    WHERE
    varstamm.edvnr = dynset.cvaredv AND
    dynset.pvaredv IN (SELECT edvnr FROM t_VarPack WHERE rekstufe = p_LastRekStufe AND ds > 0) AND
    dynset.patedv = p_PatEDV;

    next_r := p_rekStufe + 1;

    --call the next level
    GETVARPACKALLREC (p_LeiEDV, p_Lang, next_r, p_rekStufe, p_PatEDV);

    END IF;

    END IF;

    END getVarPackALLrec;
    ----------------------------------------------------------------------------

    Hope it will help the one or other :-)

    Thanks

Posting Permissions

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