Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Help please! Problem with dynamic sql

Re: Help please! Problem with dynamic sql

From: <intrepid26_at_my-deja.com>
Date: Tue, 12 Dec 2000 22:37:16 GMT
Message-ID: <9169ar$83u$1@nnrp1.deja.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US