From: Michael Bialik <michael_bialik@my-deja.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Help please! Problem with dynamic sql
Date: Tue, 12 Dec 2000 21:54:23 GMT
Organization: Deja.com
Lines: 92
Message-ID: <9166qb$5kr$1@nnrp1.deja.com>
References: <9144ag$fqc$1@nnrp1.deja.com>
NNTP-Posting-Host: 212.179.147.234
X-Article-Creation-Date: Tue Dec 12 21:54:23 2000 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT)
X-Http-Proxy: 1.1 x58.deja.com:80 (Squid/1.1.22) for client 212.179.147.234
X-MyDeja-Info: XMYDJUIDmichael_bialik


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@nnrp1.deja.com>,
  intrepid26@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/