how to check Result set results in Toad 9.6.
|
View:
New views
12 Messages
—
Rating Filter:
Alert me
|
 |
how to check Result set results in Toad 9.6.
by ajai chaganti
::
Rate this Message:
|
|
|
RE: how to check Result set results in Toad 9.6.
by Jeff Smith-7
::
Rate this Message:
What’s your return value datatype? Is it a collection, a refcursor, a plain old varchar or number?
If your package procedures does something you want to check outside of the return value, then we have another software product<http://www.quest.com/code-tester-for-oracle/> that can test those results too. From: toad@... [mailto:toad@...] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 8:42 AM To: toad@... Subject: [toad] how to check Result set results in Toad 9.6. Hi , I'm very new to TOAD. can somebody please help me how to check Result set results in toad. using a stored procedure in a package. :) Have a Great Day ......... Thanks 'n' Regards Ajaibabu Chaganti Send instant messages to your online friends http://uk.messenger.yahoo.com |
|
|
|
|
|
RE: how to check Result set results in Toad 9.6.
by Jeff Smith-7
::
Rate this Message:
On your anon block setup screen, go into the options and ask Toad to capture the results of your refcursor.
You can then see it after it has executed in the Ref Cursor Results tab in the editor. [cid:image001.png@...] From: toad@... [mailto:toad@...] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 8:54 AM To: toad@... Subject: Re: [toad] how to check Result set results in Toad 9.6. the return value procedure is a refcursor. please look into the procedure.. PROCEDURE search_Carrier( p_carrier_nm IN VARCHAR2, p_incumbent_country IN VARCHAR2, p_refcursor OUT rct_cursor ) IS -- PROC_NM constant VARCHAR2(30) := 'search_Carrier'; -- v_sql_stmt VARCHAR2(4000) := ''; -- Limit for Oracle 8.x is 4k CHAR v_carrier_nm VARCHAR2(80); v_incumbent_country VARCHAR2(10); -- BEGIN -- insert into tmp_sql_log(value_log) values(p_carrier_nm||p_incumbent_country); -- commit; -- v_carrier_nm := ltrim(rtrim(translate(upper(p_carrier_nm),'*','%'))); -- if (p_carrier_nm is NULL) or (p_carrier_nm = '') then v_carrier_nm := '%'; end if; if (p_incumbent_country is NULL) or (p_incumbent_country = '') then v_incumbent_country := '%'; else v_incumbent_country := p_incumbent_Country; end if; -- v_sql_stmt := 'SELECT og.organization_name "CARRIER" ,og.organization_id "CARRIER_ID" ,op.organization_name "PARENT_ORGANIZATION" ,og.parent_org_id ,rol.status "CARRIER_STATUS" ,og.original_date ,og.url "CARRIER_URL" ,cmt_common.get_carrier_tier(og.CARRIER_TIER) "CARRIER_TIER" '; if (v_incumbent_country != '-') then v_sql_Stmt := v_sql_stmt||' ,cl.country "INCUMBENT_COUNTRY" '; else v_sql_Stmt := v_sql_stmt||' ,'' '' "INCUMBENT_COUNTRY" '; end if; v_sql_Stmt := v_sql_stmt||' --,cmt_query.get_carrier_type_lst( og.organization_id ) "CARRIER_TYPE" FROM org_base.organizations og ,org_base.org_role rol ,org_base.org_role_lookup rl ,org_base.organizations op '; if (v_incumbent_country != '-') then v_sql_Stmt := v_sql_stmt||' ,org_base.object_attribute oa ,org_base.countries_lookup cl '; end if; v_sql_Stmt := v_sql_stmt||' WHERE og.ORGANIZATION_NAME like :CARRIER '; -- if (v_incumbent_country = '+') then -- v_sql_stmt := v_sql_stmt||' and oa.OBJECT_ID = og.ORGANIZATION_ID and oa.Value_Tx <> ''.'' and oa.value_tx = cl.country_code and oa.Attribute_Nm = ''INCUMBENT_COUNTRY'' '; elsif (v_incumbent_country = '-') then v_sql_stmt := v_sql_stmt||' and og.Organization_ID NOT IN (select oa.Object_ID From org_base.object_attribute oa Where oa.Value_Tx <> ''.'' and oa.Attribute_Nm = ''INCUMBENT_COUNTRY'' ) '; elsif (v_incumbent_country = '%') then v_sql_stmt := v_sql_stmt||' and oa.OBJECT_ID(+) = og.ORGANIZATION_ID and oa.attribute_nm(+) = ''INCUMBENT_COUNTRY'' and oa.value_tx = cl.country_code(+) '; else v_sql_stmt := v_sql_stmt||' and oa.OBJECT_ID = og.ORGANIZATION_ID and oa.attribute_nm = ''INCUMBENT_COUNTRY'' and oa.Value_Tx = :INCUMBENT_COUNTRY-- ''||v_incumbent_country||'' and oa.value_tx = cl.country_code '; --('||v_Contact_role_id||') end if; v_sql_stmt := v_sql_stmt||' and og.organization_id = rol.organization_id and rol.role_id = rl.role_id and rl.role = ''CARRIER'' and og.parent_org_id = op.organization_id(+) ORDER BY og.organization_name '; -- -- INSERT INTO pitt.tmp_sql_log (value_log) values(v_sql_stmt ); -- commit; if (v_incumbent_country != '%' and v_incumbent_country != '+' and v_incumbent_country != '-') then OPEN p_refcursor FOR v_sql_stmt USING v_carrier_nm, v_incumbent_country; else OPEN p_refcursor FOR v_sql_stmt USING v_carrier_nm; end if; -- EXCEPTION WHEN NO_DATA_FOUND THEN handle_error(PROC_NM, sqlcode, sqlerrm); WHEN OTHERS THEN handle_error(PROC_NM, sqlcode, sqlerrm); -- END search_Carrier; :) Have a Great Day ......... Thanks 'n' Regards Ajaibabu Chaganti ----- Original Message ---- From: Jeff Smith <jeff.smith@...> To: "toad@..." <toad@...> Sent: Friday, July 11, 2008 18:16:31 Subject: RE: [toad] how to check Result set results in Toad 9.6. What’s your return value datatype? Is it a collection, a refcursor, a plain old varchar or number? If your package procedures does something you want to check outside of the return value, then we have another software product<http://www.quest.com/code-tester-for-oracle/> that can test those results too. From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 8:42 AM To: toad@yahoogroups. com Subject: [toad] how to check Result set results in Toad 9.6. Hi , I'm very new to TOAD. can somebody please help me how to check Result set results in toad. using a stored procedure in a package. :) Have a Great Day ......... Thanks 'n' Regards Ajaibabu Chaganti Send instant messages to your online friends http://uk.messenger .yahoo.com Send instant messages to your online friends http://uk.messenger.yahoo.com  image001.png (49K) Download Attachment image002.jpg (486 bytes) Download Attachment image003.jpg (456 bytes) Download Attachment |
|
|
|
|
|
RE: how to check Result set results in Toad 9.6.
by Jeff Smith-7
::
Rate this Message:
Copy your anon block and run it via SQL*Plus. Do you see the same error?
From: toad@... [mailto:toad@...] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 9:11 AM To: toad@... Subject: Re: [toad] how to check Result set results in Toad 9.6. hi, I tested once again and getting the same issue. please find the attached screen shots. Thanks 'n' Regards Ajaibabu Chaganti ----- Original Message ---- From: Jeff Smith <jeff.smith@...> To: "toad@..." <toad@...> Sent: Friday, July 11, 2008 18:32:06 Subject: RE: [toad] how to check Result set results in Toad 9.6. On your anon block setup screen, go into the options and ask Toad to capture the results of your refcursor. You can then see it after it has executed in the Ref Cursor Results tab in the editor. [cid:image001.png@...] From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 8:54 AM To: toad@yahoogroups. com Subject: Re: [toad] how to check Result set results in Toad 9.6. the return value procedure is a refcursor. please look into the procedure.. PROCEDURE search_Carrier( p_carrier_nm IN VARCHAR2, p_incumbent_ country IN VARCHAR2, p_refcursor OUT rct_cursor ) IS -- PROC_NM constant VARCHAR2(30) := 'search_Carrier' ; -- v_sql_stmt VARCHAR2(4000) := ''; -- Limit for Oracle 8.x is 4k CHAR v_carrier_nm VARCHAR2(80) ; v_incumbent_ country VARCHAR2(10) ; -- BEGIN -- insert into tmp_sql_log( value_log) values(p_carrier_ nm||p_incumbent_ country); -- commit; -- v_carrier_nm := ltrim(rtrim( translate( upper(p_carrier_ nm),'*',' %'))); -- if (p_carrier_nm is NULL) or (p_carrier_nm = '') then v_carrier_nm := '%'; end if; if (p_incumbent_ country is NULL) or (p_incumbent_ country = '') then v_incumbent_ country := '%'; else v_incumbent_ country := p_incumbent_ Country; end if; -- v_sql_stmt := 'SELECT og.organization_ name "CARRIER" ,og.organization_ id "CARRIER_ID" ,op.organization_ name "PARENT_ORGANIZATION" ,og.parent_org_ id ,rol.status "CARRIER_STATUS" ,og.original_ date ,og.url "CARRIER_URL" ,cmt_common. get_carrier_ tier(og.CARRIER_ TIER) "CARRIER_TIER" '; if (v_incumbent_ country != '-') then v_sql_Stmt := v_sql_stmt|| ' ,cl.country "INCUMBENT_COUNTRY" '; else v_sql_Stmt := v_sql_stmt|| ' ,'' '' "INCUMBENT_COUNTRY" '; end if; v_sql_Stmt := v_sql_stmt|| ' --,cmt_query. get_carrier_ type_lst( og.organization_ id ) "CARRIER_TYPE" FROM org_base.organizati ons og ,org_base.org_ role rol ,org_base.org_ role_lookup rl ,org_base.organizat ions op '; if (v_incumbent_ country != '-') then v_sql_Stmt := v_sql_stmt|| ' ,org_base.object_ attribute oa ,org_base.countries _lookup cl '; end if; v_sql_Stmt := v_sql_stmt|| ' WHERE og.ORGANIZATION_ NAME like :CARRIER '; -- if (v_incumbent_ country = '+') then -- v_sql_stmt := v_sql_stmt|| ' and oa.OBJECT_ID = og.ORGANIZATION_ ID and oa.Value_Tx <> ''.'' and oa.value_tx = cl.country_code and oa.Attribute_ Nm = ''INCUMBENT_ COUNTRY'' '; elsif (v_incumbent_ country = '-') then v_sql_stmt := v_sql_stmt|| ' and og.Organization_ ID NOT IN (select oa.Object_ID From org_base.object_ attribute oa Where oa.Value_Tx <> ''.'' and oa.Attribute_ Nm = ''INCUMBENT_ COUNTRY'' ) '; elsif (v_incumbent_ country = '%') then v_sql_stmt := v_sql_stmt|| ' and oa.OBJECT_ID( +) = og.ORGANIZATION_ ID and oa.attribute_ nm(+) = ''INCUMBENT_ COUNTRY'' and oa.value_tx = cl.country_code( +) '; else v_sql_stmt := v_sql_stmt|| ' and oa.OBJECT_ID = og.ORGANIZATION_ ID and oa.attribute_ nm = ''INCUMBENT_ COUNTRY'' and oa.Value_Tx = :INCUMBENT_COUNTRY- - ''||v_incumbent_ country|| '' and oa.value_tx = cl.country_code '; --('||v_Contact_ role_id|| ') end if; v_sql_stmt := v_sql_stmt|| ' and og.organization_ id = rol.organization_ id and rol.role_id = rl.role_id and rl.role = ''CARRIER'' and og.parent_org_ id = op.organization_ id(+) ORDER BY og.organization_ name '; -- -- INSERT INTO pitt.tmp_sql_ log (value_log) values(v_sql_ stmt ); -- commit; if (v_incumbent_ country != '%' and v_incumbent_ country != '+' and v_incumbent_ country != '-') then OPEN p_refcursor FOR v_sql_stmt USING v_carrier_nm, v_incumbent_ country; else OPEN p_refcursor FOR v_sql_stmt USING v_carrier_nm; end if; -- EXCEPTION WHEN NO_DATA_FOUND THEN handle_error( PROC_NM, sqlcode, sqlerrm); WHEN OTHERS THEN handle_error( PROC_NM, sqlcode, sqlerrm); -- END search_Carrier; :) Have a Great Day ......... Thanks 'n' Regards Ajaibabu Chaganti ----- Original Message ---- From: Jeff Smith <jeff.smith@quest. com> To: "toad@yahoogroups. com" <toad@yahoogroups. com> Sent: Friday, July 11, 2008 18:16:31 Subject: RE: [toad] how to check Result set results in Toad 9.6. What’s your return value datatype? Is it a collection, a refcursor, a plain old varchar or number? If your package procedures does something you want to check outside of the return value, then we have another software product<http://www.quest.com/code-tester-for-oracle/> that can test those results too. From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 8:42 AM To: toad@yahoogroups. com Subject: [toad] how to check Result set results in Toad 9.6. Hi , I'm very new to TOAD. can somebody please help me how to check Result set results in toad. using a stored procedure in a package. :) Have a Great Day ......... Thanks 'n' Regards Ajaibabu Chaganti Send instant messages to your online friends http://uk.messenger .yahoo.com Send instant messages to your online friends http://uk.messenger .yahoo.com Send instant messages to your online friends http://uk.messenger.yahoo.com |
|
|
RE: how to check Result set results in Toad 9.6.
by Bert Scalzo
::
Rate this Message:
If it runs in SQL Plus – that will not explain the issue
When running in Toad and asking to be able to view the returned ref cursor – there has to be room somewhere in memory on the server to hold that interim info So the fix (if you want to view the ref cursor return results) will be to work with your DBA to enable your server to handle such a request – right now server is saying insufficient memory to hold the results of that ref cursor – nothing toad can do about it From: toad@... [mailto:toad@...] On Behalf Of Jeff Smith Sent: Friday, July 11, 2008 8:16 AM To: toad@... Subject: RE: [toad] how to check Result set results in Toad 9.6. Copy your anon block and run it via SQL*Plus. Do you see the same error? From: toad@... [mailto:toad@...] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 9:11 AM To: toad@... Subject: Re: [toad] how to check Result set results in Toad 9.6. hi, I tested once again and getting the same issue. please find the attached screen shots. Thanks 'n' Regards Ajaibabu Chaganti ----- Original Message ---- From: Jeff Smith <jeff.smith@...> To: "toad@..." <toad@...> Sent: Friday, July 11, 2008 18:32:06 Subject: RE: [toad] how to check Result set results in Toad 9.6. On your anon block setup screen, go into the options and ask Toad to capture the results of your refcursor. You can then see it after it has executed in the Ref Cursor Results tab in the editor. [cid:image001.png@...] From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 8:54 AM To: toad@yahoogroups. com Subject: Re: [toad] how to check Result set results in Toad 9.6. the return value procedure is a refcursor. please look into the procedure.. PROCEDURE search_Carrier( p_carrier_nm IN VARCHAR2, p_incumbent_ country IN VARCHAR2, p_refcursor OUT rct_cursor ) IS -- PROC_NM constant VARCHAR2(30) := 'search_Carrier' ; -- v_sql_stmt VARCHAR2(4000) := ''; -- Limit for Oracle 8.x is 4k CHAR v_carrier_nm VARCHAR2(80) ; v_incumbent_ country VARCHAR2(10) ; -- BEGIN -- insert into tmp_sql_log( value_log) values(p_carrier_ nm||p_incumbent_ country); -- commit; -- v_carrier_nm := ltrim(rtrim( translate( upper(p_carrier_ nm),'*',' %'))); -- if (p_carrier_nm is NULL) or (p_carrier_nm = '') then v_carrier_nm := '%'; end if; if (p_incumbent_ country is NULL) or (p_incumbent_ country = '') then v_incumbent_ country := '%'; else v_incumbent_ country := p_incumbent_ Country; end if; -- v_sql_stmt := 'SELECT og.organization_ name "CARRIER" ,og.organization_ id "CARRIER_ID" ,op.organization_ name "PARENT_ORGANIZATION" ,og.parent_org_ id ,rol.status "CARRIER_STATUS" ,og.original_ date ,og.url "CARRIER_URL" ,cmt_common. get_carrier_ tier(og.CARRIER_ TIER) "CARRIER_TIER" '; if (v_incumbent_ country != '-') then v_sql_Stmt := v_sql_stmt|| ' ,cl.country "INCUMBENT_COUNTRY" '; else v_sql_Stmt := v_sql_stmt|| ' ,'' '' "INCUMBENT_COUNTRY" '; end if; v_sql_Stmt := v_sql_stmt|| ' --,cmt_query. get_carrier_ type_lst( og.organization_ id ) "CARRIER_TYPE" FROM org_base.organizati ons og ,org_base.org_ role rol ,org_base.org_ role_lookup rl ,org_base.organizat ions op '; if (v_incumbent_ country != '-') then v_sql_Stmt := v_sql_stmt|| ' ,org_base.object_ attribute oa ,org_base.countries _lookup cl '; end if; v_sql_Stmt := v_sql_stmt|| ' WHERE og.ORGANIZATION_ NAME like :CARRIER '; -- if (v_incumbent_ country = '+') then -- v_sql_stmt := v_sql_stmt|| ' and oa.OBJECT_ID = og.ORGANIZATION_ ID and oa.Value_Tx <> ''.'' and oa.value_tx = cl.country_code and oa.Attribute_ Nm = ''INCUMBENT_ COUNTRY'' '; elsif (v_incumbent_ country = '-') then v_sql_stmt := v_sql_stmt|| ' and og.Organization_ ID NOT IN (select oa.Object_ID From org_base.object_ attribute oa Where oa.Value_Tx <> ''.'' and oa.Attribute_ Nm = ''INCUMBENT_ COUNTRY'' ) '; elsif (v_incumbent_ country = '%') then v_sql_stmt := v_sql_stmt|| ' and oa.OBJECT_ID( +) = og.ORGANIZATION_ ID and oa.attribute_ nm(+) = ''INCUMBENT_ COUNTRY'' and oa.value_tx = cl.country_code( +) '; else v_sql_stmt := v_sql_stmt|| ' and oa.OBJECT_ID = og.ORGANIZATION_ ID and oa.attribute_ nm = ''INCUMBENT_ COUNTRY'' and oa.Value_Tx = :INCUMBENT_COUNTRY- - ''||v_incumbent_ country|| '' and oa.value_tx = cl.country_code '; --('||v_Contact_ role_id|| ') end if; v_sql_stmt := v_sql_stmt|| ' and og.organization_ id = rol.organization_ id and rol.role_id = rl.role_id and rl.role = ''CARRIER'' and og.parent_org_ id = op.organization_ id(+) ORDER BY og.organization_ name '; -- -- INSERT INTO pitt.tmp_sql_ log (value_log) values(v_sql_ stmt ); -- commit; if (v_incumbent_ country != '%' and v_incumbent_ country != '+' and v_incumbent_ country != '-') then OPEN p_refcursor FOR v_sql_stmt USING v_carrier_nm, v_incumbent_ country; else OPEN p_refcursor FOR v_sql_stmt USING v_carrier_nm; end if; -- EXCEPTION WHEN NO_DATA_FOUND THEN handle_error( PROC_NM, sqlcode, sqlerrm); WHEN OTHERS THEN handle_error( PROC_NM, sqlcode, sqlerrm); -- END search_Carrier; :) Have a Great Day ......... Thanks 'n' Regards Ajaibabu Chaganti ----- Original Message ---- From: Jeff Smith <jeff.smith@quest. com> To: "toad@yahoogroups. com" <toad@yahoogroups. com> Sent: Friday, July 11, 2008 18:16:31 Subject: RE: [toad] how to check Result set results in Toad 9.6. What’s your return value datatype? Is it a collection, a refcursor, a plain old varchar or number? If your package procedures does something you want to check outside of the return value, then we have another software product<http://www.quest.com/code-tester-for-oracle/> that can test those results too. From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 8:42 AM To: toad@yahoogroups. com Subject: [toad] how to check Result set results in Toad 9.6. Hi , I'm very new to TOAD. can somebody please help me how to check Result set results in toad. using a stored procedure in a package. :) Have a Great Day ......... Thanks 'n' Regards Ajaibabu Chaganti Send instant messages to your online friends http://uk.messenger .yahoo.com Send instant messages to your online friends http://uk.messenger .yahoo.com Send instant messages to your online friends http://uk.messenger.yahoo.com |
|
|
RE: how to check Result set results in Toad 9.6.
by Jeff Smith-7
::
Rate this Message:
Right Bert, but he was having this issue before he tried to catch the cursor.
From: toad@... [mailto:toad@...] On Behalf Of Bert Scalzo Sent: Friday, July 11, 2008 9:19 AM To: toad@... Subject: RE: [toad] how to check Result set results in Toad 9.6. If it runs in SQL Plus – that will not explain the issue When running in Toad and asking to be able to view the returned ref cursor – there has to be room somewhere in memory on the server to hold that interim info So the fix (if you want to view the ref cursor return results) will be to work with your DBA to enable your server to handle such a request – right now server is saying insufficient memory to hold the results of that ref cursor – nothing toad can do about it From: toad@... [mailto:toad@...] On Behalf Of Jeff Smith Sent: Friday, July 11, 2008 8:16 AM To: toad@... Subject: RE: [toad] how to check Result set results in Toad 9.6. Copy your anon block and run it via SQL*Plus. Do you see the same error? From: toad@... [mailto:toad@...] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 9:11 AM To: toad@... Subject: Re: [toad] how to check Result set results in Toad 9.6. hi, I tested once again and getting the same issue. please find the attached screen shots. Thanks 'n' Regards Ajaibabu Chaganti ----- Original Message ---- From: Jeff Smith <jeff.smith@...> To: "toad@..." <toad@...> Sent: Friday, July 11, 2008 18:32:06 Subject: RE: [toad] how to check Result set results in Toad 9.6. On your anon block setup screen, go into the options and ask Toad to capture the results of your refcursor. You can then see it after it has executed in the Ref Cursor Results tab in the editor. [cid:image001.png@...] From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 8:54 AM To: toad@yahoogroups. com Subject: Re: [toad] how to check Result set results in Toad 9.6. the return value procedure is a refcursor. please look into the procedure.. PROCEDURE search_Carrier( p_carrier_nm IN VARCHAR2, p_incumbent_ country IN VARCHAR2, p_refcursor OUT rct_cursor ) IS -- PROC_NM constant VARCHAR2(30) := 'search_Carrier' ; -- v_sql_stmt VARCHAR2(4000) := ''; -- Limit for Oracle 8.x is 4k CHAR v_carrier_nm VARCHAR2(80) ; v_incumbent_ country VARCHAR2(10) ; -- BEGIN -- insert into tmp_sql_log( value_log) values(p_carrier_ nm||p_incumbent_ country); -- commit; -- v_carrier_nm := ltrim(rtrim( translate( upper(p_carrier_ nm),'*',' %'))); -- if (p_carrier_nm is NULL) or (p_carrier_nm = '') then v_carrier_nm := '%'; end if; if (p_incumbent_ country is NULL) or (p_incumbent_ country = '') then v_incumbent_ country := '%'; else v_incumbent_ country := p_incumbent_ Country; end if; -- v_sql_stmt := 'SELECT og.organization_ name "CARRIER" ,og.organization_ id "CARRIER_ID" ,op.organization_ name "PARENT_ORGANIZATION" ,og.parent_org_ id ,rol.status "CARRIER_STATUS" ,og.original_ date ,og.url "CARRIER_URL" ,cmt_common. get_carrier_ tier(og.CARRIER_ TIER) "CARRIER_TIER" '; if (v_incumbent_ country != '-') then v_sql_Stmt := v_sql_stmt|| ' ,cl.country "INCUMBENT_COUNTRY" '; else v_sql_Stmt := v_sql_stmt|| ' ,'' '' "INCUMBENT_COUNTRY" '; end if; v_sql_Stmt := v_sql_stmt|| ' --,cmt_query. get_carrier_ type_lst( og.organization_ id ) "CARRIER_TYPE" FROM org_base.organizati ons og ,org_base.org_ role rol ,org_base.org_ role_lookup rl ,org_base.organizat ions op '; if (v_incumbent_ country != '-') then v_sql_Stmt := v_sql_stmt|| ' ,org_base.object_ attribute oa ,org_base.countries _lookup cl '; end if; v_sql_Stmt := v_sql_stmt|| ' WHERE og.ORGANIZATION_ NAME like :CARRIER '; -- if (v_incumbent_ country = '+') then -- v_sql_stmt := v_sql_stmt|| ' and oa.OBJECT_ID = og.ORGANIZATION_ ID and oa.Value_Tx <> ''.'' and oa.value_tx = cl.country_code and oa.Attribute_ Nm = ''INCUMBENT_ COUNTRY'' '; elsif (v_incumbent_ country = '-') then v_sql_stmt := v_sql_stmt|| ' and og.Organization_ ID NOT IN (select oa.Object_ID From org_base.object_ attribute oa Where oa.Value_Tx <> ''.'' and oa.Attribute_ Nm = ''INCUMBENT_ COUNTRY'' ) '; elsif (v_incumbent_ country = '%') then v_sql_stmt := v_sql_stmt|| ' and oa.OBJECT_ID( +) = og.ORGANIZATION_ ID and oa.attribute_ nm(+) = ''INCUMBENT_ COUNTRY'' and oa.value_tx = cl.country_code( +) '; else v_sql_stmt := v_sql_stmt|| ' and oa.OBJECT_ID = og.ORGANIZATION_ ID and oa.attribute_ nm = ''INCUMBENT_ COUNTRY'' and oa.Value_Tx = :INCUMBENT_COUNTRY- - ''||v_incumbent_ country|| '' and oa.value_tx = cl.country_code '; --('||v_Contact_ role_id|| ') end if; v_sql_stmt := v_sql_stmt|| ' and og.organization_ id = rol.organization_ id and rol.role_id = rl.role_id and rl.role = ''CARRIER'' and og.parent_org_ id = op.organization_ id(+) ORDER BY og.organization_ name '; -- -- INSERT INTO pitt.tmp_sql_ log (value_log) values(v_sql_ stmt ); -- commit; if (v_incumbent_ country != '%' and v_incumbent_ country != '+' and v_incumbent_ country != '-') then OPEN p_refcursor FOR v_sql_stmt USING v_carrier_nm, v_incumbent_ country; else OPEN p_refcursor FOR v_sql_stmt USING v_carrier_nm; end if; -- EXCEPTION WHEN NO_DATA_FOUND THEN handle_error( PROC_NM, sqlcode, sqlerrm); WHEN OTHERS THEN handle_error( PROC_NM, sqlcode, sqlerrm); -- END search_Carrier; :) Have a Great Day ......... Thanks 'n' Regards Ajaibabu Chaganti ----- Original Message ---- From: Jeff Smith <jeff.smith@quest. com> To: "toad@yahoogroups. com" <toad@yahoogroups. com> Sent: Friday, July 11, 2008 18:16:31 Subject: RE: [toad] how to check Result set results in Toad 9.6. What’s your return value datatype? Is it a collection, a refcursor, a plain old varchar or number? If your package procedures does something you want to check outside of the return value, then we have another software product<http://www.quest.com/code-tester-for-oracle/> that can test those results too. From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of ajai chaganti Sent: Friday, July 11, 2008 8:42 AM To: toad@yahoogroups. com Subject: [toad] how to check Result set results in Toad 9.6. Hi , I'm very new to TOAD. can somebody please help me how to check Result set results in toad. using a stored procedure in a package. :) Have a Great Day ......... Thanks 'n' Regards Ajaibabu Chaganti Send instant messages to your online friends http://uk.messenger .yahoo.com Send instant messages to your online friends http://uk.messenger .yahoo.com Send instant messages to your online friends http://uk.messenger.yahoo.com image001.png (49K) Download Attachment image002.jpg (486 bytes) Download Attachment image003.jpg (456 bytes) Download Attachment |
|
|
|
