Re: pl/sql question and owa_pattern question
Would extproc_perl fit well enough, though, until 10g is here? On Fri, 21 Nov 2003, Mladen Gogala wrote: > PL/SQL is the fastest thing of them all when it comes to executing > SQL commands, but there are things which simply aren't practical > in 9.2 PL/SQL. Regular expression processing is one of those things. > Fortunately, you can mix the two. Without DBI, perl scripts simply > woudn't be very useful. Of course, there are things that are faster > then even the fastest perl script. Lexer written in C is one of them > and you don't need much work to write one, either, but using OCI is > not easy. OCI is a library written to confuse the enemy, not to help > developer. Using plain and simple regex or PCRE within a C program > is the same thing as above, but slightly more complicated then a lexer. > For the specific task of manipulating patterns and resolving regular > expressions, I use perl almost exclusively because I find it an optimal > tradeoff between ease of use and performance. If performance is a > paramount, as in real time application processing, then you'll have to > resort to C and, possibly, write an external procedure and, thus, > enabling oracle to use C regex calls or even pcre. I was toying with the > idea of enabling oracle to use PCRE but I gave up when I read that 10g > will have that included. > > On 11/21/2003 11:59:31 AM, Guang Mei wrote: > > Perl is a good tool for text processing. But our program is already written > > in pl/sql long time ago and there are intensive db calls in this pl/sql > > program. (text processing is only part of it). So I can not change that. > > > > BTW I did a comparison study a while ago for some of our pl/sql packages > > (specifically for our application). When there are lots of db calls (select, > > insert, update and delete), pl/sql package is faster than correponding perl > > program (I made sure sqls are prepared once and used bind variables in perl. > > All code were executed on the unix server, no other programs were running, > > etc). That's why we stick to pl/sql because our app need the performance. > > Others may have different results, it all depends on what the code does. > > > > Guang > > > > -Original Message- > > Mladen Gogala > > Sent: Thursday, November 20, 2003 11:14 PM > > To: Multiple recipients of list ORACLE-L > > > > > > I don't know about PL/SQL but here is how I would get separate words from a > > big string: > > > > #!/usr/bin/perl -w > > use strict; > > my (@ARR); > > while (<>) { > > chomp; > > @ARR = split(/[^0-9a-zA-Z_\.,<>]/); > > foreach (@ARR) { > > print "$_\n"; > > } > > } > > > > There is something called DBI and it can be used to insert separated words > > into the database, instead > > of printing them. The bottom line is that perl is an excellent tool for > > parsing strings and all sorts of string > > manipulation. > > > > On 2003.11.20 22:39, Guang Mei wrote: > > > Hi: > > > > > > In my pl/sql program, I want to process each "word" in a string. The > > > string is selected from a varchar2(300) column. The delimit that separates > > > the words is not necessary space character. The definition of the delimit > > > in this program is set as > > > > > > 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) > > > and > > > 2. the character is not one of these: '-.,/<*>_' > > > > > > Now my program is basically checking each character, find the delimit, and > > > rebuild each word. After that I process each "word". The code looks like > > > this: > > > > > > --- > > > str := "This will be a long string with length upto 300 characters, it > > > may contain some invisible characters'; > > > len := length(str)+1; > > > for i in 1..len loop > > > ch := substr(str,i,1); > > > if (not strings.isAlnum(ch) and instr('-.,/<*>_', ch)<1) then > > > if word is not null then > > > -- do some processing to variable word ! > > > word := null;-- reset it > > > end if; > > > else > > > word := word || ch; -- concat ch to word > > > end if; > > > end loop; > > > > > > --- > > > > > > I think It's taking too long because it loops through each characters. I > > > hope I could find a way to speed it up. I don't have experiience in > > > owa_pattern, but I thought there might be a way to do it here: > > > > > > > > > str := "This will be a long string with length upto 300 characters, it > > > may contain some invisible characters'; > > > newstr := str; > > > pos := 1; > > > while pos != 0 loop > > > pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out > > > these '-.,/<*>_' ??? > > > word := substr(newstr, 1, pos-1); > > > -- do some processing to variable word ! > > > if pos != 0 then > > > newstr := substr(newstr, pos+1); > > > end if; > > > end loop; > > > -- > > > > > > My simple tests showed that owa_pattern call is much slower than direct > > > string manupil
Re: pl/sql question and owa_pattern question
PL/SQL is the fastest thing of them all when it comes to executing SQL commands, but there are things which simply aren't practical in 9.2 PL/SQL. Regular expression processing is one of those things. Fortunately, you can mix the two. Without DBI, perl scripts simply woudn't be very useful. Of course, there are things that are faster then even the fastest perl script. Lexer written in C is one of them and you don't need much work to write one, either, but using OCI is not easy. OCI is a library written to confuse the enemy, not to help developer. Using plain and simple regex or PCRE within a C program is the same thing as above, but slightly more complicated then a lexer. For the specific task of manipulating patterns and resolving regular expressions, I use perl almost exclusively because I find it an optimal tradeoff between ease of use and performance. If performance is a paramount, as in real time application processing, then you'll have to resort to C and, possibly, write an external procedure and, thus, enabling oracle to use C regex calls or even pcre. I was toying with the idea of enabling oracle to use PCRE but I gave up when I read that 10g will have that included. On 11/21/2003 11:59:31 AM, Guang Mei wrote: > Perl is a good tool for text processing. But our program is already written > in pl/sql long time ago and there are intensive db calls in this pl/sql > program. (text processing is only part of it). So I can not change that. > > BTW I did a comparison study a while ago for some of our pl/sql packages > (specifically for our application). When there are lots of db calls (select, > insert, update and delete), pl/sql package is faster than correponding perl > program (I made sure sqls are prepared once and used bind variables in perl. > All code were executed on the unix server, no other programs were running, > etc). That's why we stick to pl/sql because our app need the performance. > Others may have different results, it all depends on what the code does. > > Guang > > -Original Message- > Mladen Gogala > Sent: Thursday, November 20, 2003 11:14 PM > To: Multiple recipients of list ORACLE-L > > > I don't know about PL/SQL but here is how I would get separate words from a > big string: > > #!/usr/bin/perl -w > use strict; > my (@ARR); > while (<>) { > chomp; > @ARR = split(/[^0-9a-zA-Z_\.,<>]/); > foreach (@ARR) { > print "$_\n"; > } > } > > There is something called DBI and it can be used to insert separated words > into the database, instead > of printing them. The bottom line is that perl is an excellent tool for > parsing strings and all sorts of string > manipulation. > > On 2003.11.20 22:39, Guang Mei wrote: > > Hi: > > > > In my pl/sql program, I want to process each "word" in a string. The > > string is selected from a varchar2(300) column. The delimit that separates > > the words is not necessary space character. The definition of the delimit > > in this program is set as > > > > 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) > > and > > 2. the character is not one of these: '-.,/<*>_' > > > > Now my program is basically checking each character, find the delimit, and > > rebuild each word. After that I process each "word". The code looks like > > this: > > > > --- > > str := "This will be a long string with length upto 300 characters, it > > may contain some invisible characters'; > > len := length(str)+1; > > for i in 1..len loop > > ch := substr(str,i,1); > > if (not strings.isAlnum(ch) and instr('-.,/<*>_', ch)<1) then > > if word is not null then > > -- do some processing to variable word ! > > word := null;-- reset it > > end if; > > else > > word := word || ch; -- concat ch to word > > end if; > > end loop; > > > > --- > > > > I think It's taking too long because it loops through each characters. I > > hope I could find a way to speed it up. I don't have experiience in > > owa_pattern, but I thought there might be a way to do it here: > > > > > > str := "This will be a long string with length upto 300 characters, it > > may contain some invisible characters'; > > newstr := str; > > pos := 1; > > while pos != 0 loop > > pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out > > these '-.,/<*>_' ??? > > word := substr(newstr, 1, pos-1); > > -- do some processing to variable word ! > > if pos != 0 then > > newstr := substr(newstr, pos+1); > > end if; > > end loop; > > -- > > > > My simple tests showed that owa_pattern call is much slower than direct > > string manupilation. But I would like to try it in this case if I could > > easily get the "wrods" from the string. Any suggestions? > > > > TIA. > > > > Guang > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Guang Mei > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 ht
RE: pl/sql question and owa_pattern question
Guang, Well you are almost there ... you need fifo structure namely a pl/sql array 1. create a local pl/sql array to store the delimiter (store the ascii value of the delimiter to be safe) my_array (varchar2(5)) 2. as you find a delimiter insert into the first position in the array and replace the delimiting character with # 3. lather.rinse.repeat. when it is time to put it back use a loop nIndex := 0; nPos := 0; loop npos := instr(my_str,'#',1); exit when npos := 0; nIndex := nindex + 1; my_str := substr(my_str,1,nPos-1) || chr(my_array(nIndex)) || sybstr(my_str, nPos+1); end loop; something like this should help, proof-read though ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Friday, November 21, 2003 11:44 AM To: Multiple recipients of list ORACLE-L Hi Stephane: Thanks for your good suggestion. I compared the method you suggested and the orginal one and it indeed boosted the performance (in my simple test). However the ONLY problem I am having is that by doing TRANSLATE, I lost the original delimits. The new method (you suggested) correctly "extract" the "words" (and sent for processing), But after processing I need to put processed-words back to the orginal string with orginal demilters un-changed. I tried to track to position of delimit from the orginal string by doing global_pos := global_pos + pos ; in my "while" loop, but ltrim(substr(string, pos + 1), '#') will make "global_pos" wrong when ltrim trims '#'. Any work-around? TIA. Guang -Original Message- Stephane Faroult Sent: Friday, November 21, 2003 4:19 AM To: Multiple recipients of list ORACLE-L Guang, I agree with your analysis, looping on characters is not the faster you can do, simply because there is a significant overhead (compared to C code for instance) in a language such as PL/SQL - which might be perfectly acceptable in some circumstances, much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C, might improve performance. However, in my view the best performance gains you may get is by, so to speak, pushing the bulk of the processing deeper into the kernel (which isn't by the way exclusive of native compiling). Using a function such as INSTR() will be much more efficient than looping on characters. I would suggest something such as : - First use TRANSLATE() to replace all the characters you want to get rid of by a single, well identified character, say # (use CHR() || ... for non printable characters - you can build up the string of characters to translate in the initialisation section of a package rather than typing it). - Start with initializing your string to LTRIM(string, '#') - Then as long as pos := INSTR(string, '#') isn't 0, get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos + 1), '#') to string (very similar to what you were planning to do with owa). This will be probably much faster than a character-by-character loop and calls to an owa package. HTH, Stephane Faroult >- --- Original Message --- - >From: Guang Mei <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Thu, 20 Nov 2003 19:39:55 > >Hi: > >In my pl/sql program, I want to process each "word" >in a string. The >string is selected from a varchar2(300) column. The >delimit that separates >the words is not necessary space character. The >definition of the delimit >in this program is set as > >1. Any character that is NOT AlphaNumerical (0-9, >A-Z,a-z) >and >2. the character is not one of these: '-.,/<*>_' > >Now my program is basically checking each >character, find the delimit, and >rebuild each word. After that I process each >"word". The code looks like >this: > >--- >str := "This will be a long string with length >upto 300 characters, it >may contain some invisible characters'; >len := length(str)+1; > for i in 1..len loop >ch := substr(str,i,1); >if (not strings.isAlnum(ch) and >instr('-.,/<*>_', ch)<1) then > if word is not null then >-- do some processing to variable word ! >word := null;-- reset it > end if; >else > word := word || ch; -- concat ch to word >end if; > end loop; > >--- > >I think It's taking too long because it loops >through each characters. I >hope I could find a way to speed it up. I don't >have experiience in >owa_pattern, but I thought there might be a way to >do it here: > > >str := "This will be a long string with length >upto 300 characters, it >may contain some invisible characters'; >newstr := str; >pos := 1; >while pos != 0 loop >pos := owa_pattern.amatch(newstr, 1, '\W'); >-- how can I mask out >these '-.,/<*>_' ??? >word :
RE: pl/sql question and owa_pattern question
Perl is a good tool for text processing. But our program is already written in pl/sql long time ago and there are intensive db calls in this pl/sql program. (text processing is only part of it). So I can not change that. BTW I did a comparison study a while ago for some of our pl/sql packages (specifically for our application). When there are lots of db calls (select, insert, update and delete), pl/sql package is faster than correponding perl program (I made sure sqls are prepared once and used bind variables in perl. All code were executed on the unix server, no other programs were running, etc). That's why we stick to pl/sql because our app need the performance. Others may have different results, it all depends on what the code does. Guang -Original Message- Mladen Gogala Sent: Thursday, November 20, 2003 11:14 PM To: Multiple recipients of list ORACLE-L I don't know about PL/SQL but here is how I would get separate words from a big string: #!/usr/bin/perl -w use strict; my (@ARR); while (<>) { chomp; @ARR = split(/[^0-9a-zA-Z_\.,<>]/); foreach (@ARR) { print "$_\n"; } } There is something called DBI and it can be used to insert separated words into the database, instead of printing them. The bottom line is that perl is an excellent tool for parsing strings and all sorts of string manipulation. On 2003.11.20 22:39, Guang Mei wrote: > Hi: > > In my pl/sql program, I want to process each "word" in a string. The > string is selected from a varchar2(300) column. The delimit that separates > the words is not necessary space character. The definition of the delimit > in this program is set as > > 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) > and > 2. the character is not one of these: '-.,/<*>_' > > Now my program is basically checking each character, find the delimit, and > rebuild each word. After that I process each "word". The code looks like > this: > > --- > str := "This will be a long string with length upto 300 characters, it > may contain some invisible characters'; > len := length(str)+1; > for i in 1..len loop > ch := substr(str,i,1); > if (not strings.isAlnum(ch) and instr('-.,/<*>_', ch)<1) then > if word is not null then > -- do some processing to variable word ! > word := null;-- reset it > end if; > else > word := word || ch; -- concat ch to word > end if; > end loop; > > --- > > I think It's taking too long because it loops through each characters. I > hope I could find a way to speed it up. I don't have experiience in > owa_pattern, but I thought there might be a way to do it here: > > > str := "This will be a long string with length upto 300 characters, it > may contain some invisible characters'; > newstr := str; > pos := 1; > while pos != 0 loop > pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out > these '-.,/<*>_' ??? > word := substr(newstr, 1, pos-1); > -- do some processing to variable word ! > if pos != 0 then > newstr := substr(newstr, pos+1); > end if; > end loop; > -- > > My simple tests showed that owa_pattern call is much slower than direct > string manupilation. But I would like to try it in this case if I could > easily get the "wrods" from the string. Any suggestions? > > TIA. > > Guang > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Guang Mei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -
RE: pl/sql question and owa_pattern question
Hi Stephane: Thanks for your good suggestion. I compared the method you suggested and the orginal one and it indeed boosted the performance (in my simple test). However the ONLY problem I am having is that by doing TRANSLATE, I lost the original delimits. The new method (you suggested) correctly "extract" the "words" (and sent for processing), But after processing I need to put processed-words back to the orginal string with orginal demilters un-changed. I tried to track to position of delimit from the orginal string by doing global_pos := global_pos + pos ; in my "while" loop, but ltrim(substr(string, pos + 1), '#') will make "global_pos" wrong when ltrim trims '#'. Any work-around? TIA. Guang -Original Message- Stephane Faroult Sent: Friday, November 21, 2003 4:19 AM To: Multiple recipients of list ORACLE-L Guang, I agree with your analysis, looping on characters is not the faster you can do, simply because there is a significant overhead (compared to C code for instance) in a language such as PL/SQL - which might be perfectly acceptable in some circumstances, much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C, might improve performance. However, in my view the best performance gains you may get is by, so to speak, pushing the bulk of the processing deeper into the kernel (which isn't by the way exclusive of native compiling). Using a function such as INSTR() will be much more efficient than looping on characters. I would suggest something such as : - First use TRANSLATE() to replace all the characters you want to get rid of by a single, well identified character, say # (use CHR() || ... for non printable characters - you can build up the string of characters to translate in the initialisation section of a package rather than typing it). - Start with initializing your string to LTRIM(string, '#') - Then as long as pos := INSTR(string, '#') isn't 0, get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos + 1), '#') to string (very similar to what you were planning to do with owa). This will be probably much faster than a character-by-character loop and calls to an owa package. HTH, Stephane Faroult >- --- Original Message --- - >From: Guang Mei <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Thu, 20 Nov 2003 19:39:55 > >Hi: > >In my pl/sql program, I want to process each "word" >in a string. The >string is selected from a varchar2(300) column. The >delimit that separates >the words is not necessary space character. The >definition of the delimit >in this program is set as > >1. Any character that is NOT AlphaNumerical (0-9, >A-Z,a-z) >and >2. the character is not one of these: '-.,/<*>_' > >Now my program is basically checking each >character, find the delimit, and >rebuild each word. After that I process each >"word". The code looks like >this: > >--- >str := "This will be a long string with length >upto 300 characters, it >may contain some invisible characters'; >len := length(str)+1; > for i in 1..len loop >ch := substr(str,i,1); >if (not strings.isAlnum(ch) and >instr('-.,/<*>_', ch)<1) then > if word is not null then >-- do some processing to variable word ! >word := null;-- reset it > end if; >else > word := word || ch; -- concat ch to word >end if; > end loop; > >--- > >I think It's taking too long because it loops >through each characters. I >hope I could find a way to speed it up. I don't >have experiience in >owa_pattern, but I thought there might be a way to >do it here: > > >str := "This will be a long string with length >upto 300 characters, it >may contain some invisible characters'; >newstr := str; >pos := 1; >while pos != 0 loop >pos := owa_pattern.amatch(newstr, 1, '\W'); >-- how can I mask out >these '-.,/<*>_' ??? >word := substr(newstr, 1, pos-1); >-- do some processing to variable word ! >if pos != 0 then > newstr := substr(newstr, pos+1); >end if; >end loop; >-- > >My simple tests showed that owa_pattern call is >much slower than direct >string manupilation. But I would like to try it in >this case if I could >easily get the "wrods" from the string. Any >suggestions? > >TIA. > >Guang > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information
RE: pl/sql question and owa_pattern question
Guang, I agree with your analysis, looping on characters is not the faster you can do, simply because there is a significant overhead (compared to C code for instance) in a language such as PL/SQL - which might be perfectly acceptable in some circumstances, much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C, might improve performance. However, in my view the best performance gains you may get is by, so to speak, pushing the bulk of the processing deeper into the kernel (which isn't by the way exclusive of native compiling). Using a function such as INSTR() will be much more efficient than looping on characters. I would suggest something such as : - First use TRANSLATE() to replace all the characters you want to get rid of by a single, well identified character, say # (use CHR() || ... for non printable characters - you can build up the string of characters to translate in the initialisation section of a package rather than typing it). - Start with initializing your string to LTRIM(string, '#') - Then as long as pos := INSTR(string, '#') isn't 0, get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos + 1), '#') to string (very similar to what you were planning to do with owa). This will be probably much faster than a character-by-character loop and calls to an owa package. HTH, Stephane Faroult >- --- Original Message --- - >From: Guang Mei <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Thu, 20 Nov 2003 19:39:55 > >Hi: > >In my pl/sql program, I want to process each "word" >in a string. The >string is selected from a varchar2(300) column. The >delimit that separates >the words is not necessary space character. The >definition of the delimit >in this program is set as > >1. Any character that is NOT AlphaNumerical (0-9, >A-Z,a-z) >and >2. the character is not one of these: '-.,/<*>_' > >Now my program is basically checking each >character, find the delimit, and >rebuild each word. After that I process each >"word". The code looks like >this: > >--- >str := "This will be a long string with length >upto 300 characters, it >may contain some invisible characters'; >len := length(str)+1; > for i in 1..len loop >ch := substr(str,i,1); >if (not strings.isAlnum(ch) and >instr('-.,/<*>_', ch)<1) then > if word is not null then >-- do some processing to variable word ! >word := null;-- reset it > end if; >else > word := word || ch; -- concat ch to word >end if; > end loop; > >--- > >I think It's taking too long because it loops >through each characters. I >hope I could find a way to speed it up. I don't >have experiience in >owa_pattern, but I thought there might be a way to >do it here: > > >str := "This will be a long string with length >upto 300 characters, it >may contain some invisible characters'; >newstr := str; >pos := 1; >while pos != 0 loop >pos := owa_pattern.amatch(newstr, 1, '\W'); >-- how can I mask out >these '-.,/<*>_' ??? >word := substr(newstr, 1, pos-1); >-- do some processing to variable word ! >if pos != 0 then > newstr := substr(newstr, pos+1); >end if; >end loop; >-- > >My simple tests showed that owa_pattern call is >much slower than direct >string manupilation. But I would like to try it in >this case if I could >easily get the "wrods" from the string. Any >suggestions? > >TIA. > >Guang > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql question and owa_pattern question
I don't know about PL/SQL but here is how I would get separate words from a big string: #!/usr/bin/perl -w use strict; my (@ARR); while (<>) { chomp; @ARR = split(/[^0-9a-zA-Z_\.,<>]/); foreach (@ARR) { print "$_\n"; } } There is something called DBI and it can be used to insert separated words into the database, instead of printing them. The bottom line is that perl is an excellent tool for parsing strings and all sorts of string manipulation. On 2003.11.20 22:39, Guang Mei wrote: > Hi: > > In my pl/sql program, I want to process each "word" in a string. The > string is selected from a varchar2(300) column. The delimit that separates > the words is not necessary space character. The definition of the delimit > in this program is set as > > 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) > and > 2. the character is not one of these: '-.,/<*>_' > > Now my program is basically checking each character, find the delimit, and > rebuild each word. After that I process each "word". The code looks like > this: > > --- > str := "This will be a long string with length upto 300 characters, it > may contain some invisible characters'; > len := length(str)+1; > for i in 1..len loop > ch := substr(str,i,1); > if (not strings.isAlnum(ch) and instr('-.,/<*>_', ch)<1) then > if word is not null then > -- do some processing to variable word ! > word := null;-- reset it > end if; > else > word := word || ch; -- concat ch to word > end if; > end loop; > > --- > > I think It's taking too long because it loops through each characters. I > hope I could find a way to speed it up. I don't have experiience in > owa_pattern, but I thought there might be a way to do it here: > > > str := "This will be a long string with length upto 300 characters, it > may contain some invisible characters'; > newstr := str; > pos := 1; > while pos != 0 loop > pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out > these '-.,/<*>_' ??? > word := substr(newstr, 1, pos-1); > -- do some processing to variable word ! > if pos != 0 then > newstr := substr(newstr, pos+1); > end if; > end loop; > -- > > My simple tests showed that owa_pattern call is much slower than direct > string manupilation. But I would like to try it in this case if I could > easily get the "wrods" from the string. Any suggestions? > > TIA. > > Guang > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Guang Mei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/Sql question
Tom - Thanks to you and everyone else for the great suggestions. He and I are sitting down tomorrow to straighten this out. I was concerned that there might be some PL/SQL oddity that I wasn't aware of (he is a pretty good PL/SQL programmer). I appreciate your ruling that out. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 22, 2002 7:53 AM To: Multiple recipients of list ORACLE-L Dennis, I'd guess that the developer did not try it correctly. Ask to see the code. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 5:29 PM To: Multiple recipients of list ORACLE-L Tom - The developer reports that he tried this but it didn't work. The third position is still a space value. Thanks to everyone for the good replies. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 21, 2002 3:13 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets <...snip...> marketingcodeVARCHAR2(3); <...snip...> FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); <...snip...> UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); <...snip...> insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE "/MM/DD" NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE "/MM/DD" NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE "/MM/DD" NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(1
RE: PL/Sql question
Dennis, I'd guess that the developer did not try it correctly. Ask to see the code. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 5:29 PM To: Multiple recipients of list ORACLE-L Tom - The developer reports that he tried this but it didn't work. The third position is still a space value. Thanks to everyone for the good replies. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 21, 2002 3:13 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets <...snip...> marketingcodeVARCHAR2(3); <...snip...> FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); <...snip...> UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); <...snip...> insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE "/MM/DD" NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE "/MM/DD" NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE "/MM/DD" NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159) DECIMAL EXTERNAL, TERRITORYEARNINGSAMTPOSITION(160:168) DECIMAL EXTERNAL, EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL, SOURCEFISCALYEARCONSTANT '2003', PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL, PROOFCOUNT POSITION(179:182)DECIMAL EXTERNAL, SEASONDESC POSITION(183:183)DECIMAL EXTERNAL, EXTRACTDATE POSITION(184:193) DATE "/MM/DD" NULLIF EXTRACTDATE=BLANKS, FU
RE: PL/Sql question
Tom - The developer reports that he tried this but it didn't work. The third position is still a space value. Thanks to everyone for the good replies. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 21, 2002 3:13 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets <...snip...> marketingcodeVARCHAR2(3); <...snip...> FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); <...snip...> UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); <...snip...> insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE "/MM/DD" NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE "/MM/DD" NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE "/MM/DD" NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159) DECIMAL EXTERNAL, TERRITORYEARNINGSAMTPOSITION(160:168) DECIMAL EXTERNAL, EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL, SOURCEFISCALYEARCONSTANT '2003', PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL, PROOFCOUNT POSITION(179:182)DECIMAL EXTERNAL, SEASONDESC POSITION(183:183)DECIMAL EXTERNAL, EXTRACTDATE POSITION(184:193) DATE "/MM/DD" NULLIF EXTRACTDATE=BLANKS, FUNPACKJOB POSITION(194:194) CHAR, CONNECTJOB POSITION(195:195) CHAR, STICKYALBUMJOB POSITION(196:196) CHAR, PAYSTATUS POSITION(197:197) CHAR, ORIGINALDATERECEIVED POSITION(198:207) DATE "/MM/DD" NULLIF ORIGINALDATERE, CM
RE: PL/Sql question
Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets <...snip...> marketingcodeVARCHAR2(3); <...snip...> FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); <...snip...> UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); <...snip...> insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE "/MM/DD" NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE "/MM/DD" NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE "/MM/DD" NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159) DECIMAL EXTERNAL, TERRITORYEARNINGSAMTPOSITION(160:168) DECIMAL EXTERNAL, EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL, SOURCEFISCALYEARCONSTANT '2003', PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL, PROOFCOUNT POSITION(179:182)DECIMAL EXTERNAL, SEASONDESC POSITION(183:183)DECIMAL EXTERNAL, EXTRACTDATE POSITION(184:193) DATE "/MM/DD" NULLIF EXTRACTDATE=BLANKS, FUNPACKJOB POSITION(194:194) CHAR, CONNECTJOB POSITION(195:195) CHAR, STICKYALBUMJOB POSITION(196:196) CHAR, PAYSTATUS POSITION(197:197) CHAR, ORIGINALDATERECEIVED POSITION(198:207) DATE "/MM/DD" NULLIF ORIGINALDATERE, CMSNSTATUS POSITION(208:208) CHAR ) == All tables have the marketingcode field defined as varchar2(3) (none are char(3)) Bruce -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network S
RE: PL/SQl question
I believe it is acting appropriately. You are trying to load a two-"character" byte filed into three-byte "character" field. Loader, if you don't terminate by whitespace or nulls, will add the blank into the field because it is character. Thus, you have two options: 1. Change the field to numeric. 2. Trim the data before it is loaded. Check the third position to see if it is a space or null; if so, only load n positions of data. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, August 21, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject: RE: PL/SQl question Dennis, In your PL/SQL program, did you try the RTRIM(date_field,' ') command? I know that TRIM is new, but I thought it needed additional parameters to tell it what to trim. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 10:28 AM To: Multiple recipients of list ORACLE-L > I have a question for from one of my developers related to PL/SQL and how > data is loaded. > > I have a field (marketcode) that is defined as VARCHAR2(3). > > I have a problem when I try to load the value of '20' into this field. > All values with three characters work fine. The problem is when the value > is less then 3 characters. > > When tables A and B have data loaded into this field using SQL/Loader the > resulting value in the field appears to me as '20' with the third position > =null. > > I have a separate PL/SQL process that loads this field into table C. > When PL/SQL populates this same value into this field the field appears to > me as '20' with the third position = space. I can't use SQL/Loader for > this table as the data needs to be massaged before loading into Oracle. > Thus when you try to link the tables together it does not find a match. > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=C.marketcode; > > (this returns 0 records) > > If I change the SQl statement to the following: > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=trim(C.marketcode); > > (it correctly matches these up) > > Things I have tried to remedy this problem: > 1) I have tried to modify my PL/SQL program to put a TRIM statement > around the marketcode field when I populate table C. This did not work. > 2) I have tried to check the 3rd position and if it is = space then I set > the third position to null. But the field in Oracle is still a space when > the program is finished. > > Does anyone have any thoughts on how I can properly output this field from > Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. > Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-50
RE: PL/SQl question
Check the definition of table C. It sounds like it is defined as CHAR(3) instead of VARCHAR2(3). I would also check the PL/SQL for using CHAR instead of VARCHAR2 for storing the value -- the trim should have eliminated this problem if it was put in the right place. Kevin Kennedy First Point Energy Corporation If you take RAC out of Oracle you get OLE! What can this mean? -Original Message- Sent: Wednesday, August 21, 2002 7:28 AM To: Multiple recipients of list ORACLE-L > I have a question for from one of my developers related to PL/SQL and how > data is loaded. > > I have a field (marketcode) that is defined as VARCHAR2(3). > > I have a problem when I try to load the value of '20' into this field. > All values with three characters work fine. The problem is when the value > is less then 3 characters. > > When tables A and B have data loaded into this field using SQL/Loader the > resulting value in the field appears to me as '20' with the third position > =null. > > I have a separate PL/SQL process that loads this field into table C. > When PL/SQL populates this same value into this field the field appears to > me as '20' with the third position = space. I can't use SQL/Loader for > this table as the data needs to be massaged before loading into Oracle. > Thus when you try to link the tables together it does not find a match. > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=C.marketcode; > > (this returns 0 records) > > If I change the SQl statement to the following: > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=trim(C.marketcode); > > (it correctly matches these up) > > Things I have tried to remedy this problem: > 1) I have tried to modify my PL/SQL program to put a TRIM statement > around the marketcode field when I populate table C. This did not work. > 2) I have tried to check the 3rd position and if it is = space then I set > the third position to null. But the field in Oracle is still a space when > the program is finished. > > Does anyone have any thoughts on how I can properly output this field from > Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. > Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kkennedy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQl question
Dennis, In your PL/SQL program, did you try the RTRIM(date_field,' ') command? I know that TRIM is new, but I thought it needed additional parameters to tell it what to trim. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 10:28 AM To: Multiple recipients of list ORACLE-L > I have a question for from one of my developers related to PL/SQL and how > data is loaded. > > I have a field (marketcode) that is defined as VARCHAR2(3). > > I have a problem when I try to load the value of '20' into this field. > All values with three characters work fine. The problem is when the value > is less then 3 characters. > > When tables A and B have data loaded into this field using SQL/Loader the > resulting value in the field appears to me as '20' with the third position > =null. > > I have a separate PL/SQL process that loads this field into table C. > When PL/SQL populates this same value into this field the field appears to > me as '20' with the third position = space. I can't use SQL/Loader for > this table as the data needs to be massaged before loading into Oracle. > Thus when you try to link the tables together it does not find a match. > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=C.marketcode; > > (this returns 0 records) > > If I change the SQl statement to the following: > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=trim(C.marketcode); > > (it correctly matches these up) > > Things I have tried to remedy this problem: > 1) I have tried to modify my PL/SQL program to put a TRIM statement > around the marketcode field when I populate table C. This did not work. > 2) I have tried to check the 3rd position and if it is = space then I set > the third position to null. But the field in Oracle is still a space when > the program is finished. > > Does anyone have any thoughts on how I can properly output this field from > Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. > Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQl question
Is the field in question in table C defined as CHAR or VARCHAR2? If CHAR that is why it is blank padded. Check datatype of variables in pl/sql Rick DENNIS WILLIAMS touch.com>cc: Sent by: Subject: PL/SQl question [EMAIL PROTECTED] m 08/21/2002 10:28 AM Please respond to ORACLE-L > I have a question for from one of my developers related to PL/SQL and how > data is loaded. > > I have a field (marketcode) that is defined as VARCHAR2(3). > > I have a problem when I try to load the value of '20' into this field. > All values with three characters work fine. The problem is when the value > is less then 3 characters. > > When tables A and B have data loaded into this field using SQL/Loader the > resulting value in the field appears to me as '20' with the third position > =null. > > I have a separate PL/SQL process that loads this field into table C. > When PL/SQL populates this same value into this field the field appears to > me as '20' with the third position = space. I can't use SQL/Loader for > this table as the data needs to be massaged before loading into Oracle. > Thus when you try to link the tables together it does not find a match. > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=C.marketcode; > > (this returns 0 records) > > If I change the SQl statement to the following: > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=trim(C.marketcode); > > (it correctly matches these up) > > Things I have tried to remedy this problem: > 1) I have tried to modify my PL/SQL program to put a TRIM statement > around the marketcode field when I populate table C. This did not work. > 2) I have tried to check the 3rd position and if it is = space then I set > the third position to null. But the field in Oracle is still a space when > the program is finished. > > Does anyone have any thoughts on how I can properly output this field from > Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. > Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/Sql question
Sounds like in the table the field c.marketcode is a char(3) instead of varchar2(3). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Wednesday, August 21, 2002 10:28 AM To: Multiple recipients of list ORACLE-L > I have a question for from one of my developers related to PL/SQL and how > data is loaded. > > I have a field (marketcode) that is defined as VARCHAR2(3). > > I have a problem when I try to load the value of '20' into this field. > All values with three characters work fine. The problem is when the value > is less then 3 characters. > > When tables A and B have data loaded into this field using SQL/Loader the > resulting value in the field appears to me as '20' with the third position > =null. > > I have a separate PL/SQL process that loads this field into table C. > When PL/SQL populates this same value into this field the field appears to > me as '20' with the third position = space. I can't use SQL/Loader for > this table as the data needs to be massaged before loading into Oracle. > Thus when you try to link the tables together it does not find a match. > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=C.marketcode; > > (this returns 0 records) > > If I change the SQl statement to the following: > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=trim(C.marketcode); > > (it correctly matches these up) > > Things I have tried to remedy this problem: > 1) I have tried to modify my PL/SQL program to put a TRIM statement > around the marketcode field when I populate table C. This did not work. > 2) I have tried to check the 3rd position and if it is = space then I set > the third position to null. But the field in Oracle is still a space when > the program is finished. > > Does anyone have any thoughts on how I can properly output this field from > Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. > Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Ang: RE: Pl/sql question - if statement
Roland, What ever happened to the Access list you were trying to find? David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Roland.Skoldbl [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Ang: RE: Pl/sql question - if statement om 04/09/2002 11:48 AM Please respond to ORACLE-L Yes but then it fails onthe word borttags_flagg, thi serrormessage : PLS-00103: Encountered the symbol "BORTTAGS_FLAGG" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like I reallydont see what the error is: Roland "John Hallas" <[EMAIL PROTECTED]>@fatcity.com den 2002-04-09 07:58 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Don't you need to start of with a quote before the first A If 'A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' John -Original Message- [EMAIL PROTECTED] Sent: 09 April 2002 15:43 To: Multiple recipients of list ORACLE-L anyone whom can tell me why this statement fails in a pl/sqll code: I get this error message PLS-00103: Encountered the symbol "||AvdNr||" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like betwe when i run this statement If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' then lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.PANTBELOPP ' || --PBK.LPKORGEANREL.EANREL,PBK.LPK ORGEANREL.VARUTYP ' || 'FROM A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' || --PBK.LPKORGEANREL ' || 'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' ' || 'AND ICA_ARTIKEL.DATUMhttp://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://w
Re: Ang: RE: Pl/sql question - if statement
[EMAIL PROTECTED] wrote: > Yes but then it fails onthe word borttags_flagg, thi serrormessage : > > > PLS-00103: Encountered the symbol "BORTTAGS_FLAGG" when expecting one of the >following: > >. ( * @ % & = - + < / > at in mod not rem then > <> or != or ~= >= <= <> and or like > > I reallydont see what the error is: > > > Roland from what's shown below you have an extra ' on the end. > > > > > > > "John Hallas" <[EMAIL PROTECTED]>@fatcity.com den 2002-04-09 07:58 PST > > Sänd svar till [EMAIL PROTECTED] > > Sänt av: [EMAIL PROTECTED] > > > Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Kopia: > > Don't you need to start of with a quote before the first A > > If 'A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' > > John > > -Original Message- > [EMAIL PROTECTED] > Sent: 09 April 2002 15:43 > To: Multiple recipients of list ORACLE-L > > anyone whom can tell me why this statement fails in a pl/sqll code: > > > I get this error message > PLS-00103: Encountered the symbol "||AvdNr||" when expecting one of the > following: > >. ( * @ % & = - + < / > at in mod not rem then > <> or != or ~= >= <= <> and or like >betwe > > > when i run this statement > > > > > > > If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' > then > > lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.PANTBELOPP ' || >--PBK.LPKORGEANREL.EANREL,PBK.LPK > ORGEANREL.VARUTYP ' || > 'FROM > A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' || >--PBK.LPKORGEANREL ' || > 'WHERE ICA_ARTIKEL.EAN=' || > EanLPVara || ' ' || > 'AND > ICA_ARTIKEL.DATUM -- 'AND > ICA_ARTIKEL.BORTTAGS_FLAGG = 0 ' || > 'ORDER BY DATUM DESC'; > > -- > DBMS_OUTPUT.PUT_LINE(lvSQL); > > DBMS_OUTPUT.PUT_LINE(SUBSTR(lvSQL,1,250)); > > DBMS_OUTPUT.PUT_LINE(SUBSTR(lvSQL,251,250)); > END IF; > > Please help me. I must be blind. I have tried to look at the first code > line.. > Would appreciate help very much. > > > Thanks in advance. > > Roland > > > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: John Hallas > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > > > > > > > > -- -- Bill "Shrek" Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Enthousiamos is being filled with theos. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
AW: RE: Pl/sql question - if statement
'BORTTAGS_FLAGG = 0 '' Two quotes after BORTTAGS_FLAGG = 0 ? I think one quote to much. regards Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Gesendet: Dienstag, 9. April 2002 18:48 An: Multiple recipients of list ORACLE-L Betreff: Ang: RE: Pl/sql question - if statement Yes but then it fails onthe word borttags_flagg, thi serrormessage : PLS-00103: Encountered the symbol "BORTTAGS_FLAGG" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like I reallydont see what the error is: Roland "John Hallas" <[EMAIL PROTECTED]>@fatcity.com den 2002-04-09 07:58 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Don't you need to start of with a quote before the first A If 'A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' John -Original Message- [EMAIL PROTECTED] Sent: 09 April 2002 15:43 To: Multiple recipients of list ORACLE-L anyone whom can tell me why this statement fails in a pl/sqll code: I get this error message PLS-00103: Encountered the symbol "||AvdNr||" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like betwe when i run this statement If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' then lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.PANTBELOPP ' || --PBK.LPKORGEANREL.EANREL,PBK.LPK ORGEANREL.VARUTYP ' || 'FROM A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' || --PBK.LPKORGEANREL ' || 'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' ' || 'AND ICA_ARTIKEL.DATUMhttp://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Ang: RE: Pl/sql question - if statement
Yes but then it fails onthe word borttags_flagg, thi serrormessage : PLS-00103: Encountered the symbol "BORTTAGS_FLAGG" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like I reallydont see what the error is: Roland "John Hallas" <[EMAIL PROTECTED]>@fatcity.com den 2002-04-09 07:58 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Don't you need to start of with a quote before the first A If 'A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' John -Original Message- [EMAIL PROTECTED] Sent: 09 April 2002 15:43 To: Multiple recipients of list ORACLE-L anyone whom can tell me why this statement fails in a pl/sqll code: I get this error message PLS-00103: Encountered the symbol "||AvdNr||" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like betwe when i run this statement If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' then lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.PANTBELOPP ' || --PBK.LPKORGEANREL.EANREL,PBK.LPK ORGEANREL.VARUTYP ' || 'FROM A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' || --PBK.LPKORGEANREL ' || 'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' ' || 'AND ICA_ARTIKEL.DATUMhttp://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Pl/sql question - if statement
Check your quotes. Better yet, create a variable as the string then check the variable. If I understand your code, the first line would look like this: If 'A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG' = 0 THEN I suspect you're going to have problems with the rest of your quoting as well. David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Roland.Skoldbl [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Pl/sql question - if statement om 04/09/2002 09:43 AM Please respond to ORACLE-L anyone whom can tell me why this statement fails in a pl/sqll code: I get this error message PLS-00103: Encountered the symbol "||AvdNr||" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like betwe when i run this statement If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' then lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.PANTBELOPP ' || --PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' || 'FROM A'||AvdNr||'.ICA_ARTIKEL@' ||LookUpServerName|| ' ' || --PBK.LPKORGEANREL ' || 'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' ' || 'AND ICA_ARTIKEL.DATUMhttp://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Pl/sql question - if statement
You have messed up quotes in IF condition Maybe You can use some coding style other than chaotic? It helps in debugging, believe me. Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ Roland.Skoldblom@ ica.se To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Pl/sql question - if statement 2002.04.09 17:43 Please respond to ORACLE-L anyone whom can tell me why this statement fails in a pl/sqll code: I get this error message PLS-00103: Encountered the symbol "||AvdNr||" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like betwe when i run this statement If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' then lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.PANTBELOPP ' || --PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' || 'FROM A'||AvdNr||'.ICA_ARTIKEL@' ||LookUpServerName|| ' ' || --PBK.LPKORGEANREL ' || 'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' ' || 'AND ICA_ARTIKEL.DATUMhttp://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL Question
create a function getSoftwares(p_licence_id ) which returns varchar2 string of softwares and then simply run query on licence table select licence_id , getSoftware(licenceid) from licence ; I hope you know what to write in getSoftwares . -ak - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, March 19, 2002 4:28 AM > > Hi all, > > i have 2 tables software and licence. 1 licence can have many softwares. > > softwares > > name platform Licence_id > > abc NT1 > def WIN2K1 > ghi all 2 > > i want to write a query that displays the results as follows > > licence_idsoftwares > --- --- > 1abc (NT), def(WIN2K) > 2 all > > Any suggestions on how i can do this? > > cheers! > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Big Planet INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question
Write a PL/SQL function which takes the licence_id as argument and returns a varchar2(... what you deem sufficient, up to 32K). In the function, loop on the appropriate table and concatenate. When you run select licence_id, my_ugly_func(licence_id) softwares from ... you more or less get what you want. Performance will even be decent if you have indexed by licence_id. >- Original Message - >From: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Tue, 19 Mar 2002 04:28:19 > > >Hi all, > >i have 2 tables software and licence. 1 licence can >have many softwares. > >softwares > >name platform Licence_id > >abc NT1 >def WIN2K1 >ghi all 2 > >i want to write a query that displays the results >as follows > >licence_idsoftwares >--- --- >1abc (NT), def(WIN2K) >2 all > >Any suggestions on how i can do this? > >cheers! > >-- >Please see the official ORACLE-L FAQ: >http://www.orafaq.com >-- >Author: > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 >FAX: (858) 538-5051 >San Diego, California-- Public Internet >access / Mailing Lists >To REMOVE yourself from this mailing list, send an >E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of >'ListGuru') and in >the message BODY, include a line containing: UNSUB >ORACLE-L >(or the name of mailing list you want to be removed >from). You may >also send the HELP command for other information >(like subscribing). >--- >- Stephane Faroult Oriole Corporation Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question
You could use a user function. For example, create or replace function lic_format (id in number) return varchar2 as tmp varchar2(4000); hold_tmp varchar2(50); cursor c1 is select name from software where license_id = id; begin open c1; loop fetch c1 into hold_tmp; exit when c1%notfound; tmp := tmp ||hold_tmp||','; end loop; close c1; return tmp; end; / Then, your select would be: select license_id, lic_format(license_id) from license; The output would be: LICENSE_ID FORMAT -- - 1 abc,def, 2 ghi, Granted, this doesn't do the platform in parentheses, but it could if you beef up the function, and you may want to make the function smarter so that it didn't print out that last comma, but at least this gives you an idea. - Jeff Young -Original Message- Sent: Tuesday, March 19, 2002 7:28 AM To: Multiple recipients of list ORACLE-L Hi all, i have 2 tables software and licence. 1 licence can have many softwares. softwares name platform Licence_id abc NT1 def WIN2K1 ghi all 2 i want to write a query that displays the results as follows licence_idsoftwares --- --- 1abc (NT), def(WIN2K) 2 all Any suggestions on how i can do this? cheers! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Young, Jeff A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Pl/sql question
Roland, With this sort of questions, you are forcing listers to create a rule (in e-mail utility), which will forward your messages directly into trash bin. Try to restrain yourself. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 10:10 AM Man, do you not have manuals? Have you even attempted to look it up. This is not a teaching class. Go try something and if you are having issues come back and ask about that issue. But we are not here to write your code for you. There are folks you can hire for that. -Original Message- [EMAIL PROTECTED] Sent: Monday, January 28, 2002 4:05 AM To: Multiple recipients of list ORACLE-L Oki thanks for info can you please show me an example with autonoumus transactions? Please. Thanks in advance Roland [EMAIL PROTECTED]@fatcity.com den 2002-01-28 03:30 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Just a note Inserting in an error table in the excpetion clause is a common problem because if something goes wrong and exception was thrown then You usually don't want commit But You cannot insert ereror record without commit So in ancient times there was dbms_pipe etc. Since 8. You can use autonomous transactions instead, I think it is more elegant solution Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ "Thomas, Kevin" anais.com> cc: Sent by:Subject: RE: Pl/sql question [EMAIL PROTECTED] 2002.01.28 11:20 Please respond to ORACLE-L Hi Roland, Best way to do this is just set some variables at the start of your code: l_proc_start := sysdate; l_proc_name := 'proc_name'; begin ...commands... exception when then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM ; INSERT INTO error_table ( proc_name ,proc_start ,proc_end ,proc_count ,proc_err ,proc_err_msg ) VALUES ( l_proc_name ,l_proc_start ,l_proc_end ,l_ins_count ,sqlcode ,sqlerrm ); COMMIT; END; -Original Message- Sent: 28 January 2002 08:40 To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
Ang: Re: RE: Pl/sql question
Ok sorry i found out this answer myself:-) "Igor Neyman" <[EMAIL PROTECTED]>@fatcity.com den 2002-01-28 08:25 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Roland, With this sort of questions, you are forcing listers to create a rule (in e-mail utility), which will forward your messages directly into trash bin. Try to restrain yourself. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 10:10 AM Man, do you not have manuals? Have you even attempted to look it up. This is not a teaching class. Go try something and if you are having issues come back and ask about that issue. But we are not here to write your code for you. There are folks you can hire for that. -Original Message- [EMAIL PROTECTED] Sent: Monday, January 28, 2002 4:05 AM To: Multiple recipients of list ORACLE-L Oki thanks for info can you please show me an example with autonoumus transactions? Please. Thanks in advance Roland [EMAIL PROTECTED]@fatcity.com den 2002-01-28 03:30 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Just a note Inserting in an error table in the excpetion clause is a common problem because if something goes wrong and exception was thrown then You usually don't want commit But You cannot insert ereror record without commit So in ancient times there was dbms_pipe etc. Since 8. You can use autonomous transactions instead, I think it is more elegant solution Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ "Thomas, Kevin" anais.com> cc: Sent by:Subject: RE: Pl/sql question [EMAIL PROTECTED] 2002.01.28 11:20 Please respond to ORACLE-L Hi Roland, Best way to do this is just set some variables at the start of your code: l_proc_start := sysdate; l_proc_name := 'proc_name'; begin ...commands... exception when then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM ; INSERT INTO error_table ( proc_name ,proc_start ,proc_end ,proc_count ,proc_err ,proc_err_msg ) VALUES ( l_proc_name ,l_proc_start ,l_proc_end ,l_ins_count ,sqlcode ,sqlerrm ); COMMIT; END; -Original Message- Sent: 28 January 2002 08:40 To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051
RE: RE: Pl/sql question
Man, do you not have manuals? Have you even attempted to look it up. This is not a teaching class. Go try something and if you are having issues come back and ask about that issue. But we are not here to write your code for you. There are folks you can hire for that. -Original Message- [EMAIL PROTECTED] Sent: Monday, January 28, 2002 4:05 AM To: Multiple recipients of list ORACLE-L Oki thanks for info can you please show me an example with autonoumus transactions? Please. Thanks in advance Roland [EMAIL PROTECTED]@fatcity.com den 2002-01-28 03:30 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Just a note Inserting in an error table in the excpetion clause is a common problem because if something goes wrong and exception was thrown then You usually don't want commit But You cannot insert ereror record without commit So in ancient times there was dbms_pipe etc. Since 8. You can use autonomous transactions instead, I think it is more elegant solution Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ "Thomas, Kevin" anais.com> cc: Sent by: Subject: RE: Pl/sql question [EMAIL PROTECTED] 2002.01.28 11:20 Please respond to ORACLE-L Hi Roland, Best way to do this is just set some variables at the start of your code: l_proc_start := sysdate; l_proc_name := 'proc_name'; begin ...commands... exception when then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM ; INSERT INTO error_table ( proc_name ,proc_start ,proc_end ,proc_count ,proc_err ,proc_err_msg ) VALUES ( l_proc_name ,l_proc_start ,l_proc_end ,l_ins_count ,sqlcode ,sqlerrm ); COMMIT; END; -Original Message- Sent: 28 January 2002 08:40 To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Di
RE: RE: Pl/sql question
Here is a link to a fine article about autonomous transactions by Tom Kyte : http://osi.oracle.com/~tkyte/autonomous/ - Kirti -Original Message- Sent: Monday, January 28, 2002 6:05 AM To: Multiple recipients of list ORACLE-L Oki thanks for info can you please show me an example with autonoumus transactions? Please. Thanks in advance Roland [EMAIL PROTECTED]@fatcity.com den 2002-01-28 03:30 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Just a note Inserting in an error table in the excpetion clause is a common problem because if something goes wrong and exception was thrown then You usually don't want commit But You cannot insert ereror record without commit So in ancient times there was dbms_pipe etc. Since 8. You can use autonomous transactions instead, I think it is more elegant solution Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ "Thomas, Kevin" anais.com> cc: Sent by: Subject: RE: Pl/sql question [EMAIL PROTECTED] 2002.01.28 11:20 Please respond to ORACLE-L Hi Roland, Best way to do this is just set some variables at the start of your code: l_proc_start := sysdate; l_proc_name := 'proc_name'; begin ...commands... exception when then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM ; INSERT INTO error_table ( proc_name ,proc_start ,proc_end ,proc_count ,proc_err ,proc_err_msg ) VALUES ( l_proc_name ,l_proc_start ,l_proc_end ,l_ins_count ,sqlcode ,sqlerrm ); COMMIT; END; -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Pl/sql question
- Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 14:05 > > Oki thanks for info can you please show me an example with autonoumus transactions? Please. perhaps u could just go to http://technet.oracle.com and do some research all by yourself? Marin "...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. " -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Pl/sql question
Yea more elegant if you happen to be running Oracle 8. which unfortunately we're not!! ;o) -Original Message- Sent: 28 January 2002 11:30 To: Multiple recipients of list ORACLE-L Just a note Inserting in an error table in the excpetion clause is a common problem because if something goes wrong and exception was thrown then You usually don't want commit But You cannot insert ereror record without commit So in ancient times there was dbms_pipe etc. Since 8. You can use autonomous transactions instead, I think it is more elegant solution Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ "Thomas, Kevin" anais.com> cc: Sent by: Subject: RE: Pl/sql question [EMAIL PROTECTED] 2002.01.28 11:20 Please respond to ORACLE-L Hi Roland, Best way to do this is just set some variables at the start of your code: l_proc_start := sysdate; l_proc_name := 'proc_name'; begin ...commands... exception when then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM ; INSERT INTO error_table ( proc_name ,proc_start ,proc_end ,proc_count ,proc_err ,proc_err_msg ) VALUES ( l_proc_name ,l_proc_start ,l_proc_end ,l_ins_count ,sqlcode ,sqlerrm ); COMMIT; END; -Original Message- Sent: 28 January 2002 08:40 To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Ang: RE: Pl/sql question
Oki thanks for info can you please show me an example with autonoumus transactions? Please. Thanks in advance Roland [EMAIL PROTECTED]@fatcity.com den 2002-01-28 03:30 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Just a note Inserting in an error table in the excpetion clause is a common problem because if something goes wrong and exception was thrown then You usually don't want commit But You cannot insert ereror record without commit So in ancient times there was dbms_pipe etc. Since 8. You can use autonomous transactions instead, I think it is more elegant solution Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ "Thomas, Kevin" anais.com> cc: Sent by: Subject: RE: Pl/sql question [EMAIL PROTECTED] 2002.01.28 11:20 Please respond to ORACLE-L Hi Roland, Best way to do this is just set some variables at the start of your code: l_proc_start := sysdate; l_proc_name := 'proc_name'; begin ...commands... exception when then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM ; INSERT INTO error_table ( proc_name ,proc_start ,proc_end ,proc_count ,proc_err ,proc_err_msg ) VALUES ( l_proc_name ,l_proc_start ,l_proc_end ,l_ins_count ,sqlcode ,sqlerrm ); COMMIT; END; -Original Message- Sent: 28 January 2002 08:40 To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Pl/sql question
Just a note Inserting in an error table in the excpetion clause is a common problem because if something goes wrong and exception was thrown then You usually don't want commit But You cannot insert ereror record without commit So in ancient times there was dbms_pipe etc. Since 8. You can use autonomous transactions instead, I think it is more elegant solution Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ "Thomas, Kevin" anais.com> cc: Sent by: Subject: RE: Pl/sql question [EMAIL PROTECTED] 2002.01.28 11:20 Please respond to ORACLE-L Hi Roland, Best way to do this is just set some variables at the start of your code: l_proc_start := sysdate; l_proc_name := 'proc_name'; begin ...commands... exception when then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM ; INSERT INTO error_table ( proc_name ,proc_start ,proc_end ,proc_count ,proc_err ,proc_err_msg ) VALUES ( l_proc_name ,l_proc_start ,l_proc_end ,l_ins_count ,sqlcode ,sqlerrm ); COMMIT; END; -Original Message- Sent: 28 January 2002 08:40 To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED
Re: Pl/sql question
Hi, Not much experience with pl/sql but.. [EMAIL PROTECTED]@fatcity.com on 28-01-2002 09:40:20 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Hallo, anyone who canhelp me with this? - pick out the name of the procedure thatis currently running, Put the name of the procedure hardcoded into a variable at the start of the procedure which you can than use in the exception handler -pick out the start_time of the procedure See above. but use sysdate - pick outthe end_time of the procedure when it fails select sysdate into a variable in the exception should do the trick - pick out the number of rows that were inserted inthe insertstatement inthe procedure. if you loop through the inserts with a commit at the end. add a counter. - The error code - The errormessage SQLERRM (oracle supplied variable/function/something.. Rtm) You can add all these values into a table or as input variables for another procedure to send e-mail or whatever I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst & Young. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst & Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst & Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the messa
RE: Pl/sql question
Hi Roland, Best way to do this is just set some variables at the start of your code: l_proc_start := sysdate; l_proc_name := 'proc_name'; begin ...commands... exception when then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM ; INSERT INTO error_table ( proc_name ,proc_start ,proc_end ,proc_count ,proc_err ,proc_err_msg ) VALUES ( l_proc_name ,l_proc_start ,l_proc_end ,l_ins_count ,sqlcode ,sqlerrm ); COMMIT; END; -Original Message- Sent: 28 January 2002 08:40 To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Pl/sql question
>- pick out the name of the procedure thatis currently running, check http://osi.oracle.com/~tkyte/who_called_me/index.html, dbms_utility.get_call_stack, dbms_utility.get_error_stack >-pick out the start_time of the procedure discussed some days ago >- pick outthe end_time of the procedure when it fails just sysdate >- pick out the number of rows that were inserted inthe insertstatement inthe procedure. already discuseed >- The error code sqlcode, dbms_utility.get_error_stack >_Th errormessage sqlerrm dbms_utility.get_error_stack >All these things I want to be inserted in a table. Give me a good example on how to write the code, please. You may use simple procedure that picks some arguments like start_time, end_time, proc_name, inserted/not inserted rows, error info and inserts it in a table Just use autonomous transactions. More - read docs, it is well documented in Oracle guides. Starting points - technet.oracle.com, docs.oracle.com, metalink.oracle.com (only to members :((( ) Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL question
To make such things you need dynamic SQL either execute immediate (8.1.something and above) or dbms_sql (more clumsy) Here is example using execute immediate qaqa is table of one column col1, max (col1) = 17 qaqa_seq is sequence gints@> create table qaqa (col1 number); Table created. gints@> insert into qaqa values (1); 1 row created. gints@> insert into qaqa values (17); 1 row created. gints@> create sequence qaqa_seq; Sequence created. gints@> create table matching_table (table_owner varchar2(40), table_name varchar2(40), table_column varchar2(40) 2 , sequence_owner varchar2(40), sequence_name varchar2(40)); Table created. gints@> insert into matching_table values ('GINTS', 'QAQA', 'COL1', 'GINTS', 'QAQA_SEQ'); 1 row created. gints@> commit; DECLARE v_table_owner varchar2(40); v_table_name varchar2(40); v_table_column varchar2(40); v_select_statement VARCHAR2(1000); v_seq_statement VARCHAR2(1000); v_max_result number; v_seq_result number; BEGIN FOR i IN (SELECT sequence_name, sequence_owner FROM dba_sequences WHERE sequence_name = 'QAQA_SEQ' AND sequence_owner = 'GINTS') LOOP BEGIN SELECT table_owner, table_name, table_column INTO v_table_owner, v_table_name, v_table_column FROM matching_table a WHERE i.sequence_owner = a.sequence_owner AND i.sequence_name = a.sequence_name ; EXCEPTION WHEN OTHERS THEN NULL; END; v_select_statement := 'SELECT max(' || v_table_column || ') ' || 'FROM ' || v_table_owner || '.' || v_table_name; EXECUTE IMMEDIATE v_select_statement INTO v_max_result; dbms_output.put_line('SELECT statement is: ' || v_select_statement); dbms_output.put_line('MAX number of ' || v_table_owner || '.' || v_table_name || '.' || v_table_column || ' is ' || v_max_result); v_seq_statement := 'SELECT ' || i.sequence_owner || '.' || i.sequence_name || '.nextval FROM dual'; EXECUTE IMMEDIATE v_seq_statement INTO v_seq_result; dbms_output.put_line('Select sequence nextval stetement is: ' || v_seq_statement); dbms_output.put_line('Next sequence value is: ' || v_seq_result); END LOOP; END; / output result is following SELECT statement is: SELECT max(COL1) FROM GINTS.QAQA MAX number of GINTS.QAQA.COL1 is 17 Select sequence nextval stetement is: SELECT GINTS.QAQA_SEQ.nextval FROM dual Next sequence value is: 1 How to increment sequence appropriate times I'll leave to you as an excersise ;)) Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ "Hagedorn, Linda" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: PL/SQL question Sent by: [EMAIL PROTECTED] om 2001.11.15 23:30 Please respond to ORACLE-L Can anyone can tell me how to use PL/SQL declared variables in a select statement where a . has to be between the owner and table name, and the owner and table name are variables, I'd be most appreciative. The answer is probably obvious... Feel free to point it out. On import, we occasionally have sequences that are out of sync with the data. To remedy this, I'm creating a master table that will match owner/table/column to owner/sequence, and a PL/SQL procedure that will increment sequences which are found to be lower than the max value in the associated owner/table/column. Matching_Table: Table_owner Table_name Table_c
RE: PL/SQL question
Title: PL/SQL question Thanks very much! My use of EXECUTE IMMEDIATE is passing the parser. Linda -Original Message-From: Djordje Jankovic [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 15, 2001 2:03 PMTo: Multiple recipients of list ORACLE-LSubject: RE: PL/SQL question Hi Linda, You cannot put a variable instead of an object name (where by object here I mean owner, table_name, column_name). You have few options: - generate a sql hat you would run, e.g. do select 'select max('||v_column_name||') from ' || v_owner||'.'||v_table_name||';' - use dbm_sql package, i.e. create the statement in a similar way as above and run it; - use native dynamic sql (see for example http://www.oracle.com/oramag/oracle/00-nov/index.html?o60sql.html). Djordje -Original Message-From: Hagedorn, Linda [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 15, 2001 4:30 PMTo: Multiple recipients of list ORACLE-LSubject: PL/SQL question Can anyone can tell me how to use PL/SQL declared variables in a select statement where a . has to be between the owner and table name, and the owner and table name are variables, I'd be most appreciative. The answer is probably obvious... Feel free to point it out. On import, we occasionally have sequences that are out of sync with the data. To remedy this, I'm creating a master table that will match owner/table/column to owner/sequence, and a PL/SQL procedure that will increment sequences which are found to be lower than the max value in the associated owner/table/column. Matching_Table: Table_owner Table_name Table_column Sequence_owner Sequence_name Create_dt Last_mod_dt Pseudo code: Declarations variables, output report file, counters. Read dba_sequences in cursor Select table_owner, table_name, table_column into v_table_owner, v_table_name, v_table_column from matching_table a where sequence_owner = a.sequence_owner and sequence_name = a.sequence_name ; (sequence_owner is from loop, reading dba_sequences) if row is found then Select max(v_table_column) from v_table_owner.v_table_name ; For the life of me I can't get this syntax right. The parser is complaining because v_table_owner.v_table_name isn't declared. I've tried || (concatenation), commas, single quotes, double quotes, colon, etc. If you can see the error, I'd be very happy for a reply. Thanks, Linda echo '[q]sa[ln0=aln256%Pln256/snlbx]sb3135071790101768542287578439snlbxq'|dc
RE: PL/SQL question
Title: PL/SQL question Hi Linda, You cannot put a variable instead of an object name (where by object here I mean owner, table_name, column_name). You have few options: - generate a sql hat you would run, e.g. do select 'select max('||v_column_name||') from ' || v_owner||'.'||v_table_name||';' - use dbm_sql package, i.e. create the statement in a similar way as above and run it; - use native dynamic sql (see for example http://www.oracle.com/oramag/oracle/00-nov/index.html?o60sql.html). Djordje -Original Message-From: Hagedorn, Linda [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 15, 2001 4:30 PMTo: Multiple recipients of list ORACLE-LSubject: PL/SQL question Can anyone can tell me how to use PL/SQL declared variables in a select statement where a . has to be between the owner and table name, and the owner and table name are variables, I'd be most appreciative. The answer is probably obvious... Feel free to point it out. On import, we occasionally have sequences that are out of sync with the data. To remedy this, I'm creating a master table that will match owner/table/column to owner/sequence, and a PL/SQL procedure that will increment sequences which are found to be lower than the max value in the associated owner/table/column. Matching_Table: Table_owner Table_name Table_column Sequence_owner Sequence_name Create_dt Last_mod_dt Pseudo code: Declarations variables, output report file, counters. Read dba_sequences in cursor Select table_owner, table_name, table_column into v_table_owner, v_table_name, v_table_column from matching_table a where sequence_owner = a.sequence_owner and sequence_name = a.sequence_name ; (sequence_owner is from loop, reading dba_sequences) if row is found then Select max(v_table_column) from v_table_owner.v_table_name ; For the life of me I can't get this syntax right. The parser is complaining because v_table_owner.v_table_name isn't declared. I've tried || (concatenation), commas, single quotes, double quotes, colon, etc. If you can see the error, I'd be very happy for a reply. Thanks, Linda echo '[q]sa[ln0=aln256%Pln256/snlbx]sb3135071790101768542287578439snlbxq'|dc
RE: pl/sql question
Or use a cursor variable. Jared Christopher Spence To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: pl/sql question Sent by: [EMAIL PROTECTED] om 10/02/01 10:30 AM Please respond to ORACLE-L Use DBMS_SQL or EXECUTE IMMEDIATE "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Tuesday, October 02, 2001 12:50 PM To: Multiple recipients of list ORACLE-L Can I somehow use a variable for the table name in a cursor select? Here is the example: --Declaration Section sSrcTableName VARCHAR2(50) := iFeedNm||'_1_1_' ||TO_CHAR(SYSDATE,'YYMMDD')||'_SRC'; --cursor for tmo daily source records CURSOR cTMODaily IS SELECT* FROM sSrcTableName; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql question
Yep - here's an example. There is really a whole lot more that goes with this, but I've included the pertinent portions so you can get an idea. Hope this helps. SET DEFINE OFF; CREATE OR REPLACE PROCEDURE Student_Course_Report( fromSchool varchar2, toSchoolvarchar2, fromCourse varchar2, toCoursevarchar2) AS schoolWhereClause varchar2(250); courseWhereClause varchar2(250); TYPE RefCurType IS REF CURSOR; schoolCur RefCurType; v_schoolnum varchar2(3); v_schoolnamevarchar2(35); ACRSVar varchar2(13); BEGIN IF fromSchool = 'All Schools' THEN schoolWhereClause:= ' Where schoolnum not in ( ' || || '800' || || ' , ' || || 'D01' || || ') Order by schoolnum'; END IF; OPEN schoolCur for 'Select schoolnum, name From sasi.asch ' || schoolWhereClause; LOOP Fetch schoolCur into v_schoolnum, v_schoolname; EXIT WHEN schoolCur%NOTFOUND; ACRSVar:= 'sasi.ACRS1'||v_schoolnum; OPEN courseCur for 'Select statecrs1, title, course From ' || ACRSVar || courseWhereClause; LOOP Fetch coursecur into v_statecrs1, v_title, v_course; EXIT WHEN courseCur%NOTFOUND; David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Eric.Chesebro@ chase.comTo: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: pl/sql question om 10/02/2001 11:50 AM Please respond to ORACLE-L Can I somehow use a variable for the table name in a cursor select? Here is the example: --Declaration Section sSrcTableName VARCHAR2(50) := iFeedNm||'_1_1_' ||TO_CHAR(SYSDATE,'YYMMDD')||'_SRC'; --cursor for tmo daily source records CURSOR cTMODaily IS SELECT* FROM sSrcTableName; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: pl/sql question
Use DBMS_SQL or EXECUTE IMMEDIATE "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Tuesday, October 02, 2001 12:50 PM To: Multiple recipients of list ORACLE-L Can I somehow use a variable for the table name in a cursor select? Here is the example: --Declaration Section sSrcTableName VARCHAR2(50) := iFeedNm||'_1_1_' ||TO_CHAR(SYSDATE,'YYMMDD')||'_SRC'; --cursor for tmo daily source records CURSOR cTMODaily IS SELECT* FROM sSrcTableName; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question after migrating from 7.3.4 to 8.1.7
That worked, Thanks a lot for all the replies. I was just wondering how it worked on version 7.3.4 Thanks deen -Original Message- [EMAIL PROTECTED] Sent: Friday, July 27, 2001 3:37 PM To: Multiple recipients of list ORACLE-L Use %ROWTYPE. TYPE tb_uc9_corres_type IS TABLE OF uc9_correspondence%ROWTYPE INDEX BY BINARY_INTEGER; hth, prasad "Deen Dayal" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L ate.nj.us><[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED]Subject: PL/SQL Question after migrating from om7.3.4 to 8.1.7 07/27/2001 01:45 PM Please respond to ORACLE-L Hi, I just migrated from 734 to 817, I used migration utility. Migration went fine. Some of my package specification have become invalid. When I tried to recompile, I got the following error 30/41PLS-00206: %TYPE must be applied to a variable, column, field or attribute, not to "UC9_CLAIM_AMOUNT" The source is listed below. uc9_correspondence is table in my schema. I do not know why this is throwing up an error where as 734 was happy with it. TYPE tb_uc9_corres_type IS TABLE OF uc9_correspondence%TYPE INDEX BY BINARY_INTEGER; Any help is appreciated Thanks in ADvance deen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deen Dayal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deen Dayal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL Question after migrating from 7.3.4 to 8.1.7
Use %ROWTYPE. TYPE tb_uc9_corres_type IS TABLE OF uc9_correspondence%ROWTYPE INDEX BY BINARY_INTEGER; hth, prasad "Deen Dayal" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L ate.nj.us><[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED]Subject: PL/SQL Question after migrating from om7.3.4 to 8.1.7 07/27/2001 01:45 PM Please respond to ORACLE-L Hi, I just migrated from 734 to 817, I used migration utility. Migration went fine. Some of my package specification have become invalid. When I tried to recompile, I got the following error 30/41PLS-00206: %TYPE must be applied to a variable, column, field or attribute, not to "UC9_CLAIM_AMOUNT" The source is listed below. uc9_correspondence is table in my schema. I do not know why this is throwing up an error where as 734 was happy with it. TYPE tb_uc9_corres_type IS TABLE OF uc9_correspondence%TYPE INDEX BY BINARY_INTEGER; Any help is appreciated Thanks in ADvance deen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deen Dayal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL Question after migrating from 7.3.4 to 8.1.7
Deen, If you want a table with the structure of a row in your uc9_correspondence, use THE '%ROWTYPE'as follows: TYPE tb_uc9_corres_type IS TABLE OF uc9_correspondence%ROWTYPE INDEX BY BINARY_INTEGER; Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question after migrating from 7.3.4 to 8.1.7
Deen, shouldn't the statement be: TYPE tb_uc9_corres_type IS TABLE OF uc9_correspondence.COLUMN_NAME%TYPE INDEX BY BINARY_INTEGER; ?? The %TYPE in your PL/SQL is being applied to the table which, I think, is not valid. A PL/SQL table is a one-column data type, indexed via the BINARY_INTEGER index. You could easily fix this by finding an assignment statement elsewhere in your code to see what you are trying to store. hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, July 27, 2001 1:45 PM To: Multiple recipients of list ORACLE-L Hi, I just migrated from 734 to 817, I used migration utility. Migration went fine. Some of my package specification have become invalid. When I tried to recompile, I got the following error 30/41PLS-00206: %TYPE must be applied to a variable, column, field or attribute, not to "UC9_CLAIM_AMOUNT" The source is listed below. uc9_correspondence is table in my schema. I do not know why this is throwing up an error where as 734 was happy with it. TYPE tb_uc9_corres_type IS TABLE OF uc9_correspondence%TYPE INDEX BY BINARY_INTEGER; Any help is appreciated Thanks in ADvance deen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deen Dayal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Pl/SQL question
Thanks for your help! This solved the problem. Sonja -Original Message- Sent: Monday, June 25, 2001 3:27 PM To: Multiple recipients of list ORACLE-L Sonja, Instance is easy enough. Either grant the person SELECT access to the V_$INSTANCE view under the SYS account, or create another view owned by the DBA that returns the same information. By schema, do you mean the schema where the procedure exists, or of the person executing the procedure? If it's the person executing the procedure, you can easily return the USER value (select user from dual). If it's the schema where the procedure exists, then you can (select distinct owner from all_source where name='procedure_name'). Of course, you will need to grant SELECT on ALL_SOURCE to the user where the procedure exists from the sys account for this to work. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, June 25, 2001 8:45 AM To: Multiple recipients of list ORACLE-L List hi! Oracle 8.1.7 EE on AIX. One of our developers wrote a procedure. Inside that procedure he wants to know instance and schema, while executing that procedure. Do you have any suggestions? TIA, Sonja -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-2?Q?Sonja_=A9ehovi=E6?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-2?Q?Sonja_=A9ehovi=E6?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Pl/SQL question
Sonja, Instance is easy enough. Either grant the person SELECT access to the V_$INSTANCE view under the SYS account, or create another view owned by the DBA that returns the same information. By schema, do you mean the schema where the procedure exists, or of the person executing the procedure? If it's the person executing the procedure, you can easily return the USER value (select user from dual). If it's the schema where the procedure exists, then you can (select distinct owner from all_source where name='procedure_name'). Of course, you will need to grant SELECT on ALL_SOURCE to the user where the procedure exists from the sys account for this to work. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, June 25, 2001 8:45 AM To: Multiple recipients of list ORACLE-L List hi! Oracle 8.1.7 EE on AIX. One of our developers wrote a procedure. Inside that procedure he wants to know instance and schema, while executing that procedure. Do you have any suggestions? TIA, Sonja -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-2?Q?Sonja_=A9ehovi=E6?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Pl/SQL question
Sonja, The schema will be determined by the owner of the procedure, which he should know or you could tell him and won't change. For the instance name you could grant him select on v$database or create a view of v$database.name. I'm not sure about OPS tho. HTH, Liam >From: Sonja ©ehoviæ <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Pl/SQL question >Date: Mon, 25 Jun 2001 04:45:28 -0800 > >List hi! >Oracle 8.1.7 EE on AIX. >One of our developers wrote a procedure. Inside that procedure he wants to >know instance and schema, while executing that procedure. >Do you have any suggestions? >TIA, > Sonja >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: =?iso-8859-2?Q?Sonja_=A9ehovi=E6?= > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Liam Morrin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL Question
dbms_lock.sleep will do this... Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA i2 technologies www.i2.com [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/11/01 10:20 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: PL/SQL Question I need to whip out a PL/SQL procedure real quick today and have a quick question for fellow-listers ( since today is Friday, hopefully I don't get RTFMed on this one :) ) The purpose of my procedure is to collect stats from v$session_wait periodically ( every second for example) and pump the data into a stats table. But how to make the procedure to wait for a specified time? I know of the option of using dbms_jobs to handle this. But I am wondering if there is a similar function in PL/SQL similar as the Unix 'sleep' command. TIA Dennis Meng Database Administrator Focal Communications 847-954-8328 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL Question
Try DBMS_LOCK.SLEEP ( seconds IN NUMBER); Regards Karthik Ramachandran >>> [EMAIL PROTECTED] 05/11/01 11:20AM >>> I need to whip out a PL/SQL procedure real quick today and have a quick question for fellow-listers ( since today is Friday, hopefully I don't get RTFMed on this one :) ) The purpose of my procedure is to collect stats from v$session_wait periodically ( every second for example) and pump the data into a stats table. But how to make the procedure to wait for a specified time? I know of the option of using dbms_jobs to handle this. But I am wondering if there is a similar function in PL/SQL similar as the Unix 'sleep' command. TIA Dennis Meng Database Administrator Focal Communications 847-954-8328 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karthik Ramachandran INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL Question
dbms_lock.sleep(second in number); >>> [EMAIL PROTECTED] 05/11/01 11:20AM >>> I need to whip out a PL/SQL procedure real quick today and have a quick question for fellow-listers ( since today is Friday, hopefully I don't get RTFMed on this one :) ) The purpose of my procedure is to collect stats from v$session_wait periodically ( every second for example) and pump the data into a stats table. But how to make the procedure to wait for a specified time? I know of the option of using dbms_jobs to handle this. But I am wondering if there is a similar function in PL/SQL similar as the Unix 'sleep' command. TIA Dennis Meng Database Administrator Focal Communications 847-954-8328 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL Question
[EMAIL PROTECTED] wrote: > I am wondering if there is a similar > function in PL/SQL similar as the Unix 'sleep' command. Use dbms_lock.sleep -- it's pretty much like Unix sleep, but requires you to grant execute on dbms_lock to whichever account needs it. Bill -- __ http://www.datacraft.com/http://plnet.org/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pribyl INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL Question
Dan, While this does not work as is, but probably could be in some fashion, you win the days raspberry for the most obfuscated answer. :) Jared On Friday 11 May 2001 09:56, Dasko, Dan wrote: > while trunc(sysdate, ss) = 30 loop > > I think this should do whatever's in the loop every minute on the 30 second > point. > > -Original Message- > Sent: Friday, May 11, 2001 11:20 AM > To: Multiple recipients of list ORACLE-L > > > I need to whip out a PL/SQL procedure real quick today and have a quick > question for fellow-listers ( since today is Friday, hopefully I don't get > RTFMed on this one :) ) > The purpose of my procedure is to collect stats from v$session_wait > periodically ( every second for example) and pump the data into a stats > table. > But how to make the procedure to wait for a specified time? I know of the > option of using dbms_jobs to handle this. But I am wondering if there is a > similar > function in PL/SQL similar as the Unix 'sleep' command. > > TIA > > Dennis Meng > Database Administrator > Focal Communications > 847-954-8328 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question
while trunc(sysdate, ss) = 30 loop I think this should do whatever's in the loop every minute on the 30 second point. -Original Message- Sent: Friday, May 11, 2001 11:20 AM To: Multiple recipients of list ORACLE-L I need to whip out a PL/SQL procedure real quick today and have a quick question for fellow-listers ( since today is Friday, hopefully I don't get RTFMed on this one :) ) The purpose of my procedure is to collect stats from v$session_wait periodically ( every second for example) and pump the data into a stats table. But how to make the procedure to wait for a specified time? I know of the option of using dbms_jobs to handle this. But I am wondering if there is a similar function in PL/SQL similar as the Unix 'sleep' command. TIA Dennis Meng Database Administrator Focal Communications 847-954-8328 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ This e-mail message has been scanned for the presence of all known computer viruses by the MessageLabs Virus Control Center. However, it is still recommended that you use local virus scanning software to monitor for the presence of viruses. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dasko, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL Question
Dennis, How about dbms_lock.sleep() ? Jared On Friday 11 May 2001 08:20, [EMAIL PROTECTED] wrote: > I need to whip out a PL/SQL procedure real quick today and have a quick > question for fellow-listers ( since today is Friday, hopefully I don't get > RTFMed on this one :) ) > The purpose of my procedure is to collect stats from v$session_wait > periodically ( every second for example) and pump the data into a stats > table. > But how to make the procedure to wait for a specified time? I know of the > option of using dbms_jobs to handle this. But I am wondering if there is a > similar > function in PL/SQL similar as the Unix 'sleep' command. > > TIA > > Dennis Meng > Database Administrator > Focal Communications > 847-954-8328 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question
There is indeed a "sleep", in dbms_lock, taking a single argument "seconds". -Original Message- Sent: Friday, May 11, 2001 11:20 AM To: Multiple recipients of list ORACLE-L I need to whip out a PL/SQL procedure real quick today and have a quick question for fellow-listers ( since today is Friday, hopefully I don't get RTFMed on this one :) ) The purpose of my procedure is to collect stats from v$session_wait periodically ( every second for example) and pump the data into a stats table. But how to make the procedure to wait for a specified time? I know of the option of using dbms_jobs to handle this. But I am wondering if there is a similar function in PL/SQL similar as the Unix 'sleep' command. TIA Dennis Meng Database Administrator Focal Communications 847-954-8328 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question
Use dbms_lock.sleep() HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! * This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL-question
The file has to be either fixed length or delimited. If it is then you would not have any problem. Alternately you could try importing the text file in Excel (MS office product). Convert it to Excel and import from excel to Oracle using Oracle migration assistant. HTH! Aleem -Original Message- Sent: Friday, April 06, 2001 1:45 PM To: Multiple recipients of list ORACLE-L Subject:PL/SQL-question Hi, I have imported a text-file into a table in the database. Now I find that there are many bad things in some fields, for instance there is a ? instead of the value 0. Can anyone give me a good example on a procedure that loops through a table and if found a ? in some fields, replace it with a 0.? Thanks in advance. Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL-question
update table set dodgy_field = replace( dodgy_field, '?', '0' ) / -Original Message- Sent: 06 April 2001 09:45 To: Multiple recipients of list ORACLE-L Hi, I have imported a text-file into a table in the database. Now I find that there are many bad things in some fields, for instance there is a ? instead of the value 0. Can anyone give me a good example on a procedure that loops through a table and if found a ? in some fields, replace it with a 0.? Thanks in advance. Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord David INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).