Databases
StackOverflow2013
Postsdbo
POStored procedure to View
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStored procedure to View
    primarykey
    data
    text
    <p>I have oracle stored procedure which has three input parameters and four output parameter. The stored procedure look up few tables and have some logic, not very complex, but a lot of look ups based on different condition. Another system wants use the same logic. Unfortunately calling a stored proc or putting the logic in SQL is costlier(man days) for them. They would like to access a View or a table and get the result using a where clause filter. Are there any patterns or suggested approach to achieve this?</p> <p>Let me know if you need more information. </p> <p>I can change stored proc to a function or whatever is required but can't change the interfacing system. Here is some code.</p> <pre><code>CREATE OR REPLACE PACKAGE BODY PKG_TEST IS FUNCTION F_DETERMINE_SOMETHING ( i_location_id IN T_SYSTEM_LOCATION.SYSTEM_LOCATION_ID%TYPE, i_customer_id IN T_CUSTOMER.CUSTOMER_ID%TYPE, i_ccy IN T_CURRENCY.CCY_ID%TYPE ) RETURN RC_SOME_ACCT_INFO IS o_some_acct_info RC_SOME_ACCT_INFO; CURSOR some_acct_cursor IS select some_acct_id, area, portfolio from t_override_some_acct where customer_id = i_customer_id and SYSTEM_location_id = i_location_id and ccy1_id = i_ccy; BEGIN OPEN some_acct_cursor; FETCH some_acct_cursor into o_some_acct_info.tdr_id, o_some_acct_info.bk_area, o_some_acct_info.bk_portfolio; CLOSE some_acct_cursor; return o_some_acct_info; END F_DETERMINE_SOMETHING; FUNCTION F_GET_SOME_ACCT_GRT ( i_location_id IN T_SYSTEM_LOCATION.SYSTEM_LOCATION_ID%TYPE, i_ccy IN T_CURRENCY.CCY_ID%TYPE ) RETURN RC_SOME_ACCT_INFO IS o_some_acct_info RC_SOME_ACCT_INFO; CURSOR SYSTEM_location_accounts_cursor IS select risk_account, risk_area from V_SYSTEM_LOCATION_ACCTS where SYSTEM_location_id = i_location_id and ccy1_id = i_ccy; CURSOR SYSTEM_location_traderId_cursor IS select autopric_tdr_id from V_SYSTEM_LOCATION where SYSTEM_location_id = i_location_id; BEGIN OPEN SYSTEM_location_accounts_cursor; FETCH SYSTEM_location_accounts_cursor into o_some_acct_info.bk_portfolio, o_some_acct_info.bk_area; CLOSE SYSTEM_location_accounts_cursor; OPEN SYSTEM_location_traderId_cursor; FETCH SYSTEM_location_traderId_cursor into o_some_acct_info.tdr_id; CLOSE SYSTEM_location_traderId_cursor; return o_some_acct_info; END F_GET_SOME_ACCT_GRT; FUNCTION F_DETERMINE_SOME_ACCT ( i_location_id IN T_SYSTEM_LOCATION.SYSTEM_LOCATION_ID%TYPE, i_customer_id IN T_CUSTOMER.CUSTOMER_ID%TYPE, i_bought_ccy IN T_CURRENCY.CCY_ID%TYPE, i_sold_ccy IN T_CURRENCY.CCY_ID%TYPE ) RETURN RC_SOME_ACCT_INFO IS o_some_acct_ids RC_SOME_ACCT_INFO; v_SYSTEM_location_id T_SYSTEM_LOCATION.SYSTEM_LOCATION_ID%TYPE; BEGIN v_SYSTEM_location_id := i_location_id; if (v_SYSTEM_location_id &lt;&gt; 4) then begin o_some_acct_ids := F_DETERMINE_SOMETHING( v_SYSTEM_location_id, i_customer_id, i_bought_ccy ); if ( o_some_acct_ids.bk_area is null OR o_some_acct_ids.bk_portfolio is null) then begin o_some_acct_ids := F_DETERMINE_SOMETHING( v_SYSTEM_location_id, i_customer_id, i_sold_ccy ); if ( o_some_acct_ids.bk_area is null OR o_some_acct_ids.bk_portfolio is null) then begin o_some_acct_ids := F_DETERMINE_SOMETHING( v_SYSTEM_location_id, i_customer_id, PKG_CONSTANTS.C_OTH_VALUE ); if ( o_some_acct_ids.bk_area is null OR o_some_acct_ids.bk_portfolio is null ) then begin v_SYSTEM_location_id := PKG_CONSTANTS.C_SYSTEM_LOCATION_ALL; o_some_acct_ids := F_DETERMINE_SOMETHING( v_SYSTEM_location_id, i_customer_id, i_bought_ccy ); if ( o_some_acct_ids.bk_area is null OR o_some_acct_ids.bk_portfolio is null ) then begin o_some_acct_ids := F_DETERMINE_SOMETHING( v_SYSTEM_location_id, i_customer_id, i_sold_ccy ); if ( o_some_acct_ids.bk_area is null OR o_some_acct_ids.bk_portfolio is null ) then begin o_some_acct_ids := F_DETERMINE_SOMETHING( v_SYSTEM_location_id, i_customer_id, PKG_CONSTANTS.C_OTH_VALUE ); end; end if; end; end if; end; end if; end; end if; end; end if; end; else o_some_acct_ids := F_GET_SOME_ACCT_GRT(v_SYSTEM_location_id, i_bought_ccy ); if ( o_some_acct_ids.bk_area is null OR o_some_acct_ids.bk_portfolio is null ) then begin o_some_acct_ids := F_GET_SOME_ACCT_GRT(v_SYSTEM_location_id, i_sold_ccy ); if ( o_some_acct_ids.bk_area is null OR o_some_acct_ids.bk_portfolio is null ) then begin o_some_acct_ids := F_GET_SOME_ACCT_GRT(v_SYSTEM_location_id, PKG_CONSTANTS.C_OTH_VALUE ); end; end if; end; end if; end if; return o_some_acct_ids; END F_DETERMINE_SOME_ACCT; PROCEDURE P_RETRIEVE_SYSM_CUST_DETAILS ( i_sysm_short_name IN T_CUSTOMER.SYSM_CUSTOMER_ID%TYPE, i_sysm_legal_entity IN T_SYSM_LEI_LOCATION_MAPPING.SYSM_LEGAL_ENTITY%TYPE, i_bought_ccy IN T_CURRENCY.CCY_ID%TYPE, i_sold_ccy IN T_CURRENCY.CCY_ID%TYPE, o_sysw_customer_id OUT T_CUSTOMER.SYSW_CUST_ID%TYPE, o_sysw_city OUT T_CUSTOMER.CITY%TYPE, o_tdr_id OUT T_OVERRIDE_SOME_ACCT.SOME_ACCT_ID%TYPE, o_bk_area OUT T_OVERRIDE_SOME_ACCT.AREA%TYPE, o_bk_portfolio OUT T_OVERRIDE_SOME_ACCT.PORTFOLIO%TYPE, o_error OUT varchar2 ) IS v_SYSTEM_location_id T_SYSTEM_LOCATION.SYSTEM_LOCATION_ID%TYPE; v_customer_id T_CUSTOMER.CUSTOMER_ID%TYPE; v_city T_CUSTOMER.CITY%TYPE; v_sysw_cust_id T_CUSTOMER.SYSW_CUST_ID%TYPE; o_some_acct_ids RC_SOME_ACCT_INFO; CURSOR customer_cursor IS select customer_id, sysw_cust_id, city from v_customer where sysm_customer_id = UPPER(i_sysm_short_name); CURSOR lei_location_cursor IS select location_id from T_SYSM_LEI_LOCATION_MAPPING where sysm_legal_entity = UPPER(i_sysm_legal_entity); BEGIN open lei_location_cursor; FETCH lei_location_cursor into v_SYSTEM_location_id; close lei_location_cursor; open customer_cursor; FETCH customer_cursor into v_customer_id,v_sysw_cust_id,v_city; close customer_cursor; o_some_acct_ids := F_DETERMINE_SOME_ACCT(v_SYSTEM_location_id, v_customer_id, i_bought_ccy, i_sold_ccy); if ( o_some_acct_ids.bk_area is null OR o_some_acct_ids.bk_portfolio is null ) then o_error := '&lt;&lt;Error: Data not found&gt;&gt;'; else o_sysw_customer_id := v_sysw_cust_id; o_sysw_city := v_city; o_tdr_id := o_some_acct_ids.tdr_id; o_bk_area := o_some_acct_ids.bk_area; o_bk_portfolio := o_some_acct_ids.bk_portfolio; o_error := null; end if; END P_RETRIEVE_SYSM_CUST_DETAILS; END PKG_TEST; </code></pre> <p>P_RETRIEVE_SYSM_CUST_DETAILS is the procedure that should be replaced with a view.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMurali
    1. USMurali
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POStored procedure to View
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POStored procedure to View
      1. USdArc
      1. VTFavorite
    1. COIf you can rewrite your stored procedure as a SELECT statement then you can convert it to a view. If you cannot do this it cannot be a view. For example of you have procedural logic that can't be written as inline logic in a SELECT statement you cannot create it as a view. Why don't you post some code and we can help.
      singulars
      1. POStored procedure to View
      1. USNick.McDermaid
    2. COSome details about the guts of your procedure, its interface, description of how they retrieve the data would be useful to have in the question.
      singulars
      1. POStored procedure to View
      1. USYasir Arsanukaev
    3. COCleaning up code now. Will post code soon. Thank you guys
      singulars
      1. POStored procedure to View
      1. USMurali
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload