Help please! Problem with dynamic sql

From: <intrepid26_at_my-deja.com>
Date: Tue, 12 Dec 2000 02:59:30 GMT
Message-ID: <9144ag$fqc$1@nnrp1.deja.com>

This problem is driving me crazy, but hopefully someone will be able to point out a solution. I'm using dynamic SQL to process a multi-line insert operation, using an OPEN-FOR statement. The relevent bits of my code are here:

First of all my variable declarations:

  query_str VARCHAR2(1000);
  TYPE EmpCurTyp IS REF CURSOR;
  summary_cv EmpCurTyp;
  summary_rec RPT_SUMMARYSALES%ROWTYPE;

And secondly the section with the OPEN-FOR:

OPEN summary_cv FOR query_str;

  LOOP
    FETCH summary_cv INTO summary_rec;
    EXIT WHEN summary_cv%NOTFOUND;

INSERT INTO RPT_SUMMARYSALES (USERID, REP, SOLD, CANC, PAID, WAGES) VALUES (summary_rec.USERID, summary_rec.REP, summary_rec.SOLD, summary_rec.CANC, summary_rec.PAID, summary_rec.WAGES); END LOOP; CLOSE summary_cv;

My problem is that when the cursor is opened, it returns no records even though I know for sure that the sql statement it is using (contained in query_str) returns 73 records. I've spent hours and hours trying to overcome this, but with no luck.

If it helps at all, this is the contents of the query_str variable after it has been dynamically constructed before the OPEN-FOR:

SELECT
10000 "USERID",
R.CODE || ' ' || R.FIRSTNM || ' ' || R.LASTNM "REP",

  COUNT(*) "SOLD",
  COUNT(DTECANC) "CANC",
  COUNT(DTEPAID) "PAID",

  AF.GetStaffWages(I.REPID, TO_DATE('01-OCT-2000'), TO_DATE('08-DEC- 2000')) "WAGES"
FROM
  ATR.INVOICE I,
  ATR.STAFF R
WHERE
  I.REPID = R.STAFFID

  AND I.DTE >= TO_DATE('01-OCT-2000')
  AND I.DTE <= TO_DATE('08-DEC-2000')
  AND I.TYPEID = 400

GROUP BY

  R.CODE,
  R.FIRSTNM,
  R.LASTNM,
  I.REPID

This query returns 73 records when run against the database. If some Oracle guru could please let me know where this is going wrong, I would be eternally grateful.

Thankyou in advance,
Shane Bryzak

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 11 2000 - 20:59:30 CST