Software Applications Programming Errors and solutions.

Question: What is bulk-collect and any restrictions in bulk-collect ? what is the use of limit clause in bulk-collect ? Answer: With bulk collect we can move all the resultant set in bulk to b processed in PL/SQL engine which kindles high efficiency but at the cost of memory ,thus the resultant set is moved in batches by giving how many records to be moved in one batch by specifying it in limit clause. Actually bulk collect performance is very high,because when ever resource table having large amount of data to fetch data using cursors that is degrade performance of the application,to improve performance of the application then we are using bulk collect,limit is an optional clause using in cursor fetch statement with bulk collect clause. Bulk collect reduces the context switching between the SQL and PL/SQL engines .. thereby increasing performance.. we can reduce the context switch to one but in case of very large data set. it's must that you use limit clause to manage t

This is stored procedure which show you rows return as per mention in stored procedure. CREATE PROCEDURE get_categories( IN p_action_flag VARCHAR(50), IN p_firstrow int, IN p_lastrow int) BEGIN IF p_action_flag = 'all' THEN SELECT top 50 u.*, d.name, d.image FROM user u, member gu, detail d WHERE u.user_id = gu.user_id AND u.user_id = d.user_id AND u.status = '1' AND gu.group_id IN (1, 7) GROUP BY u.user_id ORDER BY d.name LIMIT p_firstrow,p_lastrow; ELSEIF p_action_flag = 'Most Viewed' THEN SELECT top 50 u.*, d.name, d.image FROM user u, member gu, detail d WHERE u.user_id = gu.user_id AND u.user_id = d.user_id AND u.status = '1' AND gu.group_id IN (1, 7) GROUP BY u.user_id ORDER BY u.views DESC, d.name ASC LIMIT p_firstrow,p_lastrow ; END IF END