RE: PL/SQL help
Title: RE: PL/SQL help Go to Tom Kyte's sight and look for contexts - he explains a way to build sql statements using native dynamic sql and refcursors and contexts where the select statement and predicate is variable and the output might also be variable. I get there by searching www.yahoo.com for asktom -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 2:06 PM To: Multiple recipients of list ORACLE-L Subject: RE: PL/SQL help David- I've thought of both dbms_sql and dummy conditions too. Oracle Application reports use the dummy conditions all the time. I'm off to see if you can set cursor_sharing at the session level and not use bind params. I'm assuming no one else has figured this out either due to the lack of response. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 09/20/02 01:28 AM cc: Please respond to Subject: RE: PL/SQL help ORACLE-L Ron We've run into this problem and haven't really found a satisfactory answer. It sounds like a job for dbms_sql, but the docs say its slower than native dynamic sql. Has anyone tested this? One other alternative I can think of (but have never tried) is to use dummy conditions in the sql; something like: - c_sql := 'select col1 from atable where col2 = :1 and :2 is null and :3 is null'; -- David Lord > -Original Message- > From: Ron Thomas [mailto:[EMAIL PROTECTED]] > Sent: 19 September 2002 19:53 > To: Multiple recipients of list ORACLE-L > Subject: PL/SQL help > > > > I am building a dynamic sql statement which will contain > varying number of bind variables depending > on user selection criteria. As an example, the sql statement may be: > > c_sql := 'select col1 from atable where col2 = :1' ; > > or it may be > > c_sql := 'select col1 from atable where col2 = :1 and col2 > between :2 and :3' ; > > or it may be ... etc. I am trying to avoid ugly code such as: > > IF case1 THEN > OPEN csr FOR c_sql USING var1 ; > ELSIF case2 THEN > OPEN csr FOR c_sql USING var1, var2, var3 ; > ELSIF . > END IF ; > > Once the sql statement is created, it will be opened/closed > multiple times, so I want to use bind > variables to avoid parsing. > > So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, > but in the various incarnations I tried, > could not get it to work. The FM have not been much help > (still looking tho). > > What am I missing? > > Thanks, > Ron Thomas > Hypercom, Inc > [EMAIL PROTECTED] > Each new user of a new system uncovers a new class of bugs. > -- Kernighan > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ron Thomas > 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 message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirm
RE: PL/SQL help
David- I've thought of both dbms_sql and dummy conditions too. Oracle Application reports use the dummy conditions all the time. I'm off to see if you can set cursor_sharing at the session level and not use bind params. I'm assuming no one else has figured this out either due to the lack of response. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 09/20/02 01:28 AMcc: Please respond to Subject: RE: PL/SQL help ORACLE-L Ron We've run into this problem and haven't really found a satisfactory answer. It sounds like a job for dbms_sql, but the docs say its slower than native dynamic sql. Has anyone tested this? One other alternative I can think of (but have never tried) is to use dummy conditions in the sql; something like: - c_sql := 'select col1 from atable where col2 = :1 and :2 is null and :3 is null'; -- David Lord > -Original Message- > From: Ron Thomas [mailto:[EMAIL PROTECTED]] > Sent: 19 September 2002 19:53 > To: Multiple recipients of list ORACLE-L > Subject: PL/SQL help > > > > I am building a dynamic sql statement which will contain > varying number of bind variables depending > on user selection criteria. As an example, the sql statement may be: > > c_sql := 'select col1 from atable where col2 = :1' ; > > or it may be > > c_sql := 'select col1 from atable where col2 = :1 and col2 > between :2 and :3' ; > > or it may be ... etc. I am trying to avoid ugly code such as: > > IF case1 THEN > OPEN csr FOR c_sql USING var1 ; > ELSIF case2 THEN > OPEN csr FOR c_sql USING var1, var2, var3 ; > ELSIF . > END IF ; > > Once the sql statement is created, it will be opened/closed > multiple times, so I want to use bind > variables to avoid parsing. > > So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, > but in the various incarnations I tried, > could not get it to work. The FM have not been much help > (still looking tho). > > What am I missing? > > Thanks, > Ron Thomas > Hypercom, Inc > [EMAIL PROTECTED] > Each new user of a new system uncovers a new class of bugs. > -- Kernighan > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ron Thomas > 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 message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Ma
RE: PL/SQL help
Ron We've run into this problem and haven't really found a satisfactory answer. It sounds like a job for dbms_sql, but the docs say its slower than native dynamic sql. Has anyone tested this? One other alternative I can think of (but have never tried) is to use dummy conditions in the sql; something like: - c_sql := 'select col1 from atable where col2 = :1 and :2 is null and :3 is null'; -- David Lord > -Original Message- > From: Ron Thomas [mailto:[EMAIL PROTECTED]] > Sent: 19 September 2002 19:53 > To: Multiple recipients of list ORACLE-L > Subject: PL/SQL help > > > > I am building a dynamic sql statement which will contain > varying number of bind variables depending > on user selection criteria. As an example, the sql statement may be: > > c_sql := 'select col1 from atable where col2 = :1' ; > > or it may be > > c_sql := 'select col1 from atable where col2 = :1 and col2 > between :2 and :3' ; > > or it may be ... etc. I am trying to avoid ugly code such as: > > IF case1 THEN > OPEN csr FOR c_sql USING var1 ; > ELSIF case2 THEN > OPEN csr FOR c_sql USING var1, var2, var3 ; > ELSIF . > END IF ; > > Once the sql statement is created, it will be opened/closed > multiple times, so I want to use bind > variables to avoid parsing. > > So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, > but in the various incarnations I tried, > could not get it to work. The FM have not been much help > (still looking tho). > > What am I missing? > > Thanks, > Ron Thomas > Hypercom, Inc > [EMAIL PROTECTED] > Each new user of a new system uncovers a new class of bugs. > -- Kernighan > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ron Thomas > 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 message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL help
i had similar situation, 1. wrote .sql script which has 'set serveroutput on 'in it 2.set it in glogin.sql > -Original Message- > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > Sent: Saturday, February 23, 2002 3:39 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: PL/SQL help > > 1. there isn't a host command in PL/SQL. Read up on external > procedures for this. You need to be painfully aware of the > security implications of this. You need to ask yourself if this > is really necessary. If so, you can write external procs in > Java, C, or even Perl ( that last one isn't well documented... yet ) > > > 2. DBMS_OUTPUT: read up on the docs for it. there's a > built in procedure for this, dbms_output.enable. > > Jared > > > > > > "k k" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 02/22/02 01:13 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:PL/SQL help > > > Hello, > > How I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ?? > Also is there a way to issue set serveroutput on from within a stored > procedure so that when someone run the stored procedure, the stored > procedure output will be seen even if the person forgot to set > serveroutput > on for their sql*plus session ?? > > TIA > > K > > > _ > Join the world's largest e-mail service with MSN Hotmail. > http://www.hotmail.com > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: k k > 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: Ghadge,Sameer 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 help
> Begin
> dbms_output.enable(1);
> dbms_output.put_line('Jared is wrong!');
> end;
>/
Oh well, ain't the first time. :)
I always put 'set serveroutput on size 100'
in glogin.sql
Jared
"Alec Macdonell" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/22/02 04:33 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: PL/SQL help
I do not know a way to enable DBMS from within a procedure either. The
second item Jared mentions does not seem to set the session to recieve
output. I just ran a check to see and the 'enable' portion of DBMS did not
set the toggle the session for output.
I used this simple script
Begin
dbms_output.enable(1);
dbms_output.put_line('Jared is wrong!');
end;
/
IF you run this script you will recieve the procedure completed
successfully
message. If you the issue a 'set serveroutput on' and run again you will
recieve the output text twice.
If anyone knows how get DBMS to output without the serveroutput option
being
explicitly set I would love to hear it.
Alec
-Original Message-
[EMAIL PROTECTED]
Sent: Friday, February 22, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L
1. there isn't a host command in PL/SQL. Read up on external
procedures for this. You need to be painfully aware of the
security implications of this. You need to ask yourself if this
is really necessary. If so, you can write external procs in
Java, C, or even Perl ( that last one isn't well documented... yet )
2. DBMS_OUTPUT: read up on the docs for it. there's a
built in procedure for this, dbms_output.enable.
Jared
"k k" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/22/02 01:13 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc:
Subject:PL/SQL help
Hello,
How I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ??
Also is there a way to issue set serveroutput on from within a stored
procedure so that when someone run the stored procedure, the stored
procedure output will be seen even if the person forgot to set
serveroutput
on for their sql*plus session ??
TIA
K
_
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: k k
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: Alec Macdonell
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: PL/SQL help
I do not know a way to enable DBMS from within a procedure either. The
second item Jared mentions does not seem to set the session to recieve
output. I just ran a check to see and the 'enable' portion of DBMS did not
set the toggle the session for output.
I used this simple script
Begin
dbms_output.enable(1);
dbms_output.put_line('Jared is wrong!');
end;
/
IF you run this script you will recieve the procedure completed successfully
message. If you the issue a 'set serveroutput on' and run again you will
recieve the output text twice.
If anyone knows how get DBMS to output without the serveroutput option being
explicitly set I would love to hear it.
Alec
-Original Message-
[EMAIL PROTECTED]
Sent: Friday, February 22, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L
1. there isn't a host command in PL/SQL. Read up on external
procedures for this. You need to be painfully aware of the
security implications of this. You need to ask yourself if this
is really necessary. If so, you can write external procs in
Java, C, or even Perl ( that last one isn't well documented... yet )
2. DBMS_OUTPUT: read up on the docs for it. there's a
built in procedure for this, dbms_output.enable.
Jared
"k k" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/22/02 01:13 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:PL/SQL help
Hello,
How I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ??
Also is there a way to issue set serveroutput on from within a stored
procedure so that when someone run the stored procedure, the stored
procedure output will be seen even if the person forgot to set
serveroutput
on for their sql*plus session ??
TIA
K
_
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: k k
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: Alec Macdonell
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 help
Re the use of PIPE's to execute commands:
There's a page on my website on using DBMS_PIPE and perl
for debugging PL/SQL. A slight change is all that's need to make
it execute a command.
Simple to do, and fraught with security implications.
http://www.cybcon.com/~jkstill/util/debug_pipe/debug_pipe.html
Jared
Jonathan Gennick <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/22/02 02:28 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Re: PL/SQL help
You can't SET SERVEROUTPUT ON from within a PL/SQL block,
because that's a SQL*Plus command, not a PL/SQL function.
You *can* invoke dbms_output.enable(), but that still
doesn't set the flag within SQL*Plus that tells SQL*Plus to
fetch and display the output.
If you tend to forget a lot, you could issue the SET
SERVEROUTPUT ON command from your SQL*Plus login file.
I know of no way to directly execute commands from within a
PL/SQL block. I have heard of using DBMS_PIPE to send
commnands to a daemon (written in a language such as C) that
executes commands on your behalf. I've never actually done
that myself, but I can see where it's possible.
Best regards,
Jonathan Gennick
mailto:[EMAIL PROTECTED] * 906.387.1698
http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com
Friday, February 22, 2002, 4:13:19 PM, you wrote:
kk> I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ??
kk> Also is there a way to issue set serveroutput on from within a stored
kk> procedure so that when someone run the stored procedure, the stored
kk> procedure output will be seen even if the person forgot to set
serveroutput
kk> on for their sql*plus session ??
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Gennick
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: PL/SQL help
You can't SET SERVEROUTPUT ON from within a PL/SQL block, because that's a SQL*Plus command, not a PL/SQL function. You *can* invoke dbms_output.enable(), but that still doesn't set the flag within SQL*Plus that tells SQL*Plus to fetch and display the output. If you tend to forget a lot, you could issue the SET SERVEROUTPUT ON command from your SQL*Plus login file. I know of no way to directly execute commands from within a PL/SQL block. I have heard of using DBMS_PIPE to send commnands to a daemon (written in a language such as C) that executes commands on your behalf. I've never actually done that myself, but I can see where it's possible. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com Friday, February 22, 2002, 4:13:19 PM, you wrote: kk> I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ?? kk> Also is there a way to issue set serveroutput on from within a stored kk> procedure so that when someone run the stored procedure, the stored kk> procedure output will be seen even if the person forgot to set serveroutput kk> on for their sql*plus session ?? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick 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 help
1. there isn't a host command in PL/SQL. Read up on external procedures for this. You need to be painfully aware of the security implications of this. You need to ask yourself if this is really necessary. If so, you can write external procs in Java, C, or even Perl ( that last one isn't well documented... yet ) 2. DBMS_OUTPUT: read up on the docs for it. there's a built in procedure for this, dbms_output.enable. Jared "k k" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 02/22/02 01:13 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:PL/SQL help Hello, How I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ?? Also is there a way to issue set serveroutput on from within a stored procedure so that when someone run the stored procedure, the stored procedure output will be seen even if the person forgot to set serveroutput on for their sql*plus session ?? TIA K _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: k k INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Pl/SQL help - please help me
I suggest you go buy yourself a good PL/SQL manual. Nothing that you are asking for here is all that complicated. I recommend the O'Reilly book "The Oracle PL/SQL CD Bookshelf". -Original Message- [EMAIL PROTECTED] Sent: Sunday, January 27, 2002 9:15 PM To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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 help - please help me
PROCEDURE A as
stack_info VARCHAR2(4096);
BEGIN
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('Start-B -'||lv_time);
B;
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('End-B -'||lv_time);
EXCEPTION
WHEN OTHERS THEN
stack_info:=DBMS_UTILITY.FORMAT_CALL_STACK;
dbms_output.put_line(stack_info);
END;
-
PROCEDURE B as
stack_info VARCHAR2(4096);
BEGIN
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('Start-C -'||lv_time);
C;
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('End-C -'||lv_time);
EXCEPTION
WHEN OTHERS THEN
stack_info:=DBMS_UTILITY.FORMAT_CALL_STACK;
dbms_output.put_line(stack_info);
END;
--
PROCEDURE C as
stack_info VARCHAR2(4096);
i NUMBER :=0;
BEGIN
-- Division By Zero will Rise
INSERT INTO ttt VALUES(7/0);
EXCEPTION WHEN OTHERS THEN
stack_info:=DBMS_UTILITY.FORMAT_CALL_STACK;
dbms_output.put_line(stack_info);
- The error code _The errormessage
dbms_output.put_line(TO_CHAR(SQLCODE)||'-'||SQLERRM);
END;
- pick out the name of the procedure that is currently running,
- pick out the start_time of the procedure
- pick out the end_time of the procedure
This eg. will do Above Question.
set serveroutput on
BEGIN
lv_time varchar2(60);
DECLARE
BEGIN
-- Procedure A Start Time
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('Start- A -'||lv_time);
A
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('End- A -'||lv_time);
-- Procedure A Start Time
END;
END;
-
CREATE TABLE ttt
(
f1 NUMBER
)
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).
