RE: Referencing other schemas' tables in PL/SQL procedure
Naveen - many thanks! I had a vague recollection that this may be the case, rattling
around in the back of my mind, but I couldn't find confirmation in the manuals. Thanks
for the definitive answer! It's an annoying restriction, but now we can cope with it!
Paul
-Original Message-
Sent: 12 August 2003 13:06
To: Multiple recipients of list ORACLE-L
Any privileges granted through roles are not enabled in PL/SQL procedures.
You need to have the privilege granted directly not through a ROLE.
Regards
Naveen
-Original Message-
From: Paul Vincent [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 12, 2003 5:29 PM
To: Multiple recipients of list ORACLE-L
Subject: Referencing other schemas' tables in PL/SQL procedure
Hi,
one of our developers is having a problem. His userid has
the DBA role on a test database, and he's written a PL/SQL
procedure, in his schema, which is referencing (via SELECT)
and updating a table in another schema, so he's coding the select as:
CURSOR c1
IS
select distinct ORIG_MODULE
from QLDBA.GENTRAN
where TRANS_DT = to_date('15/07/2003','dd/mm/');
...however, when he tries to compile the procedure, he gets
several error messages including:
Line # = 16 Column # = 11 Error Text = PLS-00201: identifier
'QLDBA.GENTRAN' must be declared
Now, the table GENTRAN certainly exists in the QLDBA schema,
so there must be some rule being broken here. I thought
anyone with the DBA role could do any DML on any table in
any schema? Indeed, when the guy runs the select in a
SQL*Plus window, it works fine, so can any PL/SQL guru shed
some light on this? My PL/SQL skills are pretty rudimentary,
and a rummage through the PL/SQL User Guide didn't turn
anything up...
Hope someone can help!
Regards,
Paul
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Paul Vincent
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).
DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged.
Before opening attachments please check them for viruses and defects. MindTree
Consulting Private Limited (MindTree) will not be responsible for any viruses or
defects or any forwarded attachments emanating either from within MindTree or outside.
If you have received this message by mistake please notify the sender by return
e-mail and delete this message from your system. Any unauthorized use or dissemination
of this message in whole or in part is strictly prohibited. Please note that e-mails
are susceptible to change and MindTree shall not be liable for any improper, untimely
or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
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: Paul Vincent
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).
AW: Referencing other schemas' tables in PL/SQL procedure
Hi
(B
(BThat's right. I think I started that discussion. Whatever you do like AUTHID
(Bdefiner or current user, it won't help.
(BThe object privileges have to be granted directly. That can be pretty
(Bannoying if you try to seperate data owner
(Bschemas from code owners, but a couple scripts can automatize it.
(BWhat we do now: Still maintan the roles for "normal" sql access via
(Bsynonyms, additionally grant directly to the
(Busers that need to execute pl/sql code (data conversion in our case). Not
(Btoo elegant, but whatever.
(B
(BStefan
(B
(B
(B-Ursprungliche Nachricht-
(BVon: Naveen Nahata [mailto:[EMAIL PROTECTED]
(BGesendet: Dienstag, 12. August 2003 14:34
(BAn: Multiple recipients of list ORACLE-L
(BBetreff: RE: Referencing other schemas' tables in PL/SQL procedure
(B
(B
(BThere was a discussion on this list, regarding the same a few months back.
(B
(BIt is annoying, but there are reasons for this restriction as pointed out by
(Bsome in that thread.
(B
(BI didn't follow the thread deeply, but you can find it in the archives.
(B
(BRegards
(BNaveen
(B
(B-Original Message-
(BFrom: Paul Vincent [mailto:[EMAIL PROTECTED]
(BSent: Tuesday, August 12, 2003 5:54 PM
(BTo: Multiple recipients of list ORACLE-L
(BSubject: RE: Referencing other schemas' tables in PL/SQL procedure
(B
(B
(BNaveen - many thanks! I had a vague recollection that this
(Bmay be the case, rattling around in the back of my mind, but
(BI couldn't find confirmation in the manuals. Thanks for the
(Bdefinitive answer! It's an annoying restriction, but now we
(Bcan cope with it!
(B
(BPaul
(B
(B-Original Message-
(BSent: 12 August 2003 13:06
(BTo: Multiple recipients of list ORACLE-L
(B
(B
(BAny privileges granted through roles are not enabled in
(BPL/SQL procedures.
(B
(BYou need to have the privilege granted directly not through a ROLE.
(B
(BRegards
(BNaveen
(B
(B-Original Message-
(BFrom: Paul Vincent [mailto:[EMAIL PROTECTED]
(BSent: Tuesday, August 12, 2003 5:29 PM
(BTo: Multiple recipients of list ORACLE-L
(BSubject: Referencing other schemas' tables in PL/SQL procedure
(B
(B
(BHi,
(B
(Bone of our developers is having a problem. His userid has
(Bthe DBA role on a test database, and he's written a PL/SQL
(Bprocedure, in his schema, which is referencing (via SELECT)
(Band updating a table in another schema, so he's coding
(Bthe select as:
(B
(B CURSOR c1
(B IS
(B select distinct ORIG_MODULE
(B from QLDBA.GENTRAN
(B where TRANS_DT = to_date('15/07/2003','dd/mm/');
(B
(B...however, when he tries to compile the procedure, he gets
(Bseveral error messages including:
(B
(BLine # = 16 Column # = 11 Error Text = PLS-00201: identifier
(B'QLDBA.GENTRAN' must be declared
(B
(B
(BNow, the table GENTRAN certainly exists in the QLDBA schema,
(Bso there must be some rule being broken here. I thought
(Banyone with the DBA role could do any DML on any table in
(Bany schema? Indeed, when the guy runs the select in a
(BSQL*Plus window, it works fine, so can any PL/SQL guru shed
(Bsome light on this? My PL/SQL skills are pretty rudimentary,
(Band a rummage through the PL/SQL User Guide didn't turn
(Banything up...
(B
(BHope someone can help!
(B
(BRegards,
(B
(BPaul
(B--
(BPlease see the official ORACLE-L FAQ: http://www.orafaq.net
(B--
(BAuthor: Paul Vincent
(B INET: [EMAIL PROTECTED]
(B
(BFat City Network Services-- 858-538-5051
(Bhttp://www.fatcity.com
(BSan Diego, California-- Mailing list and web
(Bhosting services
(B--
(B---
(BTo REMOVE yourself from this mailing list, send an E-Mail message
(Bto: [EMAIL PROTECTED] (note EXACT spelling of
(B'ListGuru') and in
(Bthe message BODY, include a line containing: UNSUB ORACLE-L
(B(or the name of mailing list you want to be removed
(Bfrom). You may
(Balso send the HELP command for other information (like
(Bsubscribing).
(B
(B
(B
(BDISCLAIMER:
(BThis message (including attachment if any) is confidential
(Band may be privileged. Before opening attachments please
(Bcheck them for viruses and defects. MindTree Consulting
(BPrivate Limited (MindTree) will not be responsible for any
(Bviruses or defects or any forwarded attachments emanating
(Beither from within MindTree or outside. If you have received
(Bthis message by mistake please notify the sender by return
(Be-mail and delete this message from your system. Any
(Bunauthorized use or dissemination of this message in whole
(Bor in part is strictly prohibited. Please note that e-mails
(Bare susceptible to change and MindTree shall not be liable
(Bfor any improper, untimely or incomplete transmission.
(B--
(BPlease see the official ORACLE-L FAQ: http://www.orafaq.net
(B--
(BAuthor: Naveen Nahata
(B INET: [EMAIL PROTECTED]
(B
(BFat City Networ
Referencing other schemas' tables in PL/SQL procedure
Hi,
one of our developers is having a problem. His userid has the DBA role on a test
database, and he's written a PL/SQL procedure, in his schema, which is referencing
(via SELECT) and updating a table in another schema, so he's coding the select as:
CURSOR c1
IS
select distinct ORIG_MODULE
from QLDBA.GENTRAN
where TRANS_DT = to_date('15/07/2003','dd/mm/');
...however, when he tries to compile the procedure, he gets several error messages
including:
Line # = 16 Column # = 11 Error Text = PLS-00201: identifier 'QLDBA.GENTRAN' must be
declared
Now, the table GENTRAN certainly exists in the QLDBA schema, so there must be some
rule being broken here. I thought anyone with the DBA role could do any DML on any
table in any schema? Indeed, when the guy runs the select in a SQL*Plus window, it
works fine, so can any PL/SQL guru shed some light on this? My PL/SQL skills are
pretty rudimentary, and a rummage through the PL/SQL User Guide didn't turn anything
up...
Hope someone can help!
Regards,
Paul
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Paul Vincent
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: Referencing other schemas' tables in PL/SQL procedure
There was a discussion on this list, regarding the same a few months back.
It is annoying, but there are reasons for this restriction as pointed out by
some in that thread.
I didn't follow the thread deeply, but you can find it in the archives.
Regards
Naveen
-Original Message-
From: Paul Vincent [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 12, 2003 5:54 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Referencing other schemas' tables in PL/SQL procedure
Naveen - many thanks! I had a vague recollection that this
may be the case, rattling around in the back of my mind, but
I couldn't find confirmation in the manuals. Thanks for the
definitive answer! It's an annoying restriction, but now we
can cope with it!
Paul
-Original Message-
Sent: 12 August 2003 13:06
To: Multiple recipients of list ORACLE-L
Any privileges granted through roles are not enabled in
PL/SQL procedures.
You need to have the privilege granted directly not through a ROLE.
Regards
Naveen
-Original Message-
From: Paul Vincent [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 12, 2003 5:29 PM
To: Multiple recipients of list ORACLE-L
Subject: Referencing other schemas' tables in PL/SQL procedure
Hi,
one of our developers is having a problem. His userid has
the DBA role on a test database, and he's written a PL/SQL
procedure, in his schema, which is referencing (via SELECT)
and updating a table in another schema, so he's coding
the select as:
CURSOR c1
IS
select distinct ORIG_MODULE
from QLDBA.GENTRAN
where TRANS_DT = to_date('15/07/2003','dd/mm/');
...however, when he tries to compile the procedure, he gets
several error messages including:
Line # = 16 Column # = 11 Error Text = PLS-00201: identifier
'QLDBA.GENTRAN' must be declared
Now, the table GENTRAN certainly exists in the QLDBA schema,
so there must be some rule being broken here. I thought
anyone with the DBA role could do any DML on any table in
any schema? Indeed, when the guy runs the select in a
SQL*Plus window, it works fine, so can any PL/SQL guru shed
some light on this? My PL/SQL skills are pretty rudimentary,
and a rummage through the PL/SQL User Guide didn't turn
anything up...
Hope someone can help!
Regards,
Paul
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Paul Vincent
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).
DISCLAIMER:
This message (including attachment if any) is confidential
and may be privileged. Before opening attachments please
check them for viruses and defects. MindTree Consulting
Private Limited (MindTree) will not be responsible for any
viruses or defects or any forwarded attachments emanating
either from within MindTree or outside. If you have received
this message by mistake please notify the sender by return
e-mail and delete this message from your system. Any
unauthorized use or dissemination of this message in whole
or in part is strictly prohibited. Please note that e-mails
are susceptible to change and MindTree shall not be liable
for any improper, untimely or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
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: Paul Vincent
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).
DISCLAIMER:
This message
RE: Referencing other schemas' tables in PL/SQL procedure
Any privileges granted through roles are not enabled in PL/SQL procedures.
You need to have the privilege granted directly not through a ROLE.
Regards
Naveen
-Original Message-
From: Paul Vincent [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 12, 2003 5:29 PM
To: Multiple recipients of list ORACLE-L
Subject: Referencing other schemas' tables in PL/SQL procedure
Hi,
one of our developers is having a problem. His userid has
the DBA role on a test database, and he's written a PL/SQL
procedure, in his schema, which is referencing (via SELECT)
and updating a table in another schema, so he's coding the select as:
CURSOR c1
IS
select distinct ORIG_MODULE
from QLDBA.GENTRAN
where TRANS_DT = to_date('15/07/2003','dd/mm/');
...however, when he tries to compile the procedure, he gets
several error messages including:
Line # = 16 Column # = 11 Error Text = PLS-00201: identifier
'QLDBA.GENTRAN' must be declared
Now, the table GENTRAN certainly exists in the QLDBA schema,
so there must be some rule being broken here. I thought
anyone with the DBA role could do any DML on any table in
any schema? Indeed, when the guy runs the select in a
SQL*Plus window, it works fine, so can any PL/SQL guru shed
some light on this? My PL/SQL skills are pretty rudimentary,
and a rummage through the PL/SQL User Guide didn't turn
anything up...
Hope someone can help!
Regards,
Paul
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Paul Vincent
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).
DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged.
Before opening attachments please check them for viruses and defects. MindTree
Consulting Private Limited (MindTree) will not be responsible for any viruses or
defects or any forwarded attachments emanating either from within MindTree or outside.
If you have received this message by mistake please notify the sender by return
e-mail and delete this message from your system. Any unauthorized use or dissemination
of this message in whole or in part is strictly prohibited. Please note that e-mails
are susceptible to change and MindTree shall not be liable for any improper, untimely
or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
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: Referencing other schemas' tables in PL/SQL procedure
Title: RE: Referencing other schemas' tables in PL/SQL procedure
story of usual suspects
culprit: SELECT privilege granted through role
Resolution: Grant SELECT privilege as schema owner TO the procedure owner directly.
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: Paul Vincent [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 12, 2003 7:59 AM
To: Multiple recipients of list ORACLE-L
Subject: Referencing other schemas' tables in PL/SQL procedure
Hi,
one of our developers is having a problem. His userid has the DBA role on a test database, and he's written a PL/SQL procedure, in his schema, which is referencing (via SELECT) and updating a table in another schema, so he's coding the select as:
CURSOR c1
IS
select distinct ORIG_MODULE
from QLDBA.GENTRAN
where TRANS_DT = to_date('15/07/2003','dd/mm/');
...however, when he tries to compile the procedure, he gets several error messages including:
Line # = 16 Column # = 11 Error Text = PLS-00201: identifier 'QLDBA.GENTRAN' must be declared
Now, the table GENTRAN certainly exists in the QLDBA schema, so there must be some rule being broken here. I thought anyone with the DBA role could do any DML on any table in any schema? Indeed, when the guy runs the select in a SQL*Plus window, it works fine, so can any PL/SQL guru shed some light on this? My PL/SQL skills are pretty rudimentary, and a rummage through the PL/SQL User Guide didn't turn anything up...
Hope someone can help!
Regards,
Paul
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Paul Vincent
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: nextval increments by 2 in pl/sql procedure
Where do you call your procedure? If you are running the code through IAS you (ias) will start more than one session (if you are using SSO), so depending on where you are calling your procedure it migth be called more than once. Could you please give some more information. /torben -- Original Message -- Date: Tue, 13 Aug 2002 14:34:13 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] From: Danny Hughes [EMAIL PROTECTED] Subject: nextval increments by 2 in pl/sql procedure hello all, I have a pl/sql procedure that selects the nextval from a sequence. The problem is that it incrrements the sequence by 2 in a pl/sql procedure. If I select the nextval from a sqlplus session, it works fine. here is partial code that I am using. PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type, v_transid OUT story_transaction.trans_id%type ) IS BEGIN select story_transaction_seq.nextval into v_transid from dual; . END for debugging purposes, I made that code look like... PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type, v_transid OUT story_transaction.trans_id%type ) IS v_value1 NUMBER; --debug code v_value2 NUMBER; --debug code BEGIN select story_transaction_seq.currval into v_value1 from dual; --debug code select story_transaction_seq.nextval into v_transid from dual; select story_transaction_seq.currval into v_value2 from dual; --debug code . END when I ran this, v_value1 had a 200, v_transid had a 201, and v_value2 had a 202 taking the debug code out and hitting that procedure with a web page will increment the v_transid by 2 each refresh of the page. am i doing something wrong? I can't find any information on the web related to this issue. TIA Danny Hughes DBA Knobias.com 601-978-3399 x103 [EMAIL PROTECTED] www.knobias.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danny Hughes 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). Torben Holm Miracle A/S Mobil : +45 2527 7104 [EMAIL PROTECTED] http://miracleAS.dk -- 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: nextval increments by 2 in pl/sql procedure
There is nothing wrong in what you are doing. It might be that there are lots of requests for story_transaction_seq.NEXTVAL and by the time you reach the next line it gets incremented by 1 again(unlikely, but possible). I suggest you test it when you are sure that there will be no request for the value from the sequence by any other session. Naveen -Original Message- Sent: Wednesday, August 14, 2002 4:04 AM To: Multiple recipients of list ORACLE-L hello all, I have a pl/sql procedure that selects the nextval from a sequence. The problem is that it incrrements the sequence by 2 in a pl/sql procedure. If I select the nextval from a sqlplus session, it works fine. here is partial code that I am using. PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type, v_transid OUT story_transaction.trans_id%type ) IS BEGIN select story_transaction_seq.nextval into v_transid from dual; . END for debugging purposes, I made that code look like... PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type, v_transid OUT story_transaction.trans_id%type ) IS v_value1 NUMBER; --debug code v_value2 NUMBER; --debug code BEGIN select story_transaction_seq.currval into v_value1 from dual; --debug code select story_transaction_seq.nextval into v_transid from dual; select story_transaction_seq.currval into v_value2 from dual; --debug code . END when I ran this, v_value1 had a 200, v_transid had a 201, and v_value2 had a 202 taking the debug code out and hitting that procedure with a web page will increment the v_transid by 2 each refresh of the page. am i doing something wrong? I can't find any information on the web related to this issue. TIA Danny Hughes DBA Knobias.com 601-978-3399 x103 [EMAIL PROTECTED] www.knobias.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danny Hughes 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: Naveen Nahata 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: nextval increments by 2 in pl/sql procedure -SOLVED
Thanks for your responses, Not completly sure why it was skipping on a nextval then an immediate currval select yesterday, but I got with the PHP programmer and re-worked the calling program and today it is working like expected. Thanks again. [EMAIL PROTECTED] 08/14/02 07:53AM There is nothing wrong in what you are doing. It might be that there are lots of requests for story_transaction_seq.NEXTVAL and by the time you reach the next line it gets incremented by 1 again(unlikely, but possible). I suggest you test it when you are sure that there will be no request for the value from the sequence by any other session. Naveen -Original Message- Sent: Wednesday, August 14, 2002 4:04 AM To: Multiple recipients of list ORACLE-L hello all, I have a pl/sql procedure that selects the nextval from a sequence. The problem is that it incrrements the sequence by 2 in a pl/sql procedure. If I select the nextval from a sqlplus session, it works fine. here is partial code that I am using. PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type, v_transid OUT story_transaction.trans_id%type ) IS BEGIN select story_transaction_seq.nextval into v_transid from dual; . END for debugging purposes, I made that code look like... PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type, v_transid OUT story_transaction.trans_id%type ) IS v_value1 NUMBER; --debug code v_value2 NUMBER; --debug code BEGIN select story_transaction_seq.currval into v_value1 from dual; --debug code select story_transaction_seq.nextval into v_transid from dual; select story_transaction_seq.currval into v_value2 from dual; --debug code . END when I ran this, v_value1 had a 200, v_transid had a 201, and v_value2 had a 202 taking the debug code out and hitting that procedure with a web page will increment the v_transid by 2 each refresh of the page. am i doing something wrong? I can't find any information on the web related to this issue. TIA Danny Hughes DBA Knobias.com 601-978-3399 x103 [EMAIL PROTECTED] www.knobias.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danny Hughes 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: Naveen Nahata 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: Danny Hughes 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: nextval increments by 2 in pl/sql procedure
I'm not sure, but would concurrency be an issue here? With Warm Regards, Manav. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 14, 2002 5:28 AM Where do you call your procedure? If you are running the code through IAS you (ias) will start more than one session (if you are using SSO), so depending on where you are calling your procedure it migth be called more than once. Could you please give some more information. /torben -- Original Message -- Date: Tue, 13 Aug 2002 14:34:13 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] From: Danny Hughes [EMAIL PROTECTED] Subject: nextval increments by 2 in pl/sql procedure hello all, I have a pl/sql procedure that selects the nextval from a sequence. The problem is that it incrrements the sequence by 2 in a pl/sql procedure. If I select the nextval from a sqlplus session, it works fine. here is partial code that I am using. PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type, v_transid OUT story_transaction.trans_id%type ) IS BEGIN select story_transaction_seq.nextval into v_transid from dual; . END for debugging purposes, I made that code look like... PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type, v_transid OUT story_transaction.trans_id%type ) IS v_value1 NUMBER; --debug code v_value2 NUMBER; --debug code BEGIN select story_transaction_seq.currval into v_value1 from dual; --debug code select story_transaction_seq.nextval into v_transid from dual; select story_transaction_seq.currval into v_value2 from dual; --debug code . END when I ran this, v_value1 had a 200, v_transid had a 201, and v_value2 had a 202 taking the debug code out and hitting that procedure with a web page will increment the v_transid by 2 each refresh of the page. am i doing something wrong? I can't find any information on the web related to this issue. TIA Danny Hughes DBA Knobias.com 601-978-3399 x103 [EMAIL PROTECTED] www.knobias.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danny Hughes 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). Torben Holm Miracle A/S Mobil : +45 2527 7104 [EMAIL PROTECTED] http://miracleAS.dk -- 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: Manavendra Gupta 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).
nextval increments by 2 in pl/sql procedure
hello all, I have a pl/sql procedure that selects the nextval from a sequence. The problem is that it incrrements the sequence by 2 in a pl/sql procedure. If I select the nextval from a sqlplus session, it works fine. here is partial code that I am using. PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type, v_transid OUT story_transaction.trans_id%type ) IS BEGIN select story_transaction_seq.nextval into v_transid from dual; . END for debugging purposes, I made that code look like... PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type, v_transid OUT story_transaction.trans_id%type ) IS v_value1 NUMBER; --debug code v_value2 NUMBER; --debug code BEGIN select story_transaction_seq.currval into v_value1 from dual; --debug code select story_transaction_seq.nextval into v_transid from dual; select story_transaction_seq.currval into v_value2 from dual; --debug code . END when I ran this, v_value1 had a 200, v_transid had a 201, and v_value2 had a 202 taking the debug code out and hitting that procedure with a web page will increment the v_transid by 2 each refresh of the page. am i doing something wrong? I can't find any information on the web related to this issue. TIA Danny Hughes DBA Knobias.com 601-978-3399 x103 [EMAIL PROTECTED] www.knobias.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danny Hughes 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 - procedure - APOLOGY
I apologize for my dirty post here, Roland. I have had a bad day. I'm sorry. Jan Pruner To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: PL/SQL - procedure Dear Roland, first try to use your brain than ask in this group. Yesterday I've posted email about Exceptions in Oracle (to catch an error) and the tip - don't use datatype DATE to store start/end time. OK, the second tip: PROCEDURE my_procedure ( ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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 - procedure
Jan, It isn't necessary to be insulting. If you don't like reading or answering his posts, just delete them. Jared Jan Pruner [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/26/02 04:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: PL/SQL - procedure Dear Roland, first try to use your brain than ask in this group. Yesterday I've posted email about Exceptions in Oracle (to catch an error) and the tip - don't use datatype DATE to store start/end time. OK, the second tip: PROCEDURE my_procedure ( ... -- 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 - procedure
Hallo, Can anyone give me an example on a pl/sql code, which does the following: I have 4 procedures, and I want the following to be logged in a status table. Procedure names Start_time of procedure End_time_of procedure Error_code(if anything goes wrong) Error_message Please give me example onhow to write the code and also tell me how to pick out the procedure_name, start_time of_procedure, end_time_ptocedure, error_code, error_message. Thanks in advance.And just dontgive me a link, i wantthis real example too. 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 - procedure
Dear Roland, first try to use your brain than ask in this group. Yesterday I've posted email about Exceptions in Oracle (to catch an error) and the tip - don't use datatype DATE to store start/end time. OK, the second tip: PROCEDURE my_procedure ( COMMIT; RETURN; EXCEPTION WHEN OTHERS THEN ROLLBACK; INSERT INTO status_table VALUES( strt_dt, end_dt ,'my_procedure','OTHERS', err_mesage); COMMIT; END; JP On Tue 26. March 2002 12:18, you wrote: Hallo, Can anyone give me an example on a pl/sql code, which does the following: I have 4 procedures, and I want the following to be logged in a status table. Procedure names Start_time of procedure End_time_of procedure Error_code(if anything goes wrong) Error_message Please give me example onhow to write the code and also tell me how to pick out the procedure_name, start_time of_procedure, end_time_ptocedure, error_code, error_message. Thanks in advance.And just dontgive me a link, i wantthis real example too. Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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: Ang: Re: Ang: Re: PL/SQL procedure - error
I believe you will need to code it into your procedure i.e. beginning of proc get start time (via sql below) do work get end time then either store the elapsed time somewhere, or print it out. -Joe --- [EMAIL PROTECTED] wrote: But which query asks for the start date of the procedure an dwhich selectstatement is to display th etime when th eprocedure starts. Roland [EMAIL PROTECTED]@fatcity.com den 2002-01-22 08:25 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Kopia: Use SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS FROM dual; Rick Roland.Skoldb [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: Ang: Re: PL/SQL procedure - error com 01/22/2002 09:15 AM Please respond to ORACLE-L Thanks, and can you please tell me how to get hold of the time when the procedures starts and finishes.? Thanks in advance. Roland Pullikol Kumar [EMAIL PROTECTED]@fatcity.com den 2002-01-22 04:40 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Kopia: hi Roland Hope below code will help u, BEGIN p1; p2; p3; p4; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; procedure p1 is BEGIN insert into aa values (1,'1'); END; procedure p2 is BEGIN insert into aa values (2,'2'); END; procedure p3 is BEGIN insert into aa values (3,'3'); END; procedure p4 is BEGIN insert into aa values (4,'4'); END; Nitheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet
RE: Re: Ang: Re: PL/SQL procedure - error
Just put the select before the activation of each procedure in your program and after the last one: BEGIN SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS') FROM dual; p1; SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS') FROM dual; p2; SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS') FROM dual; p3; SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS') FROM dual; p4; SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS') FROM dual; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Thu, January 24, 2002 3:00 PM To: Multiple recipients of list ORACLE-L Subject: Ang: Re: Ang: Re: PL/SQL procedure - error But which query asks for the start date of the procedure an dwhich selectstatement is to display th etime when th eprocedure starts. Roland [EMAIL PROTECTED]@fatcity.com den 2002-01-22 08:25 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Kopia: Use SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS FROM dual; Rick Roland.Skoldb [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: Ang: Re: PL/SQL procedure - error com 01/22/2002 09:15 AM Please respond to ORACLE-L Thanks, and can you please tell me how to get hold of the time when the procedures starts and finishes.? Thanks in advance. Roland Pullikol Kumar [EMAIL PROTECTED]@fatcity.com den 2002-01-22 04:40 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Kopia: hi Roland Hope below code will help u, BEGIN p1; p2; p3; p4; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; procedure p1 is BEGIN insert into aa values (1,'1'); END; procedure p2 is BEGIN insert into aa values (2,'2'); END; procedure p3 is BEGIN insert into aa values (3,'3'); END; procedure p4 is BEGIN insert into aa values (4,'4'); END; Nitheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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
PL/SQL procedure - error
Hallo, anyone who can help me with a pl/sql procedure that I have four procedures that will run one after one: in an error table logs the procedurename, the start time of the procedure and the end time of the procedure.the number of rows inserted(there are insertstatements in the procedures), error code(if anything goes wrong) and error-message. Let us say that procedure 1 goes fine and the next procedure(nr 2) fails. Then I want to run rollback so that no data were inserted by procedure one at all, so it should be a rollback, so no data at all were inserted by any previous procedure. Please help me with some pl/sql code on this: 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 procedure - error
hi Roland Hope below code will help u, BEGIN p1; p2; p3; p4; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; procedure p1 is BEGIN insert into aa values (1,'1'); END; procedure p2 is BEGIN insert into aa values (2,'2'); END; procedure p3 is BEGIN insert into aa values (3,'3'); END; procedure p4 is BEGIN insert into aa values (4,'4'); END; Nitheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar 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 procedure - error
Thanks, and can you please tell me how to get hold of the time when the procedures starts and finishes.? Thanks in advance. Roland Pullikol Kumar [EMAIL PROTECTED]@fatcity.com den 2002-01-22 04:40 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Kopia: hi Roland Hope below code will help u, BEGIN p1; p2; p3; p4; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; procedure p1 is BEGIN insert into aa values (1,'1'); END; procedure p2 is BEGIN insert into aa values (2,'2'); END; procedure p3 is BEGIN insert into aa values (3,'3'); END; procedure p4 is BEGIN insert into aa values (4,'4'); END; Nitheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar 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: DDL alter in execute immediate pl/sql procedure - dynamic sql
Hi If I am reading this right you are building a PL/SQL block and trying to do and execute immediate on this, you can biuld the bloc as normal anf then create strings of the actual SQL you need to execute and then EXECUTE MMEDIATE thestring Sometiung like this works PROCEDURE P_CALENDAR_UPDATE IS --= v_Average NUMBER; v_Count NUMBER; v_Dcount NUMBER; v_Min NUMBER; v_Max NUMBER; v_Variance NUMBER; v_Sum NUMBER; p_part INTEGER; part_count NUMBER; --= BEGIN -- part_count := 0; v_CreateString := 'CREATE GLOBAL TEMPORARY TABLE t_tmp_cal2 ( v_part NUMBER) ON COMMIT DELETE ROWS'; EXECUTE IMMEDIATE v_CreateString; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -955 THEN RAISE; ELSE v_TruncString := 'TRUNCATE TABLE t_tmp_cal2'; EXECUTE IMMEDIATE v_TruncString; END IF; v_CreateString := 'INSERT INTO t_tmp_cal2 (SELECT DISTINCT v_part from t_tmp_cal1)' ; EXECUTE IMMEDIATE v_CreateString; v_SelectString := 'SELECT COUNT(v_part)from t_tmp_cal2' ; EXECUTE IMMEDIATE v_SelectString INTO part_count; statistics_loop FOR counter IN 1..part_count LOOP v_SelectString := 'SELECT v_part FROM t_tmp_cal2 where dbms_rowid.rowid_row_number(rowid) = (:1 - 1)'; EXECUTE IMMEDIATE v_SelectString INTO p_part USING counter; v_SelectString := 'SELECT STDDEV(READINGS) FROM t_tmp_cal1 WHERE v_part = :1'; EXECUTE IMMEDIATE v_SelectString INTO v_Stddev USING p_part; v_SelectString := 'SELECT COUNT(READINGS) FROM t_tmp_cal1 WHERE v_part = :1'; EXECUTE IMMEDIATE v_SelectString INTO v_Count USING p_part; v_SelectString := 'SELECT AVG(READINGS) FROM t_tmp_cal1 WHERE v_part = :1'; EXECUTE IMMEDIATE v_SelectString INTO v_Average USING p_part; v_SelectString := 'SELECT MIN(READINGS) FROM t_tmp_cal1 WHERE v_part = :1'; EXECUTE IMMEDIATE v_SelectString INTO v_Min USING p_part; v_SelectString := 'SELECT SUM(READINGS) FROM t_tmp_cal1 WHERE v_part = :1'; EXECUTE IMMEDIATE v_SelectString INTO v_Sum USING p_part; v_SelectString := 'SELECT MAX(READINGS) FROM t_tmp_cal1 WHERE v_part = :1'; EXECUTE IMMEDIATE v_SelectString INTO v_Max USING p_part; v_SelectString := 'SELECT VARIANCE(READINGS) FROM t_tmp_cal1 WHERE v_part = :1'; EXECUTE IMMEDIATE v_SelectString INTO v_Variance USING p_part; v_SelectString := 'SELECT COUNT(READINGS) FROM t_tmp_cal1 WHERE v_part = :1 AND READINGS IS NOT NULL'; EXECUTE IMMEDIATE v_SelectString INTO v_Dcount USING p_part; HTH -- = Peter McLartyE-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = Hagedorn, Linda [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 29/12/2001 05:55 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Fax to: Subject:DDL alter in execute immediate pl/sql procedure - dynamic sql If someone has a few minutes to read through this, I'd be most appreciative. I could use a second set of eyes looking this over. This procedure is designed to maintain a table/sequence map, executed after an import and increment any sequences which have a lower nextval that the max value in the column it's supposed to be matching. Often the sequences are out-of-sync after an import (even full=y and direct=y) and we have to manually adjust them. This is an effort to automate the process. These are the displays and error from the procedure, and the code follows. The problem is in the execute immediate which is doing DDL. It's Oracle 8.1.7 on Solaris so DDL in execute immediate alter is supposed to work. The execute immediate insert does work. Any suggestions or comments are welcome. Thanks, Linda top of loop, counter is:1 top of loop, counter is:2 top of loop, counter is:3 top of loop, counter is:4 top of loop, counter is:5 top of loop, counter is:6 top of loop, counter is:7 top of loop, counter is:8 top of loop, counter is:9 top of loop, counter is:10 top of loop, counter is:11 top of loop, counter is:12 Show l_sql_string 4 DECLARE L_SEQUENCE_OWNER VARCHAR2(30):= :1; L_SEQUENCE_NAME VARCHAR2(30):= :2; BEGIN insert into dbauser.table_sequence_map values (:1,:2, null,null,null,sysdate,sysdate); END; top of loop, counter is:13 top of loop, counter is:14 top of loop, counter is:15
Re: DDL alter in execute immediate pl/sql procedure - dynamic sql
Hagedorn, Linda wrote: If someone has a few minutes to read through this, I'd be most appreciative. I could use a second set of eyes looking this over. This procedure is designed to maintain a table/sequence map, executed after an import and increment any sequences which have a lower nextval that the max value in the column it's supposed to be matching. Often the sequences are out-of-sync after an import (even full=y and direct=y) and we have to manually adjust them. This is an effort to automate the process. These are the displays and error from the procedure, and the code follows. The problem is in the execute immediate which is doing DDL. It's Oracle 8.1.7 on Solaris so DDL in execute immediate alter is supposed to work. The execute immediate insert does work. Any suggestions or comments are welcome. Thanks, Linda Linda, DDL is forbidden in PL/SQL - except in some EXECUTE IMMEDIATE statement (or when using the older DBMS_SQL package) but as a SINGLE statement. When you are executing your statement you are trying to execute an anonymous block - which happens to contain a DDL statement, which is forbidden in PL/SQL (sorry, looping). I have never tried it but there is no reason why it shouldn't work, an EXECUTE IMMEDIATE within the EXECUTE IMMEDIATE is probably what you shoud try, i. e. something like : my_statement := 'begin' || chr(10) || 'execute immediate ''' || 'fancy DDL here' || '''; end;'; execute immediate :my_statement; Another solution would be to simplify the logic, but I am in Europe and currently a bit tired to suggest something reasonably intelligent. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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 procedure to return multiple rows
Dear DBAs ! I need to write a stored proc that will do the following : -- open a cursor ; -- loop over the rows returned by the cursor , filter out some of the rows that do not match a certain criteria , do some processing on other rows (that match the criteria) and then return those rows (the processed data) to the calling program ( a PERL DBI script or a java program ) . Ho do U return those multiple rows to a calling program ? It's Oracle 8.0.5 , NOT 8i :-( Thanks a lot in advance, DBAndrey Andrey, I have recently written a paper with Robert Goral showing how to map a view on any PL/SQL table. It should work with 8.0.5, provided that you have the object option installed. You may find it at http://www.oramag.com, and it's complete with a small example. It may not be exactly what you are looking for but might be an interesting alternative. -- Regards, Stephane Faroult email: [EMAIL PROTECTED] Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 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 Faroult 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 procedure to return multiple rows
Dear DBAs ! I need to write a stored proc that will do the following : -- open a cursor ; -- loop over the rows returned by the cursor , filter out some of the rows that do not match a certain criteria , do some processing on other rows (that match the criteria) and then return those rows (the processed data) to the calling program ( a PERL DBI script or a java program ) . Ho do U return those multiple rows to a calling program ? It's Oracle 8.0.5 , NOT 8i :-( Thanks a lot in advance, DBAndrey * 03-9254520 * 053-464562 * mailto:[EMAIL PROTECTED] -- 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: PL/SQL procedure to return multiple rows
hi Andrew, consider using a array (pl/sql table) as a return datatype of your function. Inside the function open ur cursor and populate that set of rows u wanna return into the return datatype.. which would be an array in this case. note that u must define a global array in some package first so that u could thereafter define and start using the array as a return datatype of ur function. if all the above sounds mumbo jumbo to u then let me know .. i will send e.g. 2 u next time;) hth deepak --- Andrey Bronfin [EMAIL PROTECTED] wrote: Dear DBAs ! I need to write a stored proc that will do the following : -- open a cursor ; -- loop over the rows returned by the cursor , filter out some of the rows that do not match a certain criteria , do some processing on other rows (that match the criteria) and then return those rows (the processed data) to the calling program ( a PERL DBI script or a java program ) . Ho do U return those multiple rows to a calling program ? It's Oracle 8.0.5 , NOT 8i :-( Thanks a lot in advance, DBAndrey * 03-9254520 * 053-464562 * mailto:[EMAIL PROTECTED] -- 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). __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal 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 procedure to return multiple rows
Audrey: What you want is a cursor variable. A cursor variable references a cursor object and may refer to different SQL statements at different times. In your case, the most important benefit of the cursor variable would be that it provides a mechanism for passing results of queries (the rows returned by fetches against a cursor) between different PL/SQL programs. Jon Walthour -Original Message- Sent: Sunday, September 16, 2001 2:55 PM To: Multiple recipients of list ORACLE-L Dear DBAs ! I need to write a stored proc that will do the following : -- open a cursor ; -- loop over the rows returned by the cursor , filter out some of the rows that do not match a certain criteria , do some processing on other rows (that match the criteria) and then return those rows (the processed data) to the calling program ( a PERL DBI script or a java program ) . Ho do U return those multiple rows to a calling program ? It's Oracle 8.0.5 , NOT 8i :-( Thanks a lot in advance, DBAndrey * 03-9254520 * 053-464562 * mailto:[EMAIL PROTECTED] -- 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: Walthour, Jon (GEAE, Compaq) 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).
