Oracle SQL Function Implementation

Question

I have created a Function which considers the two input values eg. '[123].[111].[1],[sa].[0]' It is working as expected. Now I have a requirement on different input along with the above one. eg. 'RL87654' -- ACCOUNTNUMBER

or '[123].[111].[1],[sa].[0]'

I need to bypass if the input is an above format other than braces"[]". and I will perform the other steps to get the data from that.

My only concern is on how to check the braces and move the input value to the required action.

I need to bypass if the input is an above format other than braces"[]". and I will perform the other steps to get the data from that.

My only concern is on how to check the braces and move the input value to the required action.


/*Get_Accountdetails Functions*/
create or replace function Get_Accountdetails(inpstr1 in varchar2,inpstr2 in varchar2) return get_acnt_type
as
  v_ret   get_acnt_type;   
  a  dbms_utility.uncl_array;
  b  dbms_utility.uncl_array; 
  len1  pls_integer;  
  len2  pls_integer;  
  cnt pls_integer :=1;
  inp_str1 varchar(32000) := regexp_replace(inpstr1,'[][]','"');
  inp_str2 varchar(32000) := regexp_replace(inpstr2,'[][]','"');
  inp_str3 varchar(32000) := replace(inp_str2,'"."','","');
  inp_val1 varchar(320):= '';
  inp_val2 varchar(320):= '';
  out_acval1 varchar(320):= '';
  out_aclval2 varchar(320):= '';
  out_dbval3 varchar(320):= '';
  v_aid varchar2(10);
  v_db_id  varchar2(10);
  v_lvl_id varchar2(10);
  sa_user_code  varchar2(100);    
  count1 pls_integer;  

begin
    v_ret  := get_acnt_type();     
    dbms_utility.comma_to_table(inp_str1, len1, a); 
    dbms_utility.comma_to_table(inp_str3, len2, b);

    for j in 1..len2/2 loop
        sa_user_code := replace(b(cnt),'"','');  
        for i in 1..len1 loop                                 
            inp_val1 := a(i);                               
            v_aid := regexp_substr(inp_val1, '\d+', 1, 1);
            v_db_id  := regexp_substr(inp_val1, '\d+', 1, 2);
            v_lvl_id := regexp_substr(inp_val1, '\d+', 1, 3);

            IF v_lvl_id = '1' THEN
                SELECT COUNT(*)into count1
                from gdw.dim_cust_acnt                      
                where RCL_LVL_1_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1;       

            ELSE IF v_lvl_id = '2' THEN
                SELECT COUNT(*)into count1
                from gdw.dim_cust_DEPT                      
                where RCL_LVL_2_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1; 
            ELSE
                SELECT COUNT(*)into count1
                from gdw.dim_cust_dept                      
                where RCL_LVL_3_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1;
            END IF;
            END IF;

            IF count1 > 0 THEN
                IF(UPPER (sa_user_code) = 'SA') THEN                           
                    CASE v_lvl_id

                        when 1 then --If Level 1 then check GDW.Dim_Cust_Acnt                     
                        select dim_cust_key,RCL_LVL_1_ACNT_ID,RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_acnt                                   
                        where RCL_LVL_1_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1;

                        when 2 then  --If Level 2  then check GDW.Dim_Cust_Dept
                        select dim_cust_dept_key,RCL_LVL_2_ACNT_ID,RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_dept                   
                        where RCL_LVL_2_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1;

                        else --If Level 3 then check GDW.Dim_Cust_Dept
                         select dim_cust_dept_key,RCL_LVL_3_ACNT_ID,RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_dept 
                         where RCL_LVL_3_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1;

                    END CASE; 

                    v_ret.extend; 
                    v_ret(v_ret.count) := Get_acnt_obj(out_acval1,out_aclval2,out_dbval3,v_lvl_id);

                ELSE
                    CASE v_lvl_id--If Level 1 then check GDW.Dim_Cust_Acnt
                        when 1 then 
                            select dca.dim_cust_key,dca.RCL_LVL_1_ACNT_ID,dca.RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_acnt dca
                            INNER JOIN bi_rpt.dtl_usr_acnt_scrty fc ON fc.SRC_DB_ID =dca.RCL_SRC_DB_ID 
                            AND fc.acnt_cd=dca.CUST_ID
                            where dca.RCL_LVL_1_ACNT_ID = v_aid and dca.RCL_SRC_DB_ID = v_db_id and fc.SRC_USR_CD=sa_user_code AND ROWNUM=1;          

                        when 2 then --If Level 3 then check GDW.Dim_Cust_Dept
                            select dca.dim_cust_dept_key,dca.RCL_LVL_2_ACNT_ID,dca.RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_dept dca                              
                            INNER JOIN bi_rpt.dtl_usr_acnt_scrty fc  ON fc.SRC_DB_ID =dca.RCL_SRC_DB_ID 
                            AND fc.ACNT_LVL_1_CD=dca.CUST_DIV_CD
                            where dca.RCL_LVL_2_ACNT_ID = v_aid and dca.RCL_SRC_DB_ID = v_db_id and fc.SRC_USR_CD=sa_user_code AND ROWNUM=1;                    
                        else --If Level  3 then check GDW.Dim_Cust_Dept
                            select dca.dim_cust_dept_key,dca.RCL_LVL_3_ACNT_ID,dca.RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_dept DCA                             
                            INNER JOIN bi_rpt.dtl_usr_acnt_scrty fc  ON fc.SRC_DB_ID =dca.RCL_SRC_DB_ID 
                            AND fc.ACNT_LVL_2_CD=dca.CUST_DEPT_CD
                            where dca.RCL_LVL_3_ACNT_ID = v_aid and dca.RCL_SRC_DB_ID = v_db_id and fc.SRC_USR_CD=sa_user_code AND ROWNUM=1;                      
                    END CASE; 
                    v_ret.extend; 
                    v_ret(v_ret.count) := Get_acnt_obj(out_acval1,out_aclval2,out_dbval3,v_lvl_id);

                End IF;

            ELSE
                CONTINUE;
            END IF;     
        END LOOP; 
        cnt := cnt+2;       
    END LOOP;
return v_ret;
END;
/

I have tried this, but i am a beginner in oracle, could you suggest me how to add in there. — author4
– author4, Commented Sep 3, 2019 at 8:31
Do you need to check all the braces and in the exact format or only count of braces will work for you? — Ankit Bajpai
– Ankit Bajpai, Commented Sep 3, 2019 at 9:52

Ankit Bajpai · Accepted Answer · 2019-09-03 10:39:24Z

1

if you only need to check the count of braces then you can use REGEXP_COUNT function -

IF REGEXP_COUNT(inpstr1, '\[|\]') = 10

Below query is giving the correct result as 10

SELECT REGEXP_COUNT('[123].[111].[1],[sa].[0]', '\[|\]') RESULT
FROM DUAL

RESULT
10

Demo

answered Sep 3, 2019 at 10:39

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Function Implementation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related