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
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 co
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 rep
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
(spe
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) correctl
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 ta
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
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
DB
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 i
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]
Dennis,
Try changing your insert statement to:
insert into JOBOFFERFACT_LOAD
(LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID,
MARKETINGCODE,
TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME,
PACKAGEPRICE,
PAIDPACKAGE
l: [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 T
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
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:
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
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
cc:
[EMAIL PROTECTED] Subject: Ang: RE: Pl/sql question - if
statement
[EMAIL PROTECTED] wrote:
> Yes but then it fails onthe word borttags_flagg, thi serrormessage :
>
>
> PLS-00103: Encountered the symbol "BORTTAGS_FLAGG" when expecting one of the
>following:
>
>. ( * @ % & = - + < / > at in mod not rem then
> <> or != or ~= >= <= <> and or like
>
> I
l 2002 18:48
An: Multiple recipients of list ORACLE-L
Betreff: Ang: RE: Pl/sql question - if statement
Yes but then it fails onthe word borttags_flagg, thi serrormessage :
PLS-00103: Encountered the symbol "BORTTAGS_FLAGG" when expecting one of the
following:
. ( * @ % & = - +
Yes but then it fails onthe word borttags_flagg, thi serrormessage :
PLS-00103: Encountered the symbol "BORTTAGS_FLAGG" when expecting one of the following:
. ( * @ % & = - + < / > at in mod not rem then
<> or != or ~= >= <= <> and or like
I reallydont see what the error is:
Roland
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 q
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/
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 recipi
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 ge
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 := t
eríeïa 13, LV1050 Rîga
http://www.itsystems.lv/gints/
"Thomas, Kevin"
anais.com> cc:
Sent by:Subject: RE: Pl/sql question
[EMAIL PROTECTED
eïa 13, LV1050 Rîga
http://www.itsystems.lv/gints/
"Thomas, Kevin"
anais.com> cc:
Sent by:Subject: RE: Pl/sql question
[EMAIL PROTECTED]
"Thomas, Kevin"
anais.com> cc:
Sent by: Subject: RE: Pl/sql question
[EMAIL PROTECTED]
2002.01.28 11:20
Please respond to
think it is more elegant solution
Gints Plivna
IT Sistçmas, Meríeïa 13, LV1050 Rîga
http://www.itsystems.lv/gints/
"Thomas, Kevin"
anais.com> cc:
Sent by: Subject:
- 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 rese
Meríeïa 13, LV1050 Rîga
http://www.itsystems.lv/gints/
"Thomas, Kevin"
anais.com> cc:
Sent by: Subject: RE: Pl/sql question
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 a
Subject: 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,
Hi Roland,
Best way to do this is just set some variables at the start of your code:
l_proc_start := sysdate;
l_proc_name := 'proc_name';
begin
...commands...
exception
when then
l_proc_end := sysdate;
SELECT count(*)
INTO l_ins_count
FROM ;
>- 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 i
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
Thanks very much! My use of EXECUTE IMMEDIATE is passing
the parser.
Linda
-Original Message-From: Djordje Jankovic
[mailto:[EMAIL PROTECTED]]Sent: Thursday, November 15,
2001 2:03 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: PL
Title: PL/SQL question
Hi
Linda,
You
cannot put a variable instead of an object name (where by object here I
mean owner, table_name, column_name). You have few options:
-
generate a sql hat you would run, e.g. do select 'select
max('||v_column_name||') from ' ||
v_owner||'.'||v_table_
Spence To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
Subject: RE: pl/sql question
S
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,
toSchoolvarchar
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, Ch
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_corr
Use %ROWTYPE.
TYPE tb_uc9_corres_type IS TABLE OF uc9_correspondence%ROWTYPE
INDEX BY BINARY_INTEGER;
hth,
prasad
"Deen Dayal"
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:
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
BINAR
Thanks for your help!
This solved the problem.
Sonja
-Original Message-
Sent: Monday, June 25, 2001 3:27 PM
To: Multiple recipients of list ORACLE-L
Sonja,
Instance is easy enough. Either grant the person SELECT access to the
V_$INSTANCE view under the SYS account, or create another
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?
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æ <[E
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]>
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 :) )
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
[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
--
_
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
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
Dennis,
How about dbms_lock.sleep() ?
Jared
On Friday 11 May 2001 08:20, [EMAIL PROTECTED] wrote:
> I need to whip out a PL/SQL procedure real quick today and have a quick
> question for fellow-listers ( since today is Friday, hopefully I don't get
> RTFMed on this one :) )
> The purpose of m
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
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 opi
The file has to be either fixed length or delimited. If it is then you would
not have any problem.
Alternately you could try importing the text file in Excel (MS office
product). Convert it to Excel and import from excel to Oracle using Oracle
migration assistant.
HTH!
Aleem
-Original Mes
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
60 matches
Mail list logo