Solved

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

Posted on 2016-08-03
2
90 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 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:jhacharya
jhacharya earned 250 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

615 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