Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to best create a SQL using a PL/SQL Procedure output

Posted on 2014-08-05
8
Medium Priority
?
965 Views
Last Modified: 2014-08-05
Hi,
I have a procedure that outputs with htp.p() a list of emails.
What is the best way to access that list in a SQL statement?
? stream? I have a senior moment happening where I can't even think of the name of "it" let along do it.

I am trying to create an LOV in Oracle APEX that uses the procedure for its source.

What more can I tell you?

Here is the procedure
create or replace PROCEDURE P_GET_LDAP_EMAILS (
                         p_search_str VARCHAR2)    IS 
    
 -- Adjust as necessary.
 l_ldap_host VARCHAR2(256) := 'xxxxx';
 l_ldap_port VARCHAR2(256) := '389';
 l_ldap_user VARCHAR2(256) := 'xxxxt';
 l_ldap_passwd VARCHAR2(256) := 'xxxx';
 l_ldap_base VARCHAR2(256) := xxxxx
 l_retval PLS_INTEGER; 
 l_session DBMS_LDAP.session;
 l_attrs DBMS_LDAP.string_collection;
 l_message DBMS_LDAP.message;
 l_entry DBMS_LDAP.message;
 l_attr_name VARCHAR2(256);
 l_ber_element DBMS_LDAP.ber_element;
 l_vals DBMS_LDAP.string_collection;
 
-- begin P_GET_LDAP_EMAILS(); end;
BEGIN
  -- Choose to raise exceptions.
  DBMS_LDAP.USE_EXCEPTION := TRUE;
 
  --dbms_output.put_line('1. start');
  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host, portnum => l_ldap_port);
  --dbms_output.put_line('2. init ok :'|| l_session);
  
  l_retval := DBMS_LDAP.simple_bind_s(ld => l_session, dn => l_ldap_user, passwd => l_ldap_passwd );
  
  --dbms_output.put_line('3. bind ok :'||l_retval);
  
  -- Get all attributes
  l_attrs(1) := 'mail'; --'*'; -- retrieve all attributes --proxyAddresses
  l_retval := DBMS_LDAP.search_s(ld => l_session, base => l_ldap_base, scope => DBMS_LDAP.SCOPE_SUBTREE, 
                                 filter => 'name='||p_search_str, --',
                                 attrs => l_attrs, attronly => 0, res => l_message);
 
  dbms_output.put_line('4. search ok :' || l_retval);
  dbms_output.put_line('5. result :' || l_message);

  IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
    -- Get all the entries returned by our search.
    l_entry := DBMS_LDAP.first_entry(ld => l_session, msg => l_message);
    -- entry_loop 
    WHILE l_entry IS NOT NULL LOOP
      -- Get all the attributes for this entry.
      -- DBMS_OUTPUT.PUT_LINE('---------------------------------------');
      l_attr_name := DBMS_LDAP.first_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element);
      -- attributes_loop 
      WHILE l_attr_name IS NOT NULL LOOP
        -- Get all the values for this attribute.
        l_vals := DBMS_LDAP.get_values (ld => l_session, ldapentry => l_entry, attr => l_attr_name);
        -- values_loop 
        FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
         -- if(SUBSTR(l_vals(i),1,5) = 'SMTP:')then
            --DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' ||SUBSTR(l_vals(i),6,200));
            htp.p(SUBSTR(l_vals(i),1,200));
          --end if;
        END LOOP values_loop;
        --DBMS_OUTPUT.PUT_LINE('OUT1');
        l_attr_name := DBMS_LDAP.next_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element);
      END LOOP attibutes_loop;
      l_entry := DBMS_LDAP.next_entry(ld => l_session, msg => l_entry);
    END LOOP entry_loop;
  END IF;
  -- Disconnect from the LDAP server.
  l_retval := DBMS_LDAP.unbind_s(ld => l_session);
  --DBMS_OUTPUT.PUT_LINE('l_retval: ' || l_retval);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Others :'||SQLERRM); 
END;​

Open in new window




Thank you,
Bill
0
Comment
Question by:bcarlis
  • 4
  • 4
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40241118
An APEX list isn't built on htp.p output, it's built either as a static list or from a SQL query.

If you want to use LDAP to be the driver,  then I'd turn your procedure into a table function and then use that function as the basis of the query for the dynamic list

something like this...

select * from table(your_function(:p_search_str))
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40241124
or, if your function is returning a collection of single values (addresses) then maybe something like

select null, column_value, null from  table(your_function(:p_search_str)) ;

modifying your function to return a collection of strings might look like this

CREATE OR REPLACE FUNCTION p_get_ldap_emails(p_search_str VARCHAR2)
    RETURN vcarray
IS
    -- Adjust as necessary.
    l_ldap_host   VARCHAR2(256) := 'xxxxx';
    l_ldap_port   VARCHAR2(256) := '389';
    l_ldap_user   VARCHAR2(256) := 'xxxxt';
    l_ldap_passwd VARCHAR2(256) := 'xxxx';
    l_ldap_base   VARCHAR2(256) := 'xxxxx';
    l_retval      PLS_INTEGER;
    l_session     DBMS_LDAP.session;
    l_attrs       DBMS_LDAP.string_collection;
    l_message     DBMS_LDAP.MESSAGE;
    l_entry       DBMS_LDAP.MESSAGE;
    l_attr_name   VARCHAR2(256);
    l_ber_element DBMS_LDAP.ber_element;
    l_vals        DBMS_LDAP.string_collection;

    l_array       vcarray := vcarray();
-- begin P_GET_LDAP_EMAILS(); end;
BEGIN
    -- Choose to raise exceptions.
    DBMS_LDAP.use_exception := TRUE;

    --dbms_output.put_line('1. start');
    -- Connect to the LDAP server.
    l_session := DBMS_LDAP.init(hostname => l_ldap_host, portnum => l_ldap_port);
    --dbms_output.put_line('2. init ok :'|| l_session);

    l_retval := DBMS_LDAP.simple_bind_s(ld => l_session, dn => l_ldap_user, passwd => l_ldap_passwd);

    --dbms_output.put_line('3. bind ok :'||l_retval);

    -- Get all attributes
    l_attrs(1) := 'mail'; --'*'; -- retrieve all attributes --proxyAddresses
    l_retval :=
        DBMS_LDAP.search_s(
            ld         => l_session,
            base       => l_ldap_base,
            scope      => DBMS_LDAP.scope_subtree,
            filter     => 'name=' || p_search_str, --',
            attrs      => l_attrs,
            attronly   => 0,
            res        => l_message
        );

    DBMS_OUTPUT.put_line('4. search ok :' || l_retval);
    DBMS_OUTPUT.put_line('5. result :' || l_message);

    IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0
    THEN
        -- Get all the entries returned by our search.
        l_entry := DBMS_LDAP.first_entry(ld => l_session, msg => l_message);

        -- entry_loop
        WHILE l_entry IS NOT NULL
        LOOP
            -- Get all the attributes for this entry.
            -- DBMS_OUTPUT.PUT_LINE('---------------------------------------');
            l_attr_name :=
                DBMS_LDAP.first_attribute(
                    ld          => l_session,
                    ldapentry   => l_entry,
                    ber_elem    => l_ber_element
                );

            -- attributes_loop
            WHILE l_attr_name IS NOT NULL
            LOOP
                -- Get all the values for this attribute.
                l_vals :=
                    DBMS_LDAP.get_values(ld => l_session, ldapentry => l_entry, attr => l_attr_name);

                -- values_loop
                FOR i IN l_vals.FIRST .. l_vals.LAST
                LOOP
                    -- if(SUBSTR(l_vals(i),1,5) = 'SMTP:')then
                    --DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' ||SUBSTR(l_vals(i),6,200));
                    --HTP.p(SUBSTR(l_vals(i), 1, 200));
                    l_array.EXTEND;
                    l_array(l_array.COUNT) := SUBSTR(l_vals(i), 1, 200);
                --end if;
                END LOOP values_loop;

                --DBMS_OUTPUT.PUT_LINE('OUT1');
                l_attr_name :=
                    DBMS_LDAP.next_attribute(
                        ld          => l_session,
                        ldapentry   => l_entry,
                        ber_elem    => l_ber_element
                    );
            END LOOP attibutes_loop;

            l_entry := DBMS_LDAP.next_entry(ld => l_session, msg => l_entry);
        END LOOP entry_loop;
    END IF;

    -- Disconnect from the LDAP server.
    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
    --DBMS_OUTPUT.PUT_LINE('l_retval: ' || l_retval);
    RETURN l_array;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('Others :' || SQLERRM);
END;

Open in new window



where vcarray is a sql collection type

CREATE OR REPLACE TYPE VCARRAY AS TABLE OF VARCHAR2(4000);

Open in new window

0
 
LVL 2

Author Comment

by:bcarlis
ID: 40241182
Sean that's exactly what I need!
I couldn't think "table function" and another key work "pipe"...
Yes, the htp.p was just to get the data I needed showing in the SQL Workshop command window.. :)

Thanks so much, Bill
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 74

Expert Comment

by:sdstuber
ID: 40241193
you could create it as a pipelined function, but it's probably not necessary since you'll likely be pulling the restults exhaustively
0
 
LVL 2

Author Comment

by:bcarlis
ID: 40241228
works great in SQL Workshop...
But I failed to mention my end desired use for it is in a Auto-Complete Text Field..

How does the user's typing get into my function's parameter? with the asterisk appended to it?
Plus, I don't want it to search until at least two characters have been typed.. ??

select * from table(F_GET_LDAP_EMAILS (mytyping||'*'))

I can't use the asterisk and allow the auto-complete to put the where on the full return because it would get the
      ORA-31202: DBMS_LDAP: LDAP client/server error: Sizelimit exceeded

Thank you for your help, Bill
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40241248
It's not something I've done before, but I imagine you'd need to use a dynamic action (on KeyPress event) to capture the text input and call the function as the data changed.

Getting into that is somewhat beyond the scope of this question though.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 40241256
"Getting into that is somewhat beyond the scope of this question though."

That's exactly what I was thinking.. thank you for your answer on this initial question :-)

Will post new question for next step..
0
 
LVL 2

Author Closing Comment

by:bcarlis
ID: 40241259
Works Beautiful!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

572 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