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
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)
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
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
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
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
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
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
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
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
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,
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
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
: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
Dennis,
Try changing your insert statement to:
insert into JOBOFFERFACT_LOAD
(LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID,
MARKETINGCODE,
TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME,
PACKAGEPRICE,
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
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/
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
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:
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
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 :=
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
- 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
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
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,
:
Sent by:Subject: RE: Pl/sql question
[EMAIL PROTECTED
[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
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
- 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
:
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
/
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
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
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@
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 ' ||
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
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,
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,
toSchool
Spence To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cspence@FuelS cc:
pot.com Subject: RE: pl/sql question
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
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
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:
Use %ROWTYPE.
TYPE tb_uc9_corres_type IS TABLE OF uc9_correspondence%ROWTYPE
INDEX BY BINARY_INTEGER;
hth,
prasad
Deen Dayal
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æ
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
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
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
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
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
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
[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
--
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
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
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:
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
54 matches
Mail list logo
