Solved

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

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now