Ask Anantha

Test Your PL/SQL Knowledge This puzzler has come from Steven Feuerstein for the month of February 2008. So I thought to reproduce the puzzler with its answer: The employees table in the production Oracle Database 10g Release 2 instance of the MassiveGlobalCorp company contains 2.5 million rows.   Below are three different blocks of code, each of which fetch all rows from this table and then "do stuff" with each fetched record. Which will run much slower than the other two, and why?   a . DECLARE   CURSOR employees_cur IS SELECT * FROM employees; BEGIN     FOR employee_rec IN employees_cur LOOP       do_stuff ( employee_rec );    END LOOP ; END;   b . DECLARE   CURSOR employees_cur IS SELECT * FROM employees;    l_employee    employees%ROWTYPE ; BEGIN    OPEN employees_cur ;    LOOP       FETCH employees_cur...

Oracle Tips by Burleson Consulting Today, systems with thousands of individual Oracle user ID's are not very common.  Almost all enterprise applications (ERP packages like SAP, Oracle Financials) use pre-spawned Oracle connections that are created by a single Oracle user ID with names like AR or SAPR3. The application server manages the connections to Oracle for us. This is called a "proxy", the term proxy meaning an act where a 3rd party does something on our behalf.   Unfortunately, when our end-users connect anonymously through a proxy, we do not have any end-user level security through traditional security tools like granting privileges or using roles and the only Oracle security is that is granted the ERP "master" user ID. Who goes there? From a DBA perspective, all database activity is being done by this single user and a query of the v$session view for an ERP does not identify any specific user, because they are connected to Oracle via the proxy o...