a quick pl/sql question
Hi: In pl/sql, I want to add chr(10) into a string in every 70th position. The string can be up to 2000 characters long. The follwoing code works. But is there an even FASTER way to do this? Thanks. Guang --- declare pos number := 1; len number; buf varchar2(2000); x varchar2(2100); begin buf := '012345678901234567890123456789012345678901234567890123456789012345678901234 5678901234567890123456789012345678901234567890123456789012345678901234567890 12345678901234567890123456789012345678901234567890123456789A'; len := length(buf); while pos=len loop x := x || substr(buf, pos, 70) || chr(10); pos := pos+70; end loop; end; -- 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).
RE: 8i pl/sql question
Does myFunction1 often get called with the same arguments? In your example, the second argument is never repeated, but in the real thing, would the same second argument be likely to repeat? If myFunction1 gets the same arguments, will it always return the same value? If so, then it is a deterministic function, and you can declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. This way the optimiser will know not to recalculate the function if it is called again with the same arguments, but will reuse the value it calculated before. -Original Message- Sent: Monday, December 15, 2003 7:54 PM To: Multiple recipients of list ORACLE-L can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. 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: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Flack 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: 8i pl/sql question
The first argumant (myID) is a variable that is different every time the function gets called. The second argument is a hard code number (just as in my orginal message). So I guess I could not use DETERMINISTIC here. I have not heard of DETERMINISTIC before but I will take a look of this becuase it probably can be used at a couple of places if it works as you described. Thanks. Guang -Original Message- John Flack Sent: Tuesday, December 16, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Does myFunction1 often get called with the same arguments? In your example, the second argument is never repeated, but in the real thing, would the same second argument be likely to repeat? If myFunction1 gets the same arguments, will it always return the same value? If so, then it is a deterministic function, and you can declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. This way the optimiser will know not to recalculate the function if it is called again with the same arguments, but will reuse the value it calculated before. -Original Message- Sent: Monday, December 15, 2003 7:54 PM To: Multiple recipients of list ORACLE-L can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. 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: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. 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).
RE: 8i pl/sql question
what does myfunction1() do? 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: Tuesday, December 16, 2003 10:24 AM To: Multiple recipients of list ORACLE-L The first argumant (myID) is a variable that is different every time the function gets called. The second argument is a hard code number (just as in my orginal message). So I guess I could not use DETERMINISTIC here. I have not heard of DETERMINISTIC before but I will take a look of this becuase it probably can be used at a couple of places if it works as you described. Thanks. Guang -Original Message- John Flack Sent: Tuesday, December 16, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Does myFunction1 often get called with the same arguments? In your example, the second argument is never repeated, but in the real thing, would the same second argument be likely to repeat? If myFunction1 gets the same arguments, will it always return the same value? If so, then it is a deterministic function, and you can declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. This way the optimiser will know not to recalculate the function if it is called again with the same arguments, but will reuse the value it calculated before. -Original Message- Sent: Monday, December 15, 2003 7:54 PM To: Multiple recipients of list ORACLE-L can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. 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: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. 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). ** 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. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: 8i pl/sql question
Below is the code for myfunction1 in the package, It calls a bunch of other
functions, such as getBlastMatches, escapeGene, genes.gene2protein and
addItem.
Guang
---
type blastMatch is record (
geneName gene.name%type,
percent number
);
function myfunction1 (seqid in number,
sid in number,
secondsid in number default -1) return varchar2 is
tbl blastMatches;
cnt number;
item varchar2(256);
str varchar2(256);
begin
cnt := getBlastMatches(seqid, sid, tbl, secondsid);
if cnt1 then return null; end if;
for i in 1..cnt loop
item := escapeGene(sid, tbl(i).geneName,
genes.gene2protein(tbl(i).geneName, sid)) || ' ('
|| tbl(i).percent || '%)';
if not addItem(str, item, '; ', 80) then exit; end if;
end loop;
return str;
exception
when others then return null;
end myfunction1;
--
function getBlastMatches (seqid in number,
spid in number,
matchTable out blastMatches,
secondspid in number default -1) return number is
cursor bcur is
select queryid, subjid, 100.0*identity/matchlen pct
from blastresults
where ((subjspid in (spid,secondspid) and queryid = seqid) or
(queryspid in (spid,secondspid) and subjid = seqid)) and
(identity/matchlen = .200 or positive/matchlen = .400)
order by blast.pvalToNumber(pval) asc, score desc;
match number;
cnt number := 0;
gname gene.name%type;
begin
for bmatch in bcur loop
if bmatch.queryid=seqid then match := bmatch.subjid;
else match := bmatch.queryid; end if;
BEGIN
select name into gname from gene,seqtable
where geneid=gene.id and aaseqid = match and
gene.use = 'Y' and seqtable.use='Y';
EXCEPTION
when no_data_Found then gname := NULL;
END;
if gname is not null then
cnt := cnt + 1;
matchTable(cnt).geneName := gname;
matchTable(cnt).percent := round(bmatch.pct, 0);
if cnt = maxMatches then return cnt; end if;
end if;
end loop;
return cnt;
exception
when others then return 0;
end getBlastMatches;
-Original Message-
Jamadagni, Rajendra
Sent: Tuesday, December 16, 2003 10:45 AM
To: Multiple recipients of list ORACLE-L
what does myfunction1() do?
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: Tuesday, December 16, 2003 10:24 AM
To: Multiple recipients of list ORACLE-L
The first argumant (myID) is a variable that is different every time the
function gets called. The second argument is a hard code number (just as in
my orginal message). So I guess I could not use DETERMINISTIC here. I have
not heard of DETERMINISTIC before but I will take a look of this becuase it
probably can be used at a couple of places if it works as you described.
Thanks.
Guang
-Original Message-
John Flack
Sent: Tuesday, December 16, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L
Does myFunction1 often get called with the same arguments? In your example,
the second argument is never repeated, but in the real thing, would the same
second argument be likely to repeat? If myFunction1 gets the same
arguments, will it always return the same value? If so, then it is a
deterministic function, and you can declare it as one by putting the keyword
DETERMINISTIC before the IS/AS in its header. This way the optimiser will
know not to recalculate the function if it is called again with the same
arguments, but will reuse the value it calculated before.
-Original Message-
Sent: Monday, December 15, 2003 7:54 PM
To: Multiple recipients of list ORACLE-L
can you return multiple values from a modified version of myfunction(1) ??
If so, then you can replace multiple calls by only one. And no, bulk binds
is only within dml/select statements.
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: Monday, December 15, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L
Hi:
Oracle 8173 DB.
I have a package funtion, part of it is doing something like this:
utl_file.put_line(fpn, myFunction1(myID, 1));
utl_file.put_line(fpn, myFunction1(myID, 8));
utl_file.put_line(fpn, myFunction1(myID, 6));
utl_file.put_line(fpn, myFunction1(myID, 35));
utl_file.put_line(fpn, myFunction1(myID, 33));
utl_file.put_line(fpn, myFunction1(myID, 7));
utl_file.put_line(fpn, myFunction1(myID, 102));
utl_file.put_line(fpn,
8i pl/sql question
Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. 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).
RE: 8i pl/sql question
can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. 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: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. 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). ** 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. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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
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
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 (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
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
-
To REMOVE yourself from this mailing list, send an
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 := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0
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 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this
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 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
pl/sql question and owa_pattern question
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).
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).
interesting dynamic pl/sql question
Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: interesting dynamic pl/sql question
You should be getting errors, because PL/SQL inside execute immediate knows nothing about mystorageArray (or i for that matter) declared in your stored procedure. Probably, you could get by using package variables (and referring to them properly: package_name.var_name, specifically inside your dynamic sql). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 1:49 PM To: Multiple recipients of list ORACLE-L Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Igor Neyman 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: interesting dynamic pl/sql question
Title: RE: interesting dynamic pl/sql question Ryan, what errors are you getting? 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- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 09, 2003 4:14 PM To: Multiple recipients of list ORACLE-L Subject: RE: interesting dynamic pl/sql question You should be getting errors, because PL/SQL inside execute immediate knows nothing about mystorageArray (or i for that matter) declared in your stored procedure. Probably, you could get by using package variables (and referring to them properly: package_name.var_name, specifically inside your dynamic sql). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 1:49 PM To: Multiple recipients of list ORACLE-L Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Igor Neyman 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). **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.**5
Re: interesting dynamic pl/sql question
I think ' begin mystorageArray.field_''i'' := 1; end; '; will not recongize mystoragearray as a variable . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 11:49 AM Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: AK 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).
is this a good practice...pl/sql question
Allright I am making some changes to some pl/sql code that handles batch inserts into the database. I am making changes to correct an error where our clients are sending us data with invalid state information in their address fields. A constraint prohibits the insert with records with invalid states, nulls are however allowed. The decision was made to insert the rest of the address information, leaving the state column null. OK that is enough background. Here is an example of how I am handling this. for x_rec in driving_cur loop if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing design implementation x_rec.state:=NULL; end if; insert into address(other_columns,..state) values(x_rec.other_cols,...x_rec.state); end loop; I know I am asking a best practices question, and blatantly using old fashioned insert in the middle of a loop style code. This was originally developed in 7.3, and hasn't been recoded to take advantage of the bulk enhancements. My question is regarding the practice of changing the value of a record's attribute(setting x_rec.state to null) after I have selected that record in a cursor. I have been doing this for some time, and it just dawned on me that it might not be a good idea to do this. My thinking is it might be confusing to a developer, or the fear that at some point Oracle might say..that was obviously not an intended feature, that usage no longer allowed. I am wondering if instead I should test the state column of the record and then assign that value or NULL to a local variable. I would then insert the local variable instead of the attribute from the record. Just sort of a bouncing the ball off the wall here, in fact I think I may have resolved the question internally while asking it. In any case I am wondering what others think. Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure 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: is this a good practice...pl/sql question
Steve, It may be old fashion code, but if it works within the time frame it needs to run in, why spend time recoding? At any rate, I don't see a problem. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, September 17, 2003 4:05 PM To: Multiple recipients of list ORACLE-L Allright I am making some changes to some pl/sql code that handles batch inserts into the database. I am making changes to correct an error where our clients are sending us data with invalid state information in their address fields. A constraint prohibits the insert with records with invalid states, nulls are however allowed. The decision was made to insert the rest of the address information, leaving the state column null. OK that is enough background. Here is an example of how I am handling this. for x_rec in driving_cur loop if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing design implementation x_rec.state:=NULL; end if; insert into address(other_columns,..state) values(x_rec.other_cols,...x_rec.state); end loop; I know I am asking a best practices question, and blatantly using old fashioned insert in the middle of a loop style code. This was originally developed in 7.3, and hasn't been recoded to take advantage of the bulk enhancements. My question is regarding the practice of changing the value of a record's attribute(setting x_rec.state to null) after I have selected that record in a cursor. I have been doing this for some time, and it just dawned on me that it might not be a good idea to do this. My thinking is it might be confusing to a developer, or the fear that at some point Oracle might say..that was obviously not an intended feature, that usage no longer allowed. I am wondering if instead I should test the state column of the record and then assign that value or NULL to a local variable. I would then insert the local variable instead of the attribute from the record. Just sort of a bouncing the ball off the wall here, in fact I think I may have resolved the question internally while asking it. In any case I am wondering what others think. Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure 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: Goulet, Dick 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: is this a good practice...pl/sql question
Title: RE: is this a good practice...pl/sql question Steve, Nothing wrong with setting xrec.state to null ... your developer is avoiding hard coding of NULL in the insert statement. In fact if this is working fine then only thing I'd try to change is bulk inserts instead of one by one ... Don't worry it is normal. Raj -Original Message- From: Steve McClure [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 17, 2003 4:05 PM To: Multiple recipients of list ORACLE-L Subject: is this a good practice...pl/sql question Allright I am making some changes to some pl/sql code that handles batch inserts into the database. I am making changes to correct an error where our clients are sending us data with invalid state information in their address fields. A constraint prohibits the insert with records with invalid states, nulls are however allowed. The decision was made to insert the rest of the address information, leaving the state column null. OK that is enough background. Here is an example of how I am handling this. for x_rec in driving_cur loop if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing design implementation x_rec.state:=NULL; end if; insert into address(other_columns,..state) values(x_rec.other_cols,...x_rec.state); end loop; I know I am asking a best practices question, and blatantly using old fashioned insert in the middle of a loop style code. This was originally developed in 7.3, and hasn't been recoded to take advantage of the bulk enhancements. My question is regarding the practice of changing the value of a record's attribute(setting x_rec.state to null) after I have selected that record in a cursor. I have been doing this for some time, and it just dawned on me that it might not be a good idea to do this. My thinking is it might be confusing to a developer, or the fear that at some point Oracle might say..that was obviously not an intended feature, that usage no longer allowed. I am wondering if instead I should test the state column of the record and then assign that value or NULL to a local variable. I would then insert the local variable instead of the attribute from the record. Just sort of a bouncing the ball off the wall here, in fact I think I may have resolved the question internally while asking it. In any case I am wondering what others think. Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure 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). 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: is this a good practice...pl/sql question
Steve McClure wrote: Allright I am making some changes to some pl/sql code that handles batch inserts into the database. I am making changes to correct an error where our clients are sending us data with invalid state information in their address fields. A constraint prohibits the insert with records with invalid states, nulls are however allowed. The decision was made to insert the rest of the address information, leaving the state column null. OK that is enough background. Here is an example of how I am handling this. for x_rec in driving_cur loop if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing design implementation x_rec.state:=NULL; end if; insert into address(other_columns,..state) values(x_rec.other_cols,...x_rec.state); end loop; I know I am asking a best practices question, and blatantly using old fashioned insert in the middle of a loop style code. This was originally developed in 7.3, and hasn't been recoded to take advantage of the bulk enhancements. My question is regarding the practice of changing the value of a record's attribute(setting x_rec.state to null) after I have selected that record in a cursor. I have been doing this for some time, and it just dawned on me that it might not be a good idea to do this. My thinking is it might be confusing to a developer, or the fear that at some point Oracle might say..that was obviously not an intended feature, that usage no longer allowed. I am wondering if instead I should test the state column of the record and then assign that value or NULL to a local variable. I would then insert the local variable instead of the attribute from the record. Just sort of a bouncing the ball off the wall here, in fact I think I may have resolved the question internally while asking it. In any case I am wondering what others think. Steve McClure Steve, Keeping aside all considerations about the loop, I see no problem here. The cursor variable is just short-hand notation for defining a table%ROWTYPE - you are not modifying some hidden Oracle internal state if this is what you are fearing. No 'mutating cursor', if I guess you correctly. -- Regards, Stephane Faroult Oriole Software -- 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: is this a good practice...pl/sql question
Keeping aside all considerations about the loop, I see no problem here. The cursor variable is just short-hand notation for defining a table%ROWTYPE - you are not modifying some hidden Oracle internal state if this is what you are fearing. No 'mutating cursor', if I guess you correctly. I guess I am really wondering if it is a good practice to modify the attributes of a record previously selected via a cursor. It is not just a typical variable that is defined and used in a manner that suits the algorithm. I am just wondering if it would be a better idea to treat this type of a record as a constant. To elevate it conceptually. The reason being that it does(should?)represent what was actually selected from the database. If another developer, or myself for that matter, were to come along at a later date, and use that attribute in another section of code not knowing, or forgetting, it had been altered above. I have actually used this technique extensively in a couple of routines more complex than the one I have described above. It was just as I made this little code change, I felt a pang of conscience, and wanted to ask this philosophical question. I am not worried about mucking up the database, or any such thing. I am aware that variable is just like any other attribute in any record I might have explicitly created myself. It is just that since the database instantiated this record to represent the results of my query, I am wondering if it should grant it an elevated postition. Sorry for rambling on and on etc. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure 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: is this a good practice...pl/sql question
Title: RE: is this a good practice...pl/sql question Yes it is a good practice when required. 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- From: Steve McClure [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 17, 2003 5:45 PM To: Multiple recipients of list ORACLE-L Subject: RE: is this a good practice...pl/sql question Keeping aside all considerations about the loop, I see no problem here. The cursor variable is just short-hand notation for defining a table%ROWTYPE - you are not modifying some hidden Oracle internal state if this is what you are fearing. No 'mutating cursor', if I guess you correctly. I guess I am really wondering if it is a good practice to modify the attributes of a record previously selected via a cursor. It is not just a typical variable that is defined and used in a manner that suits the algorithm. I am just wondering if it would be a better idea to treat this type of a record as a constant. To elevate it conceptually. The reason being that it does(should?)represent what was actually selected from the database. If another developer, or myself for that matter, were to come along at a later date, and use that attribute in another section of code not knowing, or forgetting, it had been altered above. I have actually used this technique extensively in a couple of routines more complex than the one I have described above. It was just as I made this little code change, I felt a pang of conscience, and wanted to ask this philosophical question. I am not worried about mucking up the database, or any such thing. I am aware that variable is just like any other attribute in any record I might have explicitly created myself. It is just that since the database instantiated this record to represent the results of my query, I am wondering if it should grant it an elevated postition. Sorry for rambling on and on etc. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure 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). 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.*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, FUNPACKJOB
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(151:159)
PL/SQl question
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).
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: 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 DWILLIAMS@life To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] touch.comcc: 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
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).
FW: PL/Sql question
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 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
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
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-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
Re: FW: PL/Sql question
um...just a thought but how about setting marketingcode to char(3) in the PL/SQL code snippet. I ran into this similar problem a couple days ago. Had a var as varchar2 in PL/SQL but in the table it was char. Changed my PL/SQL var to char, cursor in my code worked with ltrim and rtrim functions whereas before it wasn't. hth mkb --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: 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 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
Re: FW: PL/Sql question
Geez, after re-reading my post, it seems that it didn't make much sense to me, so to clarify... I had a cursor in my procedure that took as an IN param a varchar2 variable. The cursor failed to return any rows because in my where clause I was comparing a char field against a varchar2 variable. I then decided to create a local variable of type char and assigned my IN varchar2 variable to the local char variable. Using this in my cursors where clause I was then able to get rows back. There, sounds much better. mkb --- mkb [EMAIL PROTECTED] wrote: um...just a thought but how about setting marketingcode to char(3) in the PL/SQL code snippet. I ran into this similar problem a couple days ago. Had a var as varchar2 in PL/SQL but in the table it was char. Changed my PL/SQL var to char, cursor in my code worked with ltrim and rtrim functions whereas before it wasn't. hth mkb --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: 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 )
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 Services--
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, CMSNSTATUS
Pl/sql question - if statement
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
an exponent (**) 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.DATUMTO_DATE('''||inDatum||''',''-MM-DD'') ' ||
-- '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).
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
an exponent (**) 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.DATUMTO_DATE('''
||inDatum||''',''-MM-DD'') ' ||
-- '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:
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
an exponent (**) 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.DATUMTO_DATE('''
||inDatum||''',''-MM-DD'') ' ||
-- '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:
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
an exponent (**) 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
an exponent (**) 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.DATUMTO_DATE('''||inDatum||''',''-MM-DD'') ' ||
-- '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).
--
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).
THANKS - a PL/SQL question - how to catch errors without going i
Many thanks to all who replied ! Have a nice day ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Thu, April 04, 2002 9:37 PM To: Multiple recipients of list ORACLE-L ex Have you considered just adding another exception handler: i := 1; while i 10 loop begin select the_name from the_table into myvar where the_id = i ; exception when others then null; - or whatever you want to do; end; end loop; The net effect is the same. HTH - Brian -Original Message- Bronfin Sent: Thursday, April 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L ex ps , i meant i := 1; while i 10 loop select the_name from the_table into myvar where the_id = i ; end loop; DBAndre -Original Message- From: Andrey Bronfin Sent: Thu, April 04, 2002 9:50 PM To: [EMAIL PROTECTED] (E-mail); [EMAIL PROTECTED] (E-mail); oralist@lists (E-mail) Subject: a PL/SQL question - how to catch errors without going into exceptions block Dear gurus ! I'm wondering whtether i can catch an SQL error (from inside a PL/SQL proc) without jumping to the EXCEPTION block OR is there a way to jump back to the body of the proc from the EXCEPTION block (i know that GOTO can not do it). For example , assume i have users with IDs 1,2,5,6 in my table and i want to do some loop like this i := 1; while i 10 loop select the_name from the_table into myvar where the_id = 1; end loop; . I will be thrown to the EXCEPTION block as soon as i becomes 3. And i can never go back to the loop from the EXCEPTION block , in order to continue looping ;-( So , can i just tell PL/SQL something like never mind if U fail (i.e. an exception is thrown) , just go to the next iteration ... I'm wondering if there is something similar to PERL's next if . Thanks a lot Andre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: Brian McGraw 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: Andrey Bronfin 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).
a PL/SQL question - how to catch errors without going into except
Dear gurus ! I'm wondering whtether i can catch an SQL error (from inside a PL/SQL proc) without jumping to the EXCEPTION block OR is there a way to jump back to the body of the proc from the EXCEPTION block (i know that GOTO can not do it). For example , assume i have users with IDs 1,2,5,6 in my table and i want to do some loop like this i := 1; while i 10 loop select the_name from the_table into myvar where the_id = 1; end loop; . I will be thrown to the EXCEPTION block as soon as i becomes 3. And i can never go back to the loop from the EXCEPTION block , in order to continue looping ;-( So , can i just tell PL/SQL something like never mind if U fail (i.e. an exception is thrown) , just go to the next iteration ... I'm wondering if there is something similar to PERL's next if . Thanks a lot Andre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: a PL/SQL question - how to catch errors without going into ex
ps , i meant i := 1; while i 10 loop select the_name from the_table into myvar where the_id = i ; end loop; DBAndre -Original Message- From: Andrey Bronfin Sent: Thu, April 04, 2002 9:50 PM To: [EMAIL PROTECTED] (E-mail); [EMAIL PROTECTED] (E-mail); oralist@lists (E-mail) Subject: a PL/SQL question - how to catch errors without going into exceptions block Dear gurus ! I'm wondering whtether i can catch an SQL error (from inside a PL/SQL proc) without jumping to the EXCEPTION block OR is there a way to jump back to the body of the proc from the EXCEPTION block (i know that GOTO can not do it). For example , assume i have users with IDs 1,2,5,6 in my table and i want to do some loop like this i := 1; while i 10 loop select the_name from the_table into myvar where the_id = 1; end loop; . I will be thrown to the EXCEPTION block as soon as i becomes 3. And i can never go back to the loop from the EXCEPTION block , in order to continue looping ;-( So , can i just tell PL/SQL something like never mind if U fail (i.e. an exception is thrown) , just go to the next iteration ... I'm wondering if there is something similar to PERL's next if . Thanks a lot Andre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: a PL/SQL question - how to catch errors without going into ex
Enclose your statements in another BEGIN/EXCEPTION/END block. Strategic placement of these blocks will achieve what you are looking for. Lisa Koivu Oracle Database TANK Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 1:44 PM To: Multiple recipients of list ORACLE-L Subject: a PL/SQL question - how to catch errors without going into except Dear gurus ! I'm wondering whtether i can catch an SQL error (from inside a PL/SQL proc) without jumping to the EXCEPTION block OR is there a way to jump back to the body of the proc from the EXCEPTION block (i know that GOTO can not do it). For example , assume i have users with IDs 1,2,5,6 in my table and i want to do some loop like this i := 1; while i 10 loop select the_name from the_table into myvar where the_id = 1; end loop; . I will be thrown to the EXCEPTION block as soon as i becomes 3. And i can never go back to the loop from the EXCEPTION block , in order to continue looping ;-( So , can i just tell PL/SQL something like never mind if U fail (i.e. an exception is thrown) , just go to the next iteration ... I'm wondering if there is something similar to PERL's next if . Thanks a lot Andre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: Koivu, Lisa 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: a PL/SQL question - how to catch errors without going into except
many ways to do that , you can put begin .. end block around select .. inside while condition i := 1; while i 10 loop Begin select the_name from the_table into myvar where the_id = 1; Exception when no data found then null; End ; end loop; or use a group function ... i := 1; while i 10 loop select min(the_name) from the_table into myvar where the_id = 1; end loop; Bp - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 04, 2002 10:43 AM Dear gurus ! I'm wondering whtether i can catch an SQL error (from inside a PL/SQL proc) without jumping to the EXCEPTION block OR is there a way to jump back to the body of the proc from the EXCEPTION block (i know that GOTO can not do it). For example , assume i have users with IDs 1,2,5,6 in my table and i want to do some loop like this i := 1; while i 10 loop select the_name from the_table into myvar where the_id = 1; end loop; . I will be thrown to the EXCEPTION block as soon as i becomes 3. And i can never go back to the loop from the EXCEPTION block , in order to continue looping ;-( So , can i just tell PL/SQL something like never mind if U fail (i.e. an exception is thrown) , just go to the next iteration ... I'm wondering if there is something similar to PERL's next if . Thanks a lot Andre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: a PL/SQL question - how to catch errors without going into ex
Will something like this work for you (crude but ...) BEGIN LOOP BEGIN SELECT ... EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; END LOOP EXCEPTION END; -- Chris J. Guidry P.Eng. EE ATCO Electric, Metering Services Phone: (780) 420-4142 Fax: (780) 420-3854 Email: [EMAIL PROTECTED] -Original Message- From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 11:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: a PL/SQL question - how to catch errors without going into ex ps , i meant i := 1; while i 10 loop select the_name from the_table into myvar where the_id = i ; end loop; DBAndre -Original Message- From: Andrey Bronfin Sent: Thu, April 04, 2002 9:50 PM To: [EMAIL PROTECTED] (E-mail); [EMAIL PROTECTED] (E-mail); oralist@lists (E-mail) Subject:a PL/SQL question - how to catch errors without going into exceptions block Dear gurus ! I'm wondering whtether i can catch an SQL error (from inside a PL/SQL proc) without jumping to the EXCEPTION block OR is there a way to jump back to the body of the proc from the EXCEPTION block (i know that GOTO can not do it). For example , assume i have users with IDs 1,2,5,6 in my table and i want to do some loop like this i := 1; while i 10 loop select the_name from the_table into myvar where the_id = 1; end loop; . I will be thrown to the EXCEPTION block as soon as i becomes 3. And i can never go back to the loop from the EXCEPTION block , in order to continue looping ;-( So , can i just tell PL/SQL something like never mind if U fail (i.e. an exception is thrown) , just go to the next iteration ... I'm wondering if there is something similar to PERL's next if . Thanks a lot Andre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: Guidry, Chris 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: a PL/SQL question - how to catch errors without going into ex
You can always start a new block with its own exception handler Begin .. .. Begin .. Exception when ... .. End; End; -Original Message- Sent: Thursday, April 04, 2002 1:49 PM To: Multiple recipients of list ORACLE-L ps , i meant i := 1; while i 10 loop select the_name from the_table into myvar where the_id = i ; end loop; DBAndre -Original Message- From: Andrey Bronfin Sent: Thu, April 04, 2002 9:50 PM To: [EMAIL PROTECTED] (E-mail); [EMAIL PROTECTED] (E-mail); oralist@lists (E-mail) Subject: a PL/SQL question - how to catch errors without going into exceptions block Dear gurus ! I'm wondering whtether i can catch an SQL error (from inside a PL/SQL proc) without jumping to the EXCEPTION block OR is there a way to jump back to the body of the proc from the EXCEPTION block (i know that GOTO can not do it). For example , assume i have users with IDs 1,2,5,6 in my table and i want to do some loop like this i := 1; while i 10 loop select the_name from the_table into myvar where the_id = 1; end loop; . I will be thrown to the EXCEPTION block as soon as i becomes 3. And i can never go back to the loop from the EXCEPTION block , in order to continue looping ;-( So , can i just tell PL/SQL something like never mind if U fail (i.e. an exception is thrown) , just go to the next iteration ... I'm wondering if there is something similar to PERL's next if . Thanks a lot Andre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: Khedr, Waleed 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: a PL/SQL question - how to catch errors without going into ex
Andrey, Do some reading on exception handling scope in FM. This is what you want: i := 1; while i 10 loop begin select the_name from the_table into myvar where the_id = i ; EXCEPTION WHEN NO_DATA_FOUND THEN NULL: end; end loop; Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 04, 2002 1:48 PM ps , i meant i := 1; while i 10 loop select the_name from the_table into myvar where the_id = i ; end loop; DBAndre -Original Message- From: Andrey Bronfin Sent: Thu, April 04, 2002 9:50 PM To: [EMAIL PROTECTED] (E-mail); [EMAIL PROTECTED] (E-mail); oralist@lists (E-mail) Subject: a PL/SQL question - how to catch errors without going into exceptions block Dear gurus ! I'm wondering whtether i can catch an SQL error (from inside a PL/SQL proc) without jumping to the EXCEPTION block OR is there a way to jump back to the body of the proc from the EXCEPTION block (i know that GOTO can not do it). For example , assume i have users with IDs 1,2,5,6 in my table and i want to do some loop like this i := 1; while i 10 loop select the_name from the_table into myvar where the_id = 1; end loop; . I will be thrown to the EXCEPTION block as soon as i becomes 3. And i can never go back to the loop from the EXCEPTION block , in order to continue looping ;-( So , can i just tell PL/SQL something like never mind if U fail (i.e. an exception is thrown) , just go to the next iteration ... I'm wondering if there is something similar to PERL's next if . Thanks a lot Andre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: Igor Neyman 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: a PL/SQL question - how to catch errors without going into ex
Have you considered just adding another exception handler: i := 1; while i 10 loop begin select the_name from the_table into myvar where the_id = i ; exception when others then null; - or whatever you want to do; end; end loop; The net effect is the same. HTH - Brian -Original Message- Bronfin Sent: Thursday, April 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L ex ps , i meant i := 1; while i 10 loop select the_name from the_table into myvar where the_id = i ; end loop; DBAndre -Original Message- From: Andrey Bronfin Sent: Thu, April 04, 2002 9:50 PM To: [EMAIL PROTECTED] (E-mail); [EMAIL PROTECTED] (E-mail); oralist@lists (E-mail) Subject: a PL/SQL question - how to catch errors without going into exceptions block Dear gurus ! I'm wondering whtether i can catch an SQL error (from inside a PL/SQL proc) without jumping to the EXCEPTION block OR is there a way to jump back to the body of the proc from the EXCEPTION block (i know that GOTO can not do it). For example , assume i have users with IDs 1,2,5,6 in my table and i want to do some loop like this i := 1; while i 10 loop select the_name from the_table into myvar where the_id = 1; end loop; . I will be thrown to the EXCEPTION block as soon as i becomes 3. And i can never go back to the loop from the EXCEPTION block , in order to continue looping ;-( So , can i just tell PL/SQL something like never mind if U fail (i.e. an exception is thrown) , just go to the next iteration ... I'm wondering if there is something similar to PERL's next if . Thanks a lot Andre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: Brian McGraw 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: a PL/SQL question - how to catch errors without going into ex
Hi Audrey, Try this, it will stay in the loop i := 1; while i 10 loop for j in (select the_name from the_table into myvar where the_id = i) loop . end loop; end loop; . Hope it helps. Regds, Catherine -Original Message- From: Andrey Bronfin [mailto:[EMAIL PROTECTED]] Sent: Friday, April 05, 2002 2:44 AM To: Multiple recipients of list ORACLE-L Subject:a PL/SQL question - how to catch errors without going into except Dear gurus ! I'm wondering whtether i can catch an SQL error (from inside a PL/SQL proc) without jumping to the EXCEPTION block OR is there a way to jump back to the body of the proc from the EXCEPTION block (i know that GOTO can not do it). For example , assume i have users with IDs 1,2,5,6 in my table and i want to do some loop like this i := 1; while i 10 loop select the_name from the_table into myvar where the_id = 1; end loop; . I will be thrown to the EXCEPTION block as soon as i becomes 3. And i can never go back to the loop from the EXCEPTION block , in order to continue looping ;-( So , can i just tell PL/SQL something like never mind if U fail (i.e. an exception is thrown) , just go to the next iteration ... I'm wondering if there is something similar to PERL's next if . Thanks a lot Andre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: CHAN Chor Ling Catherine (CSC) 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).
PL/SQL Question
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).
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: 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).
Pl/sql question
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).
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
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 exception then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM table being inserted to; 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
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 message BODY,
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.something 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 Kevin.Thomas@cal To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 exception then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM table being inserted to; 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
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.something 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 Kevin.Thomas@cal To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 exception then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM table being inserted to; 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
RE: Pl/sql question
Yea more elegant if you happen to be running Oracle 8.something 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.something 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 Kevin.Thomas@cal To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 exception then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM table being inserted to; 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
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: 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.something 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 Kevin.Thomas@cal To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 exception then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM table being inserted to; 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).
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.something 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 Kevin.Thomas@cal To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 exception then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM table being inserted to; 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
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.something 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 Kevin.Thomas@cal To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 exception then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM table being inserted to; 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
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]
lindah@epocra cc:
tes.com 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
PL/SQL question
Title: 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
meanowner, 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
Thanks very much!Myuse 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
meanowner, 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
pl/sql question
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).
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
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
Or use a cursor variable. Jared Christopher Spence To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cspence@FuelS cc: pot.com 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 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).
PL/SQL Question after migrating from 7.3.4 to 8.1.7
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).
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 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
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).
Pl/SQL question
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).
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
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).
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
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
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
Dennis, How about dbms_lock.sleep(seconds) ? 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
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
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
[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
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).
AW: PL/SQL Question
Hi: use the procedure: dbms_lock.sleep(seconds); HTH -- Von: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] Gesendet: Freitag, 11. Mai 2001 17:20 An: Multiple recipients of list ORACLE-L Betreff: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Haunschmidt Andreas VASL/FAS 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 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).
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).
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).
RE: A Basic PL/SQL Question
Another option is to create a table with a large varchar2 column and insert the data row by row. You can then spool a SELECT from that table to a file. e.g. create table hold_output (mytext varchar2(4000) tablespace ts_small; -Original Message- Sent: Tuesday, March 27, 2001 4:09 PM To: Multiple recipients of list ORACLE-L Hi, This is my first attempt at writing a PL/SQL procedure. Everything works fine, except I have a firly large table I am running against. I am trying to display my output with DBMS_OUTPUT.PUT_LINE. I have set the buffer size to 100, apparently the maximum value, but it still isn't enough to print output for my entire table. Is there another way to display data? Or is there some way to increase the maximum? Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), NLCIO 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: Miller, Jay 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: A Basic PL/SQL Question
Hi ... you can go for UTL FILE feature of Oracle PL/SQL, it is very handy while handling large amount of data that DBMS_OUTPUT can't handle due to the buffer size constraints. Cheers, Bagchi. On Wed, 28 March 2001, "Miller, Jay" wrote: Another option is to create a table with a large varchar2 column and insert the data row by row. You can then spool a SELECT from that table to a file. e.g. create table hold_output (mytext varchar2(4000) tablespace ts_small; -Original Message- Sent: Tuesday, March 27, 2001 4:09 PM To: Multiple recipients of list ORACLE-L Hi, This is my first attempt at writing a PL/SQL procedure. Everything works fine, except I have a firly large table I am running against. I am trying to display my output with DBMS_OUTPUT.PUT_LINE. I have set the buffer size to 100, apparently the maximum value, but it still isn't enough to print output for my entire table. Is there another way to display data? Or is there some way to increase the maximum? Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), NLCIO 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: Miller, Jay 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). __ 123India.com - India's Premier Portal Get your Free Email Account at http://www.123india.com -- 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).
A Basic PL/SQL Question
Hi, This is my first attempt at writing a PL/SQL procedure. Everything works fine, except I have a firly large table I am running against. I am trying to display my output with DBMS_OUTPUT.PUT_LINE. I have set the buffer size to 100, apparently the maximum value, but it still isn't enough to print output for my entire table. Is there another way to display data? Or is there some way to increase the maximum? Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), NLCIO 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: A Basic PL/SQL Question
Use the UTL_FILE package and write the results to a temporary file. Eric -Original Message- Sent: Tuesday, March 27, 2001 4:09 PM To: Multiple recipients of list ORACLE-L Hi, This is my first attempt at writing a PL/SQL procedure. Everything works fine, except I have a firly large table I am running against. I am trying to display my output with DBMS_OUTPUT.PUT_LINE. I have set the buffer size to 100, apparently the maximum value, but it still isn't enough to print output for my entire table. Is there another way to display data? Or is there some way to increase the maximum? Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), NLCIO 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: Chesebro, Eric 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).
