PL/SQL Help
The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>
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).
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).
PL/SQL help - need some quick help
Hallo, I have some trouble to get this pl/sql code right. Anyone whpom could help me with this. It is important. Hallo, I have some trouble with this pl/sql procedure. I would like that this lvsql to be run only if the field Borttags_flagg = 0 but i get an error in the if statement, whatis wrong with this? It get the erromressage: LS-00103: Encountered the symbol "||AvdNr||" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like betwe.. when I compile the whole procedure. Please help me with this. (If the borttags_flagg = 1 then it will continue the loop and check for next one. etc... If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' || then (this lvsql is to be run only if field borttags_flagg = 0) lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.BORTTAGS_FLAGG,ICA_ARTIKEL.PANTBELOPP ' || --PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' || 'FROM A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' || --PBK.LPKORGEANREL ' || 'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' ' || 'AND ICA_ARTIKEL.DATUM testplsql.SQL Description: Binary data
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).
PL/SQL help
Hello, 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 worlds 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).
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 worlds 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).
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).
Pl/sql help needed
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 do this, 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).
Pl/sql help needed
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 do this, 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).
Need some pl/sql help
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 do this, 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).