rvsunilkumar

Certain times in PL/SQL function, the input type could be of TYPE  example: table type example : CREATE OR REPLACE TYPE "SAMPLE_TYPE_TAB" as table of number(17,0); In oder to send the corresponding object in java, we need to construct the corresponding object which is of java.sql.Struct in java. the sample pl/sql could be ( skeleton shown) CREATE OR REPLACE PACKAGE  test_package IS FUNCTION inputAsType(type_tab sample_type_tab, creator VARCHAR2) RETURN NUMBER IS num NUMBER;   here the input parameter type_tab is of type "SAMPLE_TYPE_TAB" defined above. as per the defintion it has only one column of type number.   lets see how we can build the callable statement to invoke the above function.   1. build the connection   Class.forName(driver); Connection con = DriverManager.getConnection(url + db, user, pass); 2. define the package and function name as string String packagefunction = "{? = call test_package.inputAsType(?,

Temporary destinations (temporary queues or temporary topics) are proposed as a lightweight alternative in a scalable system architecture that could be used as unique destinations for replies . Such destinations have a scope limited to the connection that created it, and are removed on the server side as soon as the connection is closed. Only a single well-known static queue is required for producers/senders to connect with consumers using temporary destinations. The JMS header field JMSReplyTo is always used in conjunction with temporary destinations. Since the identity of the temporary destination is known only by the connection or session that created it, the consumer/receiver cannot know the destination name . The solution is to have the producer/sender send the name of its temporary destination as a header field (JMSReplyTo), as part of a message, sent to a known static queue listened to by the producer/sender. Limitations of Temporary Destinations There are a few limitations

1. Use count(Column_name) instead of count(*). 2. Use UNION ALL statement instead of UNION, whenver possible         The UNION ALL  statement is much faster than UNION, because UNION ALL statement does not          look for duplicate rows, and UNION statement does look for duplicate rows. 3. Avoid using the DISTINCT clause, whenever possible.          Using DISTINCT  clause wll result in some performance degrading, use this clause only when it is          necessary. 4. Avoid the HAVING clause, whenever possible           Having clause is used to restrict the results set returned by the GROUP BY clause. When you use           GROUP BY  with the HAVING clause divides the rows into sets of grouped rows and aggregates           their value, and then HAVING clause eliminates undesired aggregated groups.          Also do not place any ( where) conditions after the HAVING clause (first_name like '%thomos%') -           this would reduce the performance of the query. 5. Choose order o

Getting total count of rows for the query and required number of records for the page in single query using oracle analytic functions. to get all the employee records whose first name is "peter" and display total count and per page display only 10 records  Simple table Structure as follows CREATE TABLE Employee ( emp_id number not null, first_name varchar2 not null, last_name varchar2 not null, join_date Date, ); the query would look like the following. select * from (select max(ROWNUM) over() totalCount, row_number() over(order by join_date asc) rownumb,allrows.* from (select emp_id,first_name,last_name,join_date from Employee where first_name like '%peter%') allrows ) where rownumb between X and Y where x and y are starting and ending row numbers, it varies depending upon the page index. here totalCount is the total number of rows that exist in db for the query.