• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

wwv_flow_utilities.get_binds and v(substr(l_name_list(i)


Below is my procedure which I found online, but dont know the meaning of wwv_flow_utilities.get_binds and v(substr(l_name_list(i)
used inside the procedure.

procedure ajax_get_data (
    p_dynamic_action in apex_plugin.t_dynamic_action,
    p_plugin         in apex_plugin.t_plugin )
    l_statement    varchar2(32767) := p_dynamic_action.attribute_01;
    l_cursor       number;
    l_value        varchar2(4000);
    l_name_list    sys.dbms_sql.varchar2_table;
    l_dummy        number;
    l_return_value varchar2(32767);
    -- We can't use EXECUTE IMMEDIATE or OPEN FOR here, because we don't know
    -- at design time how many bind variables the statement will contain.
    -- That's why we have to use DBMS_SQL which provides a more flexible
    -- interface.
    l_cursor := sys.dbms_sql.open_cursor;
    -- First do a parse to verify if the statement works
    sys.dbms_sql.parse (
        c             => l_cursor,
        statement     => replace(l_statement, chr(13), ' '),
        language_flag => sys.dbms_sql.native );
    -- We are just interested in the first column of our statement
    sys.dbms_sql.define_column(l_cursor, 1, l_value, 4000);

    -- Bind all session state references eg. :P7_CATEGORY
    l_name_list := wwv_flow_utilities.get_binds(l_statement);
    -- bind each found page item with the value in session state
    for i in 1 .. l_name_list.count
        sys.dbms_sql.bind_variable(l_cursor, l_name_list(i), v(substr(l_name_list(i), 2)), 32000);
    end loop;

    -- Here we go!
    l_dummy := sys.dbms_sql.execute(l_cursor);
    -- If the SQL statement returns multiple rows then we will concatenate them
    -- to one string
    while sys.dbms_sql.fetch_rows(l_cursor) > 0
        dbms_sql.column_value(l_cursor, 1, l_value);
        l_return_value := l_return_value||
                          case when l_return_value is not null then ',' end||
    end loop;

/* In an Oracle 11g database the following code can also be used.
   It's more convenient because you can use the normal cursor commands and you
   can also do a BULK COLLECT.
   Replace the existing code after the sys.dbms_sql.execute statement and you
   can remove the sys.dbms_sql.define_column at the beginning of the procedure.
    -- convert our dbms_sql cursor into a SYS_REFCURSOR
    l_data := sys.dbms_sql.to_refcursor(l_cursor);

        fetch l_data into l_value;
        exit when l_data%notfound;

        l_return_value := l_return_value||
                          case when l_return_value is not null then ',' end||
    end loop;
    close l_data;
    -- Send the queried values back to the browser.
    sys.owa_util.mime_header('text/plain', false);
    sys.htp.p('Cache-Control: no-cache');
    sys.htp.p('Pragma: no-cache');
exception when others then
    -- Always make sure that the cursor is closed in case an error occurs!
    if sys.dbms_sql.is_open(l_cursor) then sys.dbms_sql.close_cursor(l_cursor); end if;
    raise; /* reraise the original error */
end ajax_get_data;
2 Solutions
they are part of the APEX framework

wwv_flow_utilities.get_binds  is a function that parses a SQL statement and returns a collection (dbms_sql.varchar2_table)  where each element is one of the bind variable names

"V" is a function that returns the value of an APEX item given the item's name
Jayesh AcharyaTechnichal ConsultantCommented:
I am note sure of your level of expertise
essentially sdstuber is correct.

the wwv_flow_utilities.get_binds

the wwv_flow_utilities is the  where the code of the function resides

The Get_binds is the actual function

more often then not the name will give you an idea of what its trying to do.
 So in this case Get_binds is allowing you to get the information from a string the
l_statement  string

This string is populated with the information in p_dynamic_action.attribute_01

you are then using the data in the l_statement to pull out the information you actually need.

if you look at the code it puts this information in the l_name_list variable

The "  v(substr(l_name_list(i), 2) "

think of the l_name_list like a table of values, and the (i) is letting you look at a particular row

so the l_name_list(i) is just getting you the value in l_name_list for the row (i) (if you think of this like a simple table)

Hope this helps

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now