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

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,

RE: Pl/sql question

: Sent by:Subject: RE: Pl/sql question [EMAIL PROTECTED

Ang: RE: Pl/sql question

[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

RE: Pl/sql question

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

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

RE: 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 at the start

Ang: Re: RE: Pl/sql question

/ 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

Re: RE: Pl/sql question

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

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@

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 ' ||

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

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,

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, toSchool

RE: pl/sql question

Spence To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cspence@FuelS cc: pot.com Subject: RE: pl/sql question

RE: An SQL question , not easy ;-)

recipients of list ORACLE-L Subject: RE: An SQL question , not easy ;-) Is this not flawed in that given these results: eventtimeevent - 01-AUG-2001 12:10:00 start add 1- 1 01-AUG-2001 12:15:00 start add 1- 2 01-AUG-2001 12:25:00 start

RE: An SQL question , not easy ;-)

of Central England -Original Message- From: Thomas, Kevin [mailto:[EMAIL PROTECTED]] Sent: 15 August 2001 16:11 To: Multiple recipients of list ORACLE-L Subject: RE: An SQL question , not easy ;-) Is this not flawed

RE: An SQL question , not easy ;-)

Subject: RE: An SQL question , not easy ;-) Or use a self-join select a.call_start, count(b.call_start) interruptions from phone_call a, phone_call b where a.call_start = b.call_start and a.call_end b.call_start group by a.call_start -Original Message- Sent: 15 August 2001 16:02

RE: An SQL question , not easy ;-)

: Thomas, Kevin [mailto:[EMAIL PROTECTED]] Sent: 15 August 2001 15:07 To: Multiple recipients of list ORACLE-L Subject: RE: An SQL question , not easy ;-) David, Here's the output from your suggestion based on a table with the following rows; CALL_STARTCALL_END

RE: An SQL question , not easy ;-)

Yes, and this morning my manager just asked me for something similar too. How fortunate :0) g -Original Message- Sent: Wednesday, August 15, 2001 7:26 PM To: Multiple recipients of list ORACLE-L Hi All: There's one really neat thing about this thread...it's helped me solve an

RE: An SQL question , not easy ;-)

August 2001 16:46 To: Multiple recipients of list ORACLE-L Subject: RE: An SQL question , not easy ;-) Or use a self-join select a.call_start, count(b.call_start) interruptions from phone_call a, phone_call b where a.call_start = b.call_start and a.call_end b.call_start group

RE: An SQL question , not easy ;-)

Quick and dirty solution: Pick a time and look for all calls which started before that time and ended after that time. That will tell you how many calls were in progress at that time. Repeat this at, say, half hour intervals, and graph the results. This will show you a trend. Where it looks

Re: An SQL question , not easy ;-)

Pursuing Guy's method in a non-procedural way: select ts.timestamp, count(*) from ( select to_date('1-jan-2001','dd-mon-') + (rownum / 1440) timestamp from short_narrow_table_of_numbers where rownum = 1440 )ts, phone_calls where

RE: An SQL question , not easy ;-)

Thanks Guy ! The problem is that i need it in one non-interactive PL/SQL block . Thanks a lot !! -Original Message- Sent: Wednesday, August 15, 2001 1:00 PM To: Multiple recipients of list ORACLE-L Quick and dirty solution: Pick a time and look for all calls which started before that

RE: An SQL question , not easy ;-)

How about: - declare l_count pls_integer := 0; begin for rec in ( select call_start time, 1 incr from table union all select call_end time, -1 incr from table order by 1 ) loop l_count :=

RE: An SQL question , not easy ;-)

David, Here's the output from your suggestion based on a table with the following rows; CALL_STARTCALL_END - - 01-AUG-2001 12:10 01-AUG-2001 12:40 01-AUG-2001 12:15 01-AUG-2001 12:30 01-AUG-2001 12:25 01-AUG-2001 12:55 01-AUG-2001 12:45 01-AUG-2001 12:47

RE: An SQL question , not easy ;-)

Note that whenever a call starts, this increases the current number of calls by 1, and whenever a call ends, this decreases the current number of calls by 1. Hence you can uncouple the start and end times - you don't need to know that a given start time and a given end time belong to the same

RE: An SQL question , not easy ;-)

I'm working on this one, almost got a solution... ;-) Kev. -Original Message- Sent: 15 August 2001 14:05 To: Multiple recipients of list ORACLE-L Thanks Guy ! The problem is that i need it in one non-interactive PL/SQL block . Thanks a lot !! -Original Message- Sent:

RE: An SQL question , not easy ;-)

:[EMAIL PROTECTED]] Sent: 15 August 2001 15:07 To: Multiple recipients of list ORACLE-L Subject: RE: An SQL question , not easy ;-) David, Here's the output from your suggestion based on a table with the following rows; CALL_STARTCALL_END

RE: An SQL question , not easy ;-)

PROTECTED]] Sent: 15 August 2001 15:07 To: Multiple recipients of list ORACLE-L Subject: RE: An SQL question , not easy ;-) David, Here's the output from your suggestion based on a table with the following rows; CALL_STARTCALL_END - - 01-AUG-2001

RE: An SQL question , not easy ;-)

Is this not flawed in that given these results: eventtimeevent - 01-AUG-2001 12:10:00 start add 1- 1 01-AUG-2001 12:15:00 start add 1- 2 01-AUG-2001 12:25:00 start add 1- total 3 01-AUG-2001 12:30:00 end subtract 1 - 2

RE: An SQL question , not easy ;-)

: 15 August 2001 15:07 To: Multiple recipients of list ORACLE-L Subject: RE: An SQL question , not easy ;-) David, Here's the output from your suggestion based on a table with the following rows; CALL_STARTCALL_END - - 01-AUG-2001 12:10 01-AUG

Re: An SQL question , not easy ;-)

Andrey, At first glance, I think this SQL statement gives you the answer. It gives you a count of the simultaneous calls for each call. If you like this, then all you have to do is find the row with the maximum count. select a.call_start,a.call_end,count(*)+ 1 from pc a, pc b where

RE: An SQL question , not easy - GOT IT . MANY THANKS !!!!!!!

(l_max_count) ); end; Regards David Lord -Original Message- From: Thomas, Kevin [mailto:[EMAIL PROTECTED]] Sent: 15 August 2001 15:07 To: Multiple recipients of list ORACLE-L Subject: RE: An SQL question , not easy ;-) David, Here's the output from your suggestion based on a table

RE: An SQL question , not easy ;-)

: Thomas, Kevin [mailto:[EMAIL PROTECTED]] Sent: 15 August 2001 15:07 To: Multiple recipients of list ORACLE-L Subject: RE: An SQL question , not easy ;-) David, Here's the output from your suggestion based on a table with the following rows; CALL_STARTCALL_END

RE: An SQL question , not easy ;-)

Hi All: There's one really neat thing about this thread...it's helped me solve an on-going problem. I've got managers who want to see graphs of their system's usage. Using this mechanism applied against the DBA_AUDIT_TRAIL view works perfectly. Thanks! Mike ---

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

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

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:

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

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æ

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

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

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

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

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

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

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 --

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

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

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:

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

<    1   2