what does bulk collecter and Bulk Binding will do

[rite2sekhar]

Hi
Can U help me

1)what does bulk collecter and Bulk Binding will do

2)Difference between Execute Immediate and Dbms_SQL package
3)I have table with 100000000 rows and i\'m writing a procedure to update records i get a exception but the execution should not stop till all the rows are updated and all the exceptions should be record

with regds
Sekhar

[sanereddy]

1)what does bulk collecter and Bulk Binding will do

http://www.dbspecialists.com/present...ulk_binds.html

http://www.hk8.org/old_web/oracle/guide8i/ch05_03.htm

http://www.oracle-base.com/articles/...ocessing9i.php

read the above links, you will get pretty good idea what\'s the difference between them and how to use.

2)Difference between Execute Immediate and Dbms_SQL package

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead, EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more user friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.

http://databasejournal.com/features/...le.php/2109681
- DBMS_SQL is supported in client_side programs
- DBMS_SQL Supports DESCRIBE
- DBMS_SQL Supports SQL Statements Larger than 32KB
- DBMS_SQL Supports Multiple Row Updates and Deletes with a RETURNING Clause
- DBMS_SQL lets you reuse your statements (parse once, run more)

http://www.unix.org.ua/orelly/oracle/bipack/ch02_02.htm

3)I have table with 100000000 rows and i\'m writing a procedure to update records i get a exception but the execution should not stop till all the rows are updated and all the exceptions should be record

There are two ways of handling errors without using explicit error handling:

Default error handling

The procedure or trigger fails and returns an error code to the calling environment.

ON EXCEPTION RESUME

If the ON EXCEPTION RESUME clause appears in the CREATE PROCEDURE statement, the procedure carries on executing after an error, resuming at the statement following the one causing the error.


The precise behavior for procedures that use ON EXCEPTION RESUME is dictated by the ON_TSQL_ERROR option setting. For more information

Error handling with ON EXCEPTION RESUME
If the ON EXCEPTION RESUME clause appears in the CREATE PROCEDURE statement, the procedure checks the following statement when an error occurs. If the statement handles the error, then the procedure continues executing, resuming at the statement after the one causing the error. It does not return control to the calling environment when an error occurred.

read error handling section in the documentation.

http://www.csee.umbc.edu/help/oracle..._errs.htm#1069

Hope this helps.

Thanks,
Vamsee

Post edited by: sanereddy, at: 2004/10/08 16:01