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

Control file

Control file to load data form text file to oracle database table

LOAD DATA
INFILE 'C:\CTRFILE\TnxFile.dat'
BADFILE 'C:\CTRFILE\TnxFile.bad'
DISCARDFILE 'C:\CTRFILE\TnxFile.dsc'
APPEND
INTO TABLE "Employee_data"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
EMP_Number position (,) char,
EMP_NAME position(,) char,
EMP_ADDRESS (,)char
)

Oracle spool

Example1 :
Spool data from oracle to text file also you can add title to the text file .


set linesize 50;
set echo off;
set pagesize 9999;
set newpage none;
set heading on;
set recsep off;

spool c:\Data\FileName.txt ;

TTITLE left 'BEX Approved Batch sheets'; -- /* Add title to the text file*/
column c1 heading "emp_id" format a5
column c2 heading "emp_name" format a25
column c3 heading "emp_salary" format $9,999,990.99

SELECT emp_id c1,
emp_name c2,
emp_salary c3
From employee where (emp_salary >=60000)
spool off;

Example 2:
Spool data from oracle to text file and name the file with date and time of data spool

set linesize 50;
set echo off;
set pagesize 9999;
set newpage none;
set heading on;
set recsep off;

col sys_date new_value curr_date;
select 'c:\data\'to_char(sysdate,'yyyy_ddmon_hh24_miss')'.txt' sys_date from dual;
spool &curr_date;

column c1 heading "emp_id" format a5
column c2 heading "emp_name" format a25
column c3 heading "emp_salary" format $9,999,990.99

SELECT emp_id c1,
emp_name c2,
emp_salary c3
From employee where (emp_salary >=60000)
spool off;