Re: Help please! Problem with dynamic sql

Hi.

 Try GRANTing access rights to the owner of PL/SQL stored proc directly ( not through role ). It should include SELECT, INSERT and EXECUTE ( for function/package you are using ).

 Why do you need CURSOR at all? Try creating dynamic SQL (INSERT-AS-SELECT):  INSERT INTO RPT_SUMMARYSALES (USERID, REP, SOLD, CANC, PAID, WAGES) 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;  HTH. Michael.

In article <9144ag$fqc$1_at_nnrp1.deja.com>,   intrepid26_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Dec 12 2000 - 15:54:23 CST