?
Solved

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

Posted on 2016-08-03
2
Medium Priority
?
148 Views
Last Modified: 2016-08-04
Hi,

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 )
is
    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);
begin
    -- 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
    loop
        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
    loop
        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||
                          l_value;
    end loop;
    sys.dbms_sql.close_cursor(l_cursor);

/* 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);

    loop
        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||
                          l_value;
    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');
    sys.owa_util.http_header_close;
    sys.htp.p(l_return_value);
   
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;
0
Comment
Question by:sakthikumar
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 41740594
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
0
 
LVL 2

Assisted Solution

by:Jayesh Acharya
Jayesh Acharya earned 1000 total points
ID: 41740960
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question