CODEWITHSUNDEEP
regexreplaceplsql
Sachin Vaidya
Sachin Vaidya

Reputation: 125

issue with string construction in plsql

I am having a string like 

LEAST("col1", "col2") GREATEST("col1", "col2")

and from this string I want to a resultant string like

col1 IN LEAST(v_col1, v_col2) AND col2 IN GREATEST(v_col1, v_col2)

where v_ is appended to the column name and they represent predefined variables. Number of columns can be dynamic. I have tried too many options but I am not able to get the desired result.

Please suggest.

This are few options I am trying but seems not moving in right direction.

declare txt varchar2(1000);
anstxt varchar2(1000);
begin
txt := 'LEAST("col1","col2")';
select REGEXP_SUBSTR(TXT,'"([^"]*)"',1,1,'',1) into anstxt from dual;
DBMS_OUTPUT.PUT_LINE(anstxt);
end;


declare 
v_Var5 varchar2(50);
v_Var6 varchar2(1000);
v_Var7 varchar2(10);
v_Var8 varchar2(10);
CURSOR c3 IS
    SELECT column_expression  FROM all_ind_expressions WHERE table_owner = 'schemaname' AND table_name = 'mytable';    

names_t  c3%ROWTYPE;
    TYPE names_ntt IS TABLE OF names_t%TYPE; -- must use type
    l_names  names_ntt;
BEGIN
v_Var7 := ' IN (';
v_Var8 := ');';
--FOR i in c3
OPEN  c3;
    FETCH c3 BULK COLLECT INTO l_names;
    CLOSE c3;

    FOR indx IN 1..l_names.COUNT LOOP
    IF(l_names.COUNT > 1) THEN
      v_Var6 := v_Var6 || REPLACE(l_names(indx).column_expression, '"', '');
        DBMS_OUTPUT.PUT_LINE(l_names(indx).column_expression);
    ELSE
      v_Var6 := REPLACE(l_names(indx).column_expression, '"', '');
    END IF;
    END LOOP;
    v_Var6 := v_Var7 || REPLACE(v_Var6, ' ', ' AND ') || v_Var8;
--DBMS_OUTPUT.PUT_LINE(v_Var5);
DBMS_OUTPUT.PUT_LINE(v_Var6);
END;

Regards.

Upvotes: 1

Views: 47

Answers (1)

Sachin Vaidya
Sachin Vaidya

Reputation: 125

After so much trials, I have finally got what I was looking for. I am sharing this for the forum's reference.

DECLARE
v_Var5 varchar2(1000);
v_Var6 varchar2(1000);
v_Var7 varchar2(10);
v_Var8 varchar2(10);
v_Var9 varchar2(1000);
v_Counter NUMBER;
CURSOR c3 IS
    SELECT column_expression  FROM all_ind_expressions WHERE table_owner = 'myschema' AND table_name = 'mytable';    

names_t  c3%ROWTYPE;
    TYPE names_ntt IS TABLE OF names_t%TYPE; -- must use type
    l_names  names_ntt;
BEGIN
  v_Counter := 1;
  v_Var7 := ' IN (';
  v_Var8 := ');';

  OPEN  c3;
    FETCH c3 BULK COLLECT INTO l_names;
  CLOSE c3;

  FOR indx IN 1..l_names.COUNT 
  LOOP
    --v_Counter := l_names.COUNT;
    IF(l_names.COUNT > 1) THEN
      v_Var6 := REPLACE(l_names(indx).column_expression, '"', '');
      FOR i IN 1..l_names.COUNT LOOP
        v_Var6 := REPLACE(v_Var6, REGEXP_SUBSTR(l_names(i).column_expression,'"([^"]*)"',1,i,'',1),'v_' || REGEXP_SUBSTR(l_names(i).column_expression,'"([^"]*)"',1,i,'',1));
      END LOOP;
      v_Var9 := REGEXP_SUBSTR(l_names(indx).column_expression,'"([^"]*)"',1,indx,'',1) || ' IN (';   
      CASE 
        WHEN indx < l_names.COUNT THEN 
          v_Var5 := v_Var5 || v_Var9 || v_Var6 || ') AND ';
        WHEN indx = l_names.COUNT THEN 
          v_Var5 := v_Var5 || v_Var9 || v_Var6 || ');';
      END CASE;
     ELSE
      v_Var6 := REPLACE(l_names(indx).column_expression, '"', '');
      v_Var6 := REPLACE(v_Var6, REGEXP_SUBSTR(l_names(indx).column_expression,'"([^"]*)"',1,indx,'',1),'v_' || REGEXP_SUBSTR(l_names(indx).column_expression,'"([^"]*)"',1,indx,'',1));
      v_Var9 := REGEXP_SUBSTR(l_names(indx).column_expression,'"([^"]*)"',1,indx,'',1) || ' IN ';  
      v_Var5 := v_Var5 || v_Var9 || v_Var6;
    END IF;
  END LOOP;
    --DBMS_OUTPUT.PUT_LINE('v_Var9' || CHR(13) || v_Var9);
    --DBMS_OUTPUT.PUT_LINE('v_Var6' || CHR(13) ||v_Var6);
  DBMS_OUTPUT.PUT_LINE('v_Var5' || CHR(13) ||v_Var5);
END;

Upvotes: 1

Related Questions