Get Bind Variable Names of given SQL Statement in Oracle

get_binds.sql

CREATE OR REPLACE FUNCTION get_binds(p_sql_statement IN CLOB) RETURN apex_t_varchar2 AS
  --
  l_statement                CLOB := p_sql_statement;
  l_block_comment_start_pos  PLS_INTEGER;
  l_line_comment_start_pos   PLS_INTEGER;
  l_string_literal_start_pos PLS_INTEGER;
  l_q_quote_char             VARCHAR2(1);
  l_closing_token            VARCHAR2(2);
  l_token_start_pos          PLS_INTEGER;
  l_token_end_pos            PLS_INTEGER;
  l_start_search_pos         PLS_INTEGER;
  l_bind_start_pos           PLS_INTEGER;
  l_name                     VARCHAR2(255);
  l_length                   PLS_INTEGER;
  l_char                     VARCHAR2(2);
  l_added_binds              VARCHAR2(32767) := ':';
  l_binds                    apex_t_varchar2;
  --
BEGIN
  --
  IF l_statement IS NULL
     OR instr(l_statement,
              ':') = 0 THEN
    RETURN l_binds;
  END IF;
  --
  l_start_search_pos := 1;
  --
  LOOP
    l_block_comment_start_pos  := instr(l_statement,
                                        '/*',
                                        l_start_search_pos);
    l_line_comment_start_pos   := instr(l_statement,
                                        '--',
                                        l_start_search_pos);
    l_string_literal_start_pos := instr(l_statement,
                                        '''',
                                        l_start_search_pos);
    --
    IF l_block_comment_start_pos = 0 THEN
      l_block_comment_start_pos := 999999999;
    END IF;
    IF l_line_comment_start_pos = 0 THEN
      l_line_comment_start_pos := 999999999;
    END IF;
    IF l_string_literal_start_pos = 0 THEN
      l_string_literal_start_pos := 999999999;
    END IF;
    --
    IF l_string_literal_start_pos < l_block_comment_start_pos
       AND l_string_literal_start_pos < l_line_comment_start_pos THEN
      l_token_start_pos := l_string_literal_start_pos;
      --
      IF upper(substr(l_statement,
                      l_token_start_pos - 1,
                      1)) = 'Q' THEN
        l_q_quote_char     := substr(l_statement,
                                     l_token_start_pos + 1,
                                     1);
        l_closing_token    := CASE l_q_quote_char
                                WHEN '[' THEN
                                 ']'
                                WHEN '{' THEN
                                 '}'
                                WHEN '<' THEN
                                 '>'
                                WHEN '(' THEN
                                 ')'
                                ELSE
                                 l_q_quote_char
                              END || '''';
        l_token_start_pos  := l_token_start_pos - 1;
        l_start_search_pos := l_token_start_pos + 3;
      ELSE
        l_closing_token    := '''';
        l_start_search_pos := l_token_start_pos + 1;
      END IF;
      --
    ELSIF l_block_comment_start_pos < l_line_comment_start_pos
          AND l_block_comment_start_pos < l_string_literal_start_pos THEN
      l_token_start_pos  := l_block_comment_start_pos;
      l_start_search_pos := l_token_start_pos + 2;
      l_closing_token    := '*/';
      --
    ELSIF l_line_comment_start_pos < l_block_comment_start_pos
          AND l_line_comment_start_pos < l_string_literal_start_pos THEN
      l_token_start_pos  := l_line_comment_start_pos;
      l_start_search_pos := l_token_start_pos + 2;
      l_closing_token    := wwv_flow.lf;
      --
    ELSE
      l_token_start_pos := NULL;
    END IF;
    --
    EXIT WHEN l_token_start_pos IS NULL;
    --
    LOOP
      l_token_end_pos := instr(l_statement,
                               l_closing_token,
                               l_start_search_pos);
      --
      IF l_token_end_pos = 0
         AND l_closing_token = wwv_flow.lf THEN
        l_token_end_pos := length(l_statement);
        EXIT;
        --
      ELSIF l_token_end_pos = 0 THEN
        EXIT;
        --
      ELSIF l_closing_token = ''''
            AND substr(l_statement,
                       l_token_end_pos + 1,
                       1) = '''' THEN
        l_start_search_pos := l_token_end_pos + 2;
        --
      ELSE
        EXIT;
      END IF;
    END LOOP;
    --
    IF l_token_end_pos > 0 THEN
      l_statement := substr(l_statement,
                            1,
                            l_token_start_pos - 1) ||
                     substr(l_statement,
                            l_token_end_pos + length(l_closing_token));
    
      l_start_search_pos := l_token_start_pos;
    ELSE
      EXIT;
    END IF;
  END LOOP;
  --
  LOOP
    l_bind_start_pos := nvl(instr(l_statement,
                                  ':'),
                            0);
    EXIT WHEN(l_bind_start_pos = 0);
  
    IF substr(l_statement,
              l_bind_start_pos + 1,
              1) <> '"' THEN
    
      l_name := upper(substr(l_statement,
                             l_bind_start_pos,
                             31));
      --
      l_length := length(l_name);
      FOR j IN 2 .. l_length LOOP
        l_char := substr(l_name,
                         j,
                         1);
        IF (l_char NOT BETWEEN 'A' AND 'Z' AND l_char NOT BETWEEN '0' AND '9' AND
           l_char NOT IN ('_',
                           '$',
                           '#')) THEN
          l_name := substr(l_name,
                           1,
                           j - 1);
          EXIT;
        END IF;
      END LOOP;
    ELSE
      --
      l_name := substr(l_statement,
                       l_bind_start_pos + 2,
                       31);
    
      l_name := upper(substr(l_name,
                             1,
                             instr(l_name,
                                   '"') - 1));
      --
      IF l_name IS NOT NULL
         AND (instr(l_name,
                    wwv_flow.lf) > 0 OR instr(l_name,
                                                   wwv_flow.cr) > 0 OR
         instr(l_name,
                    ':') > 0) THEN
        l_name := NULL;
      ELSE
        l_name := ':' || l_name;
      END IF;
    END IF;
    --
    IF length(l_name) > 1
       AND instr(l_added_binds,
                 l_name || ':') = 0 THEN
      l_added_binds := l_name || l_added_binds;
      apex_string.push(l_binds,
                       lower(ltrim(l_name,
                                   ':')));
    END IF;
    --
    l_statement := substr(l_statement,
                          l_bind_start_pos + 1);
  END LOOP;
  --
  RETURN l_binds;
  --
END get_binds;