Asjon Asjon - 3 years ago 188
SQL Question

PL/SQL extract SELECT statement column names from custom query

I have a custom query and I need to know how to extract colum names from this. I've already searched for any way to do it:


  • Regex: I've build this one to get more or less what I want but it won't work (I'm using Toad 12.6.0.53).



This is the code I have tried to test my regex:

DECLARE
v_SQL VARCHAR2(32767 CHAR);
v_Result VARCHAR2(32767 CHAR);
BEGIN
v_SQL := 'SELECT
ALIASBASE.CDCMPANY,
ALIASBASE.PAFCODCTR,
ALIASBASE.PAFDTSCAD,
NUMFLUSSO.NUMERO,
ALIASBASE.PAFDESC,
ALIASBASE.PAFTYPE,
ALIASBASE.PAFNATURE,
ALIASBASE.PAFRECEIV,
ALIASBASE.PAFCAUSAL,
ALIASBASE.PAFCODDNE,
ALIASBASE.PAFMATURITY
FROM ALBSIAE';

BEGIN
SELECT
REGEXP_SUBSTR (v_SQL, '(?:\s*(?=\w+\.|.*as\s+|)(\*|\w+)(?=\s*(?=,|from)))',1, NULL,'i') REGEX_RESULT
INTO
v_Result
FROM DUAL;
END;

DBMS_OUTPUT.put_line('v_Result: ' || v_Result);
END;


But ant the end the v_Result is empty.


  • Another way is to loop between my table columns,as I've seen here and here, but I think is not my case.



So, I want to know if is there any other way to do this, am I wrong with the regex? Is something wrong in it? Does Oracle have other regex syntax?

Answer Source

You can parse the statement with DBMS_SQL.parse and get the columns with DBMS_SQL.describe_columns:

DECLARE
  v_SQL VARCHAR2(32767 CHAR);
  v_Result VARCHAR2(32767 CHAR) := '';
  l_cursor        PLS_INTEGER;
  l_col_cnt       PLS_INTEGER;
  i               PLS_INTEGER;
  l_desc_tab      DBMS_SQL.desc_tab;
BEGIN 
  v_SQL := q'[SELECT 1 as "MY_NUMBER", 'z' as "MY_CHAR" from dual]';
  l_cursor := DBMS_SQL.open_cursor;
  DBMS_SQL.parse(l_cursor, v_SQL, DBMS_SQL.native);
  DBMS_SQL.describe_columns(l_cursor, l_col_cnt, l_desc_tab);

  FOR i IN 1 .. l_col_cnt LOOP
    v_Result := v_Result ||' ' || l_desc_tab(i).col_name;
  END LOOP;

  DBMS_OUTPUT.put_line('v_Result: ' || v_Result);

END;
/
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download