asked on

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

Sean Stuber

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))

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BILL Carlisle

ASKER

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

Sean Stuber

you could create it as a pipelined function, but it's probably not necessary since you'll likely be pulling the restults exhaustively

BILL Carlisle

ASKER

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

Sean Stuber

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.

BILL Carlisle

ASKER

"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..

BILL Carlisle

ASKER

Works Beautiful!