Search Selected items

How to search for selected items in a drop down list or compo box

The selected items string should be in the following format
(first item,second item,third item,….)

Then you can use the SQL (IN clause) to retrieve the data
For some reason in ORACLE SQL, you should make the select statement as string to make it work else you will get no thing while if you are using sql server you don’t need to do that

See the following stored procedure

CREATE OR REPLACE PROCEDURE SP_Search_Emp(
ESTATUS IN VARCHAR2,ETYPE IN VARCHAR2,CUR_E_STATUS_TYPE OUT SYS_REFCURSOR )
AS
STR_ESTATUS VARCHAR2(9000);
STR_ETYPE VARCHAR2(9000);
CS VARCHAR2 (15000);

BEGIN

Select ''''Replace(ESTATUS,',',''',''')'''' into STR_ESTATUS from dual;
Select ''''Replace(ETYPE,',',''',''')'''' into STR_ETYPE from dual;

cs:='SELECT e.emp_id,e.emp_name,e.emp_address,e.emp_salary,e.emp_status,e.emp_type

FROM Emp_table e

WHERE emp_Status IN';
cs := cs '('upper(STR_ESTATUS)') AND emp_type IN (' str_etype')
)';

open cur_E_status_type for cs;
END SP_Search_Emp;

Ref Cursor

Ref cursor is one of the most powerful ways to return stored procedures query results from oracle database.
The following example shows how to use oracle ref cursor .


CREATE OR REPLACE PROCEDURE SP_EMP (CUR_EMP OUT SYS_REFCURSOR) AS

BEGIN
OPEN CUR_EMP FOR
SELECT EMP_FNAME, EMP_LNAME, EMP_DATA
FROM EMPLOYEE

Date Interval

Today I am going to show you how to list the months and years between two dates interval using Oracle SQL

Select distinct to_char(create_date,'MON-yyyy')as Create_Date from
(
SELECT TO_DATE('10/01/2008', 'MM/dd/YYYY') + rownum as create_date
FROM all_objects
WHERE TO_DATE('10/01/2008', 'MM/dd/YYYY') + rownum <= TO_DATE(to_char(sysdate,'mm/dd/yyyy'),'MM/dd/YYYY') )

Result :

OCT-2008
NOV-2008
DEC-2008
JAN-2009
FEB-2009