Please use the following document to understand about Discrete and Repetitive manufacturing
Discrete and Repetitive Schedule Monday, August 22, 2011
Saturday, August 6, 2011
Useful FND Queries
http://onlyappsr12.blogspot.com/p/usefull-fnd-queries.html
1. Concurrent Program Info
SELECT fcp.user_concurrent_program_name Concurrent_Program,
fdfcu.column_seq_num Sequence_number ,
fdfcu.default_value default_value ,
fdfcu.form_left_prompt prompt ,
fdfcu.description description ,
ffvs.flex_value_set_name ,
fl.meaning ,
fe.executable_name ,
fe.execution_file_name
FROM apps.fnd_concurrent_programs_vl fcp,
apps.fnd_lookups fl ,
apps.fnd_executables fe ,
apps.fnd_descr_flex_col_usage_vl fdfcu,
apps.fnd_application fa ,
apps.fnd_flex_value_sets ffvs
WHERE fcpt.user_concurrent_program_name = '&user_concurrent_program_name'
AND fdfcu.application_id = fa.application_id
AND fdfcu.descriptive_flex_context_code = 'Global Data Elements'
AND fdfcu.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name
AND fdfcu.flex_value_set_id = ffvs.flex_value_set_id
AND fcp.execution_method_code = fl.lookup_code
AND fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND fcp.executable_id = fe.executable_id
ORDER BY 1,
2 ,
3;
select cpt.USER_CONCURRENT_PROGRAM_NAME,
cpt.DESCRIPTION,
cp.CONCURRENT_PROGRAM_NAME,
a.APPLICATION_SHORT_NAME,
e.EXECUTABLE_NAME,
e.execution_file_name,
cp.CREATION_DATE,
uc.USER_NAME CREATOR,
cp.LAST_UPDATE_DATE,
ulu.USER_NAME UPDATER,
cp.ENABLED_FLAG,
cp.RUN_ALONE_FLAG,
cp.SRS_FLAG,
cp.PRINT_FLAG,
cp.SAVE_OUTPUT_FLAG,
cp.REQUIRED_STYLE,
cp.OUTPUT_PRINT_STYLE,
cp.PRINTER_NAME,
cp.MINIMUM_WIDTH,
cp.MINIMUM_LENGTH,
cp.OUTPUT_FILE_TYPE,
cp.ENABLE_TRACE,
cp.RESTART,
cp.NLS_COMPLIANT,
cp.REQUEST_SET_FLAG
from applsys.fnd_concurrent_programs cp,
applsys.fnd_concurrent_programs_tl cpt,
applsys.fnd_application a,
applsys.fnd_executables e,
applsys.fnd_user uc,
applsys.fnd_user ulu
where cp.CREATION_DATE > to_date('17-MAY-1995')-- Enter Creation Date
and cp.APPLICATION_ID = cpt.APPLICATION_ID
and cp.CONCURRENT_PROGRAM_ID = cpt.CONCURRENT_PROGRAM_ID
and cpt.LANGUAGE = sys_context('USERENV', 'LANG')
and cp.APPLICATION_ID = a.APPLICATION_ID
and cp.EXECUTABLE_APPLICATION_ID = e.APPLICATION_ID
and cp.EXECUTABLE_ID = e.EXECUTABLE_ID
and cp.CREATED_BY = uc.USER_ID
and cp.LAST_UPDATED_BY = ulu.USER_ID
and cpt.user_concurrent_program_name='Payables Open Interface Import'
order by cp.CONCURRENT_PROGRAM_NAME
2. Concurrent Program parameters
select cp.CONCURRENT_PROGRAM_NAME, cu.*, vs.FLEX_VALUE_SET_NAME
from applsys.fnd_concurrent_programs cp,
applsys.fnd_descr_flex_column_usages cu,
applsys.fnd_flex_value_sets vs
where cp.CREATION_DATE > to_date('17-MAY-1995') --Enter Creation Date
AND cu.application_id = cp.application_id
AND cu.descriptive_flexfield_name = '$SRS$.' || cp.concurrent_program_name
AND cu.FLEX_VALUE_SET_ID = vs.FLEX_VALUE_SET_ID
ANd cp.CONCURRENT_PROGRAM_ID=:CONCURRENT_PROGRAM_ID --(Enter CONCURRENT_PROGRAM_ID of CONCURRENT_PROGRAM)
order by cp.CONCURRENT_PROGRAM_NAME, cu.COLUMN_SEQ_NUM
3. Concurrent Request Details
select p.concurrent_program_name,
pt.user_concurrent_program_name,
rt.responsibility_name,
r.*
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_programs p,
applsys.fnd_concurrent_programs_tl pt,
applsys.fnd_responsibility rsp,
applsys.fnd_responsibility_tl rt
where r.request_id = &Request_ID -- Enter Request ID
and r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
and p.application_id = pt.application_id
and p.concurrent_program_id = pt.concurrent_program_id
and pt.language = sys_context('USERENV', 'LANG')
and r.responsibility_application_id = rsp.application_id
and r.responsibility_id = rsp.responsibility_id
and rsp.application_id = rt.application_id
and rsp.responsibility_id = rt.responsibility_id
and rt.language = sys_context('USERENV', 'LANG')
4. Request Group Details
select rga.application_short_name "Req Grp App",
rg.request_group_name "Request Group Name",
rg.request_group_code "Request Group Code",
rg.description "Request Group Desc",
cp.concurrent_program_name "Concurrent Program",
ea.application_short_name "Exec App",
e.executable_name "Executable",
rga.application_id "Req Grp App ID",
rg.request_group_id "Req Grp ID",
e.application_id "Exec App ID"
from applsys.fnd_executables e,
applsys.fnd_concurrent_programs cp,
applsys.fnd_request_group_units rgu,
applsys.fnd_request_groups rg,
applsys.fnd_application rga,
applsys.fnd_application ea
where e.application_id like '%' -- Enter Application ID
and e.executable_name like '%' -- Enter Executable Name
and cp.executable_application_id = e.application_id
and cp.executable_id = e.executable_id
and cp.enabled_flag = 'Y'
and rgu.request_unit_id = cp.concurrent_program_id
and rgu.unit_application_id = cp.application_id
and rgu.request_unit_type = 'P'
and rg.application_id = rgu.application_id
and rg.request_group_id = rgu.request_group_id
and rga.application_id = rgu.application_id
and ea.application_id = e.application_id
5. Request Set Details
select rst.USER_REQUEST_SET_NAME,
rs.REQUEST_SET_NAME,
rsst_start.USER_STAGE_NAME start_stage,
rss.DISPLAY_SEQUENCE stage_sequence,
rss.STAGE_NAME,
rss.REQUEST_SET_STAGE_ID,
rss.SUCCESS_LINK,
rss.WARNING_LINK,
rss.ERROR_LINK,
rsst.USER_STAGE_NAME,
rsst.DESCRIPTION stage_description,
rsp.sequence program_sequence,
cp.user_concurrent_program_name,
pa.*
from applsys.fnd_request_sets_tl rst,
applsys.fnd_request_sets rs,
applsys.fnd_request_set_stages_tl rsst_start,
applsys.fnd_request_set_stages rss,
applsys.fnd_request_set_stages_tl rsst,
applsys.fnd_request_set_programs rsp,
applsys.fnd_concurrent_programs_tl cp,
applsys.fnd_request_set_program_args pa
where rs.REQUEST_SET_NAME like '%' -- Enter Request Set Name
and rs.application_id = rst.application_id
and rs.REQUEST_SET_ID = rst.REQUEST_SET_ID
and rst.language = sys_context('USERENV', 'LANG')
and rsst_start.SET_APPLICATION_ID = rs.application_id
and rsst_start.REQUEST_SET_ID = rs.REQUEST_SET_ID
and rsst_start.REQUEST_SET_STAGE_ID = rs.START_STAGE
and rsst_start.language = 'US'
and rss.SET_APPLICATION_ID = rs.application_id
and rss.REQUEST_SET_ID = rs.REQUEST_SET_ID
and rsst.SET_APPLICATION_ID = rss.SET_APPLICATION_ID
and rsst.REQUEST_SET_ID = rss.REQUEST_SET_ID
and rsst.REQUEST_SET_STAGE_ID = rss.REQUEST_SET_STAGE_ID
and rsst.language = 'US'
and rsp.SET_APPLICATION_ID = rss.SET_APPLICATION_ID
and rsp.REQUEST_SET_ID = rss.REQUEST_SET_ID
and rsp.REQUEST_SET_STAGE_ID = rss.REQUEST_SET_STAGE_ID
and rsp.program_application_id = cp.application_id
and rsp.concurrent_program_id = cp.concurrent_program_id
and cp.language = 'US'
and pa.application_id (+) = rsp.SET_APPLICATION_ID
and pa.REQUEST_SET_ID (+) = rsp.REQUEST_SET_ID
and pa.REQUEST_SET_PROGRAM_ID (+) = rsp.REQUEST_SET_PROGRAM_ID
order by rst.USER_REQUEST_SET_NAME, rss.DISPLAY_SEQUENCE, rsp.sequence,
pa.DESCRIPTIVE_FLEX_APPL_ID, pa.DESCRIPTIVE_FLEXFIELD_NAME, pa.APPLICATION_COLUMN_NAME
1.Find Locks Info
SELECT sid, id1, id2, lmode, request, block
FROM v$lock
WHERE sid = &sid
2.Find Session Info
SELECT module, action, sid, serial# from v$session
where action like '%%'
3.Find Lock Mode
select a.sid, b.name, a.id1, a.lmode, a.request from v$lock a, sys.obj$ b
where b.obj# = a.id1
4.Find Locked Objects
select oracle_username, os_user_name, session_id, process,
locked_mode, o.owner, o.object_type, o.object_name
from v$locked_object l,
sys.dba_objects o
where l.object_id = o.object_id
order by o.owner, o.object_name, session_id
5.Control File Query
select * from v$controlfile
6.Folder Info Query
SELECT b.application_short_name,
d.responsibility_name,
a.OBJECT,
c.NAME Folder_Name
FROM apps.fnd_default_folders a,
apps.fnd_application b,
apps.fnd_folders c,
apps.fnd_responsibility_vl d
WHERE a.application_id = b.application_id
AND a.folder_id = c.folder_id
AND d.responsibility_id = - (a.user_id)
AND d.responsibility_name LIKE '%%'
ORDER BY b.application_short_name,
d.responsibility_name;
SELECT fcr.REQUEST_ID,
FU.USER_NAME,
fcr.PHASE_CODE,
fcr.STATUS_CODE,
(fcr.ACTUAL_COMPLETION_DATE – fcr.ACTUAL_START_DATE) * 24 * 60 TIME_MINS,
fcpt.USER_CONCURRENT_PROGRAM_NAME,
fcp.CONCURRENT_PROGRAM_NAME,
fcr.ACTUAL_START_DATE,
fcr.ACTUAL_COMPLETION_DATE,
fcp.CONCURRENT_PROGRAM_NAME
FROM apps.FND_CONCURRENT_REQUESTS FCR,
apps.FND_CONCURRENT_PROGRAMS FCP,
apps.FND_CONCURRENT_PROGRAMS_TL FCPT,
apps.FND_USER FU
WHERE 1=1
AND FCP.CONCURRENT_PROGRAM_ID=FCR.CONCURRENT_PROGRAM_ID
AND FCPT.CONCURRENT_PROGRAM_ID=FCP.CONCURRENT_PROGRAM_ID
AND FCR.REQUESTED_BY = FU.USER_ID
AND FU.USER_NAME = ‘&user’
ORDER BY FCR.REQUEST_ID DESC;
1. Concurrent Program Info
SELECT fcp.user_concurrent_program_name Concurrent_Program,
fdfcu.column_seq_num Sequence_number ,
fdfcu.default_value default_value ,
fdfcu.form_left_prompt prompt ,
fdfcu.description description ,
ffvs.flex_value_set_name ,
fl.meaning ,
fe.executable_name ,
fe.execution_file_name
FROM apps.fnd_concurrent_programs_vl fcp,
apps.fnd_lookups fl ,
apps.fnd_executables fe ,
apps.fnd_descr_flex_col_usage_vl fdfcu,
apps.fnd_application fa ,
apps.fnd_flex_value_sets ffvs
WHERE fcpt.user_concurrent_program_name = '&user_concurrent_program_name'
AND fdfcu.application_id = fa.application_id
AND fdfcu.descriptive_flex_context_code = 'Global Data Elements'
AND fdfcu.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name
AND fdfcu.flex_value_set_id = ffvs.flex_value_set_id
AND fcp.execution_method_code = fl.lookup_code
AND fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND fcp.executable_id = fe.executable_id
ORDER BY 1,
2 ,
3;
select cpt.USER_CONCURRENT_PROGRAM_NAME,
cpt.DESCRIPTION,
cp.CONCURRENT_PROGRAM_NAME,
a.APPLICATION_SHORT_NAME,
e.EXECUTABLE_NAME,
e.execution_file_name,
cp.CREATION_DATE,
uc.USER_NAME CREATOR,
cp.LAST_UPDATE_DATE,
ulu.USER_NAME UPDATER,
cp.ENABLED_FLAG,
cp.RUN_ALONE_FLAG,
cp.SRS_FLAG,
cp.PRINT_FLAG,
cp.SAVE_OUTPUT_FLAG,
cp.REQUIRED_STYLE,
cp.OUTPUT_PRINT_STYLE,
cp.PRINTER_NAME,
cp.MINIMUM_WIDTH,
cp.MINIMUM_LENGTH,
cp.OUTPUT_FILE_TYPE,
cp.ENABLE_TRACE,
cp.RESTART,
cp.NLS_COMPLIANT,
cp.REQUEST_SET_FLAG
from applsys.fnd_concurrent_programs cp,
applsys.fnd_concurrent_programs_tl cpt,
applsys.fnd_application a,
applsys.fnd_executables e,
applsys.fnd_user uc,
applsys.fnd_user ulu
where cp.CREATION_DATE > to_date('17-MAY-1995')-- Enter Creation Date
and cp.APPLICATION_ID = cpt.APPLICATION_ID
and cp.CONCURRENT_PROGRAM_ID = cpt.CONCURRENT_PROGRAM_ID
and cpt.LANGUAGE = sys_context('USERENV', 'LANG')
and cp.APPLICATION_ID = a.APPLICATION_ID
and cp.EXECUTABLE_APPLICATION_ID = e.APPLICATION_ID
and cp.EXECUTABLE_ID = e.EXECUTABLE_ID
and cp.CREATED_BY = uc.USER_ID
and cp.LAST_UPDATED_BY = ulu.USER_ID
and cpt.user_concurrent_program_name='Payables Open Interface Import'
order by cp.CONCURRENT_PROGRAM_NAME
2. Concurrent Program parameters
select cp.CONCURRENT_PROGRAM_NAME, cu.*, vs.FLEX_VALUE_SET_NAME
from applsys.fnd_concurrent_programs cp,
applsys.fnd_descr_flex_column_usages cu,
applsys.fnd_flex_value_sets vs
where cp.CREATION_DATE > to_date('17-MAY-1995') --Enter Creation Date
AND cu.application_id = cp.application_id
AND cu.descriptive_flexfield_name = '$SRS$.' || cp.concurrent_program_name
AND cu.FLEX_VALUE_SET_ID = vs.FLEX_VALUE_SET_ID
ANd cp.CONCURRENT_PROGRAM_ID=:CONCURRENT_PROGRAM_ID --(Enter CONCURRENT_PROGRAM_ID of CONCURRENT_PROGRAM)
order by cp.CONCURRENT_PROGRAM_NAME, cu.COLUMN_SEQ_NUM
3. Concurrent Request Details
select p.concurrent_program_name,
pt.user_concurrent_program_name,
rt.responsibility_name,
r.*
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_programs p,
applsys.fnd_concurrent_programs_tl pt,
applsys.fnd_responsibility rsp,
applsys.fnd_responsibility_tl rt
where r.request_id = &Request_ID -- Enter Request ID
and r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
and p.application_id = pt.application_id
and p.concurrent_program_id = pt.concurrent_program_id
and pt.language = sys_context('USERENV', 'LANG')
and r.responsibility_application_id = rsp.application_id
and r.responsibility_id = rsp.responsibility_id
and rsp.application_id = rt.application_id
and rsp.responsibility_id = rt.responsibility_id
and rt.language = sys_context('USERENV', 'LANG')
4. Request Group Details
select rga.application_short_name "Req Grp App",
rg.request_group_name "Request Group Name",
rg.request_group_code "Request Group Code",
rg.description "Request Group Desc",
cp.concurrent_program_name "Concurrent Program",
ea.application_short_name "Exec App",
e.executable_name "Executable",
rga.application_id "Req Grp App ID",
rg.request_group_id "Req Grp ID",
e.application_id "Exec App ID"
from applsys.fnd_executables e,
applsys.fnd_concurrent_programs cp,
applsys.fnd_request_group_units rgu,
applsys.fnd_request_groups rg,
applsys.fnd_application rga,
applsys.fnd_application ea
where e.application_id like '%' -- Enter Application ID
and e.executable_name like '%' -- Enter Executable Name
and cp.executable_application_id = e.application_id
and cp.executable_id = e.executable_id
and cp.enabled_flag = 'Y'
and rgu.request_unit_id = cp.concurrent_program_id
and rgu.unit_application_id = cp.application_id
and rgu.request_unit_type = 'P'
and rg.application_id = rgu.application_id
and rg.request_group_id = rgu.request_group_id
and rga.application_id = rgu.application_id
and ea.application_id = e.application_id
5. Request Set Details
select rst.USER_REQUEST_SET_NAME,
rs.REQUEST_SET_NAME,
rsst_start.USER_STAGE_NAME start_stage,
rss.DISPLAY_SEQUENCE stage_sequence,
rss.STAGE_NAME,
rss.REQUEST_SET_STAGE_ID,
rss.SUCCESS_LINK,
rss.WARNING_LINK,
rss.ERROR_LINK,
rsst.USER_STAGE_NAME,
rsst.DESCRIPTION stage_description,
rsp.sequence program_sequence,
cp.user_concurrent_program_name,
pa.*
from applsys.fnd_request_sets_tl rst,
applsys.fnd_request_sets rs,
applsys.fnd_request_set_stages_tl rsst_start,
applsys.fnd_request_set_stages rss,
applsys.fnd_request_set_stages_tl rsst,
applsys.fnd_request_set_programs rsp,
applsys.fnd_concurrent_programs_tl cp,
applsys.fnd_request_set_program_args pa
where rs.REQUEST_SET_NAME like '%' -- Enter Request Set Name
and rs.application_id = rst.application_id
and rs.REQUEST_SET_ID = rst.REQUEST_SET_ID
and rst.language = sys_context('USERENV', 'LANG')
and rsst_start.SET_APPLICATION_ID = rs.application_id
and rsst_start.REQUEST_SET_ID = rs.REQUEST_SET_ID
and rsst_start.REQUEST_SET_STAGE_ID = rs.START_STAGE
and rsst_start.language = 'US'
and rss.SET_APPLICATION_ID = rs.application_id
and rss.REQUEST_SET_ID = rs.REQUEST_SET_ID
and rsst.SET_APPLICATION_ID = rss.SET_APPLICATION_ID
and rsst.REQUEST_SET_ID = rss.REQUEST_SET_ID
and rsst.REQUEST_SET_STAGE_ID = rss.REQUEST_SET_STAGE_ID
and rsst.language = 'US'
and rsp.SET_APPLICATION_ID = rss.SET_APPLICATION_ID
and rsp.REQUEST_SET_ID = rss.REQUEST_SET_ID
and rsp.REQUEST_SET_STAGE_ID = rss.REQUEST_SET_STAGE_ID
and rsp.program_application_id = cp.application_id
and rsp.concurrent_program_id = cp.concurrent_program_id
and cp.language = 'US'
and pa.application_id (+) = rsp.SET_APPLICATION_ID
and pa.REQUEST_SET_ID (+) = rsp.REQUEST_SET_ID
and pa.REQUEST_SET_PROGRAM_ID (+) = rsp.REQUEST_SET_PROGRAM_ID
order by rst.USER_REQUEST_SET_NAME, rss.DISPLAY_SEQUENCE, rsp.sequence,
pa.DESCRIPTIVE_FLEX_APPL_ID, pa.DESCRIPTIVE_FLEXFIELD_NAME, pa.APPLICATION_COLUMN_NAME
1.Find Locks Info
SELECT sid, id1, id2, lmode, request, block
FROM v$lock
WHERE sid = &sid
2.Find Session Info
SELECT module, action, sid, serial# from v$session
where action like '%%'
3.Find Lock Mode
select a.sid, b.name, a.id1, a.lmode, a.request from v$lock a, sys.obj$ b
where b.obj# = a.id1
4.Find Locked Objects
select oracle_username, os_user_name, session_id, process,
locked_mode, o.owner, o.object_type, o.object_name
from v$locked_object l,
sys.dba_objects o
where l.object_id = o.object_id
order by o.owner, o.object_name, session_id
5.Control File Query
select * from v$controlfile
6.Folder Info Query
SELECT b.application_short_name,
d.responsibility_name,
a.OBJECT,
c.NAME Folder_Name
FROM apps.fnd_default_folders a,
apps.fnd_application b,
apps.fnd_folders c,
apps.fnd_responsibility_vl d
WHERE a.application_id = b.application_id
AND a.folder_id = c.folder_id
AND d.responsibility_id = - (a.user_id)
AND d.responsibility_name LIKE '%%'
ORDER BY b.application_short_name,
d.responsibility_name;
Concurrent Job Executed by User
SELECT fcr.REQUEST_ID,
FU.USER_NAME,
fcr.PHASE_CODE,
fcr.STATUS_CODE,
(fcr.ACTUAL_COMPLETION_DATE – fcr.ACTUAL_START_DATE) * 24 * 60 TIME_MINS,
fcpt.USER_CONCURRENT_PROGRAM_NAME,
fcp.CONCURRENT_PROGRAM_NAME,
fcr.ACTUAL_START_DATE,
fcr.ACTUAL_COMPLETION_DATE,
fcp.CONCURRENT_PROGRAM_NAME
FROM apps.FND_CONCURRENT_REQUESTS FCR,
apps.FND_CONCURRENT_PROGRAMS FCP,
apps.FND_CONCURRENT_PROGRAMS_TL FCPT,
apps.FND_USER FU
WHERE 1=1
AND FCP.CONCURRENT_PROGRAM_ID=FCR.CONCURRENT_PROGRAM_ID
AND FCPT.CONCURRENT_PROGRAM_ID=FCP.CONCURRENT_PROGRAM_ID
AND FCR.REQUESTED_BY = FU.USER_ID
AND FU.USER_NAME = ‘&user’
ORDER BY FCR.REQUEST_ID DESC;
Saturday, June 25, 2011
Use extended ASCII set as Delimtter for Oracle's q-quote operator in Oracle PL/SQL
Refer this blog to know about q-quote in Oracle
http://kerryosborne.oracle-guy.com/2009/08/quotes-in-strings-oracle-q-quote/
q'##' is a new format available in PL/SQL to declare quoted strings to avoid the escaping of quote(') character, where # or any other character defined here would be the new delimiter. Make sure that this delimiter character does not exist in .
A good approach here to avoid problems would be use the extra character available in ASCII as per he below table which will not be normally used in typed characters and can be a good delimitter to avoid most cases
CHR(128):€
CHR(129):
CHR(130):‚
CHR(131):ƒ
CHR(132):„
CHR(133):…
CHR(134):†
CHR(135):‡
CHR(136):ˆ
CHR(137):‰
CHR(138):Š
CHR(139):‹
CHR(140):Œ
CHR(141):
CHR(142):Ž
CHR(143):
CHR(144):
CHR(145):‘
CHR(146):’
CHR(147):“
CHR(148):”
CHR(149):•
CHR(150):–
CHR(151):—
CHR(152):˜
CHR(153):™
CHR(154):š
CHR(155):›
CHR(156):œ
CHR(157):
CHR(158):ž
CHR(159):Ÿ
CHR(160):
CHR(161):¡
CHR(162):¢
CHR(163):£
CHR(164):¤
CHR(165):¥
CHR(166):¦
CHR(167):§
CHR(168):¨
CHR(169):©
CHR(170):ª
CHR(171):«
CHR(172):¬
CHR(173):
CHR(174):®
CHR(175):¯
CHR(176):°
CHR(177):±
CHR(178):²
CHR(179):³
CHR(180):´
CHR(181):µ
CHR(182):¶
CHR(183):·
CHR(184):¸
CHR(185):¹
CHR(186):º
CHR(187):»
CHR(188):¼
CHR(189):½
CHR(190):¾
CHR(191):¿
CHR(192):À
CHR(193):Á
CHR(194):Â
CHR(195):Ã
CHR(196):Ä
CHR(197):Å
CHR(198):Æ
CHR(199):Ç
CHR(200):È
CHR(201):É
CHR(202):Ê
CHR(203):Ë
CHR(204):Ì
CHR(205):Í
CHR(206):Î
CHR(207):Ï
CHR(208):Ð
CHR(209):Ñ
CHR(210):Ò
CHR(211):Ó
CHR(212):Ô
CHR(213):Õ
CHR(214):Ö
CHR(215):×
CHR(216):Ø
CHR(217):Ù
CHR(218):Ú
CHR(219):Û
CHR(220):Ü
CHR(221):Ý
CHR(222):Þ
CHR(223):ß
CHR(224):à
CHR(225):á
CHR(226):â
CHR(227):ã
CHR(228):ä
CHR(229):å
CHR(230):æ
CHR(231):ç
CHR(232):è
CHR(233):é
CHR(234):ê
CHR(235):ë
CHR(236):ì
CHR(237):í
CHR(238):î
CHR(239):ï
CHR(240):ð
CHR(241):ñ
CHR(242):ò
CHR(243):ó
CHR(244):ô
CHR(245):õ
CHR(246):ö
CHR(247):÷
CHR(248):ø
CHR(249):ù
CHR(250):ú
CHR(251):û
CHR(252):ü
CHR(253):ý
CHR(254):þ
CHR(255):ÿ
http://kerryosborne.oracle-guy.com/2009/08/quotes-in-strings-oracle-q-quote/
q'#
A good approach here to avoid problems would be use the extra character available in ASCII as per he below table which will not be normally used in typed characters and can be a good delimitter to avoid most cases
CHR(128):€
CHR(129):
CHR(130):‚
CHR(131):ƒ
CHR(132):„
CHR(133):…
CHR(134):†
CHR(135):‡
CHR(136):ˆ
CHR(137):‰
CHR(138):Š
CHR(139):‹
CHR(140):Œ
CHR(141):
CHR(142):Ž
CHR(143):
CHR(144):
CHR(145):‘
CHR(146):’
CHR(147):“
CHR(148):”
CHR(149):•
CHR(150):–
CHR(151):—
CHR(152):˜
CHR(153):™
CHR(154):š
CHR(155):›
CHR(156):œ
CHR(157):
CHR(158):ž
CHR(159):Ÿ
CHR(160):
CHR(161):¡
CHR(162):¢
CHR(163):£
CHR(164):¤
CHR(165):¥
CHR(166):¦
CHR(167):§
CHR(168):¨
CHR(169):©
CHR(170):ª
CHR(171):«
CHR(172):¬
CHR(173):
CHR(174):®
CHR(175):¯
CHR(176):°
CHR(177):±
CHR(178):²
CHR(179):³
CHR(180):´
CHR(181):µ
CHR(182):¶
CHR(183):·
CHR(184):¸
CHR(185):¹
CHR(186):º
CHR(187):»
CHR(188):¼
CHR(189):½
CHR(190):¾
CHR(191):¿
CHR(192):À
CHR(193):Á
CHR(194):Â
CHR(195):Ã
CHR(196):Ä
CHR(197):Å
CHR(198):Æ
CHR(199):Ç
CHR(200):È
CHR(201):É
CHR(202):Ê
CHR(203):Ë
CHR(204):Ì
CHR(205):Í
CHR(206):Î
CHR(207):Ï
CHR(208):Ð
CHR(209):Ñ
CHR(210):Ò
CHR(211):Ó
CHR(212):Ô
CHR(213):Õ
CHR(214):Ö
CHR(215):×
CHR(216):Ø
CHR(217):Ù
CHR(218):Ú
CHR(219):Û
CHR(220):Ü
CHR(221):Ý
CHR(222):Þ
CHR(223):ß
CHR(224):à
CHR(225):á
CHR(226):â
CHR(227):ã
CHR(228):ä
CHR(229):å
CHR(230):æ
CHR(231):ç
CHR(232):è
CHR(233):é
CHR(234):ê
CHR(235):ë
CHR(236):ì
CHR(237):í
CHR(238):î
CHR(239):ï
CHR(240):ð
CHR(241):ñ
CHR(242):ò
CHR(243):ó
CHR(244):ô
CHR(245):õ
CHR(246):ö
CHR(247):÷
CHR(248):ø
CHR(249):ù
CHR(250):ú
CHR(251):û
CHR(252):ü
CHR(253):ý
CHR(254):þ
CHR(255):ÿ
Oracle PL/SQL Find Matching Braces/Brackets or any character
It is normally difficult to find matching braces while parsing.
The below code snippet will help you to find the matching braces.
This will save the sub_strings into a record type with start_pos and end_pos.
The logic is like this
Find first open brace
Loop till last open brace
Find first close brace after this open brace
Loop
Count number of open braces and close braces between this open brace and close brace.
If open count = close count -- The logic sits here
Then we have found a matching brace substring.
End if;
Find next close brace after this open brace
End Loop
The logic is like this
Find first open brace
Loop till last open brace
Find first close brace after this open brace
Loop
Count number of open braces and close braces between this open brace and close brace.
If open count = close count -- The logic sits here
Then we have found a matching brace substring.
End if;
Find next close brace after this open brace
End Loop
Find Next Open Brace
Loop End
Loop End
declarel_expression varchar2(1000) := q'# ( 1 ( 2 ( 3 ) ( 4 ) ) ( 5 ) ( 6 ) ) ( 7 ( 8 ) ( 9 ) ) #';type pos_record is record (open_pos number, end_pos number, value varchar2(400));type pos_tab_type is table of pos_record index by binary_integer;l_pos_tab pos_tab_type;l_index number := 1;l_open_pos number;l_end_pos number;l_open_count number;l_end_count number;l_sub_expression varchar2(1000);beginl_open_pos := instr(l_expression, '(', 1);while l_open_pos > 0 loopl_pos_tab(l_index).open_pos := l_open_pos;l_end_pos := instr(l_expression, ')', l_open_pos+1);l_sub_expression := substr(l_expression, l_open_pos, l_end_pos-l_open_pos+1);l_open_count := length(l_sub_expression)-length(replace(l_sub_expression,'('));l_end_count := length(l_sub_expression)-length(replace(l_sub_expression,')'));while l_open_count <> l_end_count loopl_end_pos := instr(l_expression, ')', l_end_pos+1);l_sub_expression := substr(l_expression, l_open_pos, l_end_pos-l_open_pos+1);l_open_count := length(l_sub_expression)-length(replace(l_sub_expression,'('));l_end_count := length(l_sub_expression)-length(replace(l_sub_expression,')'));end loop;l_pos_tab(l_index).end_pos := l_end_pos;l_pos_tab(l_index).value := l_sub_expression;l_open_pos := instr(l_expression, '(', l_open_pos+1);l_index := l_index + 1;end loop;l_index := l_pos_tab.first;while l_index is not null loopdbms_output.put_line(l_index||'>'||l_pos_tab(l_index).open_pos||'-'||l_pos_tab(l_index).end_pos||':'||l_pos_tab(l_index).value);l_index := l_pos_tab.next(l_index);end loop;end;
Output-------
1>2-36:( 1 ( 2 ( 3 ) ( 4 ) ) ( 5 ) ( 6 ) )2>6-22:( 2 ( 3 ) ( 4 ) )3>10-14:( 3 )4>16-20:( 4 )5>24-28:( 5 )6>30-34:( 6 )7>38-54:( 7 ( 8 ) ( 9 ) )8>42-46:( 8 )9>48-52:( 9 )
Monday, June 13, 2011
Out VARCHAR2 parameters in PL/SQL
The PL/SQL takes the maximum size of output variable for VARCHAR2 from the calling block/procedure.
Consider the following example.
--
create or replace package test_pkg as
procedure test_val(i_num in number, o_expression out varchar2);
end test_pkg;
--
--
create or replace package body test_pkg as
procedure test_val(i_num in number, o_expression out varchar2) as
begin
o_expression := LPAD('>', i_num);
end;
end;
--
declare
l_expression VARCHAR2(5);
begin
test_pkg.test_val(i_num => 5,
o_expression => l_expression);
dbms_output.put_line('Output:'||l_expression);
end;
Output: >
The value of l_expression is 5. so the maximum permissible size of o_expression is 5. If it increases as per the below execution, it will give an exception.
Error report:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DEMO.TEST_PKG", line 5
ORA-06512: at line 4
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
Thursday, June 2, 2011
PL/SQL to T-SQL CONNECT BY PRIOR sys_connect_by_path
PL/SQL has advanced keyword CONNECT BY PRIOR to handle parent child hierarchy in a RECURSIVE manner. Also along it has given a function sys_connect_by_path to point the previous rows in the hierarchy as well as level keyword to fetch the current level.
But T-SQL does not support these functions. But it supports the RECURSIVE WITH clause to fetch the hierarchy.
In PL/SQL
SELECT
LEVEL AS RLEVEL,
EMPLOYEE_ID,
MANAGER_ID,
sys_connect_by_path(EMPLOYEE_ID, '>') AS HIER_PATH,
CASE
WHEN EMPLOYEE_ID IN (SELECT MANAGER_ID FROM EMPLOYEES) THEN 0 ELSE 1 END AS IS_LEAF,
rownum as ROW_NUMBER
FROM EMPLOYEES
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
START WITH MANAGER_ID IS NULL;
Note: If you want the child employees to be fetched in order of employee_id we might have to alias
EMPLOYEES as (SELECT * FROM EMPLOYEES ORDER BY EMPLOYEE_ID)
In T-SQL
WITH connect_loop AS
(
SELECT employee_id,
manager_id,
1 as rlevel,
cast('>'+cast(employee_id as varchar) as varchar) as hier_path
FROM employees
WHERE manager_id is null
UNION ALL
SELECT T.employee_id,
T.manager_id,
CTE.rlevel + 1 as rlevel,
cast(CTE.hier_path+'>'+cast(T.employee_id as varchar) as varchar) as hier_path
FROM employees T INNER JOIN connect_loop CTE ON T.manager_id = CTE.employee_id
)
select
rlevel,
employee_id,
manager_id,
hier_path,
case when EMPLOYEE_ID in (select MANAGER_ID from EMPLOYEES ) then 0 else 1 end as IS_LEAF,
ROW_NUMBER() OVER(ORDER BY employee_id) as RowNumber
from CONNECT_LOOP
order by 4;
Note: Oracle orders the CONNECT BY beautifully and rownum also is generated sequentially. But in T-SQL we have to introduce order by 4 to format the results as per PL/SQL. This is only a workaround for employee data which is not complete as it sorts hier_path as VARCHAR.
So T-SQL here using RECURSION. But when I tried this recursion in PL/SQL it is giving an error
WITH CONNECT_LOOP AS
(
select employee_id, manager_id, 1 as rlevel, '>'||employee_id as hier_path
from EMPLOYEES
where MANAGER_ID is null
UNION ALL
SELECT T.EMPLOYEE_ID, T.MANAGER_ID, CTE.RLEVEL + 1, CTE.HIER_PATH||'>'||T.EMPLOYEE_ID AS HIER_PATH
FROM EMPLOYEES T, CONNECT_LOOP CTE
WHERE T.manager_id = CTE.employee_id
)
select
employee_id,
manager_id,
rlevel,
hier_path,
case when EMPLOYEE_ID in (select MANAGER_ID from EMPLOYEES ) then 0 else 1 end as IS_LEAF,
ROW_NUMBER() OVER(ORDER BY employee_id) as RowNumber
from CONNECT_LOOP
ORDER BY 6;
ORA-32031: illegal reference of a query name in WITH clause
32031. 00000 - "illegal reference of a query name in WITH clause"
*Cause: forward or recursive reference of a query name in WITH clause is not
allowed.
*Action: Correct query statement, then retry.
Error at Line: 8 Column: 18
CREATE TABLE T-SQL
CREATE TABLE "EMPLOYEES"
(
"EMPLOYEE_ID" INT,
"FIRST_NAME" VARCHAR(20),
"LAST_NAME" VARCHAR(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL,
"EMAIL" VARCHAR(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL,
"PHONE_NUMBER" VARCHAR(20),
"HIRE_DATE" DATETIME CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL,
"JOB_ID" VARCHAR(10) CONSTRAINT "EMP_JOB_NN" NOT NULL,
"SALARY" FLOAT,
"COMMISSION_PCT" FLOAT,
"MANAGER_ID" INT,
"DEPARTMENT_ID" INT,
CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY("EMPLOYEE_ID"),
CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY("MANAGER_ID") REFERENCES "EMPLOYEES"("EMPLOYEE_ID"),
);
CREATE TABLE PL/SQL
CREATE TABLE "EMPLOYEES"
(
"EMPLOYEE_ID" NUMBER(6, 0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL,
"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL,
"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL,
"SALARY" NUMBER(8, 2),
"COMMISSION_PCT" NUMBER(2, 2),
"MANAGER_ID" NUMBER(6, 0),
"DEPARTMENT_ID" NUMBER(4, 0),
CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY("EMPLOYEE_ID") ENABLE,
CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY("MANAGER_ID") REFERENCES "EMPLOYEES"("EMPLOYEE_ID")
);
DATA IN PL/SQL
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (198,'Donald','OConnell','DOCONNEL','650.507.9833',to_date('21-JUN-99','DD-MON-RR'),'SH_CLERK',2600,null,124,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (199,'Douglas','Grant','DGRANT','650.507.9844',to_date('13-JAN-00','DD-MON-RR'),'SH_CLERK',2600,null,124,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (200,'Jennifer','Whalen','JWHALEN','515.123.4444',to_date('17-SEP-87','DD-MON-RR'),'AD_ASST',4400,null,101,10);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (201,'Michael','Hartstein','MHARTSTE','515.123.5555',to_date('17-FEB-96','DD-MON-RR'),'MK_MAN',13000,null,100,20);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (202,'Pat','Fay','PFAY','603.123.6666',to_date('17-AUG-97','DD-MON-RR'),'MK_REP',6000,null,201,20);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (203,'Susan','Mavris','SMAVRIS','515.123.7777',to_date('07-JUN-94','DD-MON-RR'),'HR_REP',6500,null,101,40);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (204,'Hermann','Baer','HBAER','515.123.8888',to_date('07-JUN-94','DD-MON-RR'),'PR_REP',10000,null,101,70);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (205,'Shelley','Higgins','SHIGGINS','515.123.8080',to_date('07-JUN-94','DD-MON-RR'),'AC_MGR',12000,null,101,110);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (206,'William','Gietz','WGIETZ','515.123.8181',to_date('07-JUN-94','DD-MON-RR'),'AC_ACCOUNT',8300,null,205,110);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (100,'Steven','King','SKING','515.123.4567',to_date('17-JUN-87','DD-MON-RR'),'AD_PRES',24000,null,null,90);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (101,'Neena','Kochhar','NKOCHHAR','515.123.4568',to_date('21-SEP-89','DD-MON-RR'),'AD_VP',17000,null,100,90);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (102,'Lex','De Haan','LDEHAAN','515.123.4569',to_date('13-JAN-93','DD-MON-RR'),'AD_VP',17000,null,100,90);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (103,'Alexander','Hunold','AHUNOLD','590.423.4567',to_date('03-JAN-90','DD-MON-RR'),'IT_PROG',9000,null,102,60);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (104,'Bruce','Ernst','BERNST','590.423.4568',to_date('21-MAY-91','DD-MON-RR'),'IT_PROG',6000,null,103,60);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (105,'David','Austin','DAUSTIN','590.423.4569',to_date('25-JUN-97','DD-MON-RR'),'IT_PROG',4800,null,103,60);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (106,'Valli','Pataballa','VPATABAL','590.423.4560',to_date('05-FEB-98','DD-MON-RR'),'IT_PROG',4800,null,103,60);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (107,'Diana','Lorentz','DLORENTZ','590.423.5567',to_date('07-FEB-99','DD-MON-RR'),'IT_PROG',4200,null,103,60);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (108,'Nancy','Greenberg','NGREENBE','515.124.4569',to_date('17-AUG-94','DD-MON-RR'),'FI_MGR',12000,null,101,100);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (109,'Daniel','Faviet','DFAVIET','515.124.4169',to_date('16-AUG-94','DD-MON-RR'),'FI_ACCOUNT',9000,null,108,100);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (110,'John','Chen','JCHEN','515.124.4269',to_date('28-SEP-97','DD-MON-RR'),'FI_ACCOUNT',8200,null,108,100);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (111,'Ismael','Sciarra','ISCIARRA','515.124.4369',to_date('30-SEP-97','DD-MON-RR'),'FI_ACCOUNT',7700,null,108,100);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (112,'Jose Manuel','Urman','JMURMAN','515.124.4469',to_date('07-MAR-98','DD-MON-RR'),'FI_ACCOUNT',7800,null,108,100);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (113,'Luis','Popp','LPOPP','515.124.4567',to_date('07-DEC-99','DD-MON-RR'),'FI_ACCOUNT',6900,null,108,100);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (114,'Den','Raphaely','DRAPHEAL','515.127.4561',to_date('07-DEC-94','DD-MON-RR'),'PU_MAN',11000,null,100,30);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (115,'Alexander','Khoo','AKHOO','515.127.4562',to_date('18-MAY-95','DD-MON-RR'),'PU_CLERK',3100,null,114,30);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (116,'Shelli','Baida','SBAIDA','515.127.4563',to_date('24-DEC-97','DD-MON-RR'),'PU_CLERK',2900,null,114,30);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (117,'Sigal','Tobias','STOBIAS','515.127.4564',to_date('24-JUL-97','DD-MON-RR'),'PU_CLERK',2800,null,114,30);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (118,'Guy','Himuro','GHIMURO','515.127.4565',to_date('15-NOV-98','DD-MON-RR'),'PU_CLERK',2600,null,114,30);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (119,'Karen','Colmenares','KCOLMENA','515.127.4566',to_date('10-AUG-99','DD-MON-RR'),'PU_CLERK',2500,null,114,30);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (120,'Matthew','Weiss','MWEISS','650.123.1234',to_date('18-JUL-96','DD-MON-RR'),'ST_MAN',8000,null,100,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (121,'Adam','Fripp','AFRIPP','650.123.2234',to_date('10-APR-97','DD-MON-RR'),'ST_MAN',8200,null,100,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (122,'Payam','Kaufling','PKAUFLIN','650.123.3234',to_date('01-MAY-95','DD-MON-RR'),'ST_MAN',7900,null,100,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (123,'Shanta','Vollman','SVOLLMAN','650.123.4234',to_date('10-OCT-97','DD-MON-RR'),'ST_MAN',6500,null,100,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (124,'Kevin','Mourgos','KMOURGOS','650.123.5234',to_date('16-NOV-99','DD-MON-RR'),'ST_MAN',5800,null,100,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (125,'Julia','Nayer','JNAYER','650.124.1214',to_date('16-JUL-97','DD-MON-RR'),'ST_CLERK',3200,null,120,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (126,'Irene','Mikkilineni','IMIKKILI','650.124.1224',to_date('28-SEP-98','DD-MON-RR'),'ST_CLERK',2700,null,120,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (127,'James','Landry','JLANDRY','650.124.1334',to_date('14-JAN-99','DD-MON-RR'),'ST_CLERK',2400,null,120,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (128,'Steven','Markle','SMARKLE','650.124.1434',to_date('08-MAR-00','DD-MON-RR'),'ST_CLERK',2200,null,120,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (129,'Laura','Bissot','LBISSOT','650.124.5234',to_date('20-AUG-97','DD-MON-RR'),'ST_CLERK',3300,null,121,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (130,'Mozhe','Atkinson','MATKINSO','650.124.6234',to_date('30-OCT-97','DD-MON-RR'),'ST_CLERK',2800,null,121,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (131,'James','Marlow','JAMRLOW','650.124.7234',to_date('16-FEB-97','DD-MON-RR'),'ST_CLERK',2500,null,121,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (132,'TJ','Olson','TJOLSON','650.124.8234',to_date('10-APR-99','DD-MON-RR'),'ST_CLERK',2100,null,121,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (133,'Jason','Mallin','JMALLIN','650.127.1934',to_date('14-JUN-96','DD-MON-RR'),'ST_CLERK',3300,null,122,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (134,'Michael','Rogers','MROGERS','650.127.1834',to_date('26-AUG-98','DD-MON-RR'),'ST_CLERK',2900,null,122,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (135,'Ki','Gee','KGEE','650.127.1734',to_date('12-DEC-99','DD-MON-RR'),'ST_CLERK',2400,null,122,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (136,'Hazel','Philtanker','HPHILTAN','650.127.1634',to_date('06-FEB-00','DD-MON-RR'),'ST_CLERK',2200,null,122,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (137,'Renske','Ladwig','RLADWIG','650.121.1234',to_date('14-JUL-95','DD-MON-RR'),'ST_CLERK',3600,null,123,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (138,'Stephen','Stiles','SSTILES','650.121.2034',to_date('26-OCT-97','DD-MON-RR'),'ST_CLERK',3200,null,123,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (139,'John','Seo','JSEO','650.121.2019',to_date('12-FEB-98','DD-MON-RR'),'ST_CLERK',2700,null,123,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (140,'Joshua','Patel','JPATEL','650.121.1834',to_date('06-APR-98','DD-MON-RR'),'ST_CLERK',2500,null,123,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (141,'Trenna','Rajs','TRAJS','650.121.8009',to_date('17-OCT-95','DD-MON-RR'),'ST_CLERK',3500,null,124,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (142,'Curtis','Davies','CDAVIES','650.121.2994',to_date('29-JAN-97','DD-MON-RR'),'ST_CLERK',3100,null,124,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (143,'Randall','Matos','RMATOS','650.121.2874',to_date('15-MAR-98','DD-MON-RR'),'ST_CLERK',2600,null,124,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (144,'Peter','Vargas','PVARGAS','650.121.2004',to_date('09-JUL-98','DD-MON-RR'),'ST_CLERK',2500,null,124,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (145,'John','Russell','JRUSSEL','011.44.1344.429268',to_date('01-OCT-96','DD-MON-RR'),'SA_MAN',14000,0.4,100,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (146,'Karen','Partners','KPARTNER','011.44.1344.467268',to_date('05-JAN-97','DD-MON-RR'),'SA_MAN',13500,0.3,100,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278',to_date('10-MAR-97','DD-MON-RR'),'SA_MAN',12000,0.3,100,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268',to_date('15-OCT-99','DD-MON-RR'),'SA_MAN',11000,0.3,100,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018',to_date('29-JAN-00','DD-MON-RR'),'SA_MAN',10500,0.2,100,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (150,'Peter','Tucker','PTUCKER','011.44.1344.129268',to_date('30-JAN-97','DD-MON-RR'),'SA_REP',10000,0.3,145,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (151,'David','Bernstein','DBERNSTE','011.44.1344.345268',to_date('24-MAR-97','DD-MON-RR'),'SA_REP',9500,0.25,145,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (152,'Peter','Hall','PHALL','011.44.1344.478968',to_date('20-AUG-97','DD-MON-RR'),'SA_REP',9000,0.25,145,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (153,'Christopher','Olsen','COLSEN','011.44.1344.498718',to_date('30-MAR-98','DD-MON-RR'),'SA_REP',8000,0.2,145,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668',to_date('09-DEC-98','DD-MON-RR'),'SA_REP',7500,0.2,145,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508',to_date('23-NOV-99','DD-MON-RR'),'SA_REP',7000,0.15,145,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (156,'Janette','King','JKING','011.44.1345.429268',to_date('30-JAN-96','DD-MON-RR'),'SA_REP',10000,0.35,146,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (157,'Patrick','Sully','PSULLY','011.44.1345.929268',to_date('04-MAR-96','DD-MON-RR'),'SA_REP',9500,0.35,146,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (158,'Allan','McEwen','AMCEWEN','011.44.1345.829268',to_date('01-AUG-96','DD-MON-RR'),'SA_REP',9000,0.35,146,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (159,'Lindsey','Smith','LSMITH','011.44.1345.729268',to_date('10-MAR-97','DD-MON-RR'),'SA_REP',8000,0.3,146,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (160,'Louise','Doran','LDORAN','011.44.1345.629268',to_date('15-DEC-97','DD-MON-RR'),'SA_REP',7500,0.3,146,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (161,'Sarath','Sewall','SSEWALL','011.44.1345.529268',to_date('03-NOV-98','DD-MON-RR'),'SA_REP',7000,0.25,146,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (162,'Clara','Vishney','CVISHNEY','011.44.1346.129268',to_date('11-NOV-97','DD-MON-RR'),'SA_REP',10500,0.25,147,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (163,'Danielle','Greene','DGREENE','011.44.1346.229268',to_date('19-MAR-99','DD-MON-RR'),'SA_REP',9500,0.15,147,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (164,'Mattea','Marvins','MMARVINS','011.44.1346.329268',to_date('24-JAN-00','DD-MON-RR'),'SA_REP',7200,0.1,147,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (165,'David','Lee','DLEE','011.44.1346.529268',to_date('23-FEB-00','DD-MON-RR'),'SA_REP',6800,0.1,147,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (166,'Sundar','Ande','SANDE','011.44.1346.629268',to_date('24-MAR-00','DD-MON-RR'),'SA_REP',6400,0.1,147,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (167,'Amit','Banda','ABANDA','011.44.1346.729268',to_date('21-APR-00','DD-MON-RR'),'SA_REP',6200,0.1,147,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (168,'Lisa','Ozer','LOZER','011.44.1343.929268',to_date('11-MAR-97','DD-MON-RR'),'SA_REP',11500,0.25,148,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (169,'Harrison','Bloom','HBLOOM','011.44.1343.829268',to_date('23-MAR-98','DD-MON-RR'),'SA_REP',10000,0.2,148,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (170,'Tayler','Fox','TFOX','011.44.1343.729268',to_date('24-JAN-98','DD-MON-RR'),'SA_REP',9600,0.2,148,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (171,'William','Smith','WSMITH','011.44.1343.629268',to_date('23-FEB-99','DD-MON-RR'),'SA_REP',7400,0.15,148,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (172,'Elizabeth','Bates','EBATES','011.44.1343.529268',to_date('24-MAR-99','DD-MON-RR'),'SA_REP',7300,0.15,148,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (173,'Sundita','Kumar','SKUMAR','011.44.1343.329268',to_date('21-APR-00','DD-MON-RR'),'SA_REP',6100,0.1,148,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (174,'Ellen','Abel','EABEL','011.44.1644.429267',to_date('11-MAY-96','DD-MON-RR'),'SA_REP',11000,0.3,149,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266',to_date('19-MAR-97','DD-MON-RR'),'SA_REP',8800,0.25,149,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265',to_date('24-MAR-98','DD-MON-RR'),'SA_REP',8600,0.2,149,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (177,'Jack','Livingston','JLIVINGS','011.44.1644.429264',to_date('23-APR-98','DD-MON-RR'),'SA_REP',8400,0.2,149,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (178,'Kimberely','Grant','KGRANT','011.44.1644.429263',to_date('24-MAY-99','DD-MON-RR'),'SA_REP',7000,0.15,149,null);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (179,'Charles','Johnson','CJOHNSON','011.44.1644.429262',to_date('04-JAN-00','DD-MON-RR'),'SA_REP',6200,0.1,149,80);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (180,'Winston','Taylor','WTAYLOR','650.507.9876',to_date('24-JAN-98','DD-MON-RR'),'SH_CLERK',3200,null,120,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (181,'Jean','Fleaur','JFLEAUR','650.507.9877',to_date('23-FEB-98','DD-MON-RR'),'SH_CLERK',3100,null,120,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (182,'Martha','Sullivan','MSULLIVA','650.507.9878',to_date('21-JUN-99','DD-MON-RR'),'SH_CLERK',2500,null,120,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (183,'Girard','Geoni','GGEONI','650.507.9879',to_date('03-FEB-00','DD-MON-RR'),'SH_CLERK',2800,null,120,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (184,'Nandita','Sarchand','NSARCHAN','650.509.1876',to_date('27-JAN-96','DD-MON-RR'),'SH_CLERK',4200,null,121,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (185,'Alexis','Bull','ABULL','650.509.2876',to_date('20-FEB-97','DD-MON-RR'),'SH_CLERK',4100,null,121,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (186,'Julia','Dellinger','JDELLING','650.509.3876',to_date('24-JUN-98','DD-MON-RR'),'SH_CLERK',3400,null,121,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (187,'Anthony','Cabrio','ACABRIO','650.509.4876',to_date('07-FEB-99','DD-MON-RR'),'SH_CLERK',3000,null,121,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (188,'Kelly','Chung','KCHUNG','650.505.1876',to_date('14-JUN-97','DD-MON-RR'),'SH_CLERK',3800,null,122,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (189,'Jennifer','Dilly','JDILLY','650.505.2876',to_date('13-AUG-97','DD-MON-RR'),'SH_CLERK',3600,null,122,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (190,'Timothy','Gates','TGATES','650.505.3876',to_date('11-JUL-98','DD-MON-RR'),'SH_CLERK',2900,null,122,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (191,'Randall','Perkins','RPERKINS','650.505.4876',to_date('19-DEC-99','DD-MON-RR'),'SH_CLERK',2500,null,122,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (192,'Sarah','Bell','SBELL','650.501.1876',to_date('04-FEB-96','DD-MON-RR'),'SH_CLERK',4000,null,123,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (193,'Britney','Everett','BEVERETT','650.501.2876',to_date('03-MAR-97','DD-MON-RR'),'SH_CLERK',3900,null,123,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (194,'Samuel','McCain','SMCCAIN','650.501.3876',to_date('01-JUL-98','DD-MON-RR'),'SH_CLERK',3200,null,123,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (195,'Vance','Jones','VJONES','650.501.4876',to_date('17-MAR-99','DD-MON-RR'),'SH_CLERK',2800,null,123,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (196,'Alana','Walsh','AWALSH','650.507.9811',to_date('24-APR-98','DD-MON-RR'),'SH_CLERK',3100,null,124,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (197,'Kevin','Feeney','KFEENEY','650.507.9822',to_date('23-MAY-98','DD-MON-RR'),'SH_CLERK',3000,null,124,50);
DATA IN T-SQL
For T-SQL the part to_date('24-APR-98','DD-MON-RR')should be changed to cast('24-APR-98' as datetime)
Subscribe to:
Posts (Atom)