Solved

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

Posted on 2016-08-03
2
75 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 70
Oracle create type table from existing table%rowtype ? 6 63
Error in creating a view. 8 34
Oracle SQL Developer - SubString 2 27
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 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