Created
January 23, 2019 14:34
-
-
Save Dani3lSun/d284a282a8359fe209c6a60ed4d7193a to your computer and use it in GitHub Desktop.
Get Bind Variable Names of given SQL Statement in Oracle
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment