RE: PL/SQL - FORALL statement

Hi All!

I had to drop the idea of forall and have implemented the code as:

PROCEDURE ExecDML(pDPSQL IN Ctx.TDPSQL)
IS
   PROC     CONSTANT VARCHAR2(30) := 'ExecDML';
   c        PLS INTEGER;
   r        PLS INTEGER;
   i        BINARY INTEGER;
BEGIN
   -- c := dbms sql.open cursor;
   i := pDPSQL.first;
   WHILE i IS NOT NULL LOOP
   /******************* WR9126 ***************************************
      dbms sql.parse(c, pDPSQL(i), dbms sql.native);
      r := dbms sql.EXECUTE(c);
        *******************************************************************/
          EXECUTE IMMEDIATE pDPSQL(i); 
      i := pDPSQL.NEXT(i);
   END LOOP;
   COMMIT;
   --- dbms sql.close cursor(c);
EXCEPTION
   WHEN Ctx.X ABORT THEN
      RAISE;
   WHEN OTHERS THEN
      ROLLBACK;
      ---- IF dbms sql.is open(c) THEN dbms sql.close cursor(c); END IF;
      Ctx.ErrorHandler(PROC, Ctx.ERRC ORACLE, Ctx.ERRM 
ORACLE||SQLCODE||'-'||SQLERRM||': SQL is: '||pDPSQL(i));
END ExecDML;

What wanted to do is use forall to send all my dynamic sqls as one block for 
execution to sql engine rather than one sql at a time.
Thanks all for your support, open to any good feedback.
Note: the collection is an unsequenctial index by table.

Regards,

Sam 

-----Original Message-----
From: David, Romeo B. (Govt) [mailto:DavidRB@xxxxxxxxxxxxxxx]
Sent: Friday, 17 January 2003 02:46
To: Oracle
Subject: [oracle] RE: PL/SQL - FORALL statement


I think you are using the FORALL statement the wrong way. These are the
rules in using FORALL:
1. The body of the FORALL statement must be a single DML statement - such as
INSERT,UPDATE, or
   DELETE.
2. The DML statement must reference collection elements, indexed by the
index-row variable (your v Count) in the FORALL statement. The scope of the
index-variable is the FORALL statement only; you may not reference it
outside  of that statement. 
3. Do not declare a variable for index-row (v Count). It is declare
implicitly as PLS INTEGER by  the PL/SQL engine.
4. The lower and upper bounds must specify a valid range of consecutive
index numbers for the collection(s) referenced in the SQL statement.
5. The collection subscript referenced in the DML statement cannot be an
expression. For example, v count+10. 

Why don't you post your whole code and we can see what is the best way to do
it.

-----Original Message-----
From: samir.reshamwala@xxxxxxx [mailto:samir.reshamwala@xxxxxxx]
Sent: Tuesday, January 14, 2003 7:23 PM
To: Oracle
Subject: [oracle] PL/SQL - FORALL statement


Hi All!
 
I am trying to send all my dynamic sqls for execution in SQL engine from 
my PLSQL stored procedure but get error "PLS-00103: Encountered the 
symbol "EXECUTE" when expecting one of the following:
 
   . ( * @ % & - + / at mod rem select update <an exponent (**)>
   delete insert ||" 
 
the code I use in the stored proc is as:
 
FORALL v Count IN pDPSQL.first..pDPSQL.last

EXECUTE IMMEDIATE pDPSQL(v Count);

 

Please help or suggest a better way of sending all sqls in one block to 
sql engine for execution.

Thank you.

Regards,

 

Sam R
---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.


------------------------------------------------------------------------------
"This communication, including any attachments, is confidential. 
If you are not the intended recipient, you should not read
it - please contact me immediately, destroy it, and do not
copy or use any part of this communication or disclose
anything about it. Thank you."

------------------------------------------------------------------------------


---
Change your mail options at http://p2p.wrox.com/manager.asp or
to unsubscribe send a blank email to leave-oracle-1796914O@xxxxxxxxxxxxx