PL/SQL strings 2

[BADCODER]

I have a simple query:
Select A, NVL(C1,0), NVL(decode(C2,'YES','GOOD','BAD'),'UNKNOWN')
FROM T1;

I wan't a function which accepts the above query as input and returns the following query (string NOT the execution result) as output:

Select A || NVL(C1,0) || NVL(decode(C2,'YES','GOOD','BAD'),'UNKNOWN')
FROM T1;

 

[sem]

Here's a rough skeleton for such function:

function fieldMerging(pStatement in varchar2)
return varchar2 
is
  mCurs integer;
  mCnt number;
  mDesc dbms_sql.desc_rec;
  mDescs dbms_sql.desc_tab;
  mResult varchar2(2000);
  mFromClause varchar2(2000);
  mFromPos integer;
  mColName varchar2(34);
begin
  -- Test statements here
  mFromPos := instr(lower(pStatement), 'from', -1);
  mFromClause := substr(pStatement, mFromPos);
  mCurs := dbms_sql.open_cursor;
  dbms_sql.parse(mCurs, pStatement, dbms_sql.native);
  dbms_sql.describe_columns(mCurs, mCnt, mDescs);
  dbms_sql.close_cursor(mCurs);

  for j in 1..mCnt loop
    mDesc := mDescs(j);

    if mDesc.col_type in (1, 96, 11, 23  ) then /* [var]char[2], rowid, raw*/
       mColName := mDesc.col_name;
    elsif mDesc.col_type in (2,12,11)/* usefull for expressions like sysdate-1*/
       mColName := '('||mDesc.col_name||')';
    else /*can not display, add your processing*/
      mColName :=null;
    end if;
    if mColName is not null then   
      if  mResult is null then
        mResult := mColName;
      else
        mResult := mResult||'||'||mColName;
      end if;  
    end if;
  end loop;

  mResult := 'select '||mResult||' '||mFromClause;
  return mResult;
exception
  when others then
    if dbms_sql.is_open(mCurs) then
       dbms_sql.close_cursor(mCurs);        
    end if;   
    return null;

end;

It doesn't process correctly string literals with blank spaces, aliases and many other cases, but at this moment I can't suggest anything better: it's complex enough to make me tired .

Regards, Dima

 

[SirCharles]

Write it in perl.