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

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
LVL 2
BILL CarlisleAPEX DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BILL CarlisleAPEX DeveloperAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
you could create it as a pipelined function, but it's probably not necessary since you'll likely be pulling the restults exhaustively
0
BILL CarlisleAPEX DeveloperAuthor Commented:
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
sdstuberCommented:
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
BILL CarlisleAPEX DeveloperAuthor Commented:
"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
BILL CarlisleAPEX DeveloperAuthor Commented:
Works Beautiful!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.