:new.ToStagingDt := getutcdate();
end if;
:new.LstUpd := GetUTCDate;
:new.UsrName := Core_AUD.current_user;
if :new.Lck is null then
:new.Lck := 0;
end if;
end;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER TR_BU_TBL_TMX_ACTIVITY
before update on TBL_TMX_Activity
for each row
begin
:new.LstUpd := GetUTCDate;
:new.UsrName := Core_AUD.current_user;
end;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER tU_Activity_1
BEFORE UPDATE OF PrntActFK, Ordinal ON TBL_TMX_Activity
BEGIN
IF NOT Core_Trigger.trig_activityupd_running AND NOT Core_Trigger.trig_activityins_running THEN
Core_Trigger.activityupd_clear;
END IF;
END tU_Activity_1;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER tU_Activity_2
BEFORE UPDATE OF PrntActFK, Ordinal ON TBL_TMX_Activity
FOR EACH ROW
BEGIN
IF NOT Core_Trigger.trig_activityupd_running AND NOT Core_Trigger.trig_activityins_running THEN
Core_Trigger.activityupd_add(:OLD.Activity_PK, :OLD.PrntActFK, :OLD.Ordinal, :OLD.LevelVal,
:OLD.RootActivityFK,
:NEW.Activity_PK, :NEW.PrntActFK, :NEW.Ordinal, :NEW.LevelVal,
:NEW.RootActivityFK);
END IF;
END tU_Activity_2;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER tU_Activity_3
AFTER UPDATE OF PrntActFK, Ordinal ON TBL_TMX_Activity
BEGIN
IF NOT Core_Trigger.trig_activityupd_running AND NOT Core_Trigger.trig_activityins_running THEN
Core_Trigger.activityupd_process;
END IF;
END tU_Activity_3;
/
SHOW ERRORS;
ALTER TABLE TBL_TMX_ACTIVITY ADD (
CONSTRAINT PK_TBL_TMX_ACTIVITY
PRIMARY KEY
(ACTIVITY_PK)
USING INDEX);
ALTER TABLE TBL_TMX_ACTIVITY ADD (
CONSTRAINT FK_ACTIVITYLABELFK_5REEVH0A
FOREIGN KEY (ACTIVITYLABELFK)
REFERENCES ACTLABEL (ACTLABEL_PK)
ON DELETE SET NULL);
ALTER TABLE TBL_TMX_ACTIVITY ADD (
CONSTRAINT FK_DEFAPPREMPFK_5REEVH0C
FOREIGN KEY (DEFAPPREMPFK)
REFERENCES TBLEMP (EMP_PK)
ON DELETE SET NULL);
ALTER TABLE TBL_TMX_ACTIVITY ADD (
CONSTRAINT FK_OWNEREMPFK_5REEVH0B
FOREIGN KEY (OWNEREMPFK)
REFERENCES TBLEMP (EMP_PK)
ON DELETE SET NULL);
PROMPT "Table TBL_TMX_ACTIVITY End "
PROMPT "Table INST Start"
CREATE TABLE INST
(
INST_PK NUMBER(10) NOT NULL,
INST_VENFK NUMBER(10),
INST_NO VARCHAR2(75 BYTE),
INST_TTL VARCHAR2(150 BYTE),
INST_LNAME VARCHAR2(105 BYTE) NOT NULL,
INST_FNAME VARCHAR2(105 BYTE) NOT NULL,
INST_TITLE VARCHAR2(105 BYTE),
INST_MI VARCHAR2(105 BYTE),
INST_SUFFIX VARCHAR2(15 BYTE),
INST_STARTDT DATE,
INST_FULLNAME VARCHAR2(1536 BYTE),
INST_ADD1 VARCHAR2(150 BYTE),
INST_ADD2 VARCHAR2(150 BYTE),
INST_INTERNALIND NUMBER(1) NOT NULL,
INST_CITY VARCHAR2(90 BYTE),
INST_STATE VARCHAR2(105 BYTE),
INST_CNTRY VARCHAR2(105 BYTE),
INST_ZIP VARCHAR2(60 BYTE),
INST_EMAIL VARCHAR2(768 BYTE),
INST_PHN1 VARCHAR2(90 BYTE),
INST_PHN2 VARCHAR2(90 BYTE),
INST_FAX VARCHAR2(90 BYTE),
INST_URL VARCHAR2(765 BYTE),
INST_NOTE CLOB,
INST_LSTUPD DATE NOT NULL,
INST_USRNAME VARCHAR2(90 BYTE) NOT NULL,
INST_LCK NUMBER(10) NOT NULL,
INST_EMPFK NUMBER(10),
INST_TIMESTAMP NUMBER(10),
INST_TIMEZONEFK NUMBER(10)
);
CREATE INDEX FT_TABLE_GVRRF12M ON INST
(INST_NO)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');
CREATE INDEX FT_TABLE_GVRRF12N ON INST
(INST_LNAME)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');
CREATE INDEX FT_TABLE_GVRRF12O ON INST
(INST_FNAME)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');
CREATE INDEX FT_TABLE_GVRRF12P ON INST
(INST_FULLNAME)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');
CREATE UNIQUE INDEX PK_INST ON INST
(INST_PK);
CREATE INDEX IDX_INST_3W0DYX1Z ON INST
(INST_LNAME);
CREATE INDEX IDX_INST_3W0DYX20 ON INST
(INST_FNAME);
CREATE INDEX IDX_INST_3W0DYX21 ON INST
(INST_EMPFK, INST_PK);
CREATE OR REPLACE TRIGGER tD_Inst_1
BEFORE DELETE ON inst
BEGIN
Core_Trigger.Inst_clear;
END tD_Inst_1;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER tD_Inst_2
BEFORE DELETE ON inst
FOR EACH ROW
BEGIN
core_trigger.Inst_Add(:OLD.Inst_EmpFK);
END tD_inst_2;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER tD_Inst_3
AFTER DELETE ON Inst
BEGIN
-- Delete Instructors from VCS tables
Core_Trigger.Inst_process;
END tD_Inst_3;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER TR_BI_INST
before insert on Inst
for each row
begin
if :new.Inst_InternalInd is null then
:new.Inst_InternalInd := 0;
end if;
:new.Inst_LstUpd := GetUTCDate;
:new.Inst_UsrName := Core_AUD.current_user;
if :new.Inst_Lck is null then
:new.Inst_Lck := 0;
end if;
if :new.Inst_EmpFK is null then
:new.Inst_EmpFK := null;
end if;
if :new.Inst_InternalInd is null then
:new.Inst_InternalInd := 0;
elsif :new.Inst_InternalInd != 0 then
:new.Inst_InternalInd := 1;
end if;
end;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER TR_BU_INST
before update on Inst
for each row
begin
:new.Inst_LstUpd := GetUTCDate;
:new.Inst_UsrName := Core_AUD.current_user;
if :new.Inst_InternalInd is null then
:new.Inst_InternalInd := 0;
elsif :new.Inst_InternalInd != 0 then
:new.Inst_InternalInd := 1;
end if;
end;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER tr_inst_search
BEFORE INSERT OR UPDATE OF inst_fname, inst_mi, inst_lname, inst_no, inst_email ON inst
FOR EACH ROW
BEGIN
-- This is used for searching on multiple columns at once.
-- We're using this method instead of a multi_column_datastore so that
-- the method is the same in both Oracle and SQL Server
-- This concatenates each field, if it exists, with a blank
:NEW.Inst_FullName := SUBSTR(TRIM(TRIM(TRIM(TRIM(:NEW.Inst_FName||' '||:NEW.Inst_MI)||'
'||:NEW.Inst_LName)||' '||:NEW.Inst_No)||' '||:NEW.Inst_EMail),1,1536);
END tr_inst_search;
/
SHOW ERRORS;
ALTER TABLE INST ADD (
CONSTRAINT PK_INST
PRIMARY KEY
(INST_PK)
USING INDEX);
ALTER TABLE INST ADD (
CONSTRAINT FK_INST_EMPFK_1BUPEN23
FOREIGN KEY (INST_EMPFK)
REFERENCES TBLEMP (EMP_PK)
ON DELETE CASCADE);
ALTER TABLE INST ADD (
CONSTRAINT FK_INST_TIMEZONEFK_1BUPEN21
FOREIGN KEY (INST_TIMEZONEFK)
REFERENCES TBL_LMS_TIMEZONE (TIMEZONE_PK)
ON DELETE SET NULL);
ALTER TABLE INST ADD (
CONSTRAINT FK_INST_VENFK_1BUPEN22
FOREIGN KEY (INST_VENFK)
REFERENCES VEN (VEN_PK)
ON DELETE SET NULL);
PROMPT "Table INST End "
Hitting ORA-00942 ...
December 9, 2005 - 3pm UTC
Reviewer: VKOUL from WA, USA
********************************************************
PART 3
********************************************************
PROMPT "Table TBL_TMX_ACTINST Start"
CREATE TABLE TBL_TMX_ACTINST
(
ACTIVITYFK NUMBER(10) NOT NULL,
INSTFK NUMBER(10) NOT NULL,
PRIMARYIND NUMBER(10) NOT NULL,
INSTTYPE NUMBER(10) NOT NULL,
ISQUALIFIED NUMBER(10) NOT NULL,
ISRESPONSIBLE NUMBER(10) NOT NULL,
CONFIRMED NUMBER(10) NOT NULL,
INHERITED NUMBER(10) NOT NULL,
APPRNOTES CLOB,
APPRTIME DATE,
APPROVERFK NUMBER(10),
LSTUPD DATE NOT NULL,
USRNAME VARCHAR2(90 BYTE) NOT NULL,
LCK NUMBER(10) NOT NULL
);
CREATE UNIQUE INDEX PK_TBL_TMX_ACTINST ON TBL_TMX_ACTINST
(ACTIVITYFK, INSTFK);
CREATE INDEX IDX_TMX_ACTINST_AB8CHW29 ON TBL_TMX_ACTINST
(INSTFK, ACTIVITYFK);
CREATE OR REPLACE TRIGGER TR_BI_TBL_TMX_ACTINST
before insert on TBL_TMX_ActInst
for each row
begin
if :new.InstFK is null then
:new.InstFK := -1;
end if;
if :new.PrimaryInd is null then
:new.PrimaryInd := 0;
end if;
if :new.InstType is null then
:new.InstType := 1;
end if;
if :new.IsQualified is null then
:new.IsQualified := 1;
end if;
if :new.IsResponsible is null then
:new.IsResponsible := 1;
end if;
if :new.Confirmed is null then
:new.Confirmed := 0;
end if;
if :new.Inherited is null then
:new.Inherited := 0;
end if;
:new.LstUpd := GetUTCDate;
:new.UsrName := Core_AUD.current_user;
if :new.Lck is null then
:new.Lck := 0;
end if;
end;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER TR_BU_TBL_TMX_ACTINST
before update on TBL_TMX_ActInst
for each row
begin
:new.LstUpd := GetUTCDate;
:new.UsrName := Core_AUD.current_user;
end;
/
SHOW ERRORS;
ALTER TABLE TBL_TMX_ACTINST ADD (
CONSTRAINT PK_TBL_TMX_ACTINST
PRIMARY KEY
(ACTIVITYFK, INSTFK)
USING INDEX);
ALTER TABLE TBL_TMX_ACTINST ADD (
CONSTRAINT FK_ACTIVITYFK_5REEVH1E
FOREIGN KEY (ACTIVITYFK)
REFERENCES TBL_TMX_ACTIVITY (ACTIVITY_PK)
ON DELETE CASCADE);
ALTER TABLE TBL_TMX_ACTINST ADD (
CONSTRAINT FK_APPROVERFK_5REEVH1G
FOREIGN KEY (APPROVERFK)
REFERENCES TBLEMP (EMP_PK)
ON DELETE SET NULL);
ALTER TABLE TBL_TMX_ACTINST ADD (
CONSTRAINT FK_INSTFK_5REEVH1F
FOREIGN KEY (INSTFK)
REFERENCES INST (INST_PK)
ON DELETE CASCADE);
PROMPT "Table TBL_TMX_ACTINST End "
PROMPT "Table TBLEMPBUSY Start"
CREATE TABLE TBLEMPBUSY
(
EMPBUSY_PK NUMBER(10) NOT NULL,
EMPBUSY_EMPFK NUMBER(10) NOT NULL,
EMPBUSY_STARTDT DATE NOT NULL,
EMPBUSY_STARTTM DATE NOT NULL,
EMPBUSY_ENDDT DATE NOT NULL,
EMPBUSY_ENDTM DATE NOT NULL,
EMPBUSY_DESC CLOB,
EMPBUSY_SUBJECT VARCHAR2(150 BYTE),
EMPBUSY_LSTUPD DATE NOT NULL,
EMPBUSY_USRNAME VARCHAR2(90 BYTE) NOT NULL,
EMPBUSY_LCK NUMBER(10) NOT NULL,
EMPBUSY_TIMEZONEFK NUMBER(10),
TBLEMPBUSY_TIMESTAMP NUMBER(10)
);
CREATE INDEX FT_TABLE_U7NBNT0A ON TBLEMPBUSY
(EMPBUSY_SUBJECT)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');
CREATE UNIQUE INDEX PK_TBLEMPBUSY ON TBLEMPBUSY
(EMPBUSY_PK);
CREATE INDEX IDX_TBLEMPBUSY_4LOSH813 ON TBLEMPBUSY
(EMPBUSY_STARTDT);
CREATE INDEX IDX_TBLEMPBUSY_4LOSH814 ON TBLEMPBUSY
(EMPBUSY_EMPFK);
CREATE OR REPLACE TRIGGER TR_BI_TBLEMPBUSY
before insert on tblEmpBusy
for each row
begin
:new.EmpBusy_LstUpd := GetUTCDate;
:new.EmpBusy_UsrName := Core_AUD.current_user;
if :new.EmpBusy_Lck is null then
:new.EmpBusy_Lck := 0;
end if;
end;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER TR_BU_TBLEMPBUSY
before update on tblEmpBusy
for each row
begin
:new.EmpBusy_LstUpd := GetUTCDate;
:new.EmpBusy_UsrName := Core_AUD.current_user;
end;
/
SHOW ERRORS;
ALTER TABLE TBLEMPBUSY ADD (
CONSTRAINT PK_TBLEMPBUSY
PRIMARY KEY
(EMPBUSY_PK)
USING INDEX);
ALTER TABLE TBLEMPBUSY ADD (
CONSTRAINT FK_EMPBUSY_EMPFK_F99PXX02
FOREIGN KEY (EMPBUSY_EMPFK)
REFERENCES TBLEMP (EMP_PK)
ON DELETE CASCADE);
ALTER TABLE TBLEMPBUSY ADD (
CONSTRAINT FK_EMPBUSY_TIMEZONEFK_F99PXX03
FOREIGN KEY (EMPBUSY_TIMEZONEFK)
REFERENCES TBL_LMS_TIMEZONE (TIMEZONE_PK)
ON DELETE SET NULL);
PROMPT "Table TBLEMPBUSY End "
PROMPT "Table TBL_TMX_REGBATCHACTIVITIES Start"
CREATE TABLE TBL_TMX_REGBATCHACTIVITIES
(
REGBATCHFK NUMBER(10) NOT NULL,
ACTIVITYFK NUMBER(10) NOT NULL,
TRACKFK NUMBER(10) NOT NULL,
SELECTED NUMBER(10) NOT NULL,
VALIDATED NUMBER(10) NOT NULL,
REGISTERED NUMBER(10) NOT NULL,
CREDITCARDNEEDED NUMBER(10) NOT NULL,
LSTUPD DATE NOT NULL,
USRNAME VARCHAR2(90 BYTE) NOT NULL,
LCK NUMBER(10) NOT NULL
);
CREATE INDEX IDX_TMX_REGBATCHACTIV_Z2NRX328 ON TBL_TMX_REGBATCHACTIVITIES
(REGBATCHFK, ACTIVITYFK, VALIDATED);
CREATE UNIQUE INDEX PK_TBL_TMX_REGBATCHACTIVITIES ON TBL_TMX_REGBATCHACTIVITIES
(REGBATCHFK, ACTIVITYFK, TRACKFK);
CREATE INDEX IDX_TMX_REGBATCHACTIV_WWUD2S1U ON TBL_TMX_REGBATCHACTIVITIES
(REGBATCHFK);
CREATE INDEX IDX_TMX_REGBATCHACTIV_WWUD2S1V ON TBL_TMX_REGBATCHACTIVITIES
(ACTIVITYFK);
CREATE INDEX IDX_TMX_REGBATCHACTIV_WWUD2S1W ON TBL_TMX_REGBATCHACTIVITIES
(TRACKFK);
CREATE OR REPLACE TRIGGER TR_BI_TBL_TMX_REGBATC_K92F6J22
before insert on TBL_TMX_RegBatchActivities
for each row
begin
if :new.Selected is null then
:new.Selected := 0;
end if;
if :new.Validated is null then
:new.Validated := 0;
end if;
if :new.Registered is null then
:new.Registered := 0;
end if;
:new.LstUpd := GetUTCDate;
:new.UsrName := Core_AUD.current_user;
if :new.Lck is null then
:new.Lck := 0;
end if;
end;
/
SHOW ERRORS;
CREATE OR REPLACE TRIGGER TR_BU_TBL_TMX_REGBATC_K92F6J22
before update on TBL_TMX_RegBatchActivities
for each row
begin
:new.LstUpd := GetUTCDate;
:new.UsrName := Core_AUD.current_user;
end;
/
SHOW ERRORS;
ALTER TABLE TBL_TMX_REGBATCHACTIVITIES ADD (
CONSTRAINT PK_TBL_TMX_REGBATCHACTIVITIES
PRIMARY KEY
(REGBATCHFK, ACTIVITYFK, TRACKFK)
USING INDEX);
ALTER TABLE TBL_TMX_REGBATCHACTIVITIES ADD (
CONSTRAINT FK_ACTIVITYFK_GVRRF10T
FOREIGN KEY (ACTIVITYFK)
REFERENCES TBL_TMX_ACTIVITY (ACTIVITY_PK)
ON DELETE CASCADE);
ALTER TABLE TBL_TMX_REGBATCHACTIVITIES ADD (
CONSTRAINT FK_REGBATCHFK_GVRRF10S
FOREIGN KEY (REGBATCHFK)
REFERENCES TBL_TMX_REGBATCH (REGBATCH_PK)
ON DELETE CASCADE);
ALTER TABLE TBL_TMX_REGBATCHACTIVITIES ADD (
CONSTRAINT FK_TRACKFK_GVRRF10U
FOREIGN KEY (TRACKFK)
REFERENCES TBL_TMX_REGTRACK (TRACK_PK)
ON DELETE CASCADE);
PROMPT "Table TBL_TMX_REGBATCHACTIVITIES End "
SELECT object_type, COUNT(*)
FROM user_objects
GROUP BY object_type;
/*
The Output Should Match
OBJECT_TYPE COUNT(*)
------------------ ----------
FUNCTION 1
INDEX 68
LOB 12
TABLE 7
TRIGGER 31
TYPE 1
6 rows selected.
*/
********************************************************
Now the Query
WITH emps
AS
(
SELECT CAST(column_value AS INTEGER) AS EmpId
FROM TABLE(CAST(str2tbl('1,2,3') AS COMMA_LIST))
UNION ALL
SELECT EmpFK AS EmpId
FROM TBL_TMX_RegBatchUsers t1
WHERE RegBatchFK = 1
AND EmpAdded = 1
)
SELECT 1,
conflicts.EmpId,
times.ActId,
4,
1,
1,
conflicts.ConflictName,
conflicts.BusyId
FROM (SELECT att.EmpFK AS EmpId,
act.Activity_PK AS BusyId,
act.ActivityName AS ConflictName,
act.StartDt AS StartTm,
act.EndDt AS EndTm
FROM Emps
INNER JOIN TBL_TMX_Attempt att ON att.EmpFK = Emps.EmpID
AND att.EndDt IS NULL
INNER JOIN TBL_TMX_Activity act ON act.Activity_PK = att.ActivityFK
AND act.UseForConflictCheck = 1
AND act.Cancelled = 0
WHERE NOT EXISTS (SELECT NULL
FROM TBL_TMX_Activity xAct
WHERE xAct.PrntActFK = act.Activity_PK
)
UNION ALL
SELECT inst.Inst_EmpFK AS EmpId,
act.Activity_PK AS BusyId,
act.ActivityName AS ConflictName,
act.StartDt AS StartTm,
act.EndDt AS EndTm
FROM Emps
INNER JOIN inst ON inst.Inst_EmpFK = Emps.EmpID
INNER JOIN TBL_TMX_ActInst actinst ON actinst.InstFK = inst.Inst_PK
AND actinst.Confirmed = 1
INNER JOIN TBL_TMX_Activity act ON act.Activity_PK = actinst.ActivityFK
AND act.UseForConflictCheck = 1
AND act.Cancelled = 0
WHERE NOT EXISTS (SELECT NULL
FROM TBL_TMX_Activity xAct
WHERE xAct.PrntActFK = act.Activity_PK
)
UNION ALL
SELECT EmpBusy.EmpBusy_EmpFK AS EmpId,
NULL AS BusyId,
EmpBusy.EmpBusy_Subject AS ConflictName,
EmpBusy.EmpBusy_StartDt AS StartTm,
EmpBusy.EmpBusy_EndDt AS EndTm
FROM Emps
INNER JOIN tblEmpBusy EmpBusy ON EmpBusy.EmpBusy_EmpFK = Emps.EmpID
) conflicts
INNER JOIN (SELECT act.Activity_PK AS ActId, act.StartDt AS StartTm, act.EndDt AS EndTm
FROM TBL_TMX_RegBatchActivities rba
INNER JOIN TBL_TMX_Activity act ON act.Activity_PK = rba.ActivityFK AND
act.UseForConflictCheck = 1 AND act.Cancelled = 0
WHERE rba.RegBatchFK = 1 AND rba.Validated = 0
) times
ON times.EndTm > conflicts.StartTm
AND times.StartTm < conflicts.EndTm
/
REGEXP_INSTR
January 11, 2006 - 12pm UTC
Reviewer: A reader
Hi Tom
I wanted to extract one character from a string preceded and followed by space
like from Tom H kyte
I want to separate H into middle name
tom into first name and kyte into last name
using REGEXP_INSTR function as I cant use ' ' for space inside ' '
This eg will not do
select regexp_instr('Tom H kyte','^(' '+[A-Z]+' ')',1,1) from dual;
so what should i do for finding space.
Thanks
Followup January 12, 2006 - 10am UTC:
why not just use instr? to find a space would be very easy with just instr
regexp_instr
January 11, 2006 - 1pm UTC
Reviewer: A reader
Hi
I am able to that with :blank
Thanks
whu not just instr
January 12, 2006 - 12pm UTC
Reviewer: jasdeep
Hi Tom
I was not able to solve how could I write Sql to find
space -- one charater -- then space.
Then I came to know about this new function in 10g and was able to write the sql.
It will appreciative of you if you can give insight writing it with only instr.
Followup January 12, 2006 - 12pm UTC:
you asked "so what should i do for finding space." - my response: instr
instr will find a space, that was the question I was addressing.
String dissection into columns
January 24, 2006 - 7am UTC
Reviewer: Praveen from Bangalore
Hi Tom
In a table column, variable length strings of the format
A:1,B:2,C:3,... Z:26. --26 is the maximum.
Need the output in the form
col1 col2
------------
A 1
B 2
C 3
.......
Z 26
I was using a pipelined function but take very long time.
The number of records is about 4million.
Any work around without calling any function, but only using a single SQL query.
Thanks
Followup January 24, 2006 - 8am UTC:
well, if you have 4million input records and each has upto 26 "rows" embedded in them, it could
take "a while"
how long is "long" and what do you actually DO with this output and how efficient is the plsql you
have written?
Re:String dissection into columns
February 1, 2006 - 3am UTC
Reviewer: Praveen from Bangalore
The function is 'cutting' out substrings of length 14 each from the input string (whose length is a
multiple of 14) and 'pipes' it out as a 'record' to the calling SQL statement. The function is
doing the same thing as the below written query is doing.

The output from this pipelined table function is then joined with another table of size 2.5 million
and the resulting data are /*+APPEND NOLOGGING*/ into a temporary table.
The total number of records that the temporary table will finally contain is about 26 million.
The whole loading process with the pipelined function will take about 5 hours. Our expected time is
less than 1 hour.
Sorry, *Question* for the above follow-up.
February 1, 2006 - 3am UTC
Reviewer: Praveen from Bangalore
Do you think that, in the above scenario, since the function is not doing too much of processing
within it, shouldn't it execute in nearly the same time as a single query? Well, if i use the query
given above, instead of the function (less all the additional computations), it will take me only
less than half an hour to load 26 million records. And the function (less all the additional
computations) is going beyond 5 hours!
Thanks & Regards
Followup February 1, 2006 - 8am UTC:
you yourself said you are doing more in the procedure than you are in the sql query we are looking
at.
hard to say - don't know what all you are doing in the function.
But I've always said, if you can do it in SQL - do it in SQL, only use PLSQL or any procedural
language when you cannot do it in SQL straight.
I cannot imagine any "filter" that plsql would be able to do better than SQL?
reader
February 23, 2006 - 7am UTC
Reviewer: A reader
Is there a way to search two strings, for a match of
2 adjacent characters from one string to an another
I like to use it in a SQL statement (not PL/SQL)
Example:
abcdefghij
1234cd5678
Here we are trying to match
ab cd ef gh ij
with any of
12 34 cd 56 78
Thanks
Followup February 23, 2006 - 8am UTC:
I don't get it.
reader
February 23, 2006 - 9am UTC
Reviewer: A reader
Within the SQL statement , I like to concatenate
6 columns from one table and concatenate 6 columns from
another table. In the where clause, I try to check if
any one of the column in one table match anyone of the
column in another table ( I did not create this table or
its semantic logic) Each column is 2 characters long
Is there a way to search two concatenated strings, for a match of 2 adjacent characters from one
string to an another
I like to use this method within SQL statement (not PL/SQL)
Example:
abcdefghij
1234cd5678
Here we are trying to match any 2 chars from one string to any 2 chars from another string
ab cd ef gh ij <=== match any of this
with any of
12 34 cd 56 78 <=== with any of this
In this particilar case there is a match, because
"cd" occurs in both strings
Hope this explanation is clearer
Thanks
Followup February 23, 2006 - 9am UTC:
you'll have to substr out the bits that are the real columns and compare them.
where c1 like '%'||substr(c2,1,2)||'%'
or c1 like '%'||substr(c2,3,2)||'%'
......
or c1 like '%'||substr(c2,9,2)||'%';
A solution to A reader
February 23, 2006 - 3pm UTC
Reviewer: Michel Cadot from France
SQL> select * from t;
ID C1 C2
---------- ---------- ----------
1 abcdefghij 1234cd5678
2 klmnopqrst<PHONE_NUMBER>
2 rows selected.
SQL> with
2 a as ( select id, c2,
3 substr(c1, 1+rn*2, 2) ch
4 from t, (select rownum-1 rn from dual connect by level <= 10/2)
5 where substr(c1, 1+rn*2, 2) is not null )
6 select id,
7 max(decode(instr(c2,ch),0,'No','Yes')) match
8 from a
9 group by id
10 order by id
11 /
ID MAT
---------- ---
1 Yes
2 No
2 rows selected.
(10 is c1 max length)
Regards
Michel
Followup February 23, 2006 - 7pm UTC:
I like my where clause better personally. This is a tad obscure and will not perform as well for
larger sets (since it makes them even larger)
Followup
February 24, 2006 - 1am UTC
Reviewer: Michel Cadot from France
You're fully right Tom but your where clause only works if all C1 values have a fixed known length.
If you imagine one C1 value smaller then the last condition "c1 like '%'||substr(c2,9,2)||'%'"
becomes "c1 like '%%'" which will always return TRUE.
This thought leads me to my query (and the desire to write a generic query that does not need to
know the value max length; and generic query is one of the best way to get a bad performance
query).
Oh yes, you can add "and substr(c2,?,2) is not null" to each condition in your query... :)
Regards
Michel
Followup February 24, 2006 - 8am UTC:
I read:
I like to concatenate 6 columns from one table and concatenate 6 columns
and sort of figured "6".
LOL
February 24, 2006 - 2pm UTC
Reviewer: Michel Cadot from France
You're right Tom, you perfectly read 6 columns and you perfectly saw his example contains only 10
characters as you stopped your conditions to substr(c2,9,2) (why not 11?). ;)
Well, I should begin my post with "to extend the case to an unknow number of characters...".
And once again, you're right my query can be a performance nightmare with millions of rows as it
multiplies them by half c1 max length.
Best regards
Michel
Followup February 24, 2006 - 2pm UTC:
Michel -
thanks for all of the really interesting SQL solutions though. Some of your examples have been
outstanding.
I'm almost afraid to say "I don't see a clear way to do that in SQL" anymore :) Knowing you'll come
along with something.... (thanks)
Thanks!
February 24, 2006 - 4pm UTC
Reviewer: Michel Cadot from France
Thanks for the compliment and above all thanks to you not only for this site but also for your
articles in Oracle Magazine as i started to understand the analytic functions and their power in
one of them 2 years ago.
http://www.oracle.com/technology/oramag/oracle/04-mar/o24asktom.html
Regards
Michel
column matching ?!?!
February 24, 2006 - 11pm UTC
Reviewer: Gabe
Well, both your solution and Michels do report this entry ('cdcdcdcdcd','dcdcdcdcdc') as a hit.
<quote>In the where clause, I try to check if any one of the column in one table match anyone of
the column in another table</quote>
So that would be a false positive.
decode(mod(instr(c2,ch),2),0,'No','Yes') in Michels query would fix that; or
select * from t
where mod(instr(c1,substr(c2,1,2)),2) = 1
or mod(instr(c1,substr(c2,3,2)),2) = 1
or mod(instr(c1,substr(c2,5,2)),2) = 1
or mod(instr(c1,substr(c2,7,2)),2) = 1
or mod(instr(c1,substr(c2,9,2)),2) = 1
;
Followup February 25, 2006 - 11am UTC:
indeed, thanks!
column matching ?!?!
February 25, 2006 - 2am UTC
Reviewer: Michel Cadot from France
You're right.
Thanks for the correction.
Regards
Michel
parsing multiple lines
April 17, 2006 - 7pm UTC
Reviewer: karma from TX
we have millions of addresses that needs to be standardized. For example we have 3 columns addr_1,
addr_2 and addr_3. sometime addr_1 could have P.O.BOX or Post office Box etc. and any variations of
this. How to efficiently parse and standardised ? Also, how to clean when they span on multiple
addr lines?
Followup April 18, 2006 - 9am UTC:
in OWB (Oracle Warehouse Builder) such a bit of rather complex functionality exists.
In many ETL (extract/transform/load) tools - such complex functionality (normalization of
addresses) exists.
In fact, you are best off getting a 3rd party list of valid addresses (many sources) and using a
tool to do this.
if you google about for
"address cleansing"
you'll find an entire industry built around address cleansing.
counting comma separated words
June 14, 2006 - 3pm UTC
Reviewer: Rima from MN usa
We have a field that stores strings like so : '01,02,03,04,05,99'. The number of elements in the
comma separated list is variable.
What is the best way to count the elements using SQL?
We tried something like this which doesnt seem to work for the above example :
SELECT NVL(LENGTH(TRANSLATE(text, ',' || text, ',')),0) + 1
INTO n_code_qty
FROM dual;
Thanks,
Rima.
Followup June 15, 2006 - 8am UTC:
ops$tkyte@ORA10GR2> select length(s)-length(replace(s,',',''))
2 from (select '01,02,03,04,05,99' s from dual);
LENGTH(S)-LENGTH(REPLACE(S,',',''))
-----------------------------------
5
Hello Tom, I am stuck and was hoping that you can help me out.
July 20, 2006 - 12am UTC
Reviewer: Evan Song from Seattle, WA USA
Hi,
I am newbie to Oracle and I am really confused about this problem.
Well, I read through this forum and copied out StrToTable function and tried from SQL+ and works
fine; however, when I added the function to the package... it gives me a wierd error.
Here's what I did.
Package name = M_Pkg
In Spec:
TYPE myTableType IS TABLE OF M_Box.BOXNO%Type;
FUNCTION StrToTable(boxes IN VARCHAR2) RETURN myTableType;
PROCEDURE CheckAllTheBoxes_Proc(allTheBoxes IN VARCHAR2,
boxesFound OUT INTEGER);
In Body:
FUNCTION StrToTable(boxes in varchar2) RETURN myTableType
AS
l_str long default boxes || ',';
l_data myTableType := myTableType();
l_n number;
begin
loop
exit when l_str is null;
l_n := instr(l_str, ',');
l_data.extend;
l_data(l_data.count) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr(l_str, l_n+1);
end loop;
return l_data;
end StrToTable;
PROCEDURE CheckAllTheBoxes_Proc(allTheBoxes IN VARCHAR2,
boxesFound OUT INTEGER)
IS
testTable myTableType;
BEGIN
testTable := StrToTable(allTheBoxes);
SELECT COUNT(a.ID) INTO boxesFound FROM BT_Box a WHERE a.BOXNO IN (select * from THE testTable);
END CheckAllTheBoxes_Proc;
And when I compiled this package, it gave me PL/SQL: ORA-00942: table or view does not exist for
"testTable".
So, I've tried using the pipelined as follow.
In Spec:
FUNCTION StrToTable(boxes IN VARCHAR2)
RETURN myTableType pipelined;
In Body:
FUNCTION StrToTable(boxes in varchar2) RETURN myTableType pipelined
AS
l_str long default boxes || ',';
l_n number;
begin
loop
exit when l_str is null;
l_n := instr(l_str, ',');
pipe row(ltrim(rtrim(substr(l_str,1,l_n-1))));
l_str := substr(l_str, l_n+1);
end loop;
return;
end StrToTable;
And now, I am getting PLS-00653: aggregate/table functions are not allowed in PL/SQL scope.
What can I do so that my CheckAllTheBoxes_Proc to work?
allTheBoxes would have value such as 'KO_KOMAC0003, KO_MWRW0102.....' and boxesFound should come
out to be total number found for the box listed in allTheBoxes.
I tried and I guess I reached my limitation. I really appreciate if you can help me out on this
problem.
Thank you.
Followup July 22, 2006 - 4pm UTC:
use sql types - create type - OUTSIDE the package, that is the way it is designed to work.
you can put the function in a package if you like, but keep the type at the sql level.
I got it working... but I still curious how it got to work.
July 20, 2006 - 4pm UTC
Reviewer: Evan from Seattle, WA USA
Can you explain this Tom?
I've replaced this,
SELECT COUNT(a.ID) INTO boxesFound FROM BT_Box a WHERE a.BOXNO IN (select *
from THE StrToTable(allTheBoxes));
to,
SELECT COUNT(a.ID) INTO boxesFound FROM BoxTrack_Box a WHERE a.BOXNO IN (select * from THE (select
cast(StrToTable(allTheBoxes) as myTableType) from dual) b);
What exactly is "select * from THE (select cast(StrToTable(allTheBoxes) as myTableType) from dual)
b" means?
Anyhow, thank you so much TOM! I would never got this solution without your feedbacks.
Followup July 22, 2006 - 5pm UTC:
you don't need the "the", but that CAST may have been necessary. The bind can lose the "type"
information as far as sql is concerned.
Comma Seperated string
August 11, 2006 - 1am UTC
Reviewer: DM from India
Hi tom
We have a string that will have comma seperated valuesin a text box (varchar2 type)from UI(.NET).
and we have create a dynamic sql that will return the records on basis of that like select * from
table_name where name in (text_box values )?
Thanks in advance
Converting SQL file to csv file
August 22, 2006 - 8am UTC
Reviewer: Soni Kumari from India
Hi Tom,
I have created a sql report file. Its working if run from sql prompt as @c:\order_relationships.sql
But I need to convert the output in csv file. So I need to change the some of the statement.
This is first time I am going to convert report file in csv.
So not really sure as how to convert this. When I run the file its giving the output based on only
the last statement
"SELECT anchor_ord||','||
SLCT||','||
wo_nbr||','||
rel_wo_nbr||','||
wostatus||','||
opstatus||','||
loc||','||
Item||','||
qty||','
FROM siviews.si_cancel_workorder
ORDER BY anchor_ord;"
But this is not taking care of the previous formattings.
/******************************************************************************
SQL Script
Filename: order_relationships.sql
Purpose: Generates Report Data for Order Relationships
Usage: While in SQLPlus ...

******************************************************************************/
DEFINE csvfile = &1
DEFINE rptid = &2
PROMPT
PROMPT Beginning data extraction.

PROMPT
Please advice as what changes I need to do to export the formatting also in the csv file.
Thanks,
Soni
Followup August 27, 2006 - 2pm UTC:
I do not know what you mean. If you want just a script to output csv based on a table, I've got
those.
but I don't know what you mean by "it ignores the previous settings"
To store value in a variable
September 4, 2006 - 3am UTC
Reviewer: Soni Kumari from India
Hi Tom,
I have to get a CSV file form the below script. In the select statement, I have to use a DECODE
statement.
SELECT DECODE(TO_CHAR(SYSDATE,'d'),1,TO_CHAR(SYSDATE,'dd-mon-yy'),
2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
TO_CHAR(SYSDATE-7,'dd-mon-yy')) FROM DUAL
The result from the DECODE statement needs to be displayed along with the SELECT statemetn and that
will get stored in CSV file. I can't use the DECODE with the SELECT statement as there will be
various combinations of DECODE and will be used around 14 times in the SELECT statement:
SELECT 'LEADTIME,Item,SUBORD,NONEWSUPPLYDATE,AVG_FCST,OH,' v_date....... FROM DUAL;
So I have to store ythe value of v_date in a variable. But if I use PL/SQL tags in this file the
spooling doesn't work.
Please advice.

PROMPT
END;
Followup September 4, 2006 - 9am UTC:
well, that date computation is interesting - couldn't really figure out what it was (was not the
beginning of the week which was my first guess)
I'm not really sure WHAT you are trying to do here - since "into" is a plsql thing (doesn't mean
anything in SQLPlus).
the line:
v_date date := trunc(...)
is not meaningful either, it is a line of plsql code, but there are not any plsql blocks in there.
You seem to have morphed sqlplus and plsql into a "single thing", they are very much separate and
distinct.
Do you have anyone on your site that can "mentor you" through this one - you are starting at ground
zero here.
'D'
September 4, 2006 - 9am UTC
Reviewer: Matthias Rogel
"was not the beginning of the week which was my first guess"
caveat: 'D' strongly depends on your setting of NLS_TERRITORY:
SQL> alter session set nls_territory='AMERICA';
Session altered.
SQL> select TO_CHAR(SYSDATE,'d') from dual;
T
-
2
1 row selected.
SQL> alter session set nls_territory='FRANCE';
Session altered.
SQL> select TO_CHAR(SYSDATE,'d') from dual;
T
-
1
1 row selected.
Followup September 4, 2006 - 9am UTC:
I know, that is why I said "first guess", looked at it further and just found it "a strange day"
being returned. Could not really guess what the point was.
November 10, 2006 - 7am UTC
Reviewer: A reader
Tom,
I have a table which stores string value from multi rows text feild, the following is the output
from the columns
SELECT changes FROM adpds_impact.change_log
WHERE table_name = 'ACTIVITYENTITY'
AND table_key_value = 272501;
CHANGES
--------------------------------------------------------------------------------
UPDATED RECORD|PROJECTID : 0|SUBPROJECTID : 0|CONTACTID : 0|NEEDEDBY : 12:00:0|0 AM|TIMESCHEDULED :
|CONFERENCECALLPHONENUMBER : |CONFERENCECALLPASSCODE : |CON
FERENCECALLLOCATION : |ESTIMATEDDATE : 3/1/2006 : CHANGED TO : 3/31/2006|ACTUALDATE : 12:00:00
|M|SCHEDULEDAFTERHOURSWORK : |COMPLETER : |LASTUPDATEBY : |CONTAC|TMETHODID : 0|SEQUENCE :
0|PHASEID : 0|GROUPID : 0
How can i search for a Text in this record and display only that text, the requirment is
if the above text has a date(passed through parameter) say 3/1/2006 for ESTIMATEDDATE then the
query needs to display only value "ESTIMATEDATE : 3/1/2006: CHANGED TO : 3/31/2006" from the above
record value.
Thanks in advance i dont have intemedia text enabled.
Followup November 10, 2006 - 9am UTC:
can we fix your data model and make this data useful instead?
November 10, 2006 - 10am UTC
Reviewer: A reader
Yeah, i know it in a Soup right now but this is urgent
can you atleast tell me how to get just the date value from the following string
"ACTUALDATE : -> 8/31/2006 12:00:00 AM"
i just want to pick the date value "8/31/2006" irrespetive of whats in the stirng last portion
:xx:xx:xx XX would be constant.
Followup November 10, 2006 - 2pm UTC:
easy answer:
you already have the date, it was the input to the query see.... You know what it is.
URGENT
November 10, 2006 - 11am UTC
Reviewer: A reader
I think it can be done using regexp_replace, i am in 10g
but i miss to pick only the date occurance some times.
Followup November 10, 2006 - 2pm UTC:
(pssst: you already have the date!
select a.*, :dt from table a where string like '%'||:dt||'%';
you were searching for it... there you go)
January 29, 2007 - 7pm UTC
Reviewer: A reader from Columbus
i have a string like these
'1111-1112-1113-1114-1115'
it can be like these too
'1111-1112-1113-1114-1115-1116'
how to get the secong last value
like in first case 1114
in the second case 1115
Thanks,
Raul
Followup January 31, 2007 - 1pm UTC:
ops$tkyte%ORA10GR2> select x,
2 instr( x, '-', -1 ),
3 instr( x, '-', -1, 2 ),
4 substr( x, instr( x, '-', -1, 2 )+1, instr( x, '-', -1 )-instr( x, '-', -1, 2 )-1 )
y
5 from t
6 /
X INSTR(X,'-',-1) INSTR(X,'-',-1,2) Y
------------------------------ --------------- ----------------- ------------------------------
1111-1112-1113-1114-1115 20 15 1114
1111-1112-1113-1114-1115-1116 25 20 1115
123-1115-1116 9 4 1115
1115-1116 5 0 1115
1116 0 0
Pluck out the leading alphabets
February 14, 2007 - 6pm UTC
Reviewer: A reader
1. I have a string like ABC123, or it can be ABC1234556, or ZZZZZ2344. I need to pluck out the leading alphabets and return only the number part of it.
2. I also need to ensure that in string like ABC12345AB12, first after weeding out the leading alphabets, I have to check and see that the remaining part of the string does not have any alphabets.
In this example after weeding out the alphabets what is left will be 12345AB12, now that is invalid because after removing the leading alphapets the string should fail validation if it still has ALPHABETS in it.
What can be the correct and quicket solution for this requirement.
Followup February 15, 2007 - 9am UTC:
you have regular expressions at your disposal
you have instr and substring at your disposal
simple little functions to be written using those functions.
Re: Pluck out the leading alphabets
February 15, 2007 - 11am UTC
Reviewer: Frank Zhou from Braintree, MA
Here is a sql solution.
Frank
SQL> select old_str, new_str,
2 CASE WHEN REGEXP_instr(new_str, '[^[:digit:]]', 1 ) > 0
3 THEN 'INVALID'
4 END As Flag
5 from
6 (
7 select str as old_str,
8 ltrim( upper(str), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as new_str
9 from
10 (select 'ABC12345AB12' str from dual
11 union all
12 select 'ZZZZZ2344' str from dual
13 )
14 );
OLD_STR NEW_STR FLAG
------------ ------------ -------
ABC12345AB12 12345AB12 INVALID
ZZZZZ2344 2344
SQL>
Refined Query
February 16, 2007 - 12pm UTC
Reviewer: Tom Admirer from WA
Tom, For the original question, Here is the refined query...without appened commas...
Would this be any better in your perspective?
scott @ MYDB>variable x varchar2(30)
scott @ MYDB>exec :x:='William,Clinton,J'
PL/SQL procedure successfully completed.
scott @ MYDB>select substr(:x,1,instr(:x,',',1)-1) FIRST_NAME,
2 substr(:x,instr(:x,',',1)+1,(instr(:x,',',-1)-instr(:x,',',1))-1) LAST_NAME,
3 substr(:x,instr(:x,',',-1)+1) MIDDLE_INIT
4 from dual;
FIRST_NAME LAST_NAME MIDDLE_INIT
-------------------------------- javascript:doSubmit('SUBMIT_REVIEW')
Submit Review-------------------------------- --------------------------------
William Clinton J
Followup February 17, 2007 - 11am UTC:
variation on a theme... either or is good.
Another SQL solution
February 22, 2007 - 11pm UTC
Reviewer: Scott from Perth, Western Australia
G'day Tom,
In an AUSOUG presentation, Connor McD provides a nice solution to convert a comma delimited string to rows, which could always be a help. I've applied it to an LDAP hierarchy string here:
variable mystring varchar2(100)
exec :mystring := 'CN=Smith\, Fred,OU=Disabled Accounts,OU=My org unit,DC=mydomain,DC=net,DC=au'
SELECT ou, LEAD(ou) OVER (ORDER BY NULL) parent_ou FROM (
SELECT REPLACE(SUBSTR(:mystring
,loc+1
,NVL(LEAD(loc) OVER (ORDER BY loc) - loc-1
,LENGTH(:mystring) - loc))
,'\,',',') ou -- Replace escaped commas with normal ones.
FROM (SELECT DISTINCT (INSTR(REPLACE(:mystring,'\,','\~') -- Don't count the escape'd commans
, ',', 1, LEVEL)) loc
FROM DUAL
CONNECT BY LEVEL < LENGTH(:mystring)))
OU PARENT_OU
------------------------------ -------------------------
CN=Smith, Fred OU=Disabled Accounts
OU=Disabled Accounts OU=My org unit
OU=My org unit DC=mydomain
DC=mydomain DC=net
DC=net DC=au
DC=au
Although I don't know how many versions back this solution would work...
However in trying to meld that SQL with a select from a list of those strings, I've come to a dead end. It either fries the CPU, or a column/table is out of reference scope.
ie, given:
create table swtest(id number ,mystring varchar2(100));
insert into swtest values (1,'CN=Smith\, Fred,OU=Disabled Accounts,OU=My org
unit,DC=mydomain,DC=net,DC=au');
insert into swtest values (2,'CN=Bloggs\, Joe,OU=My org unit,DC=mydomain,DC=net,DC=au');
I'm trying to at least achieve an output of:
ID OU
-- --------------------
1 CN=Smith, Fred
1 OU=Disabled Accounts
1 OU=My org unit
1 DC=mydomain
1 DC=net
1 DC=au
2 CN=Bloggs, Joe
2 OU=My org unit
2 DC=mydomain
2 DC=net
2 DC=au
Can you help?
Followup February 23, 2007 - 7am UTC:
http://tkyte.blogspot.com/2006/06/varying-in-lists.html
here is an approach - data is 20 rows (adjust as needed, max number of commas you have), then we substr out what we want after hiding your escaped commas.
ops$tkyte%ORA10GR2> with data
2 as
3 (select level l from dual connect by level <= 20)
4 select id, l,
5 replace(
6 trim( substr (txt,
7 instr (txt, ',', 1, l ) + 1,
8 instr (txt, ',', 1, l+1)
9 - instr (txt, ',', 1, l) -1 ) ), '##', ',' ) txt
10 from (select id, txt, length(txt)-length(replace(txt,',',''))-2 cnt
11 from (select id, ','||replace(mystring,'\,','##')||',' txt
12 from swtest )
13 )t,
14 data
15 where data.l <= t.cnt
16 order by id, l
17 /
ID L TXT
---------- ---------- --------------------------------------------------
1 1 CN=Smith, Fred
1 2 OU=Disabled Accounts
1 3 OU=My org unit
1 4 DC=mydomain
1 5 DC=net
2 1 CN=Bloggs, Joe
2 2 OU=My org unit
2 3 DC=mydomain
2 4 DC=net
9 rows selected.
RE: Another SQL solution
February 23, 2007 - 10am UTC
Reviewer: Frank Zhou from Braintree, MA
Here is an alternative soultion which doesn't requires hard coding.
This solution is simply a "plug in" of the following SQL Pattern
http://www.jlcomp.demon.co.uk/faq/Split_Strings.html
Frank
SQL> COLUMN str FORMAT A30
SQL> SELECT id, level, replace(SUBSTR(str,
2 INSTR(str, ',', 1, LEVEL ) + 1,
3 INSTR(str, ',', 1, LEVEL+1) -
4 INSTR(str, ',', 1, LEVEL) -1 ), '##', ',' ) str
5 from
6 (select id, ','||replace(mystring,'\,','##')||',' str from swtest )
7 CONNECT BY PRIOR id = id
8 AND INSTR (str, ',', 1, LEVEL+1) > 0
9 AND PRIOR dbms_random.string ('p', 10) IS NOT NULL;
ID LEVEL STR
---------- ---------- ------------------------------
1 1 CN=Smith, Fred
1 2 OU=Disabled Accounts
1 3 OU=My org unit
1 4 DC=mydomain
1 5 DC=net
1 6 DC=au
2 1 CN=Bloggs, Joe
2 2 OU=My org unit
2 3 DC=mydomain
2 4 DC=net
2 5 DC=au
11 rows selected.
RE: Another SQL solution
September 12, 2007 - 8am UTC
Reviewer: Mike from Toronto
create table test1 as
select 'D1' AS DEPT , 'Simon,Jon,Frank' AS STR FROM DUAL
UNION ALL
select 'D2' AS DEPT , 'Peter,Adam' AS STR FROM DUAL
UNION ALL
select 'D3' AS DEPT , 'Tom,Lee,Jack' AS STR FROM DUAL
/
SELECT DEPT,
SUBSTR(str,
INSTR(str, ',', 1, LEVEL ) + 1,
INSTR(str, ',', 1, LEVEL+1) -
INSTR(str, ',', 1, LEVEL) -1 ) name
FROM (
SELECT ','||str||',' AS str ,dept
FROM test1
)
CONNECT BY PRIOR DEPT = DEPT
AND INSTR (str, ',', 1, LEVEL+1) > 0
AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
OPS$MJS@DB10 > /
DE NAME
-- ------------------------------
D1 Simon
D1 Jon
D1 Frank
D2 Peter
D2 Adam
D3 Tom
D3 Lee
D3 Jack
8 rows selected.
Works in <IP_ADDRESS>
But in <IP_ADDRESS> Linux returns
SELECT DEPT,
SUBSTR(str,
INSTR(str, ',', 1, LEVEL ) + 1,
INSTR(str, ',', 1, LEVEL+1) -
INSTR(str, ',', 1, LEVEL) -1 ) name
FROM (
SELECT ','||str||',' AS str ,dept
FROM test1
)
CONNECT BY PRIOR DEPT = DEPT
AND INSTR (str, ',', 1, LEVEL+1) > 0
AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
OPS$MJS@DB11 > /
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
Is this by design or a bug? I think the latter..
Parsing a variable length string
April 8, 2008 - 12am UTC
Reviewer: A reader from USA
I have a long String that I need to parse and store the parsed data into relational tables. For
example - the string looks like this
SSN,count,name,count,address,count,phone
123123123,1, ROBERT SMITH,2, 123 Congress, Houston, TX,124 south street,
CA,3,1000000000,1233334567,0994449394
Basically, there is a count for the phone numbers, address, name etc, so the length of the string
is variable and data goes into different tables.
Thanks a lot for your time.
Followup April 9, 2008 - 2pm UTC:
this is called "writing code". the logic looks pretty darn simple doesn't it? Sort of really basic "programming"
look for comma
break string into SSN and rest of string
look for comma
break string into cnt and rest of string
for i in 1 .. cnt
loop
look for comma
break string into address(i) and rest of string
end loop
and so on. with error handling and such of course
but pretty "basic" stuff.
Can we bulk insert and update at the same time
April 27, 2008 - 8pm UTC
Reviewer: TAG OTN
Tom
With respect to your "Followup June 13, 2004 - 5pm US/Eastern"
1. No exceptions are saved. Why ?
2. In the same procedure, instead of just using an insert, I want to do a bulk merge i.e. insert
the row, and if the row already exists then update, is that possible ?
Further can we handle exceptions in case 2?
Thank you very much
Followup April 28, 2008 - 1pm UTC:
1) I don't know what you mean by that.
2) did you even attempt the code? you have the example
Separating comma separated numbers and then doing calculations
May 7, 2008 - 12pm UTC
Reviewer: Juan from Spain
Hi Tom,
I am using your above queries in order to split a fields separated by comma. It was great!
But, then I wanna do calculations with numbers extracted:
SELECT
POWER (10,-11 )* V1 + POWER (10, -10.95)* V2 +
POWER (10,-10.9)* V3 + POWER (10, -10.85)* V4 +
POWER (10,-10.8)* V5 + POWER (10, -10.75)* V6 +
POWER (10,-10.7)* V7 + POWER (10, -10.65)* V8 +
POWER (10,-10.6)* V9 + POWER (10, -10.55)* V10+...
FROM (subselect to split from table)
The table has about a million of records.
When I did a IAS it took more than 5 hours, and with hint parallel it wasn't better.
But when I did a CTAS it took just 1h 20 minutes
Without POWER functions just sums both took about same time.
What is happening here?
Thank you in advance
Followup May 8, 2008 - 3am UTC:
what is happening here is you have stored your data in an entirely inappropriate fashion to actually use it.
Nothing more
Nothing less
This trick is great for binding an inlist.
To process millions of records - it would not be the smartest thing to do.

I'll never understand why people store delimited strings in a database. Never.
May 7, 2008 - 3pm UTC
Reviewer: A reader
Do you have a generic procedure for this ?
Separarating comma or pipe delimited text into different rows or columns - where the number of
commas or pipes can be any number - not 1 or 2 or 3 ?
Christian
May 7, 2008 - 3pm UTC
Reviewer: A reader
I don't want to use a loop in the above case - if possible in a single SQL
Christian
Separating comma separated numbers and then doing calculations
May 9, 2008 - 6am UTC
Reviewer: Juan from Spain
Hi Tom,
Thank you for your answer.
I am doing that because I am trying to fit it in a DWH project with loader and summary tools with
hundreds of tables implied. And it is difficult to justify just for two special fields that these
tools must be modify or not use these tools for these fields. But it was a first approach.
Just two questions:
1) When I did it in two steps, first split insert into table, it took 18 min, and then calculations
insert into destination table, it took 8 min.
But, I don't understand... Why do I get better results using CTAS ? and when the query is more
complex, there is a much more difference...
2) For the second approach... Which is the best way in order to load files with these records using
external tables all separated in columns?
NAME_SURNAME_ADDRESS_1,23,43,33,54_CITY_COUNTRY
"1,23,43,33,54" is a histogram.
Thank you
June 7, 2008 - 12am UTC
Reviewer: Reader
I need to write a query to separate the words in the column tst.msg_txt and insert into 3 columns
in a new table. Please advice. The words are seperated by spaces/space
create table tst (txt_msg varchar2(20));
insert into tst
values
('AZ US NA');
insert into tst
values
('NY' US NA');
insert into tst
values
('NYC' US NA');
commit;
June 10, 2008 - 9am UTC
Reviewer: Reader
I was wondering if you can answer the above question.
Followup June 10, 2008 - 11am UTC:
yes, I can.
but the fact that the example was never tested by you
ops$tkyte%ORA11GR1> insert into tst
2 values
3 ('NY' US NA');
ERROR:
ORA-01756: quoted string not properly terminated
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into tst
2 values
3 ('NYC' US NA');
ERROR:
ORA-01756: quoted string not properly terminated
gave me the mind set that "i'll just skip it, they can figure out how to substr and instr and the like - it is not hard"
June 10, 2008 - 2pm UTC
Reviewer: Reader
Apologies for sending the wrong data.
create table tst (txt_msg varchar2(20));
insert into tst
values
('AZ US NA');
insert into tst
values
('NY US NA');
insert into tst
values
('NYC US NA');
commit;
I used negative index for instr. Please let me know if this is okay or any other way to do this:
select txt_msg
,trim(substr(txt_msg,instr(txt_msg,' ',-1,1)+1)) part_3
,trim(substr(txt_msg,instr(txt_msg,' ',1,1)+1,(instr(txt_msg,' ',-1,1)-instr(txt_msg,'
',1,1))-1)) part_2
from tst;
Followup June 10, 2008 - 3pm UTC:
ops$tkyte%ORA10GR2> select txt_msg,
2 regexp_substr( txt_msg, '^[^ ]+ ' ) p1 ,
3 regexp_substr( txt_msg, ' [^ ]+ ' ) p2,
4 regexp_substr( txt_msg, ' [^ ]+$' ) p3
5 from tst
6 /
TXT_MSG P1 P2 P3
-------------------- -------------------- -------------------- ----------------
AZ US NA AZ US NA
NY US NA NY US NA
NYC US NA NYC US NA
June 10, 2008 - 3pm UTC
Reviewer: Reader
Thanks a lot.
June 11, 2008 - 12pm UTC
Reviewer: Reader
create table test (txt varchar2(50));
insert into test values('test|2|test|abc|20080602');
insert into test values('test|3|test|def|20080603');
insert into test values ('test|4|test|pqr|20080604');
commit;
select * from test;
TXT
--------------------------------------------------
test|2|test|abc|20080602
test|3|test|def|20080603
test|4|test|pqr|20080604
I need to extract the data starting from second delimiter ("|" plus 1),till the last delimiter
(inclusive of "|"). There will be date appended in the end which will be in the format 20080602 (8
characters). I am doing like below. Can you please suggest if there is any other efficient way?
select substr(txt,(instr(txt,'|',1,2) +1),((instr(txt,'|',-1,1)-8+1))) from test;
TXT
--------------------------------------------------
test|abc|
test|def|
test|pqr|
Followup June 11, 2008 - 4pm UTC:
that looks fine to me.
How to read csv from a CLOB object in pl/sql
June 19, 2008 - 9am UTC
Reviewer: Yathish from Bangalore, India
our Java application will create a CLOB object consisting of csv like
(CSC,HDMS,JK,2.5 | CSC,HDMS,AB,2.5 | CSS,HDGH,JP,2.5)
Now I want to insert each values seperated by commas as a record in to a table with each record
delimted by a pipe symbol. I couldn't see the usage of CLOB object in the listing. I would like to
kow how to read values from a CLOB object so that I can insert CSV in to the table in Stored
procedure
Followup June 19, 2008 - 10am UTC:
I hate your java.
why didn't your java do that in the first place. ugh.
reading from a clob is just like reading from a file. pretend you are reading from a file - how would you do that in <insert your procedural language here>?
Actually, since this is plsql and sql - we can do something pretty "neat"
This is a first run 'hack', there is no error handling, there is not much error checking. Take it for what it is - A CONCEPT. You need to write it 'better' perhaps...
ops$tkyte%ORA9IR2> create or replace type myScalarType as object
2 ( c1 varchar2(4000),
3 c2 varchar2(4000),
4 c3 varchar2(4000),
5 c4 varchar2(4000),
6 c5 varchar2(4000)
7 )
8 /
Type created.
ops$tkyte%ORA9IR2> create or replace type myTableType as table of myScalarType
2 /
Type created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace function csv2table( p_clob in clob ) return myTableType
2 PIPELINED
3 as
4 l_last number := 1;
5 l_current number;
6 l_len number := dbms_lob.getlength(p_clob);
7 l_piece long;
8 l_delim varchar2(5) := '|';
9 l_sep varchar2(5) := ',';
10 l_fields number := 5;
11 begin
12 loop
13 l_current := dbms_lob.instr( p_clob, l_delim, l_last, 1 );
14 if (l_current = 0 and l_last < l_len)
15 then
16 l_current := l_len+1;
17 end if;
18 exit when nvl(l_current,0) = 0;
19
20 l_piece := l_sep||trim(dbms_lob.substr( p_clob, l_current-l_last, l_last
))||rpad(l_sep,l_fields,l_sep);
21 pipe row( myScalarType(
22 substr( l_piece, instr( l_piece, l_sep, 1, 1 )+1, instr(l_piece,
l_sep,1,2)-instr(l_piece,l_sep,1,1)-1 ),
23 substr( l_piece, instr( l_piece, l_sep, 1, 2 )+1, instr(l_piece,
l_sep,1,3)-instr(l_piece,l_sep,1,2)-1 ),
24 substr( l_piece, instr( l_piece, l_sep, 1, 3 )+1, instr(l_piece,
l_sep,1,4)-instr(l_piece,l_sep,1,3)-1 ),
25 substr( l_piece, instr( l_piece, l_sep, 1, 4 )+1, instr(l_piece,
l_sep,1,5)-instr(l_piece,l_sep,1,4)-1 ),
26 substr( l_piece, instr( l_piece, l_sep, 1, 5 )+1, instr(l_piece,
l_sep,1,6)-instr(l_piece,l_sep,1,5)-1 ) ) );
27
28 l_last := l_current+1;
29 end loop;
30 return;
31 end;
32 /
Function created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> drop table t;
Table dropped.
ops$tkyte%ORA9IR2> create table t ( id number primary key, data clob );
Table created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into t values ( 1, 'CSC,HDMS,JK,2.5 | CSC,HDMS,AB,2.5 | CSS,HDGH,JP,2.5
||,2nd,3rd|,,,4th' );
1 row created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select t.id, d.*
2 from t, table( csv2table(t.data) ) d
3 /
ID C1 C2 C3 C4 C5
---------- ---------- ---------- ---------- ---------- ----------
1 CSC HDMS JK 2.5
1 CSC HDMS AB 2.5
1 CSS HDGH JP 2.5
1
1 2nd 3rd
1 4th
6 rows selected.
CSV for a CLOB Object
June 20, 2008 - 3am UTC
Reviewer: Yathish M P from Bangalore, India
This was extremely helpful and works fine..Thanks Tom
Passing comma seperated values in string as
October 1, 2008 - 12pm UTC
Reviewer: DM from IND
Hi tom
I am having a procedure which does something like ...
create or replace proc1(values IN VARCHAR2)
is
BEGIN
for I in( select col1
from some_table
where col2 IN (values);
loop
update some_table2 ......
end loop;
END;
Now the input parameter to the above procedure may have either 1 values or a string of values all
numbers only ..
like
input parameter "values" can have 224,2356,2112,...
or
input parameter "values" can have 111
at runtime it can unknown values..
now my question is how I can pass string of values to the procedure...
Any suggestions with code.
Thanx in advance
parsing the long data type
February 24, 2009 - 7am UTC
Reviewer: dk from India
Hi Tom,
We have a column c1 as long (the max length can be 2 GB).We need to parse this column in PL/SQL,but
when I declare the long data type in the procedure it unfortunately holds only ~36,000 char
limit.So how to hold and process that record in PL/SQL
Can you suggest a way how to parse such column,the seperator is a "~".
Followup February 24, 2009 - 4pm UTC:
you will not hold that record in plsql.
I'd set up a global temporary table
ops$tkyte%ORA10GR2> create global temporary table gtt (x clob) on commit delete rows;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
2 l_clob clob;
3 begin
4 insert into gtt
5 select to_lob(text)
6 from dba_views
7 where text_length = (select max(text_length) from dba_views)
8 and rownum = 1;
9
10 select x into l_clob
11 from gtt;
12
13 -- use dbms_lob on it
14 end;
15 /
PL/SQL procedure successfully completed.
and then you have everything in the dbms_lob package to use against it (in current releases, you could skip the dbms_lob package and use substr, instr, whatever you wanted on it as well)
Need to use Long
March 6, 2009 - 2am UTC
Reviewer: DK from India
Tom,
The usage of CLOB is making the process very slow,additionally it will occupy 2GB per row for the
CLOB column itself.which is huge.
I still need to use the Long data type in table and process the column in PL/SQL
Thanks,
Dheeraj
ORA-00942
March 27, 2009 - 4pm UTC
Reviewer: Karady from Bethlehem , PA
SQL> declare
2 begin
3 Insert into repo_sql_plan_statistics_all
4 Select 100, 200, 'test', vspa.* from v$sql_plan_statistics_all vspa;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> create procedure test_ins
2 as
3 begin
4 Insert into repo_sql_plan_statistics_all
5 Select 100, 200, 'test', vspa.* from v$sql_plan_statistics_all vspa;
6 end;
7 /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE TEST_INS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: SQL Statement ignored
5/42 PL/SQL: ORA-00942: table or view does not exist
SQL>
I am getting the above error . What am I doing wrong here ? Please let me know
Followup March 30, 2009 - 3pm UTC:
Greetings to the town where I grew up :)
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html
you have access to v$sql_plan_statistics_all via a role, you need a direct grant to create a procedure like that (or, if this is a utility program, you might be able to use authid current_user, invokers rights)
April 28, 2009 - 9pm UTC
Reviewer: Asheesh Dhupper from India
Indeed a nice solution.
But I have one more question. I have a table which has numeric values in string like
CREATE TABLE TEST(ids VARCHAR2(1500));
INSERT INTO TEST VALUES('''2''');
INSERT INTO TEST VALUES('''345'',''211'',''11''');
INSERT INTO TEST VALUES('''234'',''15'',''100'',''190'',''987'',121'',''33''');
INSERT INTO TEST VALUES('''150'',''232''');
now i want to extract numbers and store them as
create table test2 (nums number(10))
select * from test2 (another table)
nums
-----
2
345
211
11
234
15
100
190......
and so on..
Please help
Followup April 29, 2009 - 8am UTC:
ops$tkyte%ORA10GR2> select newids, column_value
2 from (select ','||ids||',' newids from t),
3 TABLE(
4 cast (
5 multiset(
6 select to_number(
7 trim( '''' from substr(newids, instr (newids, ',', 1, level ) + 1,
8 instr (newids, ',', 1, level+1) - instr (newids, ',', 1, level) -1 ) )
9 )
10 from dual
11 connect by level <= length(newids)-length(replace(newids,',',''))-1
12 )
13 as sys.odcinumberlist ) )
14 /
NEWIDS COLUMN_VALUE
---------------------------------------- ------------
,'2', 2
,'345','211','11', 345
,'345','211','11', 211
,'345','211','11', 11
,'234','15','100','190','987',121','33', 234
,'234','15','100','190','987',121','33', 15
,'234','15','100','190','987',121','33', 100
,'234','15','100','190','987',121','33', 190
,'234','15','100','190','987',121','33', 987
,'234','15','100','190','987',121','33', 121
,'234','15','100','190','987',121','33', 33
,'150','232', 150
,'150','232', 232
13 rows selected.
Loading in Variable length data into tables
November 24, 2009 - 12pm UTC
Reviewer: MK from Stockholm, Sweden
Hi Tom,
I was wondering what the best way of loading and storing variable length data from input files
would be in Oracle? For example I have flat files of the format.
tid1, item1, item2, item3
tid2, item1, item2, item3, item4 ... itemN
tid3, item1, item2
So basically the first comma separated value is the transaction ID and then the rest are items
(varying number of items) in those transactions. I want to know what the best and most efficient
way of loading these would be so I can later do aggregation operations or analytical operations on
the itemsets belonging to transactions. Should I use some sort of an external table format (tid,
stringitem) and then some PL/SQL code to split the stringitem into it's constituents? If there is a
better way I would love to know!
Cheers,
MK
Followup November 24, 2009 - 12pm UTC:
external tables and sqlldr both support delimited data easily.
for example, a control file for sqlldr for pipe delimited data (just change | to ,) for the emp table could be:
LOAD DATA
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
and an external table for the same might be:
CREATE TABLE "SYS_SQLLDR_X_EXT_EMP"
(
"EMPNO" NUMBER(4),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'emp.bad'
LOGFILE 'emp.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "|" LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255)
TERMINATED BY "|",
"ENAME" CHAR(255)
TERMINATED BY "|",
"JOB" CHAR(255)
TERMINATED BY "|",
"MGR" CHAR(255)
TERMINATED BY "|",
"HIREDATE" CHAR(255)
TERMINATED BY "|",
"SAL" CHAR(255)
TERMINATED BY "|",
"COMM" CHAR(255)
TERMINATED BY "|",
"DEPTNO" CHAR(255)
TERMINATED BY "|"
)
)
location
(
'emp.ctl'
)
)
external tables
November 25, 2009 - 3pm UTC
Reviewer: MK from Stockholm, Sweden
Hi,
I don't think I got an answer unfortunately. The problem I posed was of flat files where my records
have the first comma seperated value as my TrxID and everything following that is a list of items
that can be space separated or comma seperated... but the items on each line can be of varying
lengths! Hence I cannot load them into a predefined data structure whose columns I know beforehand.
All I want to then do is load these transactions into a table (or any other suggestions) as
(trx_id, items_string) and then basically expand each item_string into it's constituent items and
then store in a table with columns (tid, itemid).
For example
I have the file loaded in as
trx_id item_string (varchar2)
1 "a,b,c,d"
2 "d,f,g"
3 "x,y,z"
and then split them up into a normalized table like
trx_id, item_id
1 a
1 b
1 c
1 d
2 d
....
etc
Hope this helps to add to the original query.
Thanks
MK
Followup November 28, 2009 - 10am UTC:
then map a single string.
ops$tkyte%ORA10GR2> create or replace directory tmpdir as '/home/tkyte'
2 /
Directory created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE TABLE et
2 (
3 "STR" VARCHAR2(4000)
4 )
5 ORGANIZATION external
6 (
7 TYPE oracle_loader
8 DEFAULT DIRECTORY TMPDIR
9 ACCESS PARAMETERS
10 (
11 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
12 READSIZE 1048576
13 FIELDS LDRTRIM
14 REJECT ROWS WITH ALL NULL FIELDS
15 (
16 "STR" (1:4000) CHAR(4000)
17 )
18 )
19 location
20 (
21 't.dat'
22 )
23 )
24 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from et;
STR
--------------------
1 "a,b,c,d"
2 "d,f,g"
3 "x,y,z"
Now we start parsing
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select to_number(substr( str, 1, instr(str,' '))) transid,
2 substr( str, instr(str,' ')+1 ) data
3 from et
4 /
TRANSID DATA
---------- --------------------
1 "a,b,c,d"
2 "d,f,g"
3 "x,y,z"
and transforming...
ops$tkyte%ORA10GR2> select to_number(substr( str, 1, instr(str,' '))) transid,
2 replace( substr( str, instr(str,' ')+1 ), '"', ',' ) data
3 from et
4 /
TRANSID DATA
---------- --------------------
1 ,a,b,c,d,
2 ,d,f,g,
3 ,x,y,z,
and then we can use the technique from
http://tkyte.blogspot.com/2006/06/varying-in-lists.html
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select a.transid, b.column_value
2 from ( select to_number(substr( str, 1, instr(str,' '))) transid,
3 replace( substr( str, instr(str,' ')+1 ), '"', ',' ) data
4 from et) a,
5 TABLE( cast( multiset(
6 select
7 trim( substr (a.data,
8 instr (a.data, ',', 1, level ) + 1,
9 instr (a.data, ',', 1, level+1)
10 - instr (a.data, ',', 1, level) -1 ) )
11 as token
12 from dual
13 connect by level <= length(a.data)-length(replace(a.data,',',''))-1
14 ) as sys.odciVarchar2List ) ) b
15 /
TRANSID COLUMN_VALUE
---------- --------------------
1 a
1 b
1 c
1 d
2 d
2 f
2 g
3 x
3 y
3 z
10 rows selected.
External tables
November 30, 2009 - 3am UTC
Reviewer: MK from Stockholm, Sweden
Thanks for the answer, that helps a lot. I will look into the TABLE and Multicast a bit more. It
saves me from having to write a PL/SQL procedure that loops through the string and then breaks it
for comma separated values. This looks real neat and nifty! :)
Is there a trick to getting the rows where ALL the items in a list are present. So for example I
would only like to return the trx ID of the transaction that contains all the items in list (a,b,c)
else nothing gets returned?
The IN clause will not work I know, so would it have to be a combination of doing a Exist and Not
Exist subquery, but I am wondering if you have a better technique of dealing with this. I have
Oracle 11g R2 installed, so can use any of the latest functions that might do this better.
Cheers,
MK
Followup November 30, 2009 - 11am UTC:
ops$tkyte%ORA10GR2> select *
2 from (
3 select a.transid, b.column_value,
4 count( DISTINCT case when b.column_value in ('a','b','c') then b.column_value end)
5 over (partition by transid) cnt
6 from ( select to_number(substr( str, 1, instr(str,' '))) transid,
7 replace( substr( str, instr(str,' ')+1 ), '"', ',' ) data
8 from et) a,
9 TABLE( cast( multiset(
10 select
11 trim( substr (a.data,
12 instr (a.data, ',', 1, level ) + 1,
13 instr (a.data, ',', 1, level+1)
14 - instr (a.data, ',', 1, level) -1 ) )
15 as token
16 from dual
17 connect by level <= length(a.data)-length(replace(a.data,',',''))-1
18 ) as sys.odciVarchar2List ) ) b
19 )
20 where cnt = 3
21 /
TRANSID COLUM CNT
---------- ----- ----------
1 a 3
1 b 3
1 c 3
1 d 3
External tables for varying column data
January 19, 2010 - 1am UTC
Reviewer: MK from Stockholm, Sweden
Hi Tom,
Thanks for the previous responses regarding loading of data using External Tables and the recursive
splitting. I have been asked to write a generic package that assists people to load data with a
variable number of columns from CSV files into their respective files and then the ext table should
also be named after the file name.
So for ex: if the CSV file had only 4 columns in it, then I would have to have a function like
load_variable_data( filename, Num_of_Cols, TableName )
and users should be able to just call this function and they have access to their files. I was
wondering if this would require Dynamic SQL and if that would be a good way of going about doing
such a thing? If you could show me the best performing way of doing such a thing then that would
greatly help me.
Thanks
MK
Followup January 19, 2010 - 4pm UTC:
step one:
figure out the template CREATE TABLE statement you need. Probably looks like:
CREATE TABLE "table_name"
( c1 varchar2(4000),
...
cn varchar2(4000)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY YOUR_PATH
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
c1 char(4000), TERMINATED BY ",",
...
cn char(4000), TERMINATED BY ","
)
)
location
(
'your_filename'
)
)
/
step two: write the code to fill in the table_name, the c1..cn lists (two of them) and your_filename.
step three: using execute immediate, execute that DDL statement.
step four: add error handling and make the code nice.
Now, if the files can appear in ANY directory on the server (remember, these files DO have to be on the database server, not on the client machine - else you'll be using ODBC gateways to connect over the network and will have to set up some sort of server on the client machine) - you'll need to do more work - like parse the filename into a directory and a file name. But then you might have to create a directory on the fly which is something I would never let you do - so you need to think about things like that and make your specification a lot bigger here (missing some important technical details)
January 21, 2010 - 10am UTC
Reviewer: MK from Stockholm, Sweden
Thanks. The files will be stored in a directory on the server for which I already have an Oracle
directory with the correct grants to the developers.
Thanks,
MK
Special Characters in Query
April 14, 2010 - 5am UTC
Reviewer: Shashi from India
I want the List of characters and resolution which can leads the SQL query to not work properly
LIKE
Problem:Single Quotes " ' " in SQL query.
Solution:Use one more single Quotes
Example:Select 'D''Sa'as EName from dual;
Please provide all Such special cases so I take care while developing my application.
Followup April 14, 2010 - 9am UTC:
use bind variables and you need never worry about it.
do not use bind variables and you have much bigger fish to fry than worrying about quotes.
so, use binds - done :)
quotes are about it. Different environments might impose other things, like sqlplus by default looks for "&" in all text - but that is sqlplus not SQL.
String in For Loop
May 3, 2010 - 8am UTC
Reviewer: Abhisek
Hi Tom,
How can we perform the following scenario:
for i in(comma-sperated string) loop
dbms_ouput.put_line(each_element_of_string_seperated_by_comma);
end loop;
So if I pass string as 'A,B,C,D'
It should loop around and print
A
B
C
D
Thanks
Records wih comma into multiple Rows
June 21, 2010 - 1pm UTC
Reviewer: V.Hariharaputhran from Chennai India
Mr.Oracle,
Read your blog, How about the solving below scenario with a single SQL where we have more than one row, like
create table test (no number,no_val varchar2(100));
insert into test values (1,'TOM,KYTE,ORACLE');
insert into test values (2,'EXPERT,ONE,ON,ONE');
commit;
Expected Output
No No_Val
1 TOM
1 KYTE
1 ORACLE
2 EXPERT
2 ONE
2 ON
2 ONE
Tried a hard coded method(logic from your blog), definetly a bad way
select no,token from (
select no,
trim( substr (txt,
instr (txt, ',', 1, l ) + 1,
instr (txt, ',', 1, l+1) - instr (txt, ',', 1, l) -1 ) )as token
from (select no,no_val,','||no_val||',' txt from test) test, (select level l from dual connect by level <= 10) data
where length(no_val) >= l )
where token is not null
order by no
Still the above query's result ordering is wrong, is there a better way to write it in a single SQL
Thanks for your time.
Regards
V.Hari
Followup June 22, 2010 - 1pm UTC:
ops$tkyte%ORA11GR2> select no, substr(column_value,6) no_val
2 from (select no, ','||no_val||',' newids from test),
3 TABLE(
4 cast (
5 multiset(
6 select to_char(rownum,'fm00000') || trim( '''' from substr(newids, instr (newids,
',', 1, level ) + 1,
7 instr (newids, ',', 1, level+1) - instr (newids, ',', 1, level) -1 ) )
8 from dual
9 connect by level <= length(newids)-length(replace(newids,',',''))-1
10 )
11 as sys.odcivarchar2list ) )
12 order by no, column_value
13 /
NO NO_VAL
---------- --------------------
1 TOM
1 KYTE
1 ORACLE
2 EXPERT
2 ONE
2 ON
2 ONE
7 rows selected.
Comma seperation
June 22, 2010 - 3pm UTC
Reviewer: V.Hariharaputhran from Chennai India
Mr.Oracle,
Amazing and speech less. Thanks for your reply,time,effort,explanation and what not.
Regards
V.Hari
Followup June 22, 2010 - 6pm UTC:
it was frankly mostly a cut and paste from this page.
I just reused:
ops$tkyte%ORA10GR2> select newids, column_value
2 from (select ','||ids||',' newids from t),
3 TABLE(
4 cast (
5 multiset(
6 select to_number(
7 trim( '''' from substr(newids, instr (newids, ',', 1, level ) + 1,
8 instr (newids, ',', 1, level+1) - instr (newids, ',', 1, level) -1 ) )
9 )
10 from dual
11 connect by level <= length(newids)-length(replace(newids,',',''))-1
12 )
13 as sys.odcinumberlist ) )
14 /
had to add a bit for sorting was all.
Values inside Parenthesis of a String
August 27, 2010 - 2am UTC
Reviewer: Sangeet Menon from India
Hi Tom,
I have a String as follows:
<b>
101 (4),102 (5) ,110 (2) ,104 (1) ,160 (6)
</b>
I want to split the above string and display the digits in the parenthesis as below
4
5
2
1
6
Also Need to count the SUM of them(in this case 18)
Thank You
Sangeet Menon
Followup September 7, 2010 - 7am UTC:
.. Also Need to count the SUM of them(in this case 18)
...
that doesn't make sense. Either you need to
a) count them
b) sum them
looks like you want to sum them...
ops$tkyte%ORA10GR2> variable txt varchar2(100)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :txt := '101 (4),102 (5) ,110 (2) ,104 (1) ,160 (6)'
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data
2 as
3 (
4 select to_number( substr( token, instr(token,'(')+1, instr( token,')')-instr(token,'(')-1 ) )
x
5 from (
6 select
7 trim( substr (txt,
8 instr (txt, ',', 1, level ) + 1,
9 instr (txt, ',', 1, level+1)
10 - instr (txt, ',', 1, level) -1 ) )
11 as token
12 from (select ','||:txt||',' txt
13 from dual)
14 connect by level <=
15 length(:txt)-length(replace(:txt,',',''))+1
16 )
17 )
18 select x, sum(x) over ()
19 from data
20 /
X SUM(X)OVER()
---------- ------------
4 18
5 18
2 18
1 18
6 18
Other ways
September 7, 2010 - 8am UTC
Reviewer: Michel Cador from France
If you are in 11g:
SQL> break on report
SQL> compute sum of num on report
SQL> variable txt varchar2(100)
SQL> exec :txt := '101 (4),102 (5) ,110 (2) ,104 (1) ,160 (6)'
PL/SQL procedure successfully completed.
SQL> with
2 lines as (select level line from dual connect by level <= regexp_count(:txt,'\('))
3 select to_number(ltrim(rtrim(regexp_substr(:txt, '\([[:digit:]]\)', 1, line),')'),'(')) num
4 from lines
5 /
NUM
----------
4
5
2
1
6
----------
18
If you are in 10g:
SQL> with
2 lines as (
3 select level line
4 from dual
5 connect by level <= length(:txt)-length(replace(:txt,'(',''))
6 )
7 select to_number(ltrim(rtrim(regexp_substr(:txt, '\([[:digit:]]\)', 1, line),')'),'(')) num
8 from lines
9 /
NUM
----------
4
5
2
1
6
----------
18
Regards
Michel
String to Target Table - Design
March 8, 2011 - 9pm UTC
Reviewer: Lalitha from New Jersey
Mr.Oracle,
First thanks for your splendid to the oracle community. Here is my doubt,
Design Doubt
The existing application is in DB2, will have to design it for Oracle. The existing application takes input from WEB and BATCH to database.
Input Format :
Input Data comes as just single string Length of Varchar 4000 (Comma separated data) –
Eg - ACC01,1000,12122011,AAA
From Web (Would always send one string / Thus one record)
Batch File – Holds 10000 Records/Strings.
Eg
ACC01,1000,12122011,AAA
ACC01,1000,12122011,AAA
ACC01,1000,12122011,AAA
ACC01,1000,12122011,AAA
.
.
Process : Load data into target table post business validation. I will be using all the columns of the input data, apply business logic and insert the data into the target table's appropriate columns(basically from String to Columns here).
Methodology I think,
1.Through Shell Script and SQL Loader
a.For Batch Operation : Throught Shell Script -> Invoke Sqlloader -> load the data into Staging Table. Perform business logic and then load into target table.
b.For Web : Will have to process it row by row, as the existing application sends the data from each front end user one by one. The max number of Front end user is 50.
2.IF not Shell Script and SQL Loader
a.Load the comma separated string(from web/batch) into a column in staging table through a java/utl_file program. Parse and load the data into an Array (Like the sample codes given by above in this topic), Perform the business validation on these arrays and do bulk insert on the target table.
i . if the business logic is not complex perform it with just SQL and not with arrays.
Since my input columns in the comma seperated string can be dynamic and if I go with option one, I will have to increase the number of columns in my staging table accordingly. More over my shell script option is not yet confirmed.
I will have to go for option 2, which looks like a round about way, since it involves parsing.
The existing DB2 application does this in less than 3 for each record. Which is fine for the customer. But i feel like with Oracle bulk processing over batch it would complete the entire 10K records in less than 20 seconds.
Please let me know the best approach when shell script is not present.
THANKS FOR YOUR TIME.
Regards
Lalitha
Followup March 9, 2011 - 9am UTC:
is this data being put into a form and submitting over http?
is this data being 'file uploaded' to the server using http?
is this data in a file already on the server?
need more detail there...
string parsing in oracle
March 9, 2011 - 12pm UTC
Reviewer: Lalitha Hariharaputhran from New Jersey
Mr Oracle,
Thanks for your time and effort. Really appreciate it as i know the value of your time.
Good News the OS is AIX and I can do shell script.
Current Operation done in DB2
Web(Record by Record) – The user data entered on the screen would concatenated into a comma seperated String (Done by Java) and send the CSV String to the DB2 Procedure. This is records by record processing.
we will receive the file from browser through http.. through Java which writes it into a server folder
Web(Input is a File – Contains max of 1000 records) –
File comes to the desired directory through a java program. Then the java invokes the same DB2 procedure. Here if the input file has 100 records, the DB2 Procedure would be called 100 times. The result is sent back to the front end browser.
Batch – Async (Bulk load 20K records – No parsing) –
The file is moved to the desired directory in mainframe, the pooling job looks for new file every 15 Minutes and load the data post business validations. Result is stored in a table, the front end can query it at desired time frame.
Perform the above in Oracle in AIX.
Methodology I think:
For Web (Record by Record) / WEB(Input as File – Contains max of 1000 records) : -
Option 1 : The java program should read the data from Screen(1 record) or file(1000 records max) and pass it as an array to Oracle. Oracle package would perform the business validation and bulk load into target table.
Oracle Array type will be SQL Types(Create statement).
Guru I need some examples on mapping between Java arrays and Oracle SQL Types. Need to know whether java can send Max 1000 records in an array(oracle holds the same in PGA as I might have concurrent of 50 users Max)
The front end system does’s want to send it as an array does the hard way of sending it as a string (CSV). (I will force for arrays, if I dint succed as they might not want to change the existing front end java code) then
Option 2 : Package to parse the input string -> load into an array, perform business validations and load the data into the target table.
a.Package Module – Parsing Module –PKG.PARSE_M
b.Package Module – Business processing module –PKG.BP_M
For Batch Asnc :
File would be placed at the desired folder by the java program which inturn get from the http.
1.A pooling/ scheduler cron job(shell Scripting) would run looking for new files, on receving the new files would invoke the SQL Loader and load the data into the staging table. Call the PKG.BP_M to perform the business logic and load the data into target table using bulk load.
Please let me know your veiws and suggestions guru.
THANKS FOR YOUR TIME.
Regards
H.Lalitha
string parsing in oracle
March 10, 2011 - 1pm UTC
Reviewer: Lalitha from New Jersey
Mr. Oracle,
Can you help me in providing your feedback on the above specified design.
Appreciate your service to the oracle community. Thanks again.
Regards
Lalitha
Followup March 10, 2011 - 1pm UTC:
if you have files, just use external tables and do 100% of the processing in a stored procedure using BULK sql operations and zero lines of java.
if the data is sent via a web form, just have it go straight to a stored procedure which will parse it and do whatever it wants with it - the amount of data would be tiny (given it is a web form)
string parsing in oracle
March 10, 2011 - 1pm UTC
Reviewer: Lalitha from New Jersey
Thanks for your input guru.
The issue is WEB form collect data from many pages and send it as string of Varchar2(3000) (CSV
format), so cannot parse directly.
Regards
Lalitha
Followup March 10, 2011 - 1pm UTC:
yes you can, why can't you?
string parsing in oracle
March 10, 2011 - 2pm UTC
Reviewer: LALITHA from New Jersey
Thanks guru,
If the input string is like
TOM,KYTE,US,ORACLE,001
and if the validation is if 3rd field is US make it to USA like wise, this can be done with
Regular experison, instr and substr,
But if the input becomes more than one data
TOM,KYTE,US,ORACLE,001
CARY,DB,US,ORACLE,001
MICHEAL,BOM,US,ORACLE,001
then your mean arrays ? If say please give me a link to your site for example.
THANKS FOR YOUR TIME GURU.
Regards
Lalitha
Followup March 10, 2011 - 2pm UTC:
use what is known as a plsql index by table. search this site for "FORALL", you'll see tons of examples.
separting comma separated and comma separated values
September 18, 2011 - 2am UTC
Reviewer: Sridhar from India
Hi Tom,
I have a string like this :
1000 Cowles Clinic Way, Ste A-300, Greensboro, GA 30642 USA
This is a address field. The problem is till the address_line2 its comma separated and then
state,zipcode and country are space separated.
I need to extract all the fields like addr_line1, addr_line2, city,state,zipcode and country
separately.
Since this is a combination of both spaces and ',' kinldy help me to separate the fields.
Currently I am using this code :
select row_id,
comments,
substr
( comments ,
1 ,
instr( comments||',', ',', 1, 1 ) - 1
)
as val_1 ,
substr
( comments ,
instr( comments||',', ',', 1, 1 ) + 1 ,
instr( comments||',', ',', 1, 2 ) - instr( comments||',', ',', 1, 1 ) - 1
)
as val_2 ,
substr
( comments ,
instr( comments||',', ',', 1, 2 ) + 1 ,
instr( comments||',', ',', 1, 3 ) - instr( comments||',', ',', 1, 2 ) - 1
)
as val_3,
substr
( comments ,
instr( comments||',', ',', 1, 3 ) + 1 ,
instr( comments||',', ',', 1, 4 ) - instr( comments||',', ',', 1, 2 ) - 1
)
as val_4
from s_evt_act where row_id in ('1FAC-85C','1FAC-8BD','1G1T-RGL')
Followup September 19, 2011 - 5pm UTC:
if you know how to substr/instr around commas - why is spaces so hard?
Let us say that your query returns the four comma separated fields ( I cannot tell, I do not have your table - I do not have your data - I cannot run your query - but let's pretend it works...)
so it returns four values....
since you can:
select val_1, val_2, val_3, val_4 from (your_query);
you can therefore
select val_1, val_2, val_3, ......(substr/instr functions on val_4).... from (your_query)
to get what you need.
Separate multiple values from string
January 25, 2012 - 9am UTC
Reviewer: PP from UK
Hi,
I have a string like this:
string = 'XZY.1234.9877.7474.000.1233'
and I want to separate all the fields. The required output is :
XZY
1234
9877
7474
000
1233
Please let me know how do I do it.
pull out string between quotes
September 20, 2012 - 10am UTC
Reviewer: Gunjan
And how do I pull out string between quotes for example-
below string should give me A, SEC, B, coretypeid,C etc
"A".coreid "SEC","B".coretypeid "coretypeid", "C".coreid "coreid","D".CoreTypeAssocAliasDescr
"Record Type
Followup September 26, 2012 - 11am UTC:
ops$tkyte%ORA11GR2> variable str varchar2(220)
ops$tkyte%ORA11GR2> exec :str := '"A".coreid "SEC","B".coretypeid "coretypeid", "C".coreid
"coreid","D".CoreTypeAssocAliasDescr "Record Type"'
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> column data format a20
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select substr( x, b+1, e-b-1 ) data
2 from (
3 select instr( :str, '"', 1, 1+(level-1)*2 ) b,
4 instr( :str, '"', 1, 2+(level-1)*2 ) e,
5 :str x
6 from dual
7 connect by level <= (length(:str)-length(replace(:str,'"','')))/2
8 )
9 /
DATA
--------------------
A
SEC
B
coretypeid
C
coreid
D
Record Type
8 rows selected.
Extracting words in a string and providing all combinations
September 26, 2013 - 9pm UTC
Reviewer: Phil from UK
Hi Tom,
Well I have finally given up after spending many hours on a problem related to string splitting.
I'm trying to suggest to a client that we use Oracle Text, but until then, I need to have some word
equivalents stored in a table and then for an input phrase, output all combinations using the
equivalent lookups. Easy eh? Nope - I've tried many ways but have given up. Here was my latest
start. Any pointers on a better method will be so much appreciated:
create table test_search (vterm varchar2(200 char));
create table test_equiv(term1 varchar2(60), term2 varchar2(60));
create unique index tes_equiv_unk1 on TEST_EQUIV (term1, term2);
create or replace procedure add_equiv(pword1 varchar2, pword2 varchar2) is
-- Attempt to capture permutations for the equivalent words...
begin
begin
insert into test_equiv values (lower(pword1), lower(pword2));
exception
when dup_val_on_index then null;
end;
for x in (select term1 term
from test_equiv
where term2 = lower(pword1)
union
select term2
from test_equiv
where term1 = (pword2)) loop
begin
insert into test_equiv values (pword1, x.term);
exception
when dup_val_on_index then null;
end;
begin
insert into test_equiv values (x.term, pword2);
exception
when dup_val_on_index then null;
end;
end loop;
end;
begin add_equiv('hammer','mallet'); end;
begin add_equiv('mallet','cosh'); end;
begin add_equiv('truncheon','mallet'); end;
begin add_equiv('hammer','baton'); end;
begin add_equiv('wooden','soft'); end;
begin add_equiv('steel','metal'); end;
begin add_equiv('round','ball'); end;
begin add_equiv('soft','plastic'); end;
EG Input
========
round soft baton
Output
========
ball soft baton
ball wooden baton
ball plastic baton
ball plastic hammer
... and so on...
|


RT @jthurman42: .@cxhansen and I are at #velocityconf NY! Come see our talk at 4:15 on using @Raspberry_Pi to inform your workspace. http:/…
10/15/2013, 1:03:22 PM
Favs: 0
Retweets: 0
link--- layout: tweet ---
RT @jthurman42: .@cxhansen and I are at #velocityconf NY! Come see our talk at 4:15 on using @Raspberry_Pi to inform your workspace. http:/…
10/15/2013, 1:03:22 PM
Favs: 0
Retweets: 0
link


olitico
Admin
Logout
Dashboard
Political Parties
Government Offices
Candidates
Add Office
×
Add Office
×
Remove this Office?
Cancel
Delete
President
Senator
Governor
Member of Parliament


A Choice for Change
Ambition's candidate for president is 40-year-old Harvard graduate Cinda Maus. She graduated in 1993 with a degree in Economics.
Their candidate for vice president is 38-year-old Yale gradute Taryn Smith. She graduted in 1995 with a doctorate in Political Science.


HONG KONG
GEOGRAPHY
The geography, climate, and location of Hong Kong
FOOD
Famous dishes of Hong Kong
FESTIVALS
The significant holidays and festivals of Hong Kong
LANGUAGES
The different languages spoken in Hong Kong
ATTRACTIONS
The major attractions of Hong Kong


Table Name: tabEmployee Advance
| Sr | Fieldname | Type | Label | Options |
|---|---|---|---|---|
| 1 | naming_series |
Select | Series |
EA- |
| 2 | employee |
Link | Employee | Employee |
| 3 | employee_name |
Read Only | Employee Name | |
| 4 | column_break_4 |
Column Break | ||
| 5 | posting_date |
Date | Posting Date | |
| 6 | department |
Link | Department | Department |
| 7 | section_break_8 |
Section Break | ||
| 8 | purpose |
Small Text | Purpose | |
| 9 | column_break_11 |
Column Break | ||
| 10 | advance_amount |
Currency | Advance Amount |
Company:company:default_currency |
| 11 | paid_amount |
Currency | Paid Amount |
Company:company:default_currency |
| 12 | due_advance_amount |
Currency | Due Advance Amount |
Company:company:default_currency |
| 13 | claimed_amount |
Currency | Claimed Amount |
Company:company:default_currency |
| 14 | section_break_7 |
Section Break | ||
| 15 | status |
Select | Status |
Draft Paid Unpaid Claimed Cancelled |
| 16 | company |
Link | Company | Company |
| 17 | amended_from |
Link | Amended From | Employee Advance |
| 18 | column_break_18 |
Column Break | ||
| 19 | advance_account |
Link | Advance Account | Account |
| 20 | mode_of_payment |
Link | Mode of Payment | Mode of Payment |
Inherits from frappe.model.document.Document
No docs
No docs
No docs
No docs
No docs
No docs
Inherits from frappe.exceptions.ValidationError
Public API
/api/method/erpnext.hr.doctype.employee_advance.employee_advance.get_due_advance_amount
erpnext.hr.doctype.employee_advance.employee_advance.get_due_advance_amount (employee, posting_date)
No docs
Public API
/api/method/erpnext.hr.doctype.employee_advance.employee_advance.make_bank_entry
erpnext.hr.doctype.employee_advance.employee_advance.make_bank_entry (dt, dn)
No docs


Symphoni
SITE:
https://symphoni.cc
DESCRIPTION:
Symphoni is a Seattle based Incubator/FinTech company aiming to lower the barrier
and cost of starting and running a small business.
a geek.
a developer.
a student.
an entrepreneur.
Bayo.
I graduated from MIT in 2011, where I studied Electrical Engineering and Computer Science with minors in Creative and Technical Writing. I worked at Microsoft for 5 years as a program manager on Windows & Windows Phone. I consider himself a geek/developer/student/entrepreneur. Given this is an ever-changing world, I must keep learning, embrace ambiguity, and find the positive outcome of chaos, in order to move forward into the future.
Helping everyone reach their full potential is what drives me in life.  This led me to my passion to pioneer the future. I believe the sky is NOT the limit. Our imaginations are... I plans to leverage this believe and passion into something that can help others grow.
Geek
Developer
Student
Enterpreneur
SITE:
https://symphoni.cc
DESCRIPTION:
Symphoni is a Seattle based Incubator/FinTech company aiming to lower the barrier
and cost of starting and running a small business.
SITE:
https://warnable.com
DESCRIPTION:
Warnable, an emergency communication tool that drastically enhances
an organization’s ability to deal with any threat.
SITE:
https://trustockapp.com
DESCRIPTION:
Trustock is a cloud based inventory management solution for all types of businesses.
<EMAIL_ADDRESS>@LiliBayo
lilijason6


Choose Your Settings
Music
Sound Effects
Vibrate
Colour Blind
Back
MATH OF THE DEAD
Play!
Tutorial
Leaderboard


|
Embedded Artistry Framework
Embedded Systems C++ Framework
|
#include <boot/boot_sequencer.hpp>
#include <nrf52dk_fwdemo_hw_platform.hpp>
#include <os.hpp>
#include <platform/virtual_platform.hpp>
#include <platform_logger.hpp>
#include <platform_options.hpp>
Classes | |
| class | nRF52DK_FrameworkDemoPlatform |
Typedefs | |
| using | VirtualPlatform = nRF52DK_FrameworkDemoPlatform |
| using | PlatformBootSequencer = embvm::BootSequencer< embvm::BootOSWithMainThreadStrategy< VirtualPlatform > > |
Variables | |
| volatile bool | abort_program_ |
| Signal variable to exit the main() loop Declared in main.c, set in Sim Platform's boot.cpp using std::signal. More... | |
| using PlatformBootSequencer = embvm::BootSequencer<embvm::BootOSWithMainThreadStrategy<VirtualPlatform> > |


A weekend in the Glen
Another great weekend with the gang - and Joan came too! - off to the Niagara Glen, cramming 9 people into a 1 bedroom condo, and pulling hard on some limestone (and a touch of sandstone). It was a great time, the weather held together for us and Joan and I had a chance to see John and his new girlfriend Tracey. Add some gamecube Bongos, downtown Niagara, good food at Cafe Tu Tu Tango, some birthday cupcakes and edit: and Easter Eggs! you've got a goodtime. Awesome.
See G's pics and Chris's pics.
The weekend was pretty good climbing-wise: 2 V6's, 'Thunder and Lightning' and 'The Life O'Reilly', and I was close on the V7 sds of 'Life OReilly' when time ran out. Definitely the hardest I've ever bouldered, as far as grades. Except I think those grades are soft - I'm convinced when we were in shape before 'Ye Greate Stoppage Of Climbinge' we could have done them too and I've never sent a V6 before. I'm not certain the 'V7″ was really a grade harder, or maybe just two more V6 moves, IMHO. My impressions of the V6's at Hp40 was they were barely attainable, and the V7s I could barely touch.
Bah, I guess its pretty meaningless anyways - with temperature and humidity variations today's V5 can be tomorrow's V10 and vice versa. I also suspect that as limestone becomes patina'd the grades will move up and as individual climbers get stronger they will downgrade hard climbs. Eventually everything becomes a V4.
And grades are really just guides to how hard the problems are - stars are what we really should be counting. How fun or classic or pure a problem is isn't reflected in the grade.
About this entry
You"re currently reading "A weekend in the Glen," an entry on a crick in the net
- Published:
- 04.17.06 / 10am
- Category:
- Gravity
It was great to see you two too. Too bad I wasn't in better shape to climb. Next time.
J - I think you are being modest. You were climbing strong this past weekend. From what I've seen so far ... and guess it's not a whole lot, the grades were pretty bang on. I would even say for some of the problems they were a bit stiff ('no u-turn' & ‘hook direct' for example).
Speaking of classic lines, ‘Life O'Reilly' is a great example of a problem that is solid fun: slightly overhanging, holds that are barely positive enough, moves that are delicate & dynamic, toe hooks for balance, trusting slopey polished jibs to stand on ... and a dyno to a blind positive finishing jug. Oh yeah it's good. V-Fun.
I'm all about the classics. My climbing goal has always been, and will continue to be, to climb at a level where I can do as many 'classics' as I can.
I am getting closer. As the grades get harder less people climb at that level; as a result less attempts on fewer problems produce less classics at those grades.
Most of all I like climbs that are naturally geometric with few actual 'holds'. Climbs like The Pondering, Great White, and Mortal Kombat are wicked examples of straight up geometry.
Another factor is the homogeny. Climbs like Orange Crush, Boomerang and The Millipede Boulder are climbs I consider more enjoyable due to the commonality of the movements && || holds it has.
I seriously can't believe how well we all climbed. It was pretty insane. I feel like the grade window of possibilities has moved in a big way instead of in small steps like I'm used to. I attibute it to patience, confidence and the ability 'work' things...also having role models that can send things and then give good beta helps. OK, maybe we're getting stronger. HP was a good mental start for all of us.
J, your double V6 was one of the nicest prizes from the weekend. Nice to have that in the bag, I bet.
The point about counting stars is a valid one, I think.  Hopefully there is a lifetime's worth of starred problems in the world.


PDX Finder contains a number of models
Viewing results
Filter By:
- Patient / Tumor
- PDX model
-
MOLECULAR DATA
-
GENE MUTATION
GENETYPEVARIANTGENETYPEVARIANT
-
GENE MUTATION
-
TREATMENT INFORMATION
-
Model Dosing Study
DRUGRESPONSEDRUGRESPONSE
-
Model Dosing Study
| Model | Histology | Primary site | Collection site | Type | Age | Sex | Data Available | Mutations | Platform | Drug | Response |
|---|---|---|---|---|---|---|---|---|---|---|---|
center |
ontology term | origin tissue | sample site | Metastatic | Age | Sex |
|
|
|


If all other deployment methods do not work, CGI will work for sure. CGI is supported by all major browsers but usually has a less-than-optimal performance.
This is also the way you can use a Werkzeug application on Google’s AppEngine, there however the execution does happen in a CGI-like environment. The application’s performance is unaffected because of that.
First you need to create the CGI application file. Let’s call it yourapplication.cgi:
#!/usr/bin/python
from wsgiref.handlers import CGIHandler
from yourapplication import make_app
application = make_app()
CGIHandler().run(application)
If you’re running Python 2.4 you will need the wsgiref package. Python 2.5 and higher ship this as part of the standard library.
Usually there are two ways to configure the server. Either just copy the .cgi into a cgi-bin (and use mod_rerwite or something similar to rewrite the URL) or let the server point to the file directly.
In Apache for example you can put a like like this into the config:
ScriptName /app /path/to/the/application.cgi
For more information consult the documentation of your webserver.


Had a good one yesterday or should that be a bad one? Went up to the dark peaks to repeat one of my favorite walks from last summer - the Edale Skyline. Basically doing the round of Winhill, Mam Tor, Rushup Edge onto Kinder edge and down to Edale Cross. That was the plan at least until I got there.
My original plan of parking in Hope changed as I worried about driving down Winnats Pass at -3 degrees centigrade. Don’t like sliding down hills, even in a Volvo ;) so I ended up parking at a free car park just off Rushup Edge.
I walked along the edge and then onto Brown Knoll which is were the ‘fun’ began. If you don’t know Brown Knoll, imagine this: a large off-camber field on top of a layer of peat. There is one main path for about 2 miles over the knoll which passes right past the trig point. This wouldn’t be too bad but there are small groughs running perpendicular with the path every 50 yards or so. These randomly vary between being hard with little squishiness under foot, through 3/4 of a foot deep trenches made with each foot step to finally…. the man eater:) I stepped in to one grough and instantly realised I was sinking. It was too late, I was down to half way up my thighs. Trying to pull the deeper leg out wasn’t happening and I was worried. Luckily, I remembered the quicksand survival technique of staying flat, so basically lay down on the mud. When my torso was flat, I could rotate my now unweighted hips and my legs appeared, luckily with boots still on - note to self, tie those laces tight until circulation stops before crossing peat bog.
Now the only probably was that I was covered in mud, pretty much up to my chest. Although it was sunny, it was only about 5 degrees and I really didn’t want to get cold and wet. So I took my trousers, gaiters and coat off, and replaced them with my waterproof trousers and fleece. I’ve ever wore waterproofs next to my skin and don’t want to again. Still, at least I could start to warm up and dry off.
By the time I was dry, I had reached the edge of Kinder Scout. Whilst not on my planned route for the day, since I was now longer going to have to get to Hope, I decided to carry up the Pennine Way to Kinder Downfall, then follow the bridleway to Crowden Tower. Now this is only my 2nd attempt at crossing Kinder and my first along this path. Making the Downfall was easy, but following the path SSE was fine for the first kilometre or so down the path but as you leave the path, I got immediately lost on the groughs.  I REALLY HATE PEAT. I decided to backtrack to the Downfall and return down the Pennine Way but I met with a couple that I tagged along with to the edge of Kinder - cheers for the compass bearings!!
It was about 14:00 when I finally got off Kinder. Walked along the edge past Grindesbrook Clough and onwards to The Nab. I didn’t think I had enough time to get to Edale Cross then back so descended down to Edale and back up Mam Tor. Ran out of energy up this as I had neglected food intake. Luckily I only had about 400 metre walk from the top to the car park where I happily quenched my thirst with a litre of water / orange juice (with bits:) ).


--- layout: default permalink: '404.html' ---
Check out the latest post: {{ post.title }} or go back to blog.