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

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.

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;
-----------------------------------------------------------

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

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