Forum Stats

  • 3,853,525 Users
  • 2,264,231 Discussions
  • 7,905,378 Comments

Discussions

Find bind variable names on a given sql statement?

AllenS.
AllenS. Member Posts: 78 Blue Ribbon

Hi,

Does oracle provide a package/function/procedure that derives the bind variable names of a given query?

For ex: select * from per_people_x where person_id = :person_id or first_name = :first_name

In the example query above, I'm expecting both person_id and first_name as bind variable names to be returned.

I looked at the view V$SQL_BIND_CAPTURE but it seems only queries that have been executed are listed here. In my case, I haven't executed the query.

This is something similar to what the SqlDeveloper tool does when running a query like this where it pops up a window asking the user to enter values for all bind variables available in the query.

Appreciate any help.

Tagged:

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,877 Red Diamond

    Nope, not as far as I know. Back in 10g, I wrote my own parser for extracting bind variables for my SQL and PL/SQL cursor class on DBMS_SQL.

    Oracle APEX makes extensive use of executing dynamic SQL and PL/SQL code with full support for dynamic binding. Perhaps there is a documented API call for supplying the bind variables for a dynamic SQL or PL/SQL code block.

  • padders
    padders Member Posts: 1,081 Silver Trophy

    There is in fact a relevant APEX API within recent APEX installs although I don't believe it is either documented or made publicly available (by synonym or grant) which may be a cause for concern regarding permitting production code dependent on it.

    For example...

    DECLARE
       lt_symbol wwv_flow_plsql_lexer.t_symbol;
    BEGIN
       wwv_flow_plsql_lexer.init (
          p_code => 'SELECT * FROM per_people_x WHERE person_id = :person_id OR first_name = :first_name');
    
       LOOP
          lt_symbol := wwv_flow_plsql_lexer.get ();
    
          CASE lt_symbol
             WHEN wwv_flow_plsql_lexer.c_sy_bind_variable THEN
                dbms_output.put_line (a => wwv_flow_plsql_lexer.g_lexer.val);
             WHEN wwv_flow_plsql_lexer.c_sy_eof THEN
                EXIT;
             ELSE
                NULL;
          END CASE;
       END LOOP;
    END;
    /
    
    :PERSON_ID
    :FIRST_NAME
    

    Note that this API as tested appears to expect a bind variable name to be a valid oracle identifier whereas I believe legal bind variable names also allow the leading character to be a digit.