You Asked

Tom,

I have a requirement such that given a string of characters representing a SQL statement, I want to select out the names of the bind variables as a delimited string. For example:

SQLStr :='Select person_id, full_name from per_all_people_f where effective_start_date between :START and :END';

I want to get from SQLStr a delimited string that represents the bind variable names :

'START,END'

Does Oracle have an existing package that will do this for me, or must I write something myself?

Kris

and Connor said...

A little regex should help here

SQL> with t as
  2  ( select 'Select person_id, full_name from per_all_people_f where effective_start_date between :START and :END' str from dual )
  3  select
  4    listagg(bind,',') within group ( order by pos ) list
  5  from
  6  ( select regexp_substr(str,'\:\w+',1,i) bind, i pos
  7    from t,
  8         ( select rownum i from dual
  9           connect by level <= 10 )
 10  )
 11  where bind is not null;

LIST
---------------------------------------------------------------------------------------------------------------------------------------
:START,:END

You can adjust the code to keep or remove the colon.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great use of RegEx but there is a hitch....

Kris, June 17, 2016 - 4:33 pm UTC

Hi Conner,

That is a great use of RegEx. However my problem is that I want to exclude tokens beginning with a colon that are strings. Say I have a select statement like:

Select ' the is my :fake bind' Fake, person_id, full_name from per_all_people_f where effective_start_date between :START and :END;

If a column contains a constant text string that happens to contain a colon, it will falsely be included as a bind variable. Is there a way to eliminate false binds?

June 20, 2016 - 1:38 pm UTC

If you have colons within quotes, it's easier to replace the whole quoted string with a placeholder. Then you can use Connor's approach:

with t as ( 
  select 'Select person_id, full_name, '':skip'' from per_all_people_f where effective_start_date between :START and :END' str from dual 
), quotes_removed as (
  select regexp_replace(str, '''.*''', '''###''') str from t
)
  select * from quotes_removed;

STR                                                                                                       
-----------------------------------------------------------------------------------------------------------
Select person_id, full_name, '###' from per_all_people_f where effective_start_date between :START and :END

  
with t as ( 
  select 'Select person_id, full_name, '':skip'' from per_all_people_f where effective_start_date between :START and :END' str from dual 
), quotes_removed as (
  select regexp_replace(str, '''.*''', '''###''') str from t
), binds as (
  select regexp_substr(str,'\:\w+',1,i) bind, i pos
  from quotes_removed,
       ( select rownum i from dual
         connect by level <= 10 )
)
  select listagg(bind, ',') within group (order by pos) binds from binds;

BINDS                                                                                                                                                          
------------------------------------------------------------------------------------
:START,:END

Chris

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Great use of RegEx but there is a hitch....

More to Explore

PL/SQL demos

PL/SQL docs

List of bind variables in sql statement

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Great use of RegEx but there is a hitch....

More to Explore

PL/SQL demos

PL/SQL docs