Oracle types.ref_cursor to SQL Server 2008 migrati

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Oracle types.ref_cursor to SQL Server 2008 migrati

Author

Topic

TajKazi
Posting Yak Master

101 Posts

Posted - 2015-01-27 : 07:26:31

here in following procedure types.ref_cursor used as datatype. i know that in SQL Server there is no exact same concept. please help me, guide me to convert following code to related SQL Server code

procedure USP_GET_FILTERS_BOM(p_routing out types.ref_cursor,
p_origin out types.ref_cursor,
p_source out types.ref_cursor,
p_type out types.ref_cursor,
p_scope out types.ref_cursor,
p_currency out types.ref_cursor,
p_iserror out number)
as

e_no_routing exception; -- declare the exception
pragma EXCEPTION_INIT(e_no_routing, -20031); -- bind the exception to the exception number
e_no_origin exception; -- declare the exception
pragma EXCEPTION_INIT(e_no_origin, -20032); -- bind the exception to the exception number
e_no_source exception; -- declare the exception
pragma EXCEPTION_INIT(e_no_source, -20033); -- bind the exception to the exception number
e_no_type exception; -- declare the exception
pragma EXCEPTION_INIT(e_no_type, -20034); -- bind the exception to the exception number
e_no_scope exception; -- declare the exception
pragma EXCEPTION_INIT(e_no_scope, -20035); -- bind the exception to the exception number
e_no_currency exception; -- declare the exception
pragma EXCEPTION_INIT(e_no_currency, -20072); -- bind the exception to the exception number

v_isvalid number;
v_estimate_exists number; -- 0-
v_item_exists number; -- 1-
v_routing_exists number; --2-
v_source_exists number; --3-
v_type_exists number; --4-
v_origin_exists number; --5-
v_scope_exists number; --6-
v_currency_exists number; --6-

v_estimate number;
v_revision number;
v_routing varchar(50);
v_source varchar(50);
v_type varchar(50);
v_origin varchar(50);
v_scope varchar(50);
v_rowid varchar(50);
interfaceBom_rec TBL_EAQ_INTERFACE_BOM%ROWTYPE;

begin

v_estimate_exists :=0; -- 0-
v_item_exists :=0; -- 1-
v_routing_exists :=0; --2-
v_source_exists :=0; --3-
v_type_exists :=0; --4-
v_origin_exists :=0; --5-
v_scope_exists:=0; --6-
v_currency_exists:=0;
v_isvalid:=1;
p_iserror:=0;

--2-Routing
-- check valid Routing available in master
open p_routing for --open the cursor
select * from ESS_MASTERS
where 1=2 ;

PKG_ESS_MASTERS.USP_GET_LIST( PKG_EAQ_CONFIGS.c_Application_id,'ROUTING',p_routing,p_iserror);

--3-Source
-- check valid Source available in master
open p_source for --open the cursor
select * from ESS_MASTERS
where 1=2 ;

PKG_ESS_MASTERS.USP_GET_LIST( PKG_EAQ_CONFIGS.c_Application_id,'SOURCE',p_source,p_iserror);

--4-type
-- check valid type available in master
open p_type for --open the cursor
select * from ESS_MASTERS
where 1=2 ;

PKG_ESS_MASTERS.USP_GET_LIST( PKG_EAQ_CONFIGS.c_Application_id,'TYPE',p_type,p_iserror);

--5-origin
-- check valid type available in master
open p_origin for --open the cursor
select * from ESS_MASTERS
where 1=2 ;

PKG_ESS_MASTERS.USP_GET_LIST( PKG_EAQ_CONFIGS.c_Application_id,'ORIGIN',p_origin,p_iserror);

--6-Scope
-- check valid Scope available in master
open p_scope for --open the cursor
select * from ESS_MASTERS
where 1=2 ;

PKG_ESS_MASTERS.USP_GET_LIST( PKG_EAQ_CONFIGS.c_Application_id,'SCOPE',p_scope,p_iserror);

--7-Valid Currencies
-- check valid curencies available in master
open p_currency for --open the cursor
select a.ID ID,
a.CURRENCY CURRENCY,
b.CURRENCY_NAME CURRENCY_NAME
from TBL_EAQ_EXG_RATE a, ESS_CURRENCY b
where trunc(sysdate)>=trunc(a.effective_start_date)
and trunc(sysdate)<=trunc(a.effective_end_date)
and a.CURRENCY=b.CURRENCY(+)
and 1=2 ;

-- start checking Currencies
select count(*)
into v_currency_exists
from TBL_EAQ_EXG_RATE
where trunc(sysdate)>=trunc(effective_start_date)
and trunc(sysdate)<=trunc(effective_end_date);

-- Scope data does not exist.
if v_currency_exists <= 0 then-- if the Currencies data does not exists
raise e_no_currency;--raise an exception if Currencies data does not exist.
else
open p_currency for --open the cursor
select a.ID ID,
a.CURRENCY CURRENCY,
b.CURRENCY_NAME CURRENCY_NAME
from TBL_EAQ_EXG_RATE a, ESS_CURRENCY b
where trunc(sysdate)>=trunc(a.effective_start_date)
and trunc(sysdate)<=trunc(a.effective_end_date)
and a.CURRENCY=b.CURRENCY(+)
order by b.CURRENCY_NAME;

end if;

end --USP_GET_FILTERS_BOM;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources