pls-00553 when 'Set Serverout On' in glogin.sql
(reposting)
--
List,
Are we not supposed to 'Set Serveroutput On' in the glogin.sql file?
If the parameter is set in glogin.sql, I'm getting the following error when
using DBMS_OUTPUT in pl/sql
SQL Create or Replace Procedure TEST1 as
Begin
DBMS_OUTPUT.PUT_LINE('TEST');
End;
Compiles ok, but presents error during run-time.
PLS-00553 character set name is not recognized
(8.1.6.0 on NT4 SP6; am testing this on a small DB/OS Server)
Regards
Madhu
_
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: Madhusudhanan Sampath
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).
pls-00553 when Set Serverout On in glogin.sql
List,
Are we not supposed to 'Set Serveroutput On' in the glogin.sql file?
I'm getting the following error when using DBMS_OUTPUT in pl/sql if the
parameter is set in glogin.sql.
SQL Create or Replace Procedure TEST1 as
Begin
DBMS_OUTPUT.PUT_LINE('TEST');
End;
Compiles ok, but presents error during run-time.
PLS-00553 character set name is not recognized
(Environment is 8.1.6 on NT4 SP6; am testing this on a small DB/OS Server)
Regards
Madhu
_
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Madhusudhanan Sampath
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: Data Warehouse experts, a simple question for you| Outdated?
Any comments on the book 'Oracle 8i Data Warehousing' by Michael Corey, Michael Abbey, Ian Abramson and Ben Taub (Oracle Press) ? Thanks and Regards Madhu From: DENNIS WILLIAMS [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Data Warehouse experts, a simple question for you| Outdated? Date: Thu, 23 May 2002 07:33:26 -0800 Rachel - Glad Inmon's book is working for you. I have only read (or more correctly attempted to read) his articles, which can be found at http://www.datawarehousing.com/, or at least they previously were available. - Just be aware that when you switch from reading Inmon or one of his followers to Kimball or one of his followers, that the meaning of some terms change. - The oldest Kimball articles at http://www.intelligententerprise.com/ports/search_webhouse.shtml are the best to start with because they describe the fundamentals of data warehouse design. - I still think the email list is one of the best resources. For help with list commands, send a message to mailto:[EMAIL PROTECTED] with the word help in the body of the message. (I'm listing these for the benefit of others on this list) Not to discourage you, but companies often take the approach of yours, and hire consultants to build the site. They tend to go into a corner and develop it and then unveil it when they are finished, collect their check and leave. If you ask questions, it is easy for them to blow past you because they are the experts. So from that standpoint, don't panic, just go along for the ride and what you can learn. But it is good to read up on warehousing so you can ask intelligent questions and don't sound like a dinosaur by asking questions like whaddya mean it isn't normalized?. In DW, the real participants are the ones that interview the potential users and try to locate data the users will find useful. The DBA tends to be the one that gets ordered to load 100-gig of data every night. DW work is like a lot of other DBA work, but quite different in some respects. At least with the email list, if something sounds odd, you can ask some real people for some input. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] _ 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: Madhusudhanan Sampath 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: monitoring multiple databases using PL/SQL
From 'Oracle Database Monitoring for the Beginner' (pdf) - Chris Grabowy In a centralized configuration, the monitoring software scripts reside on one server. This obviously makes maintenance easier, but if the hosting server fails then there is no (more) monitoring of the databases regards Madhu From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: monitoring multiple databases using PL/SQL Date: Tue, 23 Apr 2002 08:00:33 -0800 Greetings - I am planning to centralize our Oracle monitoring process by using one PL/SQL procedure to query database extents, invalid objects, alert logs etc through database links. I wonder if anybody has done it before and if there is any cons with it. The pros would be ease of administration, ease of standardization etc. TIA Dennis -- 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). _ 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: Madhusudhanan Sampath 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: RE: refcursor rowcount check
Tom, Thanks for taking time off to reply. I had wanted two things - To check rowcount (to enable returning a code for no-rows-found) and secondly, to avoid hitting the database more than once for the same kind of query. I hope you agree that your method also hits db twice. (I open the cursor twice, you do a count once and then open the cursor). This method would not help me scale for bigger data sets and more complex queries. I posted the same question to Thomas Kyte (asktom.oracle.com) and he advises to pass on 'No-rows-found' checking to the calling program. This would avoid any redundant db hits and help scalability. As of now I have decided to adopt this approach - * Perform validation of input parameter * Do a normal fetch of all candidate rows into a temporary table * check the temporary table for count. (this would be a comparitively smaller set) * return ref cursor with either error code or result set. This would allow me to (a) avoid redundant hits (b) adhere to the pre-agreed interface of passing either recordsets or business-rule-error-codes. Thanks again. Madhu From: Mercadante, Thomas F [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: refcursor rowcount check Date: Mon, 22 Apr 2002 05:03:22 -0800 -Madhu How about the following: create or replace PROCEDURE Get_Emp_Rows (EmpCur IN OUT GenPack.GenCurTyp, Nstr Varchar2) IS cname Emp.Name%type; rec_count number; -- == I added this BEGIN select count(*) into rec_count -- == I added these FROM Emp where name = Nstr; -- OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; -- FETCH EmpCur into cname; --DBMS_OUTPUT.PUT_LINE(cname); --displays first row for test IF rec_count = 0 then --- EmpCur%rowcount=0 then-- I changed this OPEN EmpCur FOR SELECT 'W001' from dual; ELSE OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; End If; END Get_Emp_Rows; Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Saturday, April 20, 2002 3:18 AM To: Multiple recipients of list ORACLE-L List, I'm having a small problem while checking row count parameter in a refcursor. A stored procedure accepts parameters and returns refcursors; if no candidate rows are found, then an error code is returned to the calling program. The same cursor variable is used to retrun the rowset or error code. To check if any rows are returned, I use the ROWCOUNT attribute of the cursor variable. Rowcount is not available till I do the first fetch. However the fetch removes the first row from the recordset, in case any rows are present. The 'OUT' variable returned to the calling program has one row less than actual. How to prevent this? Is there any other better way to check if rows are present? Presently, I work around by opening the cursor again. But surely this won't hold out for bigger data sets and complex queries. create or replace PROCEDURE Get_Emp_Rows (EmpCur IN OUT GenPack.GenCurTyp, Nstr Varchar2) IS cname Emp.Name%type; BEGIN OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; FETCH EmpCur into cname; DBMS_OUTPUT.PUT_LINE(cname); --displays first row for test IF EmpCur%rowcount=0 then OPEN EmpCur FOR SELECT 'W001' from dual; ELSE OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; End If; END Get_Emp_Rows; Thanks for your time. regards -Madhu _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath 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: RE: refcursor rowcount check
EmpCur FOR SELECT name FROM Emp where name = Nstr; End If; END Get_Emp_Rows; Thanks for your time. regards -Madhu _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath 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: Mercadante, Thomas F 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). _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath 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).
refcursor rowcount check
List, I'm having a small problem while checking row count parameter in a refcursor. A stored procedure accepts parameters and returns refcursors; if no candidate rows are found, then an error code is returned to the calling program. The same cursor variable is used to retrun the rowset or error code. To check if any rows are returned, I use the ROWCOUNT attribute of the cursor variable. Rowcount is not available till I do the first fetch. However the fetch removes the first row from the recordset, in case any rows are present. The 'OUT' variable returned to the calling program has one row less than actual. How to prevent this? Is there any other better way to check if rows are present? Presently, I work around by opening the cursor again. But surely this won't hold out for bigger data sets and complex queries. create or replace PROCEDURE Get_Emp_Rows (EmpCur IN OUT GenPack.GenCurTyp, Nstr Varchar2) IS cname Emp.Name%type; BEGIN OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; FETCH EmpCur into cname; DBMS_OUTPUT.PUT_LINE(cname); --displays first row for test IF EmpCur%rowcount=0 then OPEN EmpCur FOR SELECT 'W001' from dual; ELSE OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; End If; END Get_Emp_Rows; Thanks for your time. regards -Madhu _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath 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).
forced code path?
One of Mr Jonathan Lewis' posts refers to 'forced code paths'. What is a 'forced code path'? regards madhu There are a number of possible anomalies in the information that you have sent to Oracle, and your init.ora has a number of strange settings which may be affecting things (possibly because of bugs, possibly because of resource demands and forced code paths). However, based on your initial description, I think Oracle is chewing up CPU trying to optimize your query, and I would take steps to check whether this is actually the case (e.g. keep reducing the size of the IN list). _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath 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: Get the Latest Date
Gavin, We have a similar situation. We simply maintain two tables - one to maintain login-history and another to record the latest-login. The latest-login table would be updated by a trigger on the history-table. A procedure checks both tables and returns approppriate info. -Madhu From: Gavin D'Mello [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Get the Latest Date Date: Fri, 12 Apr 2002 02:43:25 -0800 Hi, I have read up quite a lot before posting this message so please bear with me if this question is trivial. I have table which stores session information of users. I have to develop a report which gives me the number of times users have logged in ( which is straightforward ) as well as their last access time. Since every user has multiple records in the table, I was trying to find a way to get me just one row per user which returns the latest date, rather than checking for the latest date in the client logic. Is there any function in Oracle which would return the latest date ? Thank You, Gavin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gavin D'Mello 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath 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: * Oracle DBA Needed in Minnesota
Hi all, Am just starting out as a DBA. Could somebody tell me the differences between 'Production DBA' and 'Development-side DBA' ? (Skills, Tasks etc) Regards Madhu From: OraStaff [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: * Oracle DBA Needed in Minnesota Date: Sat, 06 Apr 2002 16:33:18 -0800 Position: Oracle DBA Location: Rochester, Minnesota Salary Range: 65-75K- maybe more Description: This client company-a leader in its' field, needs an Oracle DBA to provide database support. This position includes support of all aspects of the Oracle relational database product (ASE) on primarily SUN Unix platforms. The key is depth of production database experience (as opposed to development), knowledge of core Oracle infrastructure and the pieces that make up the instances. Tasks will include, but are not limited to: capacity planning, installation, upgrading, monitoring, performance and tuning, responding to trouble calls, writing administrative scripts in Unix shell languages and SQL/PL-SQL, backup and recovery, and maintaining security in the institution's Oracle environments. Sharing a 24x7 on-call rotation with the other members of the team is also expected. This is an interesting position with a solid organization with terrific benefits. *They need a Production DBA not an Development side DBA. This is a full time staff position so no sub-contractors or third parties please. Please do not call or send a resume if you are not in the U.S. and/or need sponsorship. Requirements: *Bachelor's degree in computer science, related field or equivalent experience. *Three or more years of supporting a multi-server Oracle environment. *Strong analytical and problem solving skills. *Demonstrated proficiency with the HP or SUN Unix operating system. *Advanced Replication is not required but a big plus. * Must be a U.S. citizen or perm. resident with excellent English. For immediate consideration, please email your resume as an attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Phone: 1-800-549-8502. (*Please do not call if you need sponsorship) Please Use Job Code: One/DBA/Rochester/Diane _ 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: Madhusudhanan Sampath 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: what pl/sql construct can return multiple rows?
Lots of examples avlbl from Concepts manual; also pl check asktom.com Step 1) Declare a ref cursor inside a package CREATE PACKAGE APACK AS TYPE RefCurTyp IS REF CURSOR; END APACK; Step 2) Employ the ref curosr IN OUT variable inside the procedure..note that the cursor is opened but not fetched.. PROCEDURE demo_ref ( refcurvar IN OUT RefCurTyp, choice NUMBER) IS BEGIN IF choice = 1 THEN OPEN refcurvar FOR SELECT * FROM emp; ELSIF choice = 2 THEN OPEN refcurvar FOR SELECT * FROM dept; ELSIF choice = 3 THEN OPEN refcurvar FOR SELECT * FROM sal; END IF; END demo_ref; From: Igor Neyman [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: what pl/sql construct can return multiple rows? Date: Tue, 19 Mar 2002 08:08:35 -0800 Use Ref Cursor (reference cursor) as a parameter in stored procedure. Sorry, don't have handy sample code, but you can lookup one in any PL/SQL book or docs. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 8:58 AM Hi, Is there a way to write a procedure to return multiple rows? I have some nasty SQL that I'd like to convert to run server-side, but how do you spit out multiple rows from PL/SQL? thx Bill Magaliff Framework, Inc. 914-631-2322 _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath 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).
