urgent pls...PL/SQL problem..
hi list, i'm conerting my old db structure into a new one with entirely different constraints, references, etc.. the new structure holds many new tables some splitted from old ones with few new columns. and like. now i want to move my old data into this new one. i 'm doing it through pl/sql, writing procedures, etc. now my problem is i've a master table for skills. and a detailed table referencing it. previously the detailed table stores the values with no ref. to the master, now it is storing the code corresponding the master value. i want to compare the old detailed table and old master table's values to fetch the code from the master table. when i try to fetch this through cursor, the values containing null in detailed table are returning error.. while rest going fine. how do i treat the nulls, the nvl(col,'0') to convert nulls to 0 is also not working. any idea. Saurabh Sharma [EMAIL PROTECTED]http://www.geocities.com/saurabh00pc/stride.html
Re: pl/sql problem
In addition to the reason for your error, which someone else has pointed out, this piece of code has another problem -- you never close your cursor. This will get you into no ends of trouble at some point in the future, so I though I should point it out. Also, a cursor for loop would be much cleaner, and you won't have to worry about closing the cursor. Like the following... declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); --enum t_enum_data.nm_enum_data%type;Don't need this variable declaration any more begin dbms_output.enable(90); -- This can be a value up to 99 for enumRec in c1 loop dbms_output.put_line(enumRec.nm_enum_data); end loop; end; / Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Harvinder Singh aTech.com> cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: pl/sql problem 06/06/2001 06:05 PM Please respond to ORACLE-L Hi, i am running following code declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); enum t_enum_data.nm_enum_data%type; begin open c1; loop fetch c1 into enum; exit when c1%notfound; dbms_output.put_line(enum); end loop; end; / i am getting error: declare * ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 91 ORA-06512: at "SYS.DBMS_OUTPUT", line 58 ORA-06512: at line 16 What might be the reason Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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 problem
The reason is the limitation of your output buffer. You should say 'SET SERVEROUTPUT ON SIZE 100' or give the appropriate dbms_output.enable comand and it will work. -Original Message- Sent: Wednesday, June 06, 2001 6:06 PM To: Multiple recipients of list ORACLE-L Hi, i am running following code declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); enum t_enum_data.nm_enum_data%type; begin open c1; loop fetch c1 into enum; exit when c1%notfound; dbms_output.put_line(enum); end loop; end; / i am getting error: declare * ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 91 ORA-06512: at "SYS.DBMS_OUTPUT", line 58 ORA-06512: at line 16 What might be the reason Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Gogala, Mladen 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 problem
Thats a pain in the rear errer caused by DBMS_OUTPUT. DBMS_OUTPUT buffers all its data up and then ships it to you at one time. For this reason you need to define the buffer large enough to handle all your output. In your code, put the line DBMS_OUTPUT.ENABLE(2); or some such number to set your buffer. In this case I picked 20,000 for the buffer size. -Original Message- Sent: Wednesday, June 06, 2001 5:06 PM To: Multiple recipients of list ORACLE-L Hi, i am running following code declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); enum t_enum_data.nm_enum_data%type; begin open c1; loop fetch c1 into enum; exit when c1%notfound; dbms_output.put_line(enum); end loop; end; / i am getting error: declare * ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 91 ORA-06512: at "SYS.DBMS_OUTPUT", line 58 ORA-06512: at line 16 What might be the reason Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Kevin Lange 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 problem
Hi, i am running following code declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); enum t_enum_data.nm_enum_data%type; begin open c1; loop fetch c1 into enum; exit when c1%notfound; dbms_output.put_line(enum); end loop; end; / i am getting error: declare * ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 91 ORA-06512: at "SYS.DBMS_OUTPUT", line 58 ORA-06512: at line 16 What might be the reason Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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).
