Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Pradeep.

Asked: December 11, 2001 - 9:24 am UTC

Last updated: September 26, 2012 - 11:33 am UTC

Version: 8.0.5

Viewed 50K+ times! This question is

You Asked

In my database table, there is one field for the employee name in the order of ( First_name,Last_name,Middle_initial).
Example (Brian,Robbin,D).
I want to separate each of these words and put it into a new table
with three fields (First_name, Last_name, Middle_name).

I want to write a PL/SQL , so for all the employees, it would be done at a time.

and Tom said...



instr and substr is all you need. don't use PLSQL:

variable x varchar2(25)

exec :x := 'Brian,Robbin,D'
select substr( :x||',', 1, instr(:x,',')-1 ) first_name,
substr( :x||',,', instr( :x||',,', ',') +1,
instr( :x||',,', ',', 1, 2 )-instr(:x||',,',',')-1 ) last_name,
rtrim(substr( :x||',,', instr( :x||',,',',',1,2)+1),',') middle_init
from dual
/





Rating

  (181 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

||',' ??

Jim, December 11, 2001 - 11:47 pm UTC

Tom,
I'm not sure why you are concatenating ',' and in some cases ',,' to :x?
I'll keep testing but at the moment it works without
them
Regards
Jim

Tom Kyte
December 12, 2001 - 8:13 am UTC

It is for strings that are malformed (that don't have 2 commas in them)

and I just noticed I missed one case!  see below in bold, we need that extra comma as well


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x varchar2(15) );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 'tom' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 'tom,kyte' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 'tom,kyte,j' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select substr( x||',', 1, <b>instr(x||',',',')</b>-1 ) first_name,
  2         substr( x||',,', instr( x||',,', ',') +1,
  3                           instr( x||',,', ',', 1, 2 )-instr(x||',,',',')-1 )
  4  last_name,
  5         rtrim(substr( x||',,', instr( x||',,',',',1,2)+1),',') middle_init
  6   from t
  7  /

FIRST_NAME       LAST_NAME         MIDDLE_INIT
---------------- ----------------- -----------------
tom
tom              kyte
tom              kyte              j


<b>without the extra commas, we parse wrong</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select substr( x, 1, instr(x,',')-1 ) first_name,
  2         substr( x, instr( x, ',') +1,
  3                           instr( x, ',', 1, 2 )-instr(x,',')-1 )
  4  last_name,
  5         rtrim(substr( x, instr( x,',',1,2)+1),',') middle_init
  6   from t
  7  /

FIRST_NAME      LAST_NAME       MIDDLE_INIT
--------------- --------------- ---------------
                                tom
tom                             tom,kyte
tom             kyte            j


 

INSTR a whole word

Sachin, May 14, 2003 - 6:41 pm UTC

Database Version: 8.1.7.4
Tom,
I want to search a comma delimited string for a whole word..instead of parts of word..

Example:
Vstring varchar2(50) := 'THISTHAT,THIS'

IF INSTR(Vstring, 'THISTHAT') <> 0 AND INSTR(Vstring, 'THIS') <> 0 THEN
.......

Now above statement will be true also for
Vstring = 'THISTHAT'...
so, Is there a way that we can look for whole words...

Thanks a bunch...

Tom Kyte
May 14, 2003 - 11:41 pm UTC

if instr( ','||vstring||',', ',THISTHAT,') <> 0 .....

put a leading/trailing , and look for that as part of the keyword as well

split names

Lakshmi, May 15, 2003 - 7:20 am UTC

Excellent

Simply superb

Sachin, May 15, 2003 - 10:07 am UTC

Thanks Thanks Thanks....

INSTR LIMIT

A reader, May 15, 2003 - 12:12 pm UTC

What is the character limit on string that is being searched...can it be more than 4000...
Looked in docs..couldn't find it there...

Tom Kyte
May 15, 2003 - 5:55 pm UTC

4000 in SQL

32767 in PLSQL

4gig in DBMS_LOB

4gig if you use contains instead of instr (and it'll be faster to boot probably)

Vasukh, July 02, 2003 - 11:50 am UTC

Tom,
Thanks for your help!

Oracle8.1.7
I have a varchar2(1500) field with 45 tab-delimited elements that I want to split and insert into a 45 column table.

Will the above approach give me the best performance for about 200,000 rows?

Is there a way to identify the 45 positions once, in a before-insert trigger and substr() each element in an after-insert trigger for faster performance? Are any other mehods available?

Thanks !!




Tom Kyte
July 02, 2003 - 12:00 pm UTC

I would dump it to a file and use sqlldr to load the delimited file.

it should take all of a minute or two.

see
</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

you can use "flat" to unload the table with a big string.

you can use sqlldr_exp on the 45 column table to have a ctl file built for you easily -- then sqlldr it.

Separating Tab-delimited String

Vasukh, July 02, 2003 - 3:02 pm UTC

Tom,

The process (to separate the Tab-delimited string) needs to be executed from a stored-procedure.

Sorry, I did not fully understand the solution. Should sqlldr be called as an external procedure?

thanks,

Vasukh


Tom Kyte
July 02, 2003 - 3:16 pm UTC

you asked:

Will the above approach give me the best performance for about 200,000 rows?



A stored procedure would not be part of my answer on that.  sqlldr cannot really be called as an extproc (you could run a host command).

You'll have to parse the string.  However, calling that plsql function 45 * 200,000 times will not fall into my definition of "fast".


so, a single insert into modelled after this:

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select x data,
  2             substr( x, 1, x1-1 ) data,
  3         substr( x, x1+1, x2-x1-1 ) data,
  4         substr( x, x2+1, x3-x2-1 ) data,
  5         substr( x, x3+1, x4-x3-1 ) data,
  6         substr( x, x4+1 ) data
  7    from (select x,
  8                decode( x1, 0, to_number(null), x1 ) x1,
  9                decode( x2, 0, to_number(null), x2 ) x2,
 10                decode( x3, 0, to_number(null), x3 ) x3,
 11                decode( x4, 0, to_number(null), x4 ) x4
 12    from (select x,
 13                 instr(x||',',',',1,1) x1,
 14                 instr(x||',',',',1,2) x2,
 15                 instr(x||',',',',1,3) x3,
 16                 instr(x||',',',',1,4) x4
 17            from t1
 18          )
 19          );

DATA       DATA       DATA       DATA       DATA       DATA
---------- ---------- ---------- ---------- ---------- ----------
1,2,3,4,5  1          2          3          4          5
1,2,3,4    1          2          3          4
1,2,3      1          2          3
1,2        1          2
1          1

just add x5..x44
use chr(9) instead of ','
use insert /*+ append */ into .... select .....;


 

Separating Tab-delimited String

Vasukh, July 02, 2003 - 3:24 pm UTC

Thanks!

parsing a string

Anita, October 16, 2003 - 9:28 am UTC

Tom,
Can you please help me with this....

Availabe Input (comma separted values in a table):
Two columns are:
data_points: 1,1,1,1,2,2,3,3,3,1,1,4,4
data_dates:07/08,07/15,07/22,07/29,08/05,08/12,08/19,08/26,09/02,09/09,09/16,09/23,09/30

Required Output:( Group by consecutive dates and data.)

new_data_points , start_date, end_date
1 , 07/08, 07/29
2 , 08/05, 08/12
3 , 08/19, 09/02
1 , 09/09, 09/16
4 , 09/23, 09/30

The date is always a week apart.
Grouping by the data_point to get the new_data_point and get the start and end dates using the data_dates.



Can you please give me a solution for this.

Thanks,
Anita.


Tom Kyte
October 16, 2003 - 10:47 am UTC

got a particular version in mind?

peddua@yahoo.com, October 16, 2003 - 10:54 am UTC

Not particular with the version
But we are using 9i.

Thanks,
Anita.

Tom Kyte
October 16, 2003 - 3:24 pm UTC

well, I can get you this far:

ops$tkyte@ORA920> create type myScalarType as object
  2  ( dp int, dd date )
  3  /
 
Type created.
 
ops$tkyte@ORA920> create type myTableType as table of myScalarType
  2  /
 
Type created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace function parse( p_dp in varchar2, p_dd in varchar2 )
  2  return myTAbleType
  3  PIPELINED
  4  as
  5          l_dp long := p_dp || ',';
  6          l_dd long := p_dd || ',';
  7      n  number;
  8          l_rec myScalarType := myScalarType(null,null);
  9  begin
 10          loop
 11                  n := instr( l_dp, ',' );
 12                  if ( nvl(n,0) > 0 )
 13                  then
 14                          l_rec.dp := substr( l_dp, 1, n-1 );
 15                          l_dp := substr( l_dp, n+1 );
 16                  else
 17                          l_rec.dp := null;
 18                  end if;
 19                  n := instr( l_dd, ',' );
 20                  if ( nvl(n,0) > 0 )
 21                  then
 22                          l_rec.dd := to_date( substr( l_dd, 1, n-1 ), 'mm/dd' );
 23                          l_dd := substr( l_dd, n+1 );
 24                  else
 25                          l_rec.dd := null;
 26                  end if;
 27                  exit when l_rec.dd is null and l_rec.dp is null;
 28
 29                  pipe row( l_rec );
 30          end loop;
 31          return;
 32  end;
 33  /
 
Function created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select *
  2    from TABLE( parse('1,1,1,1,2,2,3,3,3,1,1,4,4',
  3                      '07/08,07/15,07/22,07/29,08/05,08/12,08/19,08/26,09/02,09/09,09/16,09/23,09/30' ) )
  4   order by dd
  5  /
 
        DP DD
---------- ---------
         1 08-JUL-03
         1 15-JUL-03
         1 22-JUL-03
         1 29-JUL-03
         2 05-AUG-03
         2 12-AUG-03
         3 19-AUG-03
         3 26-AUG-03
         3 02-SEP-03
         1 09-SEP-03
         1 16-SEP-03
         4 23-SEP-03
         4 30-SEP-03
 
13 rows selected.
  

but I don't see how to turn that into your result -- i don't see how you got the numbers/dates you did? 

In some contects varchar2 variable limit is 32512 characters...

Piotr Jarmuz, October 16, 2003 - 11:15 am UTC

Interesting to note is the fact that varchar2 variables as parameters to stored procedures (in in/out out) may be "only" 32512 bytes long.

I've checked this in Java and Perl. 32512 is the last value that works, for any bigger it throws:

Exception in thread "main" java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested

But in PL/SQL as you said 32767

Regards,
Piotr

It is even stranger...

Piotr Jarmuz, October 16, 2003 - 11:26 am UTC

create or replace procedure print_length(a varchar2) is
begin
dbms_output.put_line(length(a));
end;
/

declare
x varchar2(32767);
begin
for i in 1..32767 loop
x:=x || 'a';
end loop;
one(x || x || 'a');
end;

will give 65535 and this is the ultimate limit...
strange indeed.

Tom, can you explain that?

Regards.

RE: In Some Contexts

Mark A. Williams, October 16, 2003 - 12:30 pm UTC

A synopsis of this from MetaLink:

Although in PL/SQL a VARCHAR2 has a maximum size of 32767 characters, some overhead is needed when the PL/SQL value is bound to a host variable. Thus, if you are binding PL/SQL values to a host variable, the maximum allowance of a VARCHAR2 variable is 32512 characters.

- Mark

I thoght so...

Piotr Jarmuz, October 16, 2003 - 12:59 pm UTC

Mark,

Thanks for your explanation... I thought so but now I am sure.

Yet another test I have done:

In an OCI external procedure you can create a C string with OCIExtProcAllocCallMemory (which I guess <this may be wrong> corresponds to varchar2 string in PL/SQL) as big as you like (I have created successfully ~ 100 MB string) but when the value is returned to SQL layer it throws the exception:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "P_JARMUZ.RUN_PROGRAM3", line 7
ORA-06512: at line 2

Regards

Hi Piotr

Mark A. Williams, October 16, 2003 - 1:39 pm UTC

Hi Piotr,

I am intrigued by your 65536 case and am looking at a trace of the execution of that right now... very interesting indeed. If I find anything worthwhile in that I will post it here. I don't do much OCI programming, but that sounds interesting as well.

Thanks!

- Mark



Anita

A reader, October 16, 2003 - 4:54 pm UTC

Thanks Tom.

Comma Delimited Data

Anuj, November 26, 2003 - 11:59 am UTC

Hi!!! Tom

I want to extract data from table which has comma delimited data 123,456,789....it could be from 1 to N.It could be same or different for each row and now i want to extract this data and insert it into table with different rows ,ofcourse other columns will have same data. ex : Tab1 is Source table and Tab2 is Target table

Tab1
col1 col2 col3
1 123,456,78 abc
2 902,87,123 xyz
3 12,13 uvw

-------------------


Tab2

col1 col2 col3

1 123 abc
1 456 abc
1 78 abc
2 902 xyz
2 87 xyz
2 123 xyz
3 12 uvw
3 13 uvw
Thanks!

split function

dxl, March 05, 2004 - 8:52 am UTC

Is there not an equivalent function to the SQLSERVER SPLIT function for character strings somewhere out there for ORACLE?
Someone must've written one by now?
I keep meaning to do it but haven't found the time yet!

Tom Kyte
March 05, 2004 - 9:08 am UTC

search this site for str2table

Pluck only first, second, seventh, and eighth columns only

A reader, March 15, 2004 - 10:06 pm UTC

Tom

I have a string which has 10 values delimited by commas. I have to pluck only first, second, seventh, and eighth columns.

Actually I have to loop though hundred such rows and insert into table t. If there are duplicate rows ( based on the values of first and second columns), then I will be doing the dup_val_on_index error condition and updating the rest of the columns.

What is the simplest way of doing it, how can I pluck just those columns from the string. Your generic solution does not work for us, as I have been asked not to create the bad_log table.

If the string is

'once,43434,third,fourth,fifth,sixth,234,66'
'once,43434,third,fourth,fifth,sixth,234,33'

Further I have to ignore rows with 33 value for the last column and pick up the other ones.

Please help.

Tom Kyte
March 16, 2004 - 7:17 am UTC

well, i certainly hope you don't have to "loop" over anything.  If these are in a flat file -- use an external table (9i) or sqlldr (8i and before) so you can query this data.

so, assuming you loaded this into t1, or have an external table t1, all you need to do is "substr"


ops$tkyte@ORA9IR2> create table t1 ( data varchar2(100) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( 'once,43434,third,fourth,fifth,sixth,234,66' );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t1 values ( 'once,43434,third,fourth,fifth,sixth,234,33' );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t2 ( c1 varchar2(10), c2 varchar2(10), c3 varchar2(10), c4 varchar2(10) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2
  2  select substr( data, 1, comma1-1 ) c1,
  3         substr( data, comma1+1, comma2-comma1-1 ) c2,
  4         substr( data, comma6+1, comma7-comma6-1 ) c3,
  5         substr( data, comma7+1, comma8-comma7-1 ) c4
  6    from (
  7  select instr(data,',',1,1) comma1,
  8         instr(data,',',1,2) comma2,
  9         instr(data,',',1,3) comma3,
 10         instr(data,',',1,4) comma4,
 11         instr(data,',',1,5) comma5,
 12         instr(data,',',1,6) comma6,
 13         instr(data,',',1,7) comma7,
 14         length(data)+1 comma8,
 15         data
 16    from t1
 17   where data NOT LIKE '%,33'
 18         )
 19  /
 
1 row created.
 
ops$tkyte@ORA9IR2> select * from t2;
 
C1         C2         C3         C4
---------- ---------- ---------- ----------
once       43434      234        66
 
 

A reader, March 16, 2004 - 10:29 am UTC

Well, that is the only option left, we are using forms9i, the flat file will on user desktop
and we cannot use utl_file, external tables as they require the file to be on the database server,
and we cannot use sqlloader as this functionality needs to be triggered of by the user from the
application whose access is through a web browser.

You have anything to comment on this. What I'm saying is what you have opined else where on your
web site

you missed the point

A reader, March 16, 2004 - 10:31 am UTC

Tom, the data is in the csv file and not in a table, you solution will work when it is in the table. Can you please demo the case where it is in the flat file and we have to get it into the table i.e. the first, second, seventh and eight columns.

Tom Kyte
March 16, 2004 - 11:37 am UTC

you missed my main points:

<quote>
well, i certainly hope you don't have to "loop" over anything. If these are in
a flat file -- use an external table (9i) or sqlldr (8i and before) so you can
query this data.

</quote>

else, use whatever programming language you like to "loop" over the data, parse it and insert it. it becomes a simple "programming 101" problem at that point.


Heck, just read the file and insert each row into a table with a trigger, let the trigger do the substring (just as coded above) and insert into the real table if you like.



Interesting scenario

A reader, March 17, 2004 - 5:09 pm UTC

'once,43434,third,fourth,fifth,sixth,2344,66'

The 234 above sometimes can also be like 2,344. Now I should not consider that comma, as the data's intent is that it is 2,344 and not 2 and 344 separately.

How can I disregard the commas within the column values, I have to do that before I pluck the columns apart.

Tom Kyte
March 17, 2004 - 6:29 pm UTC

good luck with that! have fun (someone has "messed up" big time here)

Tell me, given:

1,200,100,300,2,1,3,2,1


is that

1
200,100
300
2
1
3
2
1

or

1
200
100,300
.....


hmmm.... you cannot get there from here as far as I'm concerned unless you want garbage in...


tab delimited

A reader, March 17, 2004 - 5:49 pm UTC

Tom , if I'm using a tab delimited file, then how should I represent a tab in my code, will a ' ' represent the tab. I have to capture the tab positions.



Tom Kyte
March 17, 2004 - 6:33 pm UTC

chr(9) is a tab

next best option

A reader, March 17, 2004 - 8:09 pm UTC

Tom I guess the next best option would be to request for a tab separated file, and then translate, replace all the commas with ''.

What is your opinion.

Further, I see that when I save a xls file as tab delimited some of the values are within " ", what does that signify?

Tom Kyte
March 17, 2004 - 8:28 pm UTC

if you have a tab delimited file, you are back in business.

you would just use to_number(substr(....),'999,999,999,999')


if you have things in quotes that means they (the strings) contain the delimiter.

See

</code> http://asktom.oracle.com/~tkyte/SkipCols/index.html <code>

the delimited package might be of interest.

to_number(substr(....),'999,999,999,999')

A reader, March 17, 2004 - 10:20 pm UTC

Why use to_number(substr(....),'999,999,999,999')

why cant I just do the substr and do replace, translate and remove the commas, and insert into my loading table.

Further..

SQL> select to_number( '42,4324', '999,999,999,999') from dual;
select to_number( '42,4324', '999,999,999,999') from dual
                  *
ERROR at line 1:
ORA-01722: invalid number 

Tom Kyte
March 18, 2004 - 7:10 am UTC

you just gave the perfect reason for using to_number.


what the HECK is 42,4324

I call it "garbage" if it is supposed to be a number. I'd really like it to fail so I can get the correct data.

If you just do a replace -- you would turn that garbage, an obvious data error, into 424,324 silently.


garbage in, garbage out...

A reader, March 18, 2004 - 8:14 pm UTC

Tom

when I substr my string the seventh column happens to be mapping to a number column in my table into which I need to load the data.

When I substr it I get something like "1,992" ( with the quotes), I then do a replace, translate and load the data into the table.

My flat file is a tab delimited file.

I tried doing a replace, translate on the new line as soon as I get it( i'm using text_io), but that somehow messes up my data when I do the substr I'm not aboble to pluck the 1,992.

What else can be the approach.



Tom Kyte
March 19, 2004 - 8:08 am UTC

ops$tkyte@ORA9IR2> select to_number( trim( '"' from '"1,999"' ), '999,999,999' ) from dual;
 
TO_NUMBER(TRIM('"'FROM'"1,999"'),'999,999,999')
-----------------------------------------------
                                           1999
 
 

A reader, March 19, 2004 - 11:38 am UTC

why trim, why not replace and translate, why cant I just remove the comma and insert into my number database column.

can you please discuss the basics behind your insistence on your converting it into a number.

Tom Kyte
March 19, 2004 - 1:57 pm UTC

you asked me and i answered.

replace and translate would accept garbage and give you garbage but silently give it to you.

trim removes only leading and trailing "'s (only place you expect them)
to_number accepts only numbers


"11111111,111111111111"

is not a number, you would accept it, i would not.

check for the occurance of a value in a string

A reader, March 31, 2004 - 6:15 pm UTC

Tom

in sql I need to check if a database column value contains 'zz' at any place, or at the end of the string i.e. the last 2.

what is the best way to do this validation

Tom Kyte
March 31, 2004 - 6:22 pm UTC

create table t
( ...
x varchar2(50) check ( x like '%zz%' ),
...
)



String Parsing - Use Java API (i.e. StringTokenizer)

A reader, March 31, 2004 - 10:12 pm UTC

PLSQL does not have many built-in packages for String parsing.

On the other hands, you can use Java APIs for very power string parsing. For example, you can write a Java Stored Procedure that using Java's StringTokenizer to parsing your string.

I haven't seen any existing PLSQL packages that have the same features of StringTokenizer.

People should use Java APIs. You can't beat a proven and robust Java classes.

Tom Kyte
April 01, 2004 - 9:34 am UTC

well, there is regexp in 10g and i have personally never seen anything done in java with strings that I cannot do as easily in plsql


so -- show me, prove me wrong. give us some java that does something really cool, in a compact fashion. make sure to provide the specs in english (like you would in a production piece of code) and we'll see!


RE: Java API

Frank, April 01, 2004 - 12:01 am UTC

<quote>People should use Java APIs. You can't beat a proven and
robust Java classes.
</quote>

Sure, we'll go out and hire a Java programmer immediately, 'cause we SHOULD use Java APIs.
Or as someone put it:
Java is just a language
Java is just a language
...
(repeat this)
;)

Tom Kyte
April 01, 2004 - 9:53 am UTC

you know, one of my favorite C functions of all times is 'strtok' (the printf family of functions is not too far behind)

therefore, we should all be writing code in C


Wait, COBOL makes doing reporting pretty easy.
.......

Here Is the An Example of Java StringTokenizer Vs. PLSQL

A reader, April 01, 2004 - 4:34 pm UTC

Hi Tom,

Here is my example of how Java StringTokenizer class vs. PLSQL. It will show that implementing with Java is easier, faster, and more robust than writing your own PLSQL package. I have not seen any Oracle built-in PLSQL packages (maybe except 10g RegEx) have the same features as StringTokenizer.

Propose:
Parse the string into multiple tokens, delimited by space, semicolon, comma, colon, etc.

Package Specs:
PACKAGE cms_util is
function get_token (in_str in varchar2)
return varchar2array;

function get_tokens (p_str in varchar2)
return varchar2array as
language java
name 'com.han.cms.utilities.StringUtilities.getTokens(java.lang.String)
return com.han.cms.plsql.Varchar2Array';

function get_tokens (p_str in varchar2, p_delims in varchar2)
return varchar2array as
language java
name 'com.han.cms.utilities.StringUtilities.getTokens(java.lang.String,
java.lang.String) return com.han.cms.plsql.Varchar2Array';
end;

PLSLQ Implementation In Body (See how long the code):
PACKAGE BODY cms_util is
/**
Disclaimer: Taken this function from some web site.
Sorry, I forget the original author
*/
function get_token (in_str in varchar2)
return varchar2array is
str_left varchar2 (32767);
str_end pls_integer;
sp1 pls_integer; /* For space */
sc1 pls_integer; /* For semi-colon */
st1 pls_integer; /* For stop */
cm1 pls_integer; /* For comma */
cn1 pls_integer; /* For colon */
c_greatest_value constant pls_integer := 2147483647;
v_tokens varchar2array := varchar2array ();
v_count integer := 1;
begin
/* Use this if tabs are to be treated as tabs */
str_left := in_str;
/* Use this if tabs are to be treated as spaces */
str_left := replace (in_str, chr (9), ' ');

loop
/* Finding first occurence of respective characters */
sp1 := instr (str_left, ' ', 1);
sc1 := instr (str_left, ';', 1);
cm1 := instr (str_left, ',', 1);
st1 := instr (str_left, '.', 1);
cn1 := instr (str_left, ':', 1);

/* If the character doesn't occur set first_occurence to max value */
if sp1 = 0 then
sp1 := c_greatest_value;
end if;

if sc1 = 0 then
sc1 := c_greatest_value;
end if;

if cm1 = 0 then
cm1 := c_greatest_value;
end if;

if st1 = 0 then
st1 := c_greatest_value;
end if;

if cn1 = 0 then
cn1 := c_greatest_value;
end if;

str_end := least (sp1, sc1, cm1, st1, cn1);

if str_end = c_greatest_value then
if str_left is not null then
dbms_output.put_line (str_left);
v_tokens.extend;
v_tokens (v_count) := str_left;
v_count := v_count + 1;
end if;

exit;
end if;

if str_end = sp1 then
/* We don't want to print out spaces. Spaces appear
in dbms_outputif we
"set serveroutput on size 1000000 FORMAT WORD_WRAPPED"
*/
if (substr (str_left, 1, str_end - 1) != ' ') then
dbms_output.put_line (substr (str_left, 1, str_end - 1) );
end if;

str_left := substr (str_left, str_end + 1);
v_tokens.extend;
v_tokens (v_count) := str_left;
v_count := v_count + 1;
else
/* We don't want to print out spaces. Spaces appear in
dbms_outputif we
"set serveroutput on size 1000000 FORMAT WORD_WRAPPED" */
if (substr (str_left, 1, str_end - 1) != ' ') then
dbms_output.put_line (substr (str_left, 1, str_end - 1) );
end if;

str_left := substr (str_left, str_end);
dbms_output.put_line (substr (str_left, 1, 1) );
str_left := substr (str_left, 2);
v_tokens.extend;
v_tokens (v_count) := str_left;
v_count := v_count + 1;
end if;

if str_left is null then
exit;
end if;
end loop;

return v_tokens;
end;
end;

Java Class Inside The Database:
public class StringUtilities {
public static Varchar2Array getTokens(String values, String delimiters,
boolean returnDelims) {
StringTokenizer strTokenizer = new StringTokenizer(values, delimiters,
returnDelims);
String[] tokens = null;
int count = strTokenizer.countTokens();

if (count > 0) {
tokens = new String[count];

for (int i = 0; i < count; i++) {
tokens[i] = strTokenizer.nextToken();
}
}

//return tokens;
return new Varchar2Array(tokens);
}
}

Note: Varchar2Array is a wrapper class generated by JPublisher based on the varchar2array nested table.

CREATE OR REPLACE
type varchar2array is table of varchar2(32676);

Conclusion:
1. PLSQL lacks some basic built-in libraries, e.g. String Tokenizer
2. Use Java J2SE to supplement PLSQL when PLSQL lacks the features (i.e. NOT code some long PLSQL to do things that are already done by Java; I would even argue that it is more robust to use Java proven APIs than newly written code).

Please let me know if you need more prove.

Disclaimer: I have not reviewed 10g RegEx. Will 10g RegEx be available in previous Oracle versions? If not, what is your recommendation to existing customers who want to use RegEx? I would recommend they use Java Stored Procedure that call Java RegEx (either the JDK 1.4 RegEx or other RegEx libraries, like Apache).

Thanks.

Tom Kyte
April 02, 2004 - 9:45 am UTC

now, benchmark it in SQL using it like people use substr

Let us know how that goes....

.. and here a PL/SQL example

eugene, April 02, 2004 - 3:44 am UTC

This PL/SQL code was run with Oracle 8.0, 8i, 9iR2.


BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

SQL> create or replace procedure strToken(line IN varchar2, tokenChar IN varchar2 default ';')
  2  is
  3    TYPE  tokenTableType is TABLE of varchar2(4000)   -- table for Stringtoken
  4      index by binary_integer;
  5    --
  6    tokens    tokenTableType;
  7    vCnt      integer := 1;
  8    myLine    varchar2(4000) := null;
  9    vPos      integer := 1;
 10    --
 11  begin
 12    while (vPos <= length(line))
 13    loop
 14      if (length(replace(tokenChar,substr(line, vPos, 1), '')) = length(tokenChar)) then
 15        myLine := myLine || substr(line, vPos, 1);
 16      elsif (myLine is not NULL) then
 17        tokens(vCnt) := myLine;
 18        myLine := null;
 19        vCnt := vCnt + 1;
 20      end if;
 21      vPos := vPos + 1;
 22    end loop;
 23    --
 24    if (myLine is not NULL) then
 25      tokens(vCnt) := myLine;
 26    end if;
 27    --
 28    vCnt := 1;
 29    for vCnt in 1..tokens.count()
 30    loop
 31      dbms_output.put_line(vCnt||'  '||tokens(vCnt));
 32    end loop;
 33    --
 34  end;
 35  /

Procedure created.

SQL>
SQL> set serveroutput on
SQL>
SQL> set timing on
SQL> exec strToken('ADBC;ERFG.khjf,hjkfhsd POt',';. ');
1  ADBC
2  ERFG
3  khjf,hjkfhsd
4  POt

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.46
SQL> exec strToken('ADBC;ERFG.khjf,hjkfhsd POt',';, ');
1  ADBC
2  ERFG.khjf
3  hjkfhsd
4  POt

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.47
SQL> exec strToken('ADBC;ERFG.khjf,hjkfhsd POt',';,.');
1  ADBC
2  ERFG
3  khjf
4  hjkfhsd POt

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.47
SQL>
SQL> 

RE: Here Is the An Example of Java StringTokenizer Vs. PLSQL

A reader, April 02, 2004 - 12:31 pm UTC

<quote>
now, benchmark it in SQL using it like people use substr

Let us know how that goes....
</quote>

I think you are missing these points:

1. Oracle lacks some built-in PLSQL package (e.g. String Tokenizenier).

2. If there is NO existing built-in PLSQL package, one should try to use other existing proven library, like Java, NOT trying to spend hours of coding and testing when you can use Java inside the database.

yes, PLSQL probably can be coded to solve many problems. However, do people want to reinvent the wheel? If yes, why people use Oralce built-in package? They should just code everything on their own.

Or, is the wheel only implied as Oracle? Do people think that they need to invent when Oracle does not have certain features in its built-in packages? If yes, they are NOT living in the real world. The wheel is bigger than Oracle. So, learn more than PLSQL please. Java is NOT difficult.

Tom, isn't Oracle using use Java inside its database? Some built-in PLSQL packages just call Java.

Isn't Oracle want people to use Java inside the database?

Isn't Oracle still the ONLY database that can run Java? I don't think DB2 or SQL Server even have any plan to run Java. I remember maybe only Postgres plans to Java JVM inside its database. Anyway, Postgres isn't a database to many of us.

Thanks.


Tom Kyte
April 02, 2004 - 1:50 pm UTC

1) i've never really missed it -- instr, substr, et.al really do seem to cover it.

2) i don't spend hours. it literally takes seconds to instr/substr.

do you see me writing a string tokenizer? no. do you see me using the builtins that exist to get the answer I need in a second (efficiently)? yes.

so, there you go.

No, I don't want you to call plsql or even less java from SQL - that'll only slow it down. you call plsql from sql if and when there is quite simply *no other way*.


Now, if you had a couple documents AND you needed to parse them THEN java would be a good idea.

If you have a couple of simple strings AND they are columns in a table AND you needed to extract data from them THEN substr/instr et.al. are a good idea


So, it is not that java is evil, or to be avoided - just that the heinous overheads of calling plsql or java (specially the latter) from SQL far outweighs the keystrokes you may or may not save.



RE: Here Is the An Example of Java StringTokenizer Vs. PLSQL

A reader, April 02, 2004 - 3:42 pm UTC

Tom,

Thank you for your reply.

<quote>
1) i've never really missed it -- instr, substr, et.al really do seem to cover
it.
<quote>

Agree. Use built-in functions if they cover your problem.

<quote>
2) i don't spend hours. it literally takes seconds to instr/substr.
</quote>

Yes, I have no doubt that you probably spend seconds. However, I see many other PLSQL programmers write many lines of code trying to do things that are already done by some APIs.

<quote>
No, I don't want you to call plsql or even less java from SQL - that'll only
slow it down. you call plsql from sql if and when there is quite simply *no
other way*.
</quote>

Sorry, I wasn't thinking about calling PLSQL from SQL. I was thinking about other applications calling the PLSQL package that execute some logics (include select) and do DML statements. You do recommend the use of PLSQL package in this scenarios, right? I see too many Java programmers don't use package, resulting in many DB connection calls.

<quote>
Now, if you had a couple documents AND you needed to parse them THEN java would
be a good idea.

If you have a couple of simple strings AND they are columns in a table AND you
needed to extract data from them THEN substr/instr et.al. are a good idea


So, it is not that java is evil, or to be avoided - just that the heinous
overheads of calling plsql or java (specially the latter) from SQL far outweighs
the keystrokes you may or may not save.
</quote>

I totally agree with you on this point.

Thank you.

alphanumeric validation

A reader, May 17, 2004 - 1:04 pm UTC

Tom

I need to verify if the first 4 charaters of a varchar2 column is alphanumeric.

SQL> var lv_alphanumeric varchar2(30);
SQL> 
SQL> 
SQL> exec :lv_alphanumeric := '123a' ;

PL/SQL procedure successfully completed.

SQL> 
SQL> select substr(:lv_alphanumeric, 1,4) from dual;

SUBS
----
123a

I have to select only those values from a database table column whose first 4 characters are alpha-numeric.

Please guide me as to how I can accomplish this.

Thanks in advance
 

Tom Kyte
May 17, 2004 - 4:08 pm UTC

replace ... with the alphabet.

  1  select :x from dual
  2* where translate( upper(substr(:x,1,4)), '0123456789ABC...XYZ', rpad('X',36,'X')) = 'XXXX'
ops$tkyte@ORA9IR2> /
 
:X
--------------------------------
123a!
 

A reader, May 17, 2004 - 5:10 pm UTC

SQL> EXEC :X := 'AAAA' ;

PL/SQL procedure successfully completed.

SQL>    select :x from dual
  2     where translate( upper(substr(:x,1,4)), '0123456789ABC...XYZ',
  3   rpad('X',36,'X')) = 'XXXX' ;

:X
--------------------------------
AAAA

Well, my objective is to filter out those values whose first 4 charaters are not alpha-numeric. As you see above it returns a value even when I passed 'AAAA' as the value.

Please help.
 

Tom Kyte
May 18, 2004 - 7:38 am UTC

AAAA is alpha numeric? not sure what you mean.

A reader, May 17, 2004 - 10:51 pm UTC

If we pass a value like 'AAAA' it should not return a value, if we pass something like '1AAA', then it should return that value.


Tom Kyte
May 18, 2004 - 11:27 am UTC

and so what about AA1A etc etc etc. or 1111...

you gotta give ALL of the rules (and you may well discover that once you list them out in a list, you'll be able to use the above technique -- extend it a tad -- and do it yourself)........

so, what are all of the rules -- is is really "single digit number followed by 3 letters or numbers" or what exactly....

alpha-numeric generally includes AAAA as well as 1111 as well as 1AAA. be more specific.

A reader, May 18, 2004 - 9:31 am UTC

The database column is a varchar2 columns.

It contains values like 'AAAABCDEFTGF', 'AA23FGDSGS', 'AAAAAAA'

Now I need to select values which are like 'AA23FGDSGSD' i.e. whose first 4 charactes have a number(s) in it. For example 'AA23FGDSGS' - here the first four characters have numbers 'AA23'.



create table tom_test( x varchar2(10));
insert into tom_test values ('AAAABCDEF');
insert into tom_test values ('AA23FGDSGS');
insert into tom_test values ('AAAAAAA');
commit;

Thanks


A reader, May 18, 2004 - 9:35 am UTC

Tom

Mind you, the values can also be like '12345', '45678'. But I want only values which have alphabets and numbers in the string, and that too in the first 4 characters of the string.

create table tom_test( x varchar2(10));
insert into tom_test values ('AAAABCDEF');
insert into tom_test values ('AA23FGDSGS');
insert into tom_test values ('AAAAAAA');
insert into tom_test values ('12345');
insert into tom_test values ('45678');

commit;


Sql has a way

John Spencer, May 18, 2004 - 11:29 am UTC

Tom:

Great discussion on parsing. Regarding the Alpha numeric problem, I think that A Reader is looking for something like

SELECT *
FROM tom_test
WHERE LENGTH(REPLACE(TRANSLATE(UPPER(SUBSTR(x,1,4)),
'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
RPAD('X',26,'X')),'X',NULL)) BETWEEN 1 and 3


Gabe, May 18, 2004 - 12:12 pm UTC

To "A reader" ... what's up with the 1 and 2 star ratings? ... you've got your answer(s) ... you should be able to take it from there if it doesn't satisfy you (and that is only because you are so sloppy in the way you use the word “alphanumeric”).

The letters of the alphabet: a, A, b, B, … z, Z are referred as Alpha Characters.
The digits: 0,1,2, … 9 are referred as Numeric Characters.
An Alphanumeric Character is any of the Alpha or Numeric Characters.

So all of these strings, ‘ABcD’, ‘z2X3’, ‘0234’, contain exactly 4 alphanumeric characters.

After all your follow-ups my reading of what you want is: get all the strings having only alphanumeric chars in the first 4 positions but not all of them alpha chars and not all of them numeric chars. That is, ‘z2X3’ is OK but ‘ABcD’ and ‘0234’ are not.

BTW, you should also clarify for yourself:
1. what to do about null values
2. what to do if the string has less than 4 chars
3. is the whole thing case-sensitive or not

FYI as well … when Tom wrote '0123456789ABC...XYZ' he meant for you to fill up the rest of the alfa chars between C and X … it looks like you just used it as is.

create table tt( x varchar2(10));
insert into tt values ('MAAABCDEF');
insert into tt values ('MA23FGDSGS'); <== only this one satisfies your requirement
insert into tt values ('AA');
insert into tt values ('12345');
insert into tt values (null);

Here is what a quick and dirty query would be:

select t.x_original, t.x4
from (
select x x_original
,translate( upper(substr(x,1,4))
,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
,rpad('0',10,'0')||rpad('X',26,'X')
) x4
from tt
) t
where instr(t.x4,'0') > 0
and instr(t.x4,'X') > 0
;

You get 0 stars for your “due diligence” … and give Tom a 5, will you?

John ... your length based solution would bring back a string like '12' ... my reading of the requirement is that he/she wants both alphas and digits ... but then again, which exactly is the requirement was the only real problem here.


A reader, May 18, 2004 - 4:34 pm UTC

Holy Sparrow, I cannot imagine that Tom will get upset with the rating, and not follow up on this thread

Tom Kyte
May 19, 2004 - 7:30 am UTC

i didn't need to followup :) someone else did.

the best way to do this

Jamba, June 11, 2004 - 4:34 pm UTC

LoanCode LAmt Time Total
15938, yestica,Jocklin, F480,769,0.991,762, 24, 10,386



My flat file which save from a .xls file as a txt comma delimited file(the columns names as shown below are available in the .txt file when copied form .xls) looks like the above. The four columns with labels are the ones I need to pluck out, and load it into a table.

It sounds simple untill now.

But now it gets interesting...


The number of columns and the order of columns can change i.e. the above can also look like



LoanCode Lamt time Total
yestica ,15938, Jocklin, 762,F480,769,0.991, 24, 10,386


In order to pluck the values I need, it is apparent that I have to first loop through the first row containing labels and capture the comma positions for those labels, and then capture the data depending on the comman positions capture for each respective value.


can you please show the smartest and best way to do the above i.e loop through the header row and capture the comman before and after even column which has a label.

Thanks

Jamba

Tom Kyte
June 11, 2004 - 4:48 pm UTC

am i allowed to use a shell script? i ask, because what I would do is

head the file, get line one
pipe that to sed to make it be a comma separated list of names
create a control file with the names in that order
run sqlldr.

on unixland, simple things like this are simple. on windows, this gets to be a tad harder.

Jamba, June 11, 2004 - 5:21 pm UTC

NO shell script is not an option. The existing code is in PL/SQL.

The solution needs to be in PL/SQL.

Please suggest.

jamba

Tom Kyte
June 11, 2004 - 5:32 pm UTC

ok, so you are reading the file like this:

LoanCode LAmt Time Total
15938, yestica,Jocklin, F480,769,0.991,762, 24, 10,386

and you parse a line into an array (i'm assuming, it is what I would do)

so, line2 would be read into an array:

array(1) = 15938
array(2) = yestica
and so on.

your insert looks like:

insert into t(loanCode,lAmt,Time,total) values (array(1), array(2), ... );

since the columns could get mixed up -- i would read line 1 into an array and then have variable loadCode_idx, lAmt_idx, .....

after reading the first line, i would search the array to discover that loadCode is the n'th element in there, that lAmt was the m'th element and so on..

then you


insert into t(loanCode,lAmt,Time,total) values (array(loanCode_idx), array(lAmt_idx), ... );




pseudo code

Jamba, June 11, 2004 - 5:45 pm UTC

What I want to do is

1.Capture the total number of commas in the given line( the first line which contains the column names)

2.Loop through the line so many times, pluck each value and see what it is , for example if it is LOANCODe, then I would set the two variables comma_1, and comm_2 for that columna name etc

Does it sound o.k.

can you display the code for me

Thx


Tom Kyte
June 12, 2004 - 9:36 am UTC

sounds like you have the algorithm you want to use in mind -- you display the code for us... straight forward procedural coding 101 here.

You just described exactly what I described I think -- process first line and figure out that loancode is the nth column. very straight forward "instr/substr" code.

Code

Jamba, June 12, 2004 - 11:09 am UTC

First step:

Get the total number of commas in the header line

V_delimiter_CnT := ( LENGTH(LINE_feed) - LENGTH( REPLACE(LINE_feed, ',','')) );

for the count of delimiter loop..

For i in 1 .. V_delimiter_CnT LOOP

For each iteration look at the commas, starting from 0 ( just as the iteration count)

IF I = 0 THEN

V_COL_HEAD := SUBSTR(LINE_feed, INSTR(LINE_feed,',',0,1) +1, ( INSTR(LINE_feed,',',1,1));

ELSE

V_COL_HEAD := SUBSTR(LINE_feed, INSTR(LINE_feed,',',I,I) +1, ( INSTR(LINE_feed,',',1,I + 1) - INSTR(LINE_BUFFER,',',I,I) -1));

END IF;

IF UPPER(LTRIM(RTRIM(V_COL_HEAD)))) = 'LOANCODE' THEN

V_LCODE_POS1 = I;
V_LCODE_POS2 =I +1;

-- do the same for other column headers

END IF;

END LOOP;

--AFTER WE ITERATE THROUGH THE ENTIRE HEADER ROW, NOW MOVE ON TO THE NEXT ROW, WHICH CONTAINS THE DATA. LOAN CODE IS AVAILABLE BETWEEN COMMAS I AND I +1. NOW TO PLUCK THE VALUE DO THE BELOW


BEGIN

V_LCODE := LTRIM(RTRIM(SUBSTR(LINE_BUFFER, V_LCODE_POS1 +1,V_LCODE_POS2 - V_LCODE_POS1 -1)));
-- SAME FOR OTHER COLUMNS
END;

NOW insert into the table..

BEGIN

INSERT INTO TABLE T VALUES(V_LCODE , OTHER VALUES);

END;



Tom, please sugges if the above code can be written in a better and more efficient way.

Thanks





Tom Kyte
June 12, 2004 - 11:57 am UTC

ops$tkyte@ORA9IR2> declare
  2      l_text varchar2(200) := 'loancode,    loanamt,        foobar';
  3      l_tmp  varchar2(200);
  4      l_loancode_idx number;
  5      l_loanamt_idx  number;
  6      l_foobar_idx   number;
  7  begin
  8      l_text := ',' || upper(replace(l_text,' ','')) || ',';
  9
 10      l_tmp := substr( l_text, 1, instr( l_text, ',LOANCODE,' ) );
 11      l_loancode_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 12
 13      l_tmp := substr( l_text, 1, instr( l_text, ',LOANAMT,' ) );
 14      l_loanamt_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 15
 16      l_tmp := substr( l_text, 1, instr( l_text, ',FOOBAR,' ) );
 17      l_foobar_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 18
 19      dbms_output.put_line
 20      ( l_loancode_idx || ', ' ||
 21        l_loanamt_idx  || ', ' ||
 22        l_foobar_idx );
 23  end;
 24  /
1, 2, 3
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> declare
  2      l_text varchar2(200) := 'foobar, loanamt, loancode';
  3      l_tmp  varchar2(200);
  4      l_loancode_idx number;
  5      l_loanamt_idx  number;
  6      l_foobar_idx   number;
  7  begin
  8      l_text := ',' || upper(replace(l_text,' ','')) || ',';
  9
 10      l_tmp := substr( l_text, 1, instr( l_text, ',LOANCODE,' ) );
 11      l_loancode_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 12
 13      l_tmp := substr( l_text, 1, instr( l_text, ',LOANAMT,' ) );
 14      l_loanamt_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 15
 16      l_tmp := substr( l_text, 1, instr( l_text, ',FOOBAR,' ) );
 17      l_foobar_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 18
 19      dbms_output.put_line
 20      ( l_loancode_idx || ', ' ||
 21        l_loanamt_idx  || ', ' ||
 22        l_foobar_idx );
 23  end;
 24  /
3, 2, 1
 
PL/SQL procedure successfully completed.


<b>would be a way to setup the indices rather easily (you might check that the nvl(indices,0) != 0 to make sure you are getting the columns you want)

and as I said, I would be parsing a line into an array:</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2      type array is table of varchar2(4000) index by binary_integer;
  3      l_data array;
  4      l_txt  long := 'how,now,browish,looking,cow';
  5
  6      procedure parse( p_str in varchar2 )
  7      is
  8          l_str long := p_str || ',';
  9          l_n   number;
 10      begin
 11          l_data.delete;
 12          loop
 13              l_n := instr( l_str, ',' );
 14              exit when nvl(l_n,0) = 0;
 15
 16              l_data( l_data.count+1 ) := substr( l_str, 1, l_n-1 );
 17              l_str := substr( l_str, l_n+1 );
 18          end loop;
 19      end;
 20
 21  begin
 22      parse( l_txt );
 23      for i in 1 .. l_data.count
 24      loop
 25          dbms_output.put_line( l_data(i) );
 26      end loop;
 27  end;
 28  /
how
now
browish
looking
cow
 
PL/SQL procedure successfully completed.
 


<b>and then 

   insert into t ( loanamt, loancode, foobar ) 
   values ( l_data( l_loanamt_idx ), l_data( l_loancode_idx ), l_data( l_foobar_idx ) );

</b>

putting it all together:

ops$tkyte@ORA9IR2> create table t ( loancode int, loanamt int, foobar int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2      l_text varchar2(200) := 'loancode,    loanamt,        foobar';
  3      l_tmp  varchar2(200);
  4      l_loancode_idx number;
  5      l_loanamt_idx  number;
  6      l_foobar_idx   number;
  7
  8
  9      type array is table of varchar2(4000) index by binary_integer;
 10      l_data array;
 11      l_txt  long := '111, 222, 333';
 12
 13      procedure parse( p_str in varchar2 )
 14      is
 15          l_str long := p_str || ',';
 16          l_n   number;
 17      begin
 18          l_data.delete;
 19          loop
 20              l_n := instr( l_str, ',' );
 21              exit when nvl(l_n,0) = 0;
 22
 23              l_data( l_data.count+1 ) := substr( l_str, 1, l_n-1 );
 24              l_str := substr( l_str, l_n+1 );
 25          end loop;
 26      end;
 27  begin
 28      l_text := ',' || upper(replace(l_text,' ','')) || ',';
 29
 30      l_tmp := substr( l_text, 1, instr( l_text, ',LOANCODE,' ) );
 31      l_loancode_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 32
 33      l_tmp := substr( l_text, 1, instr( l_text, ',LOANAMT,' ) );
 34      l_loanamt_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 35
 36      l_tmp := substr( l_text, 1, instr( l_text, ',FOOBAR,' ) );
 37      l_foobar_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 38
 39      parse( l_txt );
 40
 41      insert into t ( loancode, loanamt, foobar )
 42      values ( l_data( l_loancode_idx ), l_data( l_loanamt_idx ), l_data( l_foobar_idx ) );
 43  end;
 44  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t;
 
  LOANCODE    LOANAMT     FOOBAR
---------- ---------- ----------
       111        222        333



<b>as an exercise for you -- make it even more efficient -- use array processing and bulk load the table (as an exercise for you...)</b>
 

How about my solution

Jamba, June 12, 2004 - 12:25 pm UTC

Tom

How about my solution , is it correct. Will it work according to you.

Your solutio is great.

Tom Kyte
June 12, 2004 - 1:19 pm UTC

no clue. I don't really debug others code (that's for you to do). When asked "how would you do this", I'll show you (as i did).

write you code, and then debug it.

or, use mine as a template. Your's is more psuedo code than anything compilable. I would use an array, use nice compact, easy to understand subroutines - lots of modular easy to debug code.

A reader, June 12, 2004 - 4:49 pm UTC

Tom

The actual file contains more than 100 rows. The first row is the header row. The below should be done only for the header row.

28 l_text := ',' || upper(replace(l_text,' ','')) || ',';
29
30 l_tmp := substr( l_text, 1, instr( l_text, ',LOANCODE,' ) );
31 l_loancode_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
32
33 l_tmp := substr( l_text, 1, instr( l_text, ',LOANAMT,' ) );
34 l_loanamt_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
35
36 l_tmp := substr( l_text, 1, instr( l_text, ',FOOBAR,' ) );
37 l_foobar_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
38


From the next row onwards we have to the the below


39 parse( l_txt );

41 insert into t ( loancode, loanamt, foobar )
42 values ( l_data( l_loancode_idx ), l_data( l_loanamt_idx ), l_data(
l_foobar_idx ) );
43 end;
44 /


In your example you have conveniently names two variables l_txt and l_text , one for header row, and the other for the data.

I dont have that luxury. I will be reading the file, when I read the first row, I have to find out the positions of the columns we require, and from the second row onwards I have to insert data into the table.

How should I verify for the first row in your solution?

Tom Kyte
June 13, 2004 - 10:47 am UTC

yes, because I have an EXAMPLE OF HOW I WOULD PARSE

a) line 1
b) line 2 and on

you of course will be using utl_file to read line 1
and then lines 2, .......


You open file
you read a line
you parse it to get column headings
you then process each subsequent line

Jamba, June 12, 2004 - 5:25 pm UTC

"as an exercise for you -- make it even more efficient -- use array processing
and bulk load the table (as an exercise for you...)
"

I would love to use bulk inserts here, but my business logic does not allow me to do so

in the flat file, loancode might repete, in that case I have to add up the loanamount and then insert one summary row. I do thi susing a when dup val on index excception , and I update the given loadcode with the second loan amount.

If I use bulk inserts I dont this this will be possible. Please advise.


Tom Kyte
June 13, 2004 - 10:47 am UTC

search this site for

"save exceptions"

Searched your site for "Save Exceptions"

A reader, June 13, 2004 - 11:19 am UTC

I found the below

----------
12 loop
13 fetch c BULK COLLECT INTO data LIMIT 100;
14 begin
15 FORALL i IN 1 .. data.count SAVE EXCEPTIONS
16 insert into t2 values data(i);
17 EXCEPTION
18 WHEN dml_errors THEN
19 errors := SQL%BULK_EXCEPTIONS.COUNT;
20 l_cnt := l_cnt + errors;
21 FOR i IN 1..errors LOOP
22 dbms_output.put_line
23 ('Error occurred during iteration ' ||
24 SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
25 ' Oracle error is ' ||
26 SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
27 end loop;
28 end;

On line 24 I am printing out the SUBSCRIPT into DATA of the offending record.

therefore:

data( SQL%BULK_EXCEPTIONS(i).ERROR_INDEX )

contains the primary key - et.al. - of the "bad record" to do with what you
like.

-----------------


I guess for my case I should use FORLLL. I dont think I can use BULKCOLLECT, as at every place use used BULKCOLLECT there is a cursor declaration.

Thanks

Tom Kyte
June 13, 2004 - 11:41 am UTC

i was trying to point you to save execeptions, yes. that is a feature of the forall.

A reader, June 13, 2004 - 11:29 am UTC

Tom I plugged in the

----
You open file
you read a line
you parse it to get column headings
you then process each subsequent line
------

into your code as pseudo code. Do you think use of a counter variable to determine the first row is essential, is there any other way to determine the first row of the file ?






ops$tkyte@ORA9IR2> declare
2 l_text varchar2(200) := 'loancode, loanamt, foobar';
3 l_tmp varchar2(200);
4 l_loancode_idx number;
5 l_loanamt_idx number;
6 l_foobar_idx number;
7
8
9 type array is table of varchar2(4000) index by binary_integer;
10 l_data array;
11 l_txt long := '111, 222, 333';
12
13 procedure parse( p_str in varchar2 )
14 is
15 l_str long := p_str || ',';
16 l_n number;
17 begin
18 l_data.delete;
19 loop
20 l_n := instr( l_str, ',' );
21 exit when nvl(l_n,0) = 0;
22
23 l_data( l_data.count+1 ) := substr( l_str, 1, l_n-1 );
24 l_str := substr( l_str, l_n+1 );
25 end loop;
26 end;
27 begin

-- OPEN THE FILE using UTL_FILE

LOOP

GET THE ROWS FROM THE FILE

GET THE FIRST ROW( USE A counter variable to determine the first row)

28 l_text := ',' || upper(replace(l_text,' ','')) || ',';
29
30 l_tmp := substr( l_text, 1, instr( l_text, ',LOANCODE,' ) );
31 l_loancode_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
32
33 l_tmp := substr( l_text, 1, instr( l_text, ',LOANAMT,' ) );
34 l_loanamt_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
35
36 l_tmp := substr( l_text, 1, instr( l_text, ',FOOBAR,' ) );
37 l_foobar_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
38

FROM SECOND ROW ONWARDS COME DIRECTLY TO LINE 39

39 parse( l_txt );
40
41 insert into t ( loancode, loanamt, foobar )
42 values ( l_data( l_loancode_idx ), l_data( l_loanamt_idx ), l_data(
l_foobar_idx ) );

INCREMENT THE COUNTER ROW

END THE LOOP

CLOSE THE FILE

43 end;
44 /


Tom Kyte
June 13, 2004 - 11:42 am UTC

you open a file

you read a line

you sort of KNOW it is the first line, no?


fopen
read
set up headers
loop
read exiting when no more data
process row
end loop
fclose

no counters needed anywhere as far as I can see.



A reader, June 13, 2004 - 12:07 pm UTC

----------
you open a file

you read a line

you sort of KNOW it is the first line, no?


fopen
read
set up headers
loop
read exiting when no more data
process row
end loop
fclose

no counters needed anywhere as far as I can see.
----
1.
You open the file and start getting the lines in a loop. How can you get just the first line set up the headers , and then start the loop. THE HEADER LINE IS ALSO A PART OF THE SOURCE FILE i.e. the file you are reading.


fopen

LOOP
read

-- I was referring to use of a counter here
set up headers if it is the first line
--

read exiting when no more data
-- process the row from the second line onwards
process row
--
end loop
fclose


2.Dont we have to read once for each row.






Tom Kyte
June 13, 2004 - 12:20 pm UTC

get_line

it is the first "record" in the file.

this is "file processing 101" stuff here. pretty basic "read a record, process a record".

you fopen
you get_line <<== this must be your headers
you loop
getting more lines to process <<<== this must be your data.

A reader, June 13, 2004 - 12:16 pm UTC

"you sort of KNOW it is the first line, no?"

Done your way, I will be looking to set up headers for each row I read, and headers happen to be only the first line of the source file, from the second line onwards I have the data.

If we do it your way, when I loop to the second line. I will try to set up the headers, for a row which doesnt have the headers but the data. Hence the headers which have been setup while reading the first line, now will get disturbed, and will result in no data being inserted.

I'm a little bit confused here.


Tom Kyte
June 13, 2004 - 12:35 pm UTC

no no no.

you 

open file
you read a row, these are your headers
you set up the variables that tell you "column 1 is foobar", "column 2 is barfoo"
you then loop
    reading more data, which you process, using the variables you set up outside
    the loop



this is pretty basic "file processing stuff".  

Do not over analyze this, it really is "as easy as it sounds".

sigh.  here is the completed thought I was trying to nudge you towards:

ops$tkyte@ORA9IR2> create or replace package demo_pkg
  2  as
  3      procedure LoadFile( p_dir in varchar2, p_fname in varchar2 );
  4  end;
  5  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body demo_pkg
  2  as
  3
  4  type array is table of varchar2(4000) index by binary_integer;
  5
  6
  7  procedure parse( p_str in varchar2, p_data in out array )
  8  is
  9      l_str long := p_str || ',';
 10      l_n   number;
 11  begin
 12      p_data.delete;
 13      loop
 14          l_n := instr( l_str, ',' );
 15          exit when nvl(l_n,0) = 0;
 16          p_data( p_data.count+1 ) := substr( l_str, 1, l_n-1 );
 17          l_str := substr( l_str, l_n+1 );
 18      end loop;
 19  end;
 20
 21  procedure process_header( p_str in varchar2,
 22                            p_loancode_idx out number,
 23                            p_loanamt_idx  out number,
 24                            p_foobar_idx   out number )
 25  is
 26      l_text long := ',' || upper(replace(p_str,' ','')) || ',';
 27      l_tmp  long;
 28  begin
 29      l_tmp := substr( l_text, 1, instr( l_text, ',LOANCODE,' ) );
 30      p_loancode_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 31
 32      l_tmp := substr( l_text, 1, instr( l_text, ',LOANAMT,' ) );
 33      p_loanamt_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 34
 35      l_tmp := substr( l_text, 1, instr( l_text, ',FOOBAR,' ) );
 36      p_foobar_idx := length(l_tmp)-nvl(length(replace(l_tmp,',','')),0);
 37  end;
 38
 39
 40  procedure LoadFile( p_dir in varchar2, p_fname in varchar2 )
 41  is
 42      l_input    utl_file.file_type;
 43      l_buffer   long;
 44      l_data         array;
 45      l_loancode_idx number;
 46      l_loanamt_idx  number;
 47      l_foobar_idx   number;
 48  begin
 49      l_input := utl_file.fopen( p_dir, p_fname, 'r', 32000 );
 50
 51      utl_file.get_line( l_input, l_buffer );
 52      process_header( l_buffer, l_loancode_idx, l_loanamt_idx, l_foobar_idx );
 53
 54      loop
 55          begin
 56              utl_file.get_line( l_input, l_buffer );
 57          exception
 58              when no_data_found then
 59                  exit;
 60          end;
 61
 62          parse( l_buffer, l_data );
 63
 64          insert into t ( loancode, loanamt, foobar )
 65          values ( l_data( l_loancode_idx ), l_data( l_loanamt_idx ), l_data( l_foobar_idx ) );
 66      end loop;
 67      utl_file.fclose( l_input );
 68      commit;
 69  end;
 70
 71
 72  end;
 73  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> !echo "loancode, loanamt, foobar" > /tmp/test1.dat
 
ops$tkyte@ORA9IR2> !echo "1, 2, 3" >> /tmp/test1.dat
 
ops$tkyte@ORA9IR2> !echo "11, 22, 33" >> /tmp/test1.dat
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> !echo "foobar, loanamt, loancode" > /tmp/test2.dat
 
ops$tkyte@ORA9IR2> !echo "333, 222, 111" >> /tmp/test2.dat
 
ops$tkyte@ORA9IR2> !echo "3333, 2222, 1111" >> /tmp/test2.dat
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from t;
 
4 rows deleted.
 
ops$tkyte@ORA9IR2> exec demo_pkg.loadFile( '/tmp', 'test1.dat' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec demo_pkg.loadFile( '/tmp', 'test2.dat' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
 
  LOANCODE    LOANAMT     FOOBAR
---------- ---------- ----------
         1          2          3
        11         22         33
       111        222        333
      1111       2222       3333
 
ops$tkyte@ORA9IR2>



<b>It will be left as as exercise for you to add proper amounts of error handling to this functioning code</b>


 

A reader, June 13, 2004 - 12:26 pm UTC

you fopen
you get_line <<== this must be your headers
you loop
getting more lines to process <<<== this must be your data.

--------

I think I'm getting your point



you are saying

fopen

get_line for headers

loop

get_line for processing data

end loop



I'm saying


fopen

loop

get_line

if first line set up headers

if second line process the row for data

end loop


Well yes, your method is convenient and needs no counters at all.

One question though

fopen

get_line for headers -- here I get the first line

loop

get_line for processing data -- dont I start getting from first line again here ?

end loop

fclose

Thanks

Tom Kyte
June 13, 2004 - 12:37 pm UTC

see above.

answer to question is "no, of course not. you are just reading line by line by line. to you and to you alone the first row is special. to utl_file, it is just bytes terminated by a newline. the header is not "meaningful""

A reader, June 13, 2004 - 3:42 pm UTC

12 loop
13 fetch c BULK COLLECT INTO data LIMIT 100;
14 begin
15 FORALL i IN 1 .. data.count SAVE EXCEPTIONS
16 insert into t2 values data(i);
17 EXCEPTION
18 WHEN dml_errors THEN
19 errors := SQL%BULK_EXCEPTIONS.COUNT;
20 l_cnt := l_cnt + errors;
21 FOR i IN 1..errors LOOP
22 dbms_output.put_line
23 ('Error occurred during iteration ' ||
24 SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
25 ' Oracle error is ' ||
26 SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
27 end loop;
28 end;




Tom

When we use a cursor and BULK COLLECT we can set the limite for each insert like the below

BULK COLLECT INTO data LIMIT 100

But when I just use FOR ALL , how many rows get inserted at a given time.

Can I control the number of rows I insert when I use just the FOR ALL.

Thanks




Tom Kyte
June 13, 2004 - 5:06 pm UTC

forall i in 1 .. data.count <<=== ?

you tell us that i runs from 1 to whatever.

A reader, June 13, 2004 - 3:54 pm UTC

Well in the below given example, you have been able to use FOR ALL because you have a total record cound before hand, see i in 1 .. data.count.

But in the above case using utl_file, at given time we would get to to see one and only one row, in this scenario how can we use FOR ALL.

for all example:
-----------------
loop
13 fetch c BULK COLLECT INTO data LIMIT 100;
14 begin
15 FORALL i IN 1 .. data.count SAVE EXCEPTIONS
16 insert into t2 values data(i);
17 EXCEPTION
18 WHEN dml_errors THEN
19 errors := SQL%BULK_EXCEPTIONS.COUNT;
20 l_cnt := l_cnt + errors;
21 FOR i IN 1..errors LOOP
22 dbms_output.put_line
23 ('Error occurred during iteration ' ||
24 SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
25 ' Oracle error is ' ||
26 SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
27 end loop;
28 end;


our current case:
-----------------

loop
55 begin
56 utl_file.get_line( l_input, l_buffer );
57 exception
58 when no_data_found then
59 exit;
60 end;
61
62 parse( l_buffer, l_data );
63
64 insert into t ( loancode, loanamt, foobar )
65 values ( l_data( l_loancode_idx ), l_data( l_loanamt_idx ), l_data(
l_foobar_idx ) );
66 end loop;

As you see at a given time we get only one row.

Even on your website, for examples on loading from file to table, you have not used bulk loading anywhere.

Please illustrate bulk insert in our given case.



Tom Kyte
June 13, 2004 - 5:07 pm UTC

i just used bulk loading in that example.

You would need to read in an array the elements here. as I said, left as an exercise (yes, I could do it but I'm choosing not to at this juncture).


I wrote the entire thing so far. Your turn.

A reader, June 13, 2004 - 5:26 pm UTC

Well, what I'm stating here is

If I have a cursor , I can use bulk collect , collect 100 or 200 at a time and then bulk inser it using forall in 1 .. 200.

But in our case, YES we use an array , but the array at a given has only one row at a given time, because we are getting one line at a time from the source file( in the cursor and bulk collect example we are getting 100 at a time from the table).

So in our case even if I say

For FOR ALL IN 1 .. l_data.COUNT

it will have only data enough for only one row in it because I'm getting one line at a time from source file and parsing it. Note again, I parse one row at a time and load the array with its column.

Did I make myself clear. Please exaplain if I'm missing something.

Tom Kyte
June 13, 2004 - 5:41 pm UTC

I'm saying "as a programmer, you know about arrays, you know how to fill up arrays, you are paid to write code".

this is just procedural coding stuff here. ok:

40 procedure LoadFile( p_dir in varchar2, p_fname in varchar2 )
41 is
42 l_input utl_file.file_type;
43 l_buffer long;
44 l_data array;
45 l_loancode_idx number;
46 l_loanamt_idx number;
47 l_foobar_idx number;
48
49 l_loancodes array;
50 l_loanamts array;
51 l_foobars array;

52 begin
53 l_input := utl_file.fopen( p_dir, p_fname, 'r', 32000 );
54
55 utl_file.get_line( l_input, l_buffer );
56 process_header( l_buffer, l_loancode_idx, l_loanamt_idx, l_foobar_idx );
57
58 loop
59 begin
60 utl_file.get_line( l_input, l_buffer );
61 exception
62 when no_data_found then
63 exit;
64 end;
65
66 parse( l_buffer, l_data );
67 l_loancodes( l_loancodes.count+1 ) := l_data( l_loancode_idx );
68 l_loanamts ( l_loanamts.count+1 ) := l_data( l_loanamt_idx );
69 l_foobars ( l_foobars.count+1 ) := l_data( l_foobar_idx );

70 end loop;
71 utl_file.fclose( l_input );
72
73 forall i in 1 .. l_loancodes.count
74 insert into t ( loancode, loanamt, foobar )
75 values ( l_loancodes(i), l_loanamts(i), l_foobars(i) );

76 commit;
77 end;
78
79
80 end;
81 /

Package body created.


parameterize delimiter in the parse routine

A reader, June 14, 2004 - 12:31 am UTC

Tom, really surprised that you hardcoded delimiter in the parse procedure to ','. In my opinion it should be parameterized so that one can send any delimiter they like , it may be a ',' or a chr(9) or a |.

Is there a reason why you did not parameterize it?


Tom Kyte
June 14, 2004 - 7:45 am UTC

because it is an example.

because it has no error handling either.

because it is a "demo", proof of concept, how to do this.

because it is NOT production code.



CSV specs

Duke Ganote, June 22, 2004 - 11:46 am UTC

A more interesting challenge would be full CSV specs (see 
http://en.wikipedia.org/wiki/Csv
 where the fields may be delimited by double-quotes as well as commas (and MUST be if the field contains double quotes).

As for the previous reviewer, parameterization is simple enough, adds some clarity, but is unnecessary for demo code:

SQL> 
  1  select x, instr(x||&&delimiter,&&delimiter,1)
  2*   from ( select 'asdfasdf,12344|123' X from dual )
SQL> /
Enter value for delimiter: ','
old   1: select x, instr(x||&&delimiter,&&delimiter,1)
new   1: select x, instr(x||',',',',1)

X                  INSTR(X||',',',',1)
------------------ -------------------
asdfasdf,12344|123                   9
 

array as a parameter

A reader, June 27, 2004 - 12:48 pm UTC

Tom, is it possible for a database procedure to accept arrays as a parameter and inser the same into a table, i.e. can we convert the above procedure you provided to be a procedure which accepts respective arrays for each column, and call this procedure from the client side and bulk insert into a table.

We have observed that oracle forms at least in 9i does not allow bulk binds.



Tom Kyte
June 28, 2004 - 7:37 am UTC

yes, you can use plsql table types.


create package my_pkg
as
type array is table of <datatype> index by binary_integer;
....

MAX SIZE OF line in UTL_FILE.get_LINE

A reader, July 16, 2004 - 9:00 am UTC

HI Tom,

I'm reading a file with UTL_FILE.get_LINE as
UTL_FILE.get_LINE (file_handle,cur_line.line);
But this is throwing me an error when it finds a line with 2000 characters. How can I handle this?

please advise as usual.

Thanks,

Tom Kyte
July 16, 2004 - 11:25 am UTC

what does your fopen look like? by default, max line size is 1022 characters.

you might have to specify the linesize in the fopen call.

Worked Fine ..Thanks !!

A reader, July 16, 2004 - 12:19 pm UTC

I'd not mentioned anything in FOPEN. ANd now I've put 32000 there. And my procedure is working fine.
Thanks very much for your time.

Reading this site alone is simply a treat & A GREAT learning experience.

Thanks

generic loader

dxl, August 20, 2004 - 7:00 am UTC

Tom

I would like to write a generic data loader that takes a text file from a client pc uploads it onto an appserver and loads the data from the app server running java via jdbc to a table in oracle. (I know that we should ftp the file onto the db server and use sql loader but we are restricted from doing this for now.)

I have had to do this a few times already for various applications so i thought i'd have a go at writing a generic loader to get the data into a table ready for validation.

So far in my experience having tried different methods eg varrays of object types, table types or varchars containing delimited strings etc.. i have found the quickest way is to use oracle batch updates from jdbc into gtt tables.
Therefore what i propose to do is to get the app server to extract the delimited data strings from the file and batch insert the whole strings into a gtt which is made up of large varchar2 fields. Then a stored procedure can be used to take each delimited string and split the string into an application specific staging table eg one containing all columns of varchars ready for validation or even the real table with all the correct datatypes etc.

Do you think this is the best way, given the restrictions i have?? (we are on 8.1.7.4 at the moment but will be on 9i in the near future)


My problem that i am having is how best to convert a unknown number of delimited elements in a string into real table made up of eg varchar2(4000) fields?

ie

GTT table is something like:

App_id Batch_id DATA_STRING
------ -------- -----------------------------
3 4 abc,def,ghi,j,klm,n,
3 4 op,q,rstu,vxw,y,z,

and insert it into an app table eg

Field1 Field2 Field3 Field4 Field5 Field6
------ ------ ------ ------ ------ ------
abc def ghi j klm n
op q rstu vxw y z


the data strings will have the same number of elements for each app or at least the same number of delimiters maybe some elements are null.

So the data_string needs to be queried in order to first determine how many elements each data line should have.

Then I need to dynamically create a table made up of varchar2 fields to hold this data ie:

desc STAGE_TABLE


FIELD1 VARCHAR2(4000) NULL,
FIELD2 VARCHAR2(4000) NULL,
..
FIELD10 VARCHAR2(4000) NULL

1)
Maybe each application could build this table prior to running a load so that the stored procedure can then take the delimited string dynamically and load it into this table? So in this case how should i best convert the data_strings into separated fields?

I could dynamically create an object to hold the separated data using str2tbl, but then i need to load it into the STAGE_TABLE, how can i do this efficiently without using procedural code? please can you give an example of how to do this?? I know about str2tbl and using TABLE(CAST(str2tbl('abc,def,h,ij'))) etc but i can't quite work out a good non-procedural way to achieve this??

2) instead i could query the data string to dynamically create a staging table of varchar2 fields and then somehow load the data in? same problem as i 1) to get the strings into this dynamically build table but time because the table was build dynamically would the stored procedure be able to reference it at compile time or would you use dbms_sql to do this?? I'm not too familiar with dbms_sql so an example would be helpful here.




Please can you help with this approach or suggest a better way! My main problem is in taking the delimited string, parsing it and inserting the separted fields into a staging table in bulk without writing procedural code.

Many thanks
(if you need more information i can provide it)


Tom Kyte
August 20, 2004 - 11:16 am UTC

one word for you:

sqlldr

ok but

dxl, August 20, 2004 - 11:36 am UTC

i understand what you're saying but given that i cannot get the text file onto the database machine and i HAVE to go through the app server via jdbc:

what would your answer be now?!

is it some sort of pivot type query?

Tom Kyte
August 21, 2004 - 10:43 am UTC

you are writing code, no "pivot queries" you are just inserting into a table.

if you want to do this efficiently, using the least resources in the most maintainable fashion you will use sqlldr -- you don't need to be on the server, sqlldr goes over the network

otherwise you are writing code, code to parse, bind, execute simple inserts with.

ok

dxl, August 23, 2004 - 4:18 am UTC

Yes i've accepted the fact that i have to write code to do this and i'm ok with that!

All i was asking (maybe not very clearly) was how best (using code) to convert a table of data looking like:


App_id Batch_id DATA_STRING
------ -------- -----------------------------
3 4 abc,def,ghi,j,klm,n,
3 4 op,q,rstu,vxw,y,z,



and get it into a table so that it looks like :



ID APP_ID BATCHID Field1 Field2 Field3 Field4 Field5 Field6
-- ------ ------- ------ ------ ------ ------ ------ ------
1 3 4 abc def ghi j klm n
2 3 4 op q rstu vxw y z


What would be the most efficient techniques to achieve this transformation in sql when i don't know before hand how many delimited elements make up the data string??

Tom Kyte
August 23, 2004 - 8:12 am UTC

you will write java code to do this? not sure what you are asking for here. seems in the java code you have 3, 4, abc, def, ..... and you would

a) prepare an insert statement
b) read a string, parse it, bind it, execute a)
c) goto b until eof



.

dxl, August 23, 2004 - 9:12 am UTC

Well i was trying to write a generic data loader so wanted to do it using plsql not from the java front end.

Ok so how about just this question (forget java, sql loader i am asking about sql or plsql):

I just want to know if it is possible to write any sql that could take this table looking like:

App_id Batch_id DATA_STRING
------ -------- -----------------------------
3 4 abc,def,ghi,j,klm,n,
3 4 op,q,rstu,vxw,y,z,


and insert it into a table that looks like:

ID APP_ID BATCHID Field1 Field2 Field3 Field4 Field5 Field6
-- ------ ------- ------ ------ ------ ------ ------ ------
1 3 4 abc def ghi j klm n
2 3 4 op q rstu vxw y z


without doing it procedurally and also what if the number of elements in the data string were unknown until runtime, ie each data_string has the same number of elements but we don't know what that is until runtime. Assume that the second table has been prebuilt with the correct number of elements (or maybe we could build it dynamically?).

I don't know how to ask this anymore clearly. I just want to know if this is possible or does the unknown number of elements of the data_string make it impossible??

Thanks

Tom Kyte
August 23, 2004 - 10:02 am UTC

in order to build an insert into statement -- you NEED to know the number of columns (eg: you cannot parse the insert into statement in the first place if you didn't know the number of columns)

ops$tkyte@ORA9IR2> select length(data_string)-length(replace(data_string,',','')) from t where rownum = 1;
 
LENGTH(DATA_STRING)-LENGTH(REPLACE(DATA_STRING,',',''))
-------------------------------------------------------
                                                      6
 
<b>that query tells you how many columns you have...</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select id, app_id, batch_id,
  2         substr( f1, 1, instr(f1,',')-1 ) f1,
  3         substr( f2, 1, instr(f2,',')-1 ) f2,
  4         substr( f3, 1, instr(f3,',')-1 ) f3,
  5         substr( f4, 1, instr(f4,',')-1 ) f4,
  6         substr( f5, 1, instr(f5,',')-1 ) f5,
  7         substr( f6, 1, instr(f6,',')-1 ) f6
  8    from (
  9  select rownum id, app_id, batch_id,
 10         substr( data_string, instr(data_string, ',', 1, 1 ) + 1 ) f1,
 11         substr( data_string, instr(data_string, ',', 1, 2 ) + 1 ) f2,
 12         substr( data_string, instr(data_string, ',', 1, 3 ) + 1 ) f3,
 13         substr( data_string, instr(data_string, ',', 1, 4 ) + 1 ) f4,
 14         substr( data_string, instr(data_string, ',', 1, 5 ) + 1 ) f5,
 15         substr( data_string, instr(data_string, ',', 1, 6 ) + 1 ) f6
 16    from (select app_id, batch_id, ','||data_string data_string from t)
 17         )
 18  /
 
        ID     APP_ID   BATCH_ID F1   F2   F3   F4   F5   F6
---------- ---------- ---------- ---- ---- ---- ---- ---- ----
         1          3          4 abc  def  ghi  j    klm  n
         2          3          4 op   q    rstu vxw  y    z

<b>
then it is a simple matter of substring/instring to parse</b>
 

Excelent

Senthil Kumar, August 29, 2004 - 7:12 am UTC

Helped Me a Lot

What about Lengthy Parameters

R.Senthil Kumar, August 29, 2004 - 7:33 am UTC

Hi,

One of my Parameter Looks Like
84, 50249, 2, 1, 2, , , 1100000005, 50250, , , , , INR, N, 2, 1, 2, 2, 2, 2, 2, 2, 2, , Y,J,66666,888,RAM_MATERIAL,RM001RG2003,TAX,1



Tom Kyte
August 29, 2004 - 12:01 pm UTC

what about them? you have 4000 bytes to play with.

You know the max field lengths of your data.

otherwise, you have the object type.

anto, November 05, 2004 - 11:51 am UTC

Hi Tom,

What is the easiest way to check whether there is any tab character in any of the field values in a table ?

Tom Kyte
November 05, 2004 - 5:36 pm UTC

instr on all of the fields

where instr( c1, chr(9) ) > 0 or instr( c2, chr(9) ) > 0 ...

anto, November 08, 2004 - 5:05 pm UTC

Thanks, Tom

Slightly different seperators

Aaron Rouse, November 17, 2004 - 3:54 pm UTC

Hello,

I have the need to parse through some data in a very similar fashion only it has one level above the example. The table I am accessing stores user's computer information and users can have any number of computers though normally it is no more than one or two computers per user. Each computer record has three values and is seperated by a / and then if more than one record those are seperated by commas. Here is an example of the table I would be pulling from and the table I want to put the data in.

-- Table to pull data from
CREATE TABLE TABLEONE
(
USER_ID INT,
COMPUTER_RCD VARCHAR2(1000)
);
-- Two Computers
INSERT INTO TABLEONE
VALUES
(27137529, 'AMEHRABIAN-OFS/DELL_ITSTDV45C1K/8NBDC1S, MEHRABIAN-OFS/DELL_ITSTDV45C1K/GRX3601');
-- One Computer
INSERT INTO TABLEONE
VALUES
(27137531, 'AHYAR11-OFS/DELL_ITSTDV40C0K/1WWF61S');
-- Three Computers
INSERT INTO TABLEONE
VALUES
(27137578, 'ADELUNG1-L-OFS/DELL_ITSTDV31W2K-STD/8W5P701, ADELUNG-L1-OFS/DELL_ITSTDV45C0K/56KMP31, ARNOLD1/DELL_ITSTDV40C1K/6XJ0D01');

-- Table to insert into
CREATE TABLE TABLETWO
(
USER_ID INT,
COMPUTER_NAME VARCHAR2(25),
COMPUTER_ID VARCHAR(40),
COMPUTER_SERIAL VARCHAR(20)
);


Tom Kyte
November 17, 2004 - 4:35 pm UTC

select user_id,
substr( rcd, comma_1+1, comma_2-comma_1-1 ) r1,
substr( rcd, comma_2+1, comma_3-comma_2-1 ) r2,
substr( rcd, comma_3+1, comma_4-comma_3-1 ) r3
from (
select user_id,
instr(rcd,',',1,1) comma_1,
instr(rcd,',',1,2) comma_2,
instr(rcd,',',1,3) comma_3,
instr(rcd,',',1,4) comma_4,
rcd
from (
select user_id, ','||computer_rcd||',' rcd
from tableone
)
)
/


gets you started, apply same technique to parse the resulting r1,r2,r3 components and a multi-table insert to insert the resulting data.

Thanks ...

Aaron Rouse, November 17, 2004 - 5:16 pm UTC

Thanks, definately gets me down the right path. I just realized some people actually have up to 26 machines so going to re-evaluate the steps and goals on this. Since really the only limiting factor in their system is the varchar2(1000) on how many machines can be tracked per user.


Tom Kyte
November 18, 2004 - 10:02 am UTC

would then be tempted to pipeline it (test this, just off the top of my head, not fully debugged)




ops$tkyte@ORA9IR2> create or replace type myTabletype as table of myScalarType
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function parse_it( p_cursor in sys_refcursor ) return myTableType
  2  pipelined
  3  as
  4      l_row myScalarType := myScalarType( null, null, null, null );
  5      l_comma number;
  6      l_piece long;
  7      l_rcd   long;
  8      l_slash number;
  9      l_user_id number;
 10  begin
 11      loop
 12          fetch p_cursor into l_user_id, l_rcd;
 13          exit when p_cursor%notfound;
 14          l_row.user_id := l_user_id;
 15          l_rcd := l_rcd || ',';
 16          loop
 17              l_comma := instr( l_rcd, ',' );
 18              exit when nvl(l_comma,0) = 0;
 19              l_piece := substr( l_rcd, 1, l_comma-1 );
 20              l_rcd := substr( l_rcd, l_comma+1 );
 21              l_slash := instr( l_piece, '/' );
 22              l_row.computer_name := substr( l_piece, 1, l_slash-1 );
 23              l_piece := substr( l_piece, l_slash+1 );
 24              l_slash := instr( l_piece, '/' );
 25              l_row.computer_id := substr( l_piece, 1, l_slash-1 );
 26              l_row.computer_serial := substr( l_piece, l_slash+1 );
 27
 28              pipe row (l_row);
 29          end loop;
 30      end loop;
 31      close p_cursor;
 32      return;
 33  end;
 34  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from table( parse_it( cursor( select * from tableone ) ) );
 
   USER_ID COMPUTER_NAME             COMPUTER_ID          COMPUTER_SERIAL
---------- ------------------------- -------------------- --------------------
  27137529 AMEHRABIAN-OFS            DELL_ITSTDV45C1K     8NBDC1S
  27137529 MEHRABIAN-OFS             DELL_ITSTDV45C1K     GRX3601
  27137531 AHYAR11-OFS               DELL_ITSTDV40C0K     1WWF61S
  27137578 ADELUNG1-L-OFS            DELL_ITSTDV31W2K-STD 8W5P701
  27137578 ADELUNG-L1-OFS            DELL_ITSTDV45C0K     56KMP31
  27137578 ARNOLD1                   DELL_ITSTDV40C1K     6XJ0D01
 
6 rows selected.
 

A reader, November 23, 2004 - 11:40 am UTC

I have a huge string

lv_string := 'adfsfsdfscdndfasdfdfsdfusddsdfasffsdgjsdfsfsdffsdfmmbtu'

I need to to convert the usd,cdn,mmbtu, gj to upper case where ever they occur.

I can do the replace function but using that I can take care of either only the usd or gj or cdn or mmbtu. How can I take care of all of them.

Tom Kyte
November 23, 2004 - 12:53 pm UTC

multiple replaces

replace( replace( replace( str, 'usd', 'USD' ), 'cdn', 'CDN' ), 'mmbtu', 'MMBUT')



multiple replaces

Gabe, November 23, 2004 - 1:19 pm UTC

To "A reader" ...

The only thing left for you to clarify (and code accordingly) is what to do in cases like ...

flip@FLOP> select str, replace( replace( replace( str, 'usd', 'USD' )
2 , 'cdn', 'CDN' ), 'mmbtu','MMBTU') processed_str
3 from (select 'mmbtusd' str from dual);

STR PROCESSED_STR
------- ------------------------------
mmbtusd mmbtUSD


Tom Kyte
November 23, 2004 - 2:26 pm UTC

good point -- you have excellent eyes, you catch lots of these nuances :)

Replace part of a string

A reader, November 23, 2004 - 7:16 pm UTC

I have a varchar2(100) string.

I need to replace characters 10 thru 15 with another string.

Is

str := substr(str,1,10-1)||new_str||substr(str,15+1)

the best way do do this?

Thanks



Tom Kyte
November 23, 2004 - 7:57 pm UTC

well, 10-1 and 15+1 could be written differently but otherwise -- yes....


PL/I, one of my favorite languages would have allowed for


substr( str, 10, 6 ) = new_str;

how cool is that :)

(no, that is not PL/SQL -- but PL/I -- pl-one...)

Multiple replaces

A reader, November 23, 2004 - 7:18 pm UTC

For the case Gabe pointed out, regexp_replace in 10g will really come in handy.

s/\(pat1\|pat2\|pat3\)/\U&/g

Regexps are really terrific for complex search/replace.

A reader, November 23, 2004 - 9:34 pm UTC

substr( str, 10, 6 ) = new_str;

That is cool! But very few languages out there allow an expression as the "lvalue" of an assignment, wonder why! IIRC, even the most hybrid of languages out there, Perl, doesnt allow this!

Perl does allow

Ralf, November 24, 2004 - 2:23 am UTC

In Perl you can write:

$a="abcdefg";
substr($a,2,1)="xy";
print $a; # this prints axycdefg

hth

Tom Kyte
November 24, 2004 - 7:22 am UTC

(still won't make me think about using perl :)

Ideas about parsing this

Chenna, February 02, 2005 - 5:36 pm UTC

Tom

I have the following string

From 0 ZY to 300 ZY @ 6.10ABC per ZY From 301 ZY to 600 ZY @ 5.50ABC per ZY From 601 ZY to

the rest @ INDIAN OIL TRADE REPORT D ZY CADABC per ZY

I need to parse it like the below

300 ZY @ 6.10ABC per ZY
300 ZY @ 5.50ABC per ZY
rest @ INDIAN OIL TRADE REPORT D ZY CADABC per ZY

Note:

The numbers will differe but the From and to will always be in the same place, and the last 'to the rest @ INDIAN OIL TRADE REPORT D ZY CADABC per ZY' might be or might not be there'

Can you please guide me to a good solutions, our database is 9i release 2

Tom Kyte
February 03, 2005 - 1:43 am UTC

guess i would loop through looking for "From" to break the string into

From 0 ZY to 300 ZY @ 6.10ABC per ZY
From 301 ZY to 600 ZY @ 5.50ABC per ZY
From 601 ZY to the rest @ INDIAN OIL TRADE REPORT D ZY CADABC per ZY


and go from there. It looks like a procedure to me.

Clob parse

Phil, February 03, 2005 - 12:15 am UTC

Hi Tom,


I am trying to parse a value seperate clob. I do not want an inlist just rows.

I am trying to "extend" str2tbl to read a clob and return me some rows, and I think I should be using another technique.

I have a clob which is on the lines of

:20:HHH
:21:YYY
:99:This is row 1
:99:This is row 2
:99:This is row 3
:99:This is row 4

where there are a 1000 or so of :99: values in the clob

I am trying to get a result of

:20:HHH :21:YYY :99:This is row 1
:20:HHH :21:YYY :99:This is row 2
:20:HHH :21:YYY :99:This is row 3
:20:HHH :21:YYY :99:This is row 4

The values of any element ie :99 , will not exceed 4000k so I will always be returning a varchar2 for all columns

if I have a string then str2tbl is just the thing for it, however for a clob I am strugling.

I would like to do this as part of an SQL statement similar to str2tbl as the final outcome will be an insert .. select

I would appreciate your thoughts/examples

Version 9ir2

kind regards
phil






Tom Kyte
February 03, 2005 - 1:49 am UTC

why are you struggling with a clob? there are substr, instr, etc for clobs?


if you can do it to a string...

Maybe this will help:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1352202934074 <code>

in there I treat the alert log as a bfile and read it a line at a time. You have it easy since the clob won't need utl_raw.

Use that same logic and you can go line by line through the clob.

thanks

phil, February 03, 2005 - 5:57 pm UTC

Thanks Tom,

You must feel like you are leading donkeys to water and forcing them to drink sometimes !!

kind regards
phil

Vin, February 04, 2005 - 10:34 am UTC

SQL> Create table t ( x number, y varchar2(30) );
SQL> Insert into t values ( 1,'aa' );
SQL> Insert into t values ( 2,'aa,bb');
SQL> Insert into t values ( 3, 'aa,bb,cc');

SQL> Create table t1 ( x number, y varchar2(30) );
SQL> Insert into t1 values ( 1,'aa' );
SQL> Insert into t1 values ( 2,'bb');
SQL> Insert into t1 values ( 3, 'cc');
SQL> Insert into t1 values ( 3, 'a,b,c');
SQL> commit;

SQL> Select * from t;
         X Y
---------- ------------------------------
         1 aa
         2 aa,bb
         3 aa,bb,cc

SQL> Select * from t1;
         X Y
---------- ------------------------------
         1 aa
         2 bb
         3 cc
         3 a,b,c

SQL> Select y from t Where x=3;
Y
------------------------------
aa,bb,cc

SQL> Select * from t1 Where y in (Select y from t Where x=3);

no rows selected

Is there any way to find the matching pattern in the subquery to the main. The resultset from subquery should be 
like ('aa','bb','cc') which when passed to the main query Select * from t1 Where y in ('aa','bb','cc'); will fetch
         X Y
---------- ------------------------------
         1 aa
         2 bb
         3 cc 

There could be N number of comma seperated strings for y
Oracle Version is 8174
Thanks 

As Always To The Point. Many Thanks

Vin, February 04, 2005 - 1:28 pm UTC


How to use the above list of values in a single Insert Statement

Vin, February 07, 2005 - 1:27 pm UTC

SQL> Select * from t;
         X Y
---------- ------------------------------
         1 aa
         2 aa,bb
         3 aa,bb,cc

Select * From the (Select cast(str2tbl(Y) as mytableType) From t Where X=3;

COLUMN_VALUE
-------------
aa
bb
cc

How to use these COLUMN_VALUE in a Insert Statement so that the output would be

SQL> Select * from t;
         X Y
---------- ------------------------------
         1 aa
         2 aa
         2 bb
         3 aa
         3 bb
         3 cc

Thanks

 

Tom Kyte
February 07, 2005 - 4:08 pm UTC

ops$tkyte@ORA9IR2> select * from t, table(str2tbl(t.x));
 
X                         COLUMN_VALUE
------------------------- ------------------------------
1,2                       1
1,2                       2
3,4                       3
3,4                       4


column value is what you are looking for there.... 1,2,3,4....


 

Tried this, but in vain, any insight

Vin, February 07, 2005 - 4:53 pm UTC

SQL> Begin
  2  For x in (Select x From t Where instr(y,',') >=1)
  3   Loop
  4     For y in (Select * From the (Select cast(vstr(Y) as vString_List )
  5                                    From t Where X = x.X))
  6     Loop
  7       Insert into t(X,Y) Select X,y.column_value from t;
  8      End Loop;
  9   End Loop;
 10  End;
 11  /
Begin
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 4 

Please correct me if Iam wrong

Vin, February 07, 2005 - 7:00 pm UTC

Oracle version is 8174, so pipelined function not supported, so the solution given may not work.

select * from t, table(str2tbl(t.x));

and so I used the below solution, but still error, any help would be appreciated.

SQL> Begin
  2  For x in (Select x From t Where instr(y,',') >=1)
  3   Loop
  4     For y in (Select * From the (Select cast(vstr(Y) as vString_List )
  5                                    From t Where X = x.X))
  6     Loop
  7       Insert into t(X,Y) Select X,y.column_value from t;
  8      End Loop;
  9   End Loop;
 10  End;
 11  /
Begin
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 4 


 

Tom Kyte
February 08, 2005 - 1:10 am UTC

8i

ops$tkyte@ORA817DEV> create or replace type myTableType as table of number;
  2  /
 
Type created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace function str2tbl( p_str in varchar2 ) return myTableType
  2  as
  3      l_str   long default p_str || ',';
  4      l_n        number;
  5      l_data    myTableType := myTabletype();
  6  begin
  7      loop
  8          l_n := instr( l_str, ',' );
  9          exit when (nvl(l_n,0) = 0);
 10          l_data.extend;
 11          l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 12          l_str := substr( l_str, l_n+1 );
 13      end loop;
 14      return l_data;
 15  end;
 16  /
 
Function created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> drop table t;
 
Table dropped.
 
ops$tkyte@ORA817DEV> create table t ( x varchar2(20) );
 
Table created.
 
ops$tkyte@ORA817DEV> insert into t values ( '1,2');
 
1 row created.
 
ops$tkyte@ORA817DEV> insert into t values ( '3,4');
 
1 row created.
 
ops$tkyte@ORA817DEV> select * from t, table(cast( str2tbl(t.x) as myTableType) );
 
X                    COLUMN_VALUE
-------------------- ------------
1,2                             1
1,2                             2
3,4                             3
3,4                             4
 

I understood what you meant, Solved my problem: Many Thanks

Vin, February 07, 2005 - 9:10 pm UTC

Still is it possible to do using the FOR LOOP for the above situation.

Tom Kyte
February 08, 2005 - 1:21 am UTC

4 For y in (Select * From the (Select cast(vstr(Y) as vString_List )
5 From t Where X = x.X))


you would have to use "where PRIMARY_KEY = x.PRIMARY_KEY" -- in order to get at most one and at least one row in the "from t where" part -- if you get too many rows, you get too many rows.



How to do this susbstr

A reader, February 21, 2005 - 8:24 pm UTC

SQL> var lv_var varchar2(3);
SQL> 
SQL> exec :lv_var := ' bc';

PL/SQL procedure successfully completed.


I.

SQL> 
SQL> SELECT      UPPER(SUBSTR(:lv_var, -2, 2) ) 
  2  FROM dual;

UP
--
BC


II.
SQL> ed
Wrote file afiedt.buf

  1  SELECT      UPPER(SUBSTR(:lv_var, -3, 3) )
  2* FROM dual
SQL> /

UPP
---
 BC

SQL>  exec :lv_var :='bc';

PL/SQL procedure successfully completed.



I.

SQL> SELECT      UPPER(SUBSTR(:lv_var, -2,2))
  2  from dual;

UP
--
BC

II.

SQL> SELECT      UPPER(SUBSTR(:lv_var, -3, 3) )
  2  FROM dual
  3  /

UPP
---



when the length of the :lv_var is 3  then both I and II work , but the length of :lv_var is 2 only II works.
I need to return BC even  when the length of :lv_var is 2. I have to have -3,3 for my substr as I have to verify the last 3 words of the string.

Please help. 

Tom Kyte
February 22, 2005 - 8:13 am UTC

do you want the last 3 characters of a string?  

ops$tkyte@ORA9IR2> create table t
  2  as
  3  select substr( '12345', 1, rownum ) x
  4    from all_users
  5   where rownum <= 5;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x, substr( x, greatest(length(x)-2,1) ) from t;
 
X     SUBST
----- -----
1     1
12    12
123   123
1234  234
12345 345
 

Coalesce

Venkat, February 22, 2005 - 5:06 pm UTC

select coalesce(substr(:val,-3),:val) from dual will do the same thing. My question is, does coalesce always evaluate all the expressions in the list (coalesce(expr1, expr2, ...)) or does it stop at the first not null expression?

Thanks!

Tom Kyte
February 22, 2005 - 5:24 pm UTC

ops$tkyte@ORA10GR1> select coalesce( null, 1, 1/0 ) from dual;
 
COALESCE(NULL,1,1/0)
--------------------
                   1
 
ops$tkyte@ORA10GR1> select coalesce( null, null, 1/0 ) from dual;
select coalesce( null, null, 1/0 ) from dual
                              *
ERROR at line 1:
ORA-01476: divisor is equal to zero
 

I should have done that before asking!

Venkat, February 22, 2005 - 6:32 pm UTC

Thanks for taking the time to illustrate the point!

Selecting from a PL/SQL table

A reader, April 21, 2005 - 7:23 pm UTC

If I have a PL/SQL table (index by binary integer or a real object-type table), how can I select from it?

where id in (select id from plsql_table) doesnt quite seem to work

Thanks

Tom Kyte
April 22, 2005 - 10:17 am UTC

you cannot -- you need to use a SQL type (a collection), not an index by table.

A reader, April 22, 2005 - 11:33 am UTC

So, in the context of the HTML DB g_fXX arrays which are index by tables, I need to use the htmldb_util.table_to_string() to convert the table into a string and then your str2tbl to convert it back into a "select-able" table!

This table-to-string-to-table seemed a little redundant to me, but I guess there is no way around it.

Thanks

A reader, June 14, 2005 - 10:14 pm UTC

Hi Tom,

I have a situation like this:

Comma seperated list of jobs will be sent to a procedure; and procedure has to return all the employees matching with those jobs.

How can I use that IN parameter (with comma seperated values) in the WHERE clause?


eg:

PROCEDURE P1(str varchar2)
IS
TYPE C1 IS REF CURSOR ;
BEGIN
OPEN C1 FOR
SELECT ENAME,SAL
FROM EMP WHERE JOB IN (str);
END;

SQL/> EXEC P1('MANAGER,ANALYST,CLERK')

Please Help!



Tom Kyte
June 15, 2005 - 3:19 am UTC

search this site for

varying in list




Parsit function fails when trying to bind predicate

Aaron Valdes, October 14, 2005 - 2:48 am UTC

Hi Tom,

Your pipelined function parseit works great but for some reason im getting an ora-01008 when using it in a package (the select is the source query for a merge statement).

For example your query:

select * from table( parse_it( cursor( select * from tableone
) ) );

Works great but if I try to parameterize it then in fails in the fetch part of the parsit function:

select * from table( parse_it( cursor( select * from tableone where user_id = p_user_id;
) ) );

Error: ORA-01008 not all variables bound. But hardcoding the value in the package does work:

select * from table( parse_it( cursor( select * from tableone where user_id = 126545
) ) );

I just cant grasp why this can not be parameterized. It only likes hardcoded values.

Any thoughts?

Thanks a bunch

P.S. Any ideas when your going to be in Sunny California?

Tom Kyte
October 14, 2005 - 7:56 am UTC

need an example, like this showing it works:


ops$tkyte@ORA10G> create or replace package pkg
  2  as
  3      procedure p( p_id in number );
  4  end;
  5  /
 
Package created.
 
ops$tkyte@ORA10G> create or replace package body pkg
  2  as
  3
  4  procedure p( p_id in number )
  5  is
  6  begin
  7      for x in ( select *
  8                   from table( parse_it( cursor( select * from tableone where user_id = p_id ) ) )
  9               )
 10      loop
 11          dbms_output.put_line( x.computer_name || ', ' || x.computer_id || ', ' || x.computer_serial );
 12      end loop;
 13  end;
 14
 15  end;
 16  /
 
Package body created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec pkg.p( 27137529 )
AMEHRABIAN-OFS, DELL_ITSTDV45C1K, 8NBDC1S
MEHRABIAN-OFS, DELL_ITSTDV45C1K, GRX3601
 
PL/SQL procedure successfully completed.


but showing how you are getting it to not work ;)

see the asktom home page - I'll be at NoCOUG in November. 

RE: Parsit function fails when trying to bind predicate

Aaron Valdes, October 14, 2005 - 2:47 pm UTC

Thanks for the quick response. Here is the example. Seems to be the merge:

   -- Create table
   create table TABLETWO
   (
     USER_ID         INTEGER,
     COMPUTER_NAME   VARCHAR2(25),
     COMPUTER_ID     VARCHAR2(40),
     COMPUTER_SERIAL VARCHAR2(20)
   );
   
   create or replace package pkg
   as
       procedure p( p_id in number );
       procedure merge_p (p_id in number);
   end;
   /
   
   create or replace package body pkg
    as
   
    procedure p( p_id in number )
     is
     begin
         for x in ( select *
                     from table( parse_it( cursor( select * from tableone where 
   user_id = p_id ) ) )
                 )
         loop
             dbms_output.put_line( x.computer_name || ', ' || x.computer_id || 
   ', ' || x.computer_serial );
        end loop;
     end;
     --
     --
     --
     procedure merge_p (p_id in number)
     is
     begin
       merge into tabletwo a
       using (select *
              from table( parse_it( cursor( select * from tableone 
                                            where user_id = p_id ) ) ) ) b
       on (a.user_id = b.user_id)
       when matched then
       update
       set a.computer_name = b.computer_name,
           a.computer_id = b.computer_id,
           a.computer_serial = b.computer_serial
       when not matched then
       insert (a.user_id, a.computer_name, a.computer_id, a.computer_serial)
       values (b.user_id, b.computer_name, b.computer_id, b.computer_serial);
     end merge_p;
    end;
    /
    
 SQL> exec pkg.merge_p ( 27137531 );
 
 begin pkg.merge_p ( 27137531 ); end;
 
 ORA-01008: not all variables bound
 ORA-06512: at "AVALDES.PARSE_IT", line 13
 ORA-06512: at "AVALDES.PKG", line 22
 ORA-06512: at line 1
 
    
 Yet hardcoding p_id in the merge works. Again thanks for your help.


 

Tom Kyte
October 14, 2005 - 5:53 pm UTC

I reproduced in 9ir2 but not 10gr1....

Please contact support with the test case and they might be able to find the related bug and there may be a patch.

RE: Parsit function fails when trying to bind predicate

Aaron Valdes, October 15, 2005 - 12:31 am UTC

Thanks Tom, I will get with support. Another reason to move to 10g. I can't believe how excited I am for LOG ERRORS.

Your new book is great. The searchable PDF of One on One is very handy, since my hard copy is looking a little worn from the constant index scanning. ;)

Looking forward to part 2.

Aaron

What is the best way to parse this string

A reader, October 27, 2005 - 2:36 pm UTC


'{ABC|CEF|INDIA} SOME TEXT AND CALUCULATIONS HERE {CDE|DDD|AUSTRALIA} SOME TEXT AND CALUCULATIONS HERE {TTT|BBB|CHINA}'

I have a string like the below. I looking to write a function which can pluck out the parts of string which are country names, and see if they are the same country. If they are different they my function will return 'N' else it will return 'Y'.
Note that I can have any number of combinations like {CDE|DDD|AUSTRALIA}, and not limited to 3 as shown in the above string.

What is the best way to do it?

Thanks

Tom Kyte
October 27, 2005 - 2:57 pm UTC

give me the logic, in just text - like a specification, that says how to find "countries" in the string


the algorithm will just "fall out" from that spec.


1) Is a country ALWAYS between | and }?
2) if so, is a country the ONLY THING between | and }?
if 1) is true and 2) is false - how do you tell then?

A reader, October 27, 2005 - 6:08 pm UTC

1.True
2.True

Tom Kyte
October 28, 2005 - 1:54 am UTC

could be something like this

ops$tkyte@ORA10GR2> create or replace type str2tblType as table of varchar2(30)
  2  /

Type created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace function str2tbl( p_str in varchar2 ) return str2tblType
  2  PIPELINED
  3  as
  4      l_str      long default p_str;
  5          l_piece    long;
  6      l_n        number;
  7  begin
  8      loop
  9          l_n := instr( l_str, '}' );
 10          exit when (nvl(l_n,0) = 0);
 11                  l_piece := substr( l_str, 1, l_n-1 );
 12          l_str := substr( l_str, l_n+1 );
 13                  l_n := instr( l_piece, '|', -1 );
 14          exit when (nvl(l_n,0) = 0);
 15          pipe row( ltrim(rtrim(substr(l_piece,l_n+1))) );
 16      end loop;
 17      return;
 18  end;
 19  /

Function created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select *
  2    from table( str2tbl( '{ABC|CEF|INDIA} SOME TEXT AND CALUCULATIONS HERE {CDE|DDD|AUSTRALIA} SOME TEXT AND CALUCULATIONS HERE {TTT|BBB|CHINA}' ) );

COLUMN_VALUE
------------------------------
INDIA
AUSTRALIA
CHINA
 

another ...

Gabe, October 28, 2005 - 11:45 am UTC

Assuming they are only interested if the countries in the string are different or not [and ‘}’ is only used as an end-of-country delimiter] …

flip@FLOP> select v orig, case
2 when fc is null then '!'
3 when instr(replace(v,fc),'}') = 0 then 'Y'
4 else 'N'
5 end "Same?"
6 from ( select v
7 ,case
8 when nvl(instr(v,'|',1,2),0) > 0 and
9 nvl(instr(v,'}',1,1),0) > 0 then
10 substr(v,instr(v,'|',1,2),instr(v,'}',1,1)-instr(v,'|',1,2)+1)
11 end fc
12 from str
13 )
14 ;

ORIG S
--------------------------------------------------------------------------- -
{ABC|CEF|INDIA} some text {CDE|DDD|AUSTRALIA} some more {TTT|BBB|CHINA} N
{x|y|UK} abc {z|w|FRANCE} asd {TTT|BBB|UK} N
{x|y|CANADA} abc {z|w|CANADA} asd {TTT|BBB|CANADA} Y
abc {x|y|JAPAN} abc Y
xyz !
{x|y|USA} Y

6 rows selected.


SQL Tokenizer + connect by

Alberto Dell'Era, October 30, 2005 - 1:18 pm UTC

Just had this crazy idea:

create table t (line int, x varchar2(4000));
insert into t (line,x) values (1, 'a,bb,c,d');
insert into t (line,x) values (2, 'x,yy,z,k');

Version using regexp_substr:

select line, regexp_substr (x, '[^,]+', 1, level) as token
from t
connect by prior line = line
and regexp_instr (x, '[^,]+', 1, level) > 0
and prior dbms_random.string ('p', 10) is not null
;

LINE TOKEN
---------- -----------
1 a
1 bb
1 c
1 d
2 x
2 yy
2 z
2 k

Version using plain substr (less elagant but more performant) :

select line, substr (x,
instr (x, ',', 1, level ) + 1,
instr (x, ',', 1, level+1) - instr (x, ',', 1, level) -1 )
as token
from (select line, ','||x||',' as x from t)
connect by prior line = line
and instr (x, ',', 1, level+1) > 0
and prior dbms_random.string ('p', 10) is not null
;

LINE TOKEN
---------- -----------
1 a
1 bb
1 c
1 d
2 x
2 yy
2 z
2 k

The "prior dbms_random.string ('p', 10) is not null" trick (that I borrowed here from a post by Mikito Harakiri) is needed to avoid the error "ORA-01436: CONNECT BY loop in user data". Wish I could avoid it but I've found no way so far.

Both don't scale well with the number of tokens, but anyway, an optimized version of the substr variant was able to parse 100,000 rows with 10 tokens of 10 chars each in 39 seconds on my lowly laptop (9.2.0.1) - no too bad ;)

Hitting Error ORA-00942 ...

VKOUL, December 07, 2005 - 8:36 pm UTC

Hi Tom,

I am hitting ORA-00942 table or view does not exist when using WITH clause, here is the sql

SQL> WITH emps
  2  AS
  3  (
  4   SELECT CAST(column_value AS INTEGER) AS EmpId
  5   FROM   TABLE(CAST(str2tbl('1,2,3') AS COMMA_LIST))
  6   UNION  ALL
  7   SELECT EmpFK AS EmpId
  8   FROM   TBL_TMX_RegBatchUsers t1
  9   WHERE  RegBatchFK =  1
 10   AND    EmpAdded   =  1
 11  )
 12  SELECT 1,
 13         conflicts.EmpId,
 14         times.ActId,
 15         4,
 16         1,
 17         1,
 18         conflicts.ConflictName,
 19         conflicts.BusyId
 20  FROM   (SELECT att.EmpFK AS EmpId,
 21                 act.Activity_PK AS BusyId,
 22                 act.ActivityName AS ConflictName,
 23                 act.StartDt AS StartTm,
 24                 act.EndDt AS EndTm
 25          FROM   Emps
 26          INNER  JOIN TBL_TMX_Attempt att  ON att.EmpFK = Emps.EmpID
 27                                           AND att.EndDt IS NULL
 28          INNER  JOIN TBL_TMX_Activity act ON act.Activity_PK = att.ActivityFK
 29                                           AND act.UseForConflictCheck =  1
 30                                           AND act.Cancelled =  0
 31          WHERE  NOT EXISTS (SELECT NULL
 32                             FROM   TBL_TMX_Activity xAct
 33                             WHERE  xAct.PrntActFK = act.Activity_PK
 34                            )
 35          UNION  ALL
 36          SELECT inst.Inst_EmpFK AS EmpId,
 37                 act.Activity_PK AS BusyId,
 38                 act.ActivityName AS ConflictName,
 39                 act.StartDt AS StartTm,
 40                 act.EndDt AS EndTm
 41          FROM   Emps
 42          INNER  JOIN inst                    ON inst.Inst_EmpFK = Emps.EmpID
 43          INNER  JOIN TBL_TMX_ActInst actinst ON actinst.InstFK = inst.Inst_PK
 44                                              AND actinst.Confirmed =  1
 45          INNER  JOIN TBL_TMX_Activity act    ON act.Activity_PK = actinst.ActivityFK
 46                                              AND act.UseForConflictCheck =  1
 47                                              AND act.Cancelled =  0
 48          WHERE  NOT EXISTS (SELECT NULL
 49                             FROM   TBL_TMX_Activity xAct
 50                             WHERE  xAct.PrntActFK = act.Activity_PK
 51                            )
 52          UNION  ALL
 53          SELECT EmpBusy.EmpBusy_EmpFK AS EmpId,
 54                 NULL AS BusyId,
 55                 EmpBusy.EmpBusy_Subject AS ConflictName,
 56                 EmpBusy.EmpBusy_StartDt AS StartTm,
 57                 EmpBusy.EmpBusy_EndDt   AS EndTm
 58          FROM   Emps
 59          INNER  JOIN tblEmpBusy EmpBusy ON EmpBusy.EmpBusy_EmpFK = Emps.EmpID
 60         ) conflicts,
 61         (SELECT act.Activity_PK AS ActId, act.StartDt AS StartTm, act.EndDt AS EndTm
 62          FROM   TBL_TMX_RegBatchActivities rba
 63          INNER  JOIN TBL_TMX_Activity act ON act.Activity_PK = rba.ActivityFK
 64                                           AND act.UseForConflictCheck =  1
 65                                           AND act.Cancelled =  0
 66          WHERE  rba.RegBatchFK = 1
 67          AND    rba.Validated =  0
 68         ) times
 69  WHERE  times.EndTm   > conflicts.StartTm
 70  AND    times.StartTm < conflicts.EndTm;
 AND    EmpAdded   =  1
               *
ERROR at line 10:
ORA-00942: table or view does not exist


SQL> 
SQL> -- if I remove last 2 lines from the SQL, the Query Runs
SQL> 
SQL> del 69 70
SQL> l
  1  WITH emps
  2  AS
  3  (
  4   SELECT CAST(column_value AS INTEGER) AS EmpId
  5   FROM   TABLE(CAST(str2tbl('1,2,3') AS COMMA_LIST))
  6   UNION  ALL
  7   SELECT EmpFK AS EmpId
  8   FROM   TBL_TMX_RegBatchUsers t1
  9   WHERE  RegBatchFK =  1
 10   AND    EmpAdded   =  1
 11  )
 12  SELECT 1,
 13         conflicts.EmpId,
 14         times.ActId,
 15         4,
 16         1,
 17         1,
 18         conflicts.ConflictName,
 19         conflicts.BusyId
 20  FROM   (SELECT att.EmpFK AS EmpId,
 21                 act.Activity_PK AS BusyId,
 22                 act.ActivityName AS ConflictName,
 23                 act.StartDt AS StartTm,
 24                 act.EndDt AS EndTm
 25          FROM   Emps
 26          INNER  JOIN TBL_TMX_Attempt att  ON att.EmpFK = Emps.EmpID
 27                                           AND att.EndDt IS NULL
 28          INNER  JOIN TBL_TMX_Activity act ON act.Activity_PK = att.ActivityFK
 29                                           AND act.UseForConflictCheck =  1
 30                                           AND act.Cancelled =  0
 31          WHERE  NOT EXISTS (SELECT NULL
 32                             FROM   TBL_TMX_Activity xAct
 33                             WHERE  xAct.PrntActFK = act.Activity_PK
 34                            )
 35          UNION  ALL
 36          SELECT inst.Inst_EmpFK AS EmpId,
 37                 act.Activity_PK AS BusyId,
 38                 act.ActivityName AS ConflictName,
 39                 act.StartDt AS StartTm,
 40                 act.EndDt AS EndTm
 41          FROM   Emps
 42          INNER  JOIN inst                    ON inst.Inst_EmpFK = Emps.EmpID
 43          INNER  JOIN TBL_TMX_ActInst actinst ON actinst.InstFK = inst.Inst_PK
 44                                              AND actinst.Confirmed =  1
 45          INNER  JOIN TBL_TMX_Activity act    ON act.Activity_PK = actinst.ActivityFK
 46                                              AND act.UseForConflictCheck =  1
 47                                              AND act.Cancelled =  0
 48          WHERE  NOT EXISTS (SELECT NULL
 49                             FROM   TBL_TMX_Activity xAct
 50                             WHERE  xAct.PrntActFK = act.Activity_PK
 51                            )
 52          UNION  ALL
 53          SELECT EmpBusy.EmpBusy_EmpFK AS EmpId,
 54                 NULL AS BusyId,
 55                 EmpBusy.EmpBusy_Subject AS ConflictName,
 56                 EmpBusy.EmpBusy_StartDt AS StartTm,
 57                 EmpBusy.EmpBusy_EndDt   AS EndTm
 58          FROM   Emps
 59          INNER  JOIN tblEmpBusy EmpBusy ON EmpBusy.EmpBusy_EmpFK = Emps.EmpID
 60         ) conflicts,
 61         (SELECT act.Activity_PK AS ActId, act.StartDt AS StartTm, act.EndDt AS EndTm
 62          FROM   TBL_TMX_RegBatchActivities rba
 63          INNER  JOIN TBL_TMX_Activity act ON act.Activity_PK = rba.ActivityFK
 64                                           AND act.UseForConflictCheck =  1
 65                                           AND act.Cancelled =  0
 66          WHERE  rba.RegBatchFK = 1
 67          AND    rba.Validated =  0
 68*        ) times
SQL> /

no rows selected

SQL> l
  1  WITH emps
  2  AS
  3  (
  4   SELECT CAST(column_value AS INTEGER) AS EmpId
  5   FROM   TABLE(CAST(str2tbl('1,2,3') AS COMMA_LIST))
  6   UNION  ALL
  7   SELECT EmpFK AS EmpId
  8   FROM   TBL_TMX_RegBatchUsers t1
  9   WHERE  RegBatchFK =  1
 10   AND    EmpAdded   =  1
 11  )
 12  SELECT 1,
 13         conflicts.EmpId,
 14         times.ActId,
 15         4,
 16         1,
 17         1,
 18         conflicts.ConflictName,
 19         conflicts.BusyId
 20  FROM   (SELECT att.EmpFK AS EmpId,
 21                 act.Activity_PK AS BusyId,
 22                 act.ActivityName AS ConflictName,
 23                 act.StartDt AS StartTm,
 24                 act.EndDt AS EndTm
 25          FROM   Emps
 26          INNER  JOIN TBL_TMX_Attempt att  ON att.EmpFK = Emps.EmpID
 27                                           AND att.EndDt IS NULL
 28          INNER  JOIN TBL_TMX_Activity act ON act.Activity_PK = att.ActivityFK
 29                                           AND act.UseForConflictCheck =  1
 30                                           AND act.Cancelled =  0
 31          WHERE  NOT EXISTS (SELECT NULL
 32                             FROM   TBL_TMX_Activity xAct
 33                             WHERE  xAct.PrntActFK = act.Activity_PK
 34                            )
 35          UNION  ALL
 36          SELECT inst.Inst_EmpFK AS EmpId,
 37                 act.Activity_PK AS BusyId,
 38                 act.ActivityName AS ConflictName,
 39                 act.StartDt AS StartTm,
 40                 act.EndDt AS EndTm
 41          FROM   Emps
 42          INNER  JOIN inst                    ON inst.Inst_EmpFK = Emps.EmpID
 43          INNER  JOIN TBL_TMX_ActInst actinst ON actinst.InstFK = inst.Inst_PK
 44                                              AND actinst.Confirmed =  1
 45          INNER  JOIN TBL_TMX_Activity act    ON act.Activity_PK = actinst.ActivityFK
 46                                              AND act.UseForConflictCheck =  1
 47                                              AND act.Cancelled =  0
 48          WHERE  NOT EXISTS (SELECT NULL
 49                             FROM   TBL_TMX_Activity xAct
 50                             WHERE  xAct.PrntActFK = act.Activity_PK
 51                            )
 52          UNION  ALL
 53          SELECT EmpBusy.EmpBusy_EmpFK AS EmpId,
 54                 NULL AS BusyId,
 55                 EmpBusy.EmpBusy_Subject AS ConflictName,
 56                 EmpBusy.EmpBusy_StartDt AS StartTm,
 57                 EmpBusy.EmpBusy_EndDt   AS EndTm
 58          FROM   Emps
 59          INNER  JOIN tblEmpBusy EmpBusy ON EmpBusy.EmpBusy_EmpFK = Emps.EmpID
 60         ) conflicts,
 61         (SELECT act.Activity_PK AS ActId, act.StartDt AS StartTm, act.EndDt AS EndTm
 62          FROM   TBL_TMX_RegBatchActivities rba
 63          INNER  JOIN TBL_TMX_Activity act ON act.Activity_PK = rba.ActivityFK
 64                                           AND act.UseForConflictCheck =  1
 65                                           AND act.Cancelled =  0
 66          WHERE  rba.RegBatchFK = 1
 67          AND    rba.Validated =  0
 68         ) times
 69* WHERE  times.EndTm   > sysdate
SQL> 
SQL> 
SQL> /

no rows selected

SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> 
SQL> 
SQL>

The query works as long as I put conditions which does not involve both in-line views at the same time.

I am at a loss to know what is going on ...

Is there a known bug or something which I am missing ?

Thanks
 

Tom Kyte
December 08, 2005 - 1:56 am UTC

you give me absolutely no way to reproduce this :(



Way to reproduce above

Bob B, December 08, 2005 - 9:19 am UTC

I think I've run into a similar situation to above a few months back. I no longer have access to a 10g box to replicate, but here's a small template query that may help you see what's going on.

WITH INFO AS (
SELECT COLUMN_VALUE NUM
FROM TABLE( STR2TBL( '0,1,2,3,4,5,6,7,8,9' ) )
), INFO2 AS (
SELECT i1.NUM, i2.NUM
FROM INFO i1, INFO i2
)
SELECT *
FROM INFO2

I don't know if its exactly the same, but I believe the issue has to do with the optimizer rewriting the query as if TABLE( STR2TBL( <info> ) ) is an actual table. Out of curiosity, what indexes are on place on the EmpBusy table?


Tom Kyte
December 08, 2005 - 11:54 am UTC

ops$tkyte@ORA10GR2> create table info( num number );

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> WITH INFO AS (
  2    SELECT COLUMN_VALUE NUM
  3    FROM TABLE( STR2TBL( '0,1,2,3,4,5,6,7,8,9' ) )
  4  ), INFO2 AS (
  5    SELECT i1.NUM i1_num, i2.NUM i2_num
  6    FROM INFO i1, INFO i2
  7  )
  8  SELECT *
  9  FROM INFO2
 10  /

I1_NUM                         I2_NUM
------------------------------ ------------------------------
0                              0
...

nope - works (had ambigous column names, I fixed that) 

Hitting Error ORA-00942

VKOUL, December 08, 2005 - 2:24 pm UTC

I can reproduce it in another user, is there an email where I can send you an import file of 9i database without data involving 7 tables ? The file is just 131,072 bytes.

The funny thing is, there are two non-unique indexes on table TBLEMPBUSY, dropping both of which, the query gives no syntax error. But I do not want this query to be implmented without these dropped indexes.


Tom Kyte
December 09, 2005 - 1:17 am UTC

no, make it smaller, you can chop out all but the relevant columns, no tablespaces, probably even get rid of some of the tables.

Or, since you have a test case - you could use support, which is where this will end up if it is a bug anyway ;)

Hitting Error ORA-00942

VKOUL, December 09, 2005 - 3:25 am UTC

Hi Tom,

I'll contact support for this.

Thanks for your time.


Hitting ORA-00942 Error ...

VKOUL, December 09, 2005 - 3:20 pm UTC

Hi Tom,

For support I have created a script, I do not want to chop pther columns as it might have some effect on the cause of failure of the query, so below is the complete script with tablespace information stripped.

- This error is even coming on Oracle 10g on windows
- Earlier, I said dropping two indexes makes the query run, but I noticed dropping only one index makes the query go and that index is IDX_TBLEMPBUSY_4LOSH813 of table TBLEMPBUSY.

- I have done installation in a fresh schema
- I am not able to publish the whole script, so I am doing it in parts

********************************************************
PART 1
********************************************************
PROMPT "Type COMMA_LIST Start"

CREATE OR REPLACE TYPE comma_list AS TABLE OF VARCHAR2(1024)
/

PROMPT "Type COMMA_LIST End "

PROMPT "Function STR2TBL Start"

CREATE OR REPLACE FUNCTION Str2Tbl(p_str_in VARCHAR2, p_delim varchar2 := ',')
RETURN comma_list IS
l_str VARCHAR2(32767);
l_n NUMBER;
l_s number;
l_pln number;
l_data comma_list := comma_list();
l_temp VARCHAR2(1024);
p_str VARCHAR2(4096) := p_str_in;
BEGIN
-- Check for no delimiter, list contains a single item
l_s := length( p_str );
l_pln := length( p_delim );
IF substr(p_str,l_s-l_pln+1 ) = p_delim THEN
l_str := p_str;
ELSE
l_str := p_str || p_delim;
END IF;
l_n := instr( l_str, p_delim );
IF nvl(l_n,0) = 0 THEN
l_data.EXTEND;
l_data( l_data.COUNT ) := ltrim(rtrim(l_str));
ELSE
-- one or more items in the list, parse away.
LOOP
l_n := instr( l_str, p_delim );
EXIT WHEN (nvl(l_n,0) = 0);
l_data.EXTEND;
l_temp := ltrim(rtrim(substr(l_str,1,l_n-1)));
IF l_temp IS NOT NULL THEN
l_data( l_data.COUNT ) := l_temp;
END IF;
l_str := substr( l_str, l_n+l_pln );
END LOOP;
END IF;
RETURN l_data;
END Str2Tbl;
/

PROMPT "Function STR2TBL End "

PROMPT "Table TBL_TMX_REGBATCHUSERS Start"

CREATE TABLE TBL_TMX_REGBATCHUSERS
(
REGBATCHFK NUMBER(10) NOT NULL,
EMPFK NUMBER(10) NOT NULL,
EMPSELECTED NUMBER(10) NOT NULL,
TARGET NUMBER(10),
REGSTATUS NUMBER(10),
BILLTOORGFK NUMBER(10),
EMPADDED NUMBER(10),
LSTUPD DATE NOT NULL,
USRNAME VARCHAR2(90 BYTE) NOT NULL,
LCK NUMBER(10) NOT NULL
);


CREATE UNIQUE INDEX PK_TBL_TMX_REGBATCHUSERS ON TBL_TMX_REGBATCHUSERS
(REGBATCHFK, EMPFK);


CREATE INDEX IDX_TMX_REGBATCHUSERS_WWUD2S1O ON TBL_TMX_REGBATCHUSERS
(EMPFK);


CREATE INDEX IDX_TMX_REGBATCHUSERS_WWUD2S1P ON TBL_TMX_REGBATCHUSERS
(BILLTOORGFK);


CREATE OR REPLACE TRIGGER TR_BI_TBL_TMX_REGBATCHUSERS
before insert on TBL_TMX_RegBatchUsers
for each row
begin
: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_REGBATCHUSERS
before update on TBL_TMX_RegBatchUsers
for each row
begin
:new.LstUpd := GetUTCDate;

:new.UsrName := Core_AUD.current_user;

end;
/
SHOW ERRORS;



ALTER TABLE TBL_TMX_REGBATCHUSERS ADD (
CONSTRAINT PK_TBL_TMX_REGBATCHUSERS
PRIMARY KEY
(REGBATCHFK, EMPFK)
USING INDEX);


ALTER TABLE TBL_TMX_REGBATCHUSERS ADD (
CONSTRAINT FK_EMPFK_GVRRF10L
FOREIGN KEY (EMPFK)
REFERENCES TBLEMP (EMP_PK)
ON DELETE CASCADE);

ALTER TABLE TBL_TMX_REGBATCHUSERS ADD (
CONSTRAINT FK_REGBATCHFK_GVRRF10K
FOREIGN KEY (REGBATCHFK)
REFERENCES TBL_TMX_REGBATCH (REGBATCH_PK)
ON DELETE CASCADE);

PROMPT "Table TBL_TMX_REGBATCHUSERS End "

PROMPT "Table TBL_TMX_ATTEMPT Start"

CREATE TABLE TBL_TMX_ATTEMPT
(
ATTEMPT_PK NUMBER(10) NOT NULL,
EMPFK NUMBER(10) NOT NULL,
ACTIVITYFK NUMBER(10) NOT NULL,
AUTHREGFK NUMBER(10),
STARTDT DATE,
ENDDT DATE,
WAIVEIND NUMBER(10) NOT NULL,
COMPLETIONSTATUS NUMBER(10),
SUCCESS NUMBER(10),
SCORE NUMBER(20,4),
ATTNDSTATUSFK NUMBER(10),
GRDFK NUMBER(10),
NOTE CLOB,
ELAPSEDSECONDS NUMBER(10),
LAUNCHCOURSEVERSION CHAR(38 BYTE),
MERGEDSKL NUMBER(10),
CURRENTATTEMPTIND NUMBER(10),
LAUNCHCOUNT NUMBER(10) NOT NULL,
LINKFK NUMBER(10),
SOURCE NUMBER(10),
SESSIONID VARCHAR2(90 BYTE),
EXPIRATIONDATE DATE,
REPLANDATE DATE,
EXPIRATIONTYPE NUMBER(10),
MODIFIERFK NUMBER(10),
CERTNOTES CLOB,
WASACTIVATION NUMBER(1),
ORIGINALEVTFK NUMBER(10),
OFFLINEIND NUMBER(10),
LASTSYNCDT DATE,
LSTUPD DATE NOT NULL,
USRNAME VARCHAR2(90 BYTE) NOT NULL,
LCK NUMBER(10) NOT NULL,
WEBEXATTENDEEID NUMBER(10),
WEBEXREGISTERID NUMBER(10)
);

CREATE INDEX IDX_TMX_ATTEMPT_Z2NRX31W ON TBL_TMX_ATTEMPT
(EMPFK, ACTIVITYFK, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S19 ON TBL_TMX_ATTEMPT
(ACTIVITYFK, EMPFK, CURRENTATTEMPTIND, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S1B ON TBL_TMX_ATTEMPT
(ENDDT, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S1E ON TBL_TMX_ATTEMPT
(GRDFK, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S1H ON TBL_TMX_ATTEMPT
(LAUNCHCOURSEVERSION, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S1K ON TBL_TMX_ATTEMPT
(ACTIVITYFK, EMPFK, AUTHREGFK);


CREATE UNIQUE INDEX PK_TBL_TMX_ATTEMPT ON TBL_TMX_ATTEMPT
(ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S17 ON TBL_TMX_ATTEMPT
(EMPFK, ACTIVITYFK, CURRENTATTEMPTIND, COMPLETIONSTATUS, ENDDT, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S18 ON TBL_TMX_ATTEMPT
(ACTIVITYFK, EMPFK, ENDDT);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S1A ON TBL_TMX_ATTEMPT
(STARTDT, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S1C ON TBL_TMX_ATTEMPT
(AUTHREGFK, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S1D ON TBL_TMX_ATTEMPT
(ATTNDSTATUSFK, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S1F ON TBL_TMX_ATTEMPT
(LINKFK, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S1G ON TBL_TMX_ATTEMPT
(CURRENTATTEMPTIND, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S1I ON TBL_TMX_ATTEMPT
(ORIGINALEVTFK, EMPFK, ATTEMPT_PK);


CREATE INDEX IDX_TMX_ATTEMPT_WWUD2S1J ON TBL_TMX_ATTEMPT
(ATTEMPT_PK, STARTDT, ENDDT);


CREATE INDEX IDX_TMX_ATTEMPT_Z2NRX31V ON TBL_TMX_ATTEMPT
(MODIFIERFK);


CREATE OR REPLACE TRIGGER TR_BI_TBL_TMX_ATTEMPT
before insert on TBL_TMX_Attempt
for each row
begin
if :new.WaiveInd is null then
:new.WaiveInd := 0;
end if;

if :new.LaunchCount is null then
:new.LaunchCount := 0;
end if;

if :new.wasActivation is null then
:new.wasActivation := 0;
end if;

if :new.OfflineInd is null then
:new.OfflineInd := 0;
end if;

:new.LstUpd := GetUTCDate;

:new.UsrName := Core_AUD.current_user;

if :new.Lck is null then
:new.Lck := 0;
end if;

if :new.wasActivation is not null and :new.wasActivation != 0 then
:new.wasActivation := 1;
end if;

end;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER TR_BU_TBL_TMX_ATTEMPT
before update on TBL_TMX_Attempt
for each row
begin
:new.LstUpd := GetUTCDate;

:new.UsrName := Core_AUD.current_user;

if :new.wasActivation is not null and :new.wasActivation != 0 then
:new.wasActivation := 1;
end if;

end;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER tu_attempt_1
BEFORE UPDATE OF EndDt ON TBL_TMX_Attempt
BEGIN
Core_Trigger.attemptupd_clear;
END tu_attempt_1;
/
SHOW ERRORS;

ALTER TRIGGER TU_ATTEMPT_1 DISABLE;



CREATE OR REPLACE TRIGGER tu_attempt_2
BEFORE UPDATE OF EndDt ON TBL_TMX_Attempt
FOR EACH ROW
Declare
dt_ExpirationDate date;
dt_ReplanDate date;
i_ExpirationRule integer;
i_ExpirationParamValue integer;
i_ExpirationParamDate date;
BEGIN
-- if the old completion date is null and the new completion
-- date is not null, then we need to send an activity
-- completion notification
--Update expiration Date
if (:NEW.CompletionStatus= 1) then
begin
select expirationdate, expirationdate-replandays
into dt_ExpirationDate, dt_ReplanDate
FROM tbl_tmx_UserRequiredAct userReq
INNER JOIN tbl_tmx_requiredActivity Req on userReq.activityFK = Req.activityFK
WHERE userReq.activityfk= :NEW.ActivityFK and empfk= :NEW.EmpFK;
exception
when no_data_found then
begin
dt_ExpirationDate := NULL;
dt_ReplanDate := NULL;
end;
end;

--Select the Expiration Rule ...
--select the ExpirationParamValue ....
--select ExpirationParamDate
begin
select ExpirationRule, ExpirationParamValue,ExpirationParamDate
into i_ExpirationRule , i_ExpirationParamValue, i_ExpirationParamDate
from TBL_TMX_RequiredActivity where ActivityFK = :NEW.ActivityFK ;
exception
when no_data_found then
begin
i_ExpirationRule :=NULL;
i_ExpirationParamValue :=NULL ;
i_ExpirationParamDate := NULL;
end;
end;


if( i_ExpirationRule = 3 ) then
begin
if( ( TO_CHAR(i_ExpirationParamDate,'MM') > TO_CHAR(:NEW.EndDt,'MM') ) or ( (TO_CHAR(:NEW.EndDt,'MM') = TO_CHAR(i_ExpirationParamDate,'MM')) AND ( TO_CHAR(i_ExpirationParamDate,'DD') >TO_CHAR(:NEW.EndDt,'DD') ) ) ) then
begin

--this is to populate the Expiration date when the expirationdate is falling as future date
--need to add the param value to the enddt by subtracting 1 ..

i_ExpirationParamValue := i_ExpirationParamValue -1;
end;
end if;
end;
end if;



if (:NEW.EndDt >= dt_ReplanDate and :NEW.EndDt <= dt_ExpirationDate ) then
begin
select CASE ExpirationRule
WHEN 0 THEN NULL
WHEN 1 THEN dt_ExpirationDate+coalesce(ExpirationParamValue,0)
WHEN 2 THEN ExpirationParamDate
WHEN 3 THEN TO_DATE(TO_CHAR(dt_ExpirationDate,'YYYY')+ExpirationParamValue || '/' || TO_CHAR(ExpirationParamDate,'MM') || '/' || TO_CHAR(ExpirationParamDate,'DD') ,'YYYY/MM/DD')
END
into dt_ExpirationDate
from TBL_TMX_RequiredActivity where ActivityFK = :NEW.ActivityFK ;
exception
when no_data_found then
begin
dt_ExpirationDate := NULL;
end;
end;

else
begin
select CASE ExpirationRule
WHEN 0 THEN NULL
WHEN 1 THEN :NEW.EndDt+coalesce(ExpirationParamValue,0)
WHEN 2 THEN ExpirationParamDate
WHEN 3 THEN TO_DATE(TO_CHAR(:NEW.EndDt,'YYYY')+i_ExpirationParamValue || '/' || TO_CHAR(i_ExpirationParamDate,'MM') || '/' || TO_CHAR(i_ExpirationParamDate,'DD') ,'YYYY/MM/DD')
END
into dt_ExpirationDate
from TBL_TMX_RequiredActivity where ActivityFK = :NEW.ActivityFK ;
exception
when no_data_found then
begin
dt_ExpirationDate := NULL;
end;
end;

end if;

begin
select dt_ExpirationDate-ReplanDays
into dt_ReplanDate
from TBL_TMX_RequiredActivity where ActivityFK = :NEW.ActivityFK;
exception
when no_data_found then
begin
dt_ReplanDate := NULL;
end;
end;
if(:OLD.source=2) then
begin
dt_ExpirationDate := coalesce(least(:OLD.ExpirationDate,dt_ExpirationDate),:OLD.ExpirationDate,dt_ExpirationDate);
end;
end if;
else

dt_ExpirationDate := NULL;
dt_ReplanDate := NULL;
end if;

if(:NEW.LinkFk is null) then
:NEW.ExpirationDate := dt_ExpirationDate;
:NEW.ReplanDate := dt_ReplanDate;
end if;
--end of updation
IF ( :OLD.CompletionStatus = 0 OR :OLD.CompletionStatus is NULL ) AND :NEW.CompletionStatus = 1 THEN
Core_Trigger.attemptupd_add(:NEW.Attempt_PK, :NEW.ActivityFK, :NEW.EmpFK);
END IF;
END tu_attempt_2;
/
SHOW ERRORS;

ALTER TRIGGER TU_ATTEMPT_2 DISABLE;



CREATE OR REPLACE TRIGGER tu_attempt_3
AFTER UPDATE OF EndDt ON TBL_TMX_Attempt
BEGIN
Core_Trigger.attemptupd_process;
END tu_attempt_3;
/
SHOW ERRORS;

ALTER TRIGGER TU_ATTEMPT_3 DISABLE;



ALTER TABLE TBL_TMX_ATTEMPT ADD (
CONSTRAINT PK_TBL_TMX_ATTEMPT
PRIMARY KEY
(ATTEMPT_PK)
USING INDEX
TABLESPACE SUMTOTAL_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));


ALTER TABLE TBL_TMX_ATTEMPT ADD (
CONSTRAINT FK_ACTIVITYFK_GVRRF10G
FOREIGN KEY (ACTIVITYFK)
REFERENCES TBL_TMX_ACTIVITY (ACTIVITY_PK)
ON DELETE CASCADE);

ALTER TABLE TBL_TMX_ATTEMPT ADD (
CONSTRAINT FK_EMPFK_GVRRF10F
FOREIGN KEY (EMPFK)
REFERENCES TBLEMP (EMP_PK)
ON DELETE CASCADE);

ALTER TABLE TBL_TMX_ATTEMPT ADD (
CONSTRAINT FK_MODIFIERFK_GVRRF10H
FOREIGN KEY (MODIFIERFK)
REFERENCES TBLEMP (EMP_PK)
ON DELETE SET NULL);

PROMPT "Table TBL_TMX_ATTEMPT End "



Tom Kyte
December 10, 2005 - 5:05 am UTC

you should chop out everything, every single thing, that is not relevant. I had to hit page down many times to see this - SMALLER is BETTER.


lose all storage clauses, tablespaces - that sort of stuff. you want something that anyone can run on any system.


no triggers, the code can even go away - you are hitting this on parse. make it easy for someone looking at this to see what it wrong.

I won't even consider a 3 part problem where part I makes me scroll over and over with lots of unnecessary stuff (triggers??? cannot see how they are relevant - but as you reduce the problem you might be able to say "hey, take away the trigger and it works??!? you know how much faster you'll get from problem to solution that way. The fine art of debugging involves reducing the problem to its very essence)


SMALL
yet 100% complete
but CONCISE

that is the description of a good test case.

Hitting ORA-00942 ...

VKOUL, December 09, 2005 - 3:22 pm UTC

********************************************************
PART 2
********************************************************
PROMPT "Table TBL_TMX_ACTIVITY Start"

CREATE TABLE TBL_TMX_ACTIVITY
(
ACTIVITY_PK NUMBER(10) NOT NULL,
ACTIVITYNAME VARCHAR2(900 BYTE) NOT NULL,
ACTIVITYSEARCH VARCHAR2(1665 BYTE),
ACTIVITYLABELFK NUMBER(10),
PRNTACTFK NUMBER(10),
CODE VARCHAR2(765 BYTE),
CCENTER NUMBER(10),
ACTIVITYDESC CLOB,
ACTIVITYDESCFMT CLOB,
URL VARCHAR2(3072 BYTE),
ACTIVE NUMBER(10) NOT NULL,
OPENFORREG NUMBER(10) NOT NULL,
NOREGREQD NUMBER(10),
MAXATTEMPTS NUMBER(10),
MAXATTEMPTSPERPARENT NUMBER(10),
CANCELLED NUMBER(10) NOT NULL,
HIDDEN NUMBER(10) NOT NULL,
PRIVATE NUMBER(10) NOT NULL,
EMPNOTES CLOB,
EMPNOTESFMT CLOB,
INSTRNOTES CLOB,
INSTRNOTESFMT CLOB,
ISDELETED NUMBER(10) NOT NULL,
COSTBASE NUMBER(17,2),
COSTCNCL NUMBER(17,2),
COSTLATECNCL NUMBER(17,2),
COSTNOSHOW NUMBER(17,2),
CURRENCYFK NUMBER(10),
PAYTERMFK NUMBER(10),
ECOMREQD NUMBER(10),
STARTDT DATE,
ENDDT DATE,
REGDEADLINEDT DATE,
REGCNCLDEADLINEDT DATE,
TIMEZONEFK NUMBER(10),
USEFORCONFLICTCHECK NUMBER(10),
REQAPPR NUMBER(10),
DEFAPPREMPFK NUMBER(10),
OWNEREMPFK NUMBER(10),
REQPERSONNEL CLOB,
CBTLAUNCHMTDFK NUMBER(10),
CANBECOPIED NUMBER(10),
CANBESUBSCR NUMBER(10),
CANBEFUL NUMBER(10),
ROLLUPFROMFUL NUMBER(10),
STICKYAUDIENCEIND NUMBER(10),
COPYORIGINALACTIVITY NUMBER(10),
MINCAPACITY NUMBER(10),
MAXCAPACITY NUMBER(10),
RECORDSESSION NUMBER(10),
REQUIREDIND NUMBER(10) NOT NULL,
PICKRULE NUMBER(10),
PICKRULETYPE NUMBER(10) NOT NULL,
USETYPE NUMBER(10) NOT NULL,
RESTRICTED NUMBER(10) NOT NULL,
ACTTOCOMPLETE NUMBER(10),
CONTRIBUTEROLLUPIND NUMBER(10),
MINPCTGRD NUMBER(10),
ESTDUR FLOAT(126),
SENDACTCOMPLETENTF NUMBER(10),
SENDACTREMINDERNTF NUMBER(10),
SENDACTREGNTF NUMBER(10),
ORDINAL NUMBER(10),
LEVELVAL NUMBER(10),
ROOTACTIVITYFK NUMBER(10),
REFERENCEACTFK NUMBER(10),
ORIGINALPK NUMBER(10),
DEFFACFK NUMBER(10),
HDNFRMTRANSCRIPT NUMBER(10) NOT NULL,
TOSTAGINGDT DATE,
LSTUPD DATE NOT NULL,
USRNAME VARCHAR2(90 BYTE) NOT NULL,
LCK NUMBER(10) NOT NULL,
TIMESTAMP NUMBER(10),
CONTACT VARCHAR2(1024 BYTE),
ESTCRDHRS FLOAT(126)
);

CREATE INDEX IDX_TMX_ACTIVITY_GVOF9C0W ON TBL_TMX_ACTIVITY
(UPPER("ACTIVITYNAME"), ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_GVOF9C1E ON TBL_TMX_ACTIVITY
(ACTIVITY_PK, PRNTACTFK, ACTIVE, HIDDEN, CANCELLED, NOREGREQD, REFERENCEACTFK, ENDDT);


CREATE INDEX IDX_TMX_ACTIVITY_GVOF9C1J ON TBL_TMX_ACTIVITY
(LEVELVAL, ORDINAL);


CREATE INDEX FT_TABLE_U7NBNT0K ON TBL_TMX_ACTIVITY
(ACTIVITYNAME)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');


CREATE INDEX FT_TABLE_U7NBNT0L ON TBL_TMX_ACTIVITY
(ACTIVITYSEARCH)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');


CREATE INDEX FT_TABLE_U7NBNT0M ON TBL_TMX_ACTIVITY
(CODE)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');


CREATE INDEX FT_TABLE_U7NBNT0N ON TBL_TMX_ACTIVITY
(ACTIVITYDESC)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW17 ON TBL_TMX_ACTIVITY
(ENDDT, STARTDT, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW19 ON TBL_TMX_ACTIVITY
(REFERENCEACTFK, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW1A ON TBL_TMX_ACTIVITY
(CANBECOPIED, ACTIVE, CANCELLED, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW1B ON TBL_TMX_ACTIVITY
(CANBEFUL, ACTIVE, CANCELLED, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW1C ON TBL_TMX_ACTIVITY
(CANBESUBSCR, ACTIVE, CANCELLED, ACTIVITY_PK);


CREATE UNIQUE INDEX PK_TBL_TMX_ACTIVITY ON TBL_TMX_ACTIVITY
(ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW0R ON TBL_TMX_ACTIVITY
(CODE, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW0S ON TBL_TMX_ACTIVITY
(ACTIVE, ACTIVITY_PK, PRNTACTFK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW0T ON TBL_TMX_ACTIVITY
(CANCELLED, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW0U ON TBL_TMX_ACTIVITY
(ACTIVITYLABELFK, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW0V ON TBL_TMX_ACTIVITY
(PRNTACTFK, ACTIVITY_PK, ROOTACTIVITYFK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW0W ON TBL_TMX_ACTIVITY
(ACTIVITY_PK, ROOTACTIVITYFK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW0X ON TBL_TMX_ACTIVITY
(ROOTACTIVITYFK, ACTIVITY_PK, PRNTACTFK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW0Y ON TBL_TMX_ACTIVITY
(DEFAPPREMPFK, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW0Z ON TBL_TMX_ACTIVITY
(OWNEREMPFK, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW10 ON TBL_TMX_ACTIVITY
(CBTLAUNCHMTDFK, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW11 ON TBL_TMX_ACTIVITY
(ORDINAL, ACTIVITY_PK);


CREATE UNIQUE INDEX IDX_TMX_ACTIVITY_AB8CHW12 ON TBL_TMX_ACTIVITY
(ROOTACTIVITYFK, ACTIVITY_PK, ACTIVITYLABELFK, ACTIVE, PRNTACTFK, ISDELETED, CANCELLED);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW13 ON TBL_TMX_ACTIVITY
(ORIGINALPK, ACTIVITYLABELFK, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW14 ON TBL_TMX_ACTIVITY
(DEFFACFK, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW15 ON TBL_TMX_ACTIVITY
(USEFORCONFLICTCHECK, ACTIVE, CANCELLED, ACTIVITY_PK);


CREATE INDEX IDX_TMX_ACTIVITY_AB8CHW16 ON TBL_TMX_ACTIVITY
(STARTDT, ENDDT, ACTIVITY_PK);


CREATE OR REPLACE TRIGGER actafterrowtrg
after delete on tbl_tmx_activity
for each row
begin
if ( pkg_status.statusflag )
then
pkg_status.modifiedtab(pkg_status.ncount):= :old.activity_pk;
pkg_status.ncount := pkg_status.ncount + 1;

end if;
end;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER actafterstatetrg
after delete on tbl_tmx_activity
begin

if ( pkg_status.statusflag )

then

pkg_status.statusflag := FALSE;

For i in 1.. pkg_status.modifiedtab.count
Loop

Delete from Tbl_TAX_Item
Where extintid = pkg_status.modifiedtab(i) and ConTypeId_FK = -6;

end loop;

pkg_status.statusflag := TRUE;
pkg_status.ncount := 1;

end if;

exception

when others then

pkg_status.statusflag := TRUE;
pkg_status.ncount := 1;

raise;

end;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER actbeforetrg
before delete on tbl_tmx_activity
begin
if ( pkg_status.statusflag )
then
pkg_status.ncount := 1;
pkg_status.modifiedtab := pkg_status.cleartab;

end if;
end;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER tI_Activity_1
BEFORE INSERT ON TBL_TMX_Activity
BEGIN
IF NOT Core_Trigger.trig_activityins_running THEN
Core_Trigger.activityins_clear;
END IF;
END tI_Activity_1;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER tI_Activity_2
BEFORE INSERT ON TBL_TMX_Activity
FOR EACH ROW
BEGIN
IF NOT Core_Trigger.trig_activityins_running THEN
Core_Trigger.activityins_add(:NEW.Activity_PK, :NEW.PrntActFK);
END IF;
END tI_Activity_2;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER tI_Activity_3
AFTER Insert ON TBL_TMX_Activity
BEGIN
IF NOT Core_Trigger.trig_activityins_running THEN
Core_Trigger.activityins_process;
END IF;
END tI_Activity_3;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER tr_activity_search
BEFORE INSERT OR UPDATE OF ActivityName, Code ON TBL_TMX_Activity
FOR EACH ROW
BEGIN
:NEW.ActivitySearch := SUBSTR(TRIM(TRIM(:NEW.ActivityName)||' '||TRIM(:NEW.Code)),1,1665);
END tr_activity_search;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER TR_BI_TBL_TMX_ACTIVITY
before insert on TBL_TMX_Activity
for each row
begin
if :new.CCenter is null then
:new.CCenter := 0;
end if;

if :new.Active is null then
:new.Active := 1;
end if;

if :new.OpenForReg is null then
:new.OpenForReg := 1;
end if;

if :new.NoRegReqd is null then
:new.NoRegReqd := 0;
end if;

if :new.Cancelled is null then
:new.Cancelled := 0;
end if;

if :new.Hidden is null then
:new.Hidden := 0;
end if;

if :new.Private is null then
:new.Private := 0;
end if;

if :new.IsDeleted is null then
:new.IsDeleted := 0;
end if;

if :new.UseForConflictCheck is null then
:new.UseForConflictCheck := 1;
end if;

if :new.OwnerEmpFK is null then
:new.OwnerEmpFK := -1;
end if;

if :new.CanBeCopied is null then
:new.CanBeCopied := 0;
end if;

if :new.CanBeSubscr is null then
:new.CanBeSubscr := 0;
end if;

if :new.CanBeFul is null then
:new.CanBeFul := 0;
end if;

if :new.RequiredInd is null then
:new.RequiredInd := 0;
end if;

if :new.PickRuleType is null then
:new.PickRuleType := 3;
end if;

if :new.UseType is null then
:new.UseType := 0;
end if;

if :new.Restricted is null then
:new.Restricted := 0;
end if;

if :new.ContributeRollupInd is null then
:new.ContributeRollupInd := 1;
end if;

if :new.SendActCompleteNTF is null then
:new.SendActCompleteNTF := 1;
end if;

if :new.SendActReminderNTF is null then
:new.SendActReminderNTF := 1;
end if;

if :new.SendActRegNTF is null then
:new.SendActRegNTF := 1;
end if;

if :new.HdnFrmTranscript is null then
:new.HdnFrmTranscript := 0;
end if;

if :new.ToStagingDt is null then
: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 ...

VKOUL, December 09, 2005 - 3:24 pm UTC

********************************************************
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

A reader, January 11, 2006 - 12:11 pm UTC

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


Tom Kyte
January 12, 2006 - 10:42 am UTC

why not just use instr? to find a space would be very easy with just instr

regexp_instr

A reader, January 11, 2006 - 1:14 pm UTC

Hi

I am able to that with :blank

Thanks

whu not just instr

jasdeep, January 12, 2006 - 12:04 pm UTC

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.

Tom Kyte
January 12, 2006 - 12:14 pm 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

Praveen, January 24, 2006 - 7:36 am UTC

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


Tom Kyte
January 24, 2006 - 8:28 am 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

Praveen, February 01, 2006 - 3:08 am UTC

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.

SELECT * FROM (
SELECT SUBSTR(str,1,14) ITEM, A.* FROM Table1 A
UNION ALL
SELECT SUBSTR(str,15,14) ITEM, A.* FROM Table1 A
UNION ALL
SELECT SUBSTR(str,29,14) ITEM, A.* FROM Table1 A
UNION ALL
SELECT SUBSTR(str,43,14) ITEM, A.* FROM Table1 A
UNION ALL
SELECT SUBSTR(str,57,14) ITEM, A.* FROM Table1 A
UNION ALL
SELECT SUBSTR(str,71,14) ITEM, A.* FROM Table1 A
......upto 26 times
) WHERE ITEM IS NOT NULL;


But inside the function, I use a few condition checks to filter out certain records, while determining the values of a few other columns, which will be difficult to handled in the above query.

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.

Praveen, February 01, 2006 - 3:24 am UTC

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

Tom Kyte
February 01, 2006 - 8:24 am 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

A reader, February 23, 2006 - 7:42 am UTC

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

Tom Kyte
February 23, 2006 - 8:25 am UTC

I don't get it.

reader

A reader, February 23, 2006 - 9:04 am UTC

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



Tom Kyte
February 23, 2006 - 9:59 am 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

Michel Cadot, February 23, 2006 - 3:35 pm UTC

SQL> select * from t;
        ID C1         C2
---------- ---------- ----------
         1 abcdefghij 1234cd5678
         2 klmnopqrst 1234567890

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 

Tom Kyte
February 23, 2006 - 7:23 pm 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

Michel Cadot, February 24, 2006 - 1:41 am UTC

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


Tom Kyte
February 24, 2006 - 8:23 am UTC

I read:

I like to concatenate 6 columns from one table and concatenate 6 columns


and sort of figured "6".

LOL

Michel Cadot, February 24, 2006 - 2:08 pm UTC

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

Tom Kyte
February 24, 2006 - 2:41 pm 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!

Michel Cadot, February 24, 2006 - 4:32 pm UTC

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.

</code> https://asktom.oracle.com/Misc/oramag/on-format-negation-and-sliding.html <code>

Regards
Michel

column matching ?!?!

Gabe, February 24, 2006 - 11:46 pm UTC

Well, both your solution and Michel’s 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 Michel’s 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
;


Tom Kyte
February 25, 2006 - 11:11 am UTC

indeed, thanks!

column matching ?!?!

Michel Cadot, February 25, 2006 - 2:18 am UTC


You're right.
Thanks for the correction.

Regards
Michel

parsing multiple lines

karma, April 17, 2006 - 7:57 pm UTC

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?

Tom Kyte
April 18, 2006 - 9:21 am 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

Rima, June 14, 2006 - 3:38 pm UTC

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.

Tom Kyte
June 15, 2006 - 8:20 am 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.

Evan Song, July 20, 2006 - 12:00 am UTC

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.

Tom Kyte
July 22, 2006 - 4:32 pm 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.

Evan, July 20, 2006 - 4:20 pm UTC

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.

Tom Kyte
July 22, 2006 - 5:25 pm 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

DM, August 11, 2006 - 1:09 am UTC

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

Tom Kyte
August 11, 2006 - 10:40 am UTC

Converting SQL file to csv file

Soni Kumari, August 22, 2006 - 8:02 am UTC

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 ...

             SQL> @order_relationships.sql &1 &2

             Where &1 = Extract file name used in SPOOL command
                   &2 = Report Identifier
******************************************************************************/
WHENEVER OSERROR EXIT SQL.OSCODE ROLLBACK
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
SET ECHO OFF
SET ESCAPE ON
SET SERVEROUTPUT ON
SET DOCUMENT OFF
SET VERIFY OFF
SET HEADING OFF
SET RECSEP OFF
SET NEWPAGE 0
SET VERIFY OFF
SET CONCAT ON

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
/******************************************************************************
Author: Soni Kumari                                   
Date:   18-Aug-2006

Edit
History:

  18-Aug-2006 Soni Kumari Issue #RHEY-6RWLVU
        o Initial version.
******************************************************************************/
DEFINE csvfile =  &1
DEFINE rptid = &2

PROMPT
PROMPT Beginning data extraction.

SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 500
SET TRIMSPOOL ON
SET TERMOUT OFF
COLUMN current_date NEW_VALUE report_date
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') current_date FROM DUAL;
COLUMN current_date NEW_VALUE report_time
SELECT TO_CHAR(SYSDATE,'HH:MI:SS AM') current_date FROM DUAL;

SELECT SYSDATE FROM DUAL;

SET TERMOUT ON

--Setup page headings and footings
TTITLE CENTER 'Order Relationships' RIGHT report_date SKIP 1 RIGHT report_time SKIP 3 'Anchor Order:  '  FORMAT A10 anc_ord_var SKIP 1 FORMAT A10 slct_var SKIP 2

BTITLE LEFT 'No Related WorkOrder' RIGHT 'Page ' FORMAT 999 SQL.PNO

--Format the columns
COLUMN anchor_ord NEW_VALUE anc_ord_var NOPRINT
COLUMN slct NEW_VALUE slct_var NOPRINT
COLUMN wo_nbr           HEADING 'Dependent|Work Order' FORMAT A10 WORD_WRAPPED 
COLUMN rel_wo_nbr       HEADING 'Related|Work Order'   FORMAT A10 WORD_WRAPPED 
COLUMN wostatus         HEADING 'Wostatus'    FORMAT A10 WORD_WRAPPED 
COLUMN opstatus         HEADING 'OpStatus' FORMAT A10 WORD_WRAPPED 
COLUMN loc              HEADING 'Location' FORMAT A10 WORD_WRAPPED 
COLUMN item             HEADING 'Item' FORMAT A10 WORD_WRAPPED 
COLUMN qty              HEADING 'Qty' FORMAT 9999 


BREAK ON anchor_ord SKIP PAGE 1 NODUPLICATES ON slct NODUPLICATES

SET TERMOUT OFF

SPOOL &csvfile

--
--Column Headings
--

--
-- Write out Report Indentifier
--
SELECT '&rptid'||','
  FROM DUAL;

--
-- Column Headings
--
--
-- Detail Information
--
SELECT anchor_ord||','||
       SLCT||','||
       wo_nbr||','||
       rel_wo_nbr||','||
       wostatus||','||
       opstatus||','||
       loc||','||
       Item||','||
       qty||','
  FROM siviews.si_cancel_workorder
 ORDER BY anchor_ord;

SPOOL OFF
SET TERMOUT ON
SET FEEDBACK ON
SET PAGESIZE 0
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF
BTITLE OFF

PROMPT Data extraction complete.
PROMPT

Please advice as what changes I need to do to export the formatting also in the csv file.

Thanks,
Soni  

Tom Kyte
August 27, 2006 - 2:19 pm 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

Soni Kumari, September 04, 2006 - 3:17 am UTC

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.


SET ECHO OFF
SET ESCAPE ON
SET SERVEROUTPUT ON
SET DOCUMENT OFF
SET VERIFY OFF

DEFINE csvfile = &1

PROMPT
PROMPT Beginning data extraction.

SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 500

v_date DATE := TRUNC(SYSDATE,'dd-mon-yy');

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')) INTO v_date FROM DUAL;

SET TRIMSPOOL ON
SET TERMOUT OFF

SPOOL &csvfile
-- Column Headings
SELECT 'LEADTIME,Item,SUBORD,NONEWSUPPLYDATE,AVG_FCST,OH,' FROM DUAL;
SPOOL OFF
SET TERMOUT ON

PROMPT Data extraction complete.
PROMPT
END;


Tom Kyte
September 04, 2006 - 9:04 am 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'

Matthias Rogel, September 04, 2006 - 9:11 am UTC

"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.


 

Tom Kyte
September 04, 2006 - 9:12 am 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.

A reader, November 10, 2006 - 7:32 am UTC

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.

Tom Kyte
November 10, 2006 - 9:05 am UTC

can we fix your data model and make this data useful instead?

A reader, November 10, 2006 - 10:42 am UTC

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.

Tom Kyte
November 10, 2006 - 2:43 pm UTC

easy answer:


you already have the date, it was the input to the query see.... You know what it is.

URGENT

A reader, November 10, 2006 - 11:10 am UTC

I think it can be done using regexp_replace, i am in 10g
but i miss to pick only the date occurance some times.


Tom Kyte
November 10, 2006 - 2:47 pm 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)

A reader, January 29, 2007 - 7:34 pm UTC

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
Tom Kyte
January 31, 2007 - 1:18 pm 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

A reader, February 14, 2007 - 6:01 pm UTC

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.
Tom Kyte
February 15, 2007 - 9:51 am 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

Frank Zhou, February 15, 2007 - 11:30 am UTC

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

Tom Admirer, February 16, 2007 - 12:30 pm UTC

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
Tom Kyte
February 17, 2007 - 11:14 am UTC

variation on a theme... either or is good.

Another SQL solution

Scott, February 22, 2007 - 11:22 pm UTC

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?
Tom Kyte
February 23, 2007 - 7:43 am UTC

http://asktom.oracle.com/Misc/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

Frank Zhou, February 23, 2007 - 10:34 am UTC

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

Mike, September 12, 2007 - 8:42 am UTC

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 10.2.0.3

But in 11.1.0.6 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

A reader, April 08, 2008 - 12:23 am UTC

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.
Tom Kyte
April 09, 2008 - 2:00 pm 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

TAG OTN, April 27, 2008 - 8:01 pm UTC

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

Tom Kyte
April 28, 2008 - 1:14 pm 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

Juan, May 07, 2008 - 12:32 pm UTC

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

Tom Kyte
May 08, 2008 - 3:28 am 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 would (strongly) suggest you look to store your DATA as DATA, not as munged up strings that are less than useful in real life.

I'll never understand why people store delimited strings in a database. Never.

A reader, May 07, 2008 - 3:33 pm UTC

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

A reader, May 07, 2008 - 3:38 pm UTC

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

Juan, May 09, 2008 - 6:00 am UTC

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





Reader, June 07, 2008 - 12:27 am UTC

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;

Reader, June 10, 2008 - 9:18 am UTC

I was wondering if you can answer the above question.
Tom Kyte
June 10, 2008 - 11:48 am 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"

Reader, June 10, 2008 - 2:45 pm UTC

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;


Tom Kyte
June 10, 2008 - 3:23 pm 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


Reader, June 10, 2008 - 3:47 pm UTC

Thanks a lot.

Reader, June 11, 2008 - 12:08 pm UTC

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|

Tom Kyte
June 11, 2008 - 4:01 pm UTC

that looks fine to me.

How to read csv from a CLOB object in pl/sql

Yathish, June 19, 2008 - 9:10 am UTC

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
Tom Kyte
June 19, 2008 - 10:51 am 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

Yathish M P, June 20, 2008 - 3:49 am UTC

This was extremely helpful and works fine..Thanks Tom

Passing comma seperated values in string as

DM, October 01, 2008 - 12:29 pm UTC

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

dk, February 24, 2009 - 7:40 am UTC

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 "~".
Tom Kyte
February 24, 2009 - 4:49 pm 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

DK, March 06, 2009 - 2:25 am UTC

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
Tom Kyte
March 06, 2009 - 10:21 am UTC

you'll need to use dbms_sql and piecewise fetch the long.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:665224430110


and if you are parsing through 2gb of stuff, you might discover "clob was not thing slowing us down, plsql parsing 2gb of data was"

ORA-00942

Karady, March 27, 2009 - 4:08 pm UTC

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 


Tom Kyte
March 30, 2009 - 3:44 pm 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)

Asheesh Dhupper, April 28, 2009 - 9:35 pm UTC

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
Tom Kyte
April 29, 2009 - 8:51 am 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

MK, November 24, 2009 - 12:17 pm UTC

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

Tom Kyte
November 24, 2009 - 12:30 pm 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

MK, November 25, 2009 - 3:11 pm UTC

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
Tom Kyte
November 28, 2009 - 10:56 am 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://asktom.oracle.com/Misc/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

MK, November 30, 2009 - 3:30 am UTC

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
Tom Kyte
November 30, 2009 - 11:52 am 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

MK, January 19, 2010 - 1:48 am UTC

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
Tom Kyte
January 19, 2010 - 4:42 pm 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)

MK, January 21, 2010 - 10:42 am UTC

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

Shashi, April 14, 2010 - 5:29 am UTC

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.
Tom Kyte
April 14, 2010 - 9:10 am 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

Abhisek, May 03, 2010 - 8:11 am UTC

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

V.Hariharaputhran, June 21, 2010 - 1:22 pm UTC

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


Tom Kyte
June 22, 2010 - 1:48 pm 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

V.Hariharaputhran, June 22, 2010 - 3:04 pm UTC

Mr.Oracle,

Amazing and speech less. Thanks for your reply,time,effort,explanation and what not.

Regards
V.Hari
Tom Kyte
June 22, 2010 - 6:36 pm 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

Sangeet Menon, August 27, 2010 - 2:50 am UTC

Hi Tom,


I have a String as follows:


101 (4),102 (5) ,110 (2) ,104 (1) ,160 (6)



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
Tom Kyte
September 07, 2010 - 7:45 am 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

Michel Cador, September 07, 2010 - 8:58 am UTC


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

Lalitha, March 08, 2011 - 9:18 pm UTC

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
Tom Kyte
March 09, 2011 - 9:18 am 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

Lalitha Hariharaputhran, March 09, 2011 - 12:50 pm UTC

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

Lalitha, March 10, 2011 - 1:23 pm UTC

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
Tom Kyte
March 10, 2011 - 1:41 pm 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

Lalitha, March 10, 2011 - 1:52 pm UTC

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
Tom Kyte
March 10, 2011 - 1:58 pm UTC

yes you can, why can't you?

string parsing in oracle

LALITHA, March 10, 2011 - 2:06 pm UTC

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. YOU ARE GREAT.

Regards
Lalitha

Tom Kyte
March 10, 2011 - 2:28 pm 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

Sridhar, September 18, 2011 - 2:57 am UTC

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')

Tom Kyte
September 19, 2011 - 5:41 pm 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

PP, January 25, 2012 - 9:08 am UTC

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.
Tom Kyte
January 26, 2012 - 10:10 am UTC

http://asktom.oracle.com/Misc/varying-in-lists.html

just replace my use of a comma with your dot.

pull out string between quotes

Gunjan, September 20, 2012 - 10:10 am UTC


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
Tom Kyte
September 26, 2012 - 11:33 am 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

Phil, September 26, 2013 - 9:38 pm UTC

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...

Regexp

Dinesh, March 26, 2014 - 1:12 pm UTC

Table:

Col1 Col2
----- -----
A 1,2,3,4,5
B 8,9,3,4,5
C 8,3,2,2,6
D 0,4,3,2,6

Output:

Below query out will get as expected: ( we need to process 15 lachs records – Huge data)

A,1
A,2
A,3
A,4
A,5
B,8
....

please help on this

This is Another Method

Arun Kumar B, May 07, 2014 - 1:11 pm UTC

select substr('Brian,Robbin,D',1,(instr('Brian,Robbin,D',',')-1)) as first_name,
substr('Brian,Robbin,D',(instr('Brian,Robbin,D',',',1,2)+1),length('Brian,Robbin,D')) as last_name,
substr('Brian,Robbin,D',(instr('Brian,Robbin,D',',')+1),((instr('Brian,Robbin,D',',',-1,1)-1)-(instr('Brian,Robbin,D',',',-1,2)))) as middle_name from dual;

Answer

Arun Kumar B, May 07, 2014 - 1:35 pm UTC

Hi Dinesh,
The below query will give u r result.

If the table name is 'ipl' then

select name||','||substr(value,1,(instr(value,','))-1) from ipl
union
select name||','||substr(value,3,(instr(value,','))-1) from ipl
union
select name||','||substr(value,5,(instr(value,','))-1) from ipl
union
select name||','||substr(value,7,(instr(value,','))-1) from ipl
union
select name||','||substr(value,9,(instr(value,','))-1) from ipl;