Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Help please! Problem with dynamic sql
Hi Michael,
Thanks for the reply, however my problems still remain. I was executing this procedure as the owner of the database schema, so I don't think it's a security issue. I also tried to execute this statement without using a cursor using EXECUTE IMMEDIATE (prior to using a cursor), however after a couple of hours of this not working I read this section in the Oracle documentation:
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}] [USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]...];
where dynamic_string is a string expression that represents a SQL statement or PL/SQL block, define_variable is a variable that stores a SELECTed column value, record is a user-defined or %ROWTYPE record that stores a SELECTed row, and bind_argument is an expression whose value is passed to the dynamic SQL statement or PL/SQL block. (The NOCOPY compiler hint is not allowed in an EXECUTE IMMEDIATE statement.)
Except for multi-row queries, the string can contain any SQL statement (without the terminator) or any PL/SQL block (with the terminator). The string can also contain placeholders for bind arguments. However, you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement. For the right way, see "Passing the Names of Schema Objects".
The last paragraph tells me that I can't use multi row queries, which is what mine is. I'm still trying to find a solution/work around to this problem, so any other suggestions that I might try? :)
Shane
In article <9166qb$5kr$1_at_nnrp1.deja.com>,
Michael Bialik <michael_bialik_at_my-deja.com> wrote:
> 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/
>
Sent via Deja.com
http://www.deja.com/
Received on Tue Dec 12 2000 - 16:37:16 CST