RE: what is wrong with this procedure
temp_name should be %rowtype variable; -Original Message- Sent: Saturday, June 02, 2001 2:18 AM To: Multiple recipients of list ORACLE-L create or replace procedure remove_bucket as cursor c1 is select table_name from ( select table_name from user_constraints where r_constraint_name ='PK_T_ACC_USAGE_1'); temp_name varchar2(255); temp_count number(10); str varchar2(2000); begin open c1; loop fetch c1 into temp_name; exit when c1%notfound; select count(*) into temp_count from temp_name; dbms_output.put_line(temp_count); end loop; close c1; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('INVALID SYNTAX'); end; / I am geeting following error: 15/1 PL/SQL: SQL Statement ignored 15/38PLS-00201: identifier 'TEMP_NAME' must be declared what is the possible solution... 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: Shahid Malik(IT) 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 is wrong with this procedure
You have a variable called temp_name and then you are SELECTING from temp_name? That is the problem Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *4 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra 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 is wrong with this procedure
Well, for one thing, I think you need to use dynamic sql (the DBMS_SQL package) to use a variable as a table name. At 01:17 PM 6/1/01 -0800, you wrote: >create or replace procedure remove_bucket >as >cursor c1 is >select table_name from ( >select table_name from user_constraints >where r_constraint_name ='PK_T_ACC_USAGE_1'); >temp_name varchar2(255); >temp_count number(10); >str varchar2(2000); >begin >open c1; >loop >fetch c1 into temp_name; >exit when c1%notfound; >select count(*) into temp_count from temp_name; >dbms_output.put_line(temp_count); >end loop; >close c1; >EXCEPTION >WHEN OTHERS >THEN DBMS_OUTPUT.PUT_LINE('INVALID SYNTAX'); >end; >/ > >I am geeting following error: > >15/1 PL/SQL: SQL Statement ignored >15/38PLS-00201: identifier 'TEMP_NAME' must be declared > >what is the possible solution... > >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: Regina Harter 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).