Home » SQL & PL/SQL » SQL & PL/SQL » Using OF Pl/sql table of Records
Using OF Pl/sql table of Records [message #444346] Sat, 20 February 2010 13:41 Go to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Hello all,

Thru the Oracle Ldap Program i am attaining the expected result set and Which i need to pass the Below result set after Formatting the Condition directly to the table in the database.

Table

create table temp1
( ccommanname varchar2(45),
certificatetype varchar2 (96),
expiry time date);

Formatting Condition

1>Ccommanname column ==> the data in rdkuserid which has to Filtered By stripping the Email ID
2>certificatetype column Should Hold the data in this way
(if rdkuserid =123456654321.to.domain.com
if it is .To.domain.com it has to be converted as 'EEnc'
or else .From.domain.com it has to be converted as 'ddec')
3>expire time is rdkkeyexpiretime which has to converted in this DD/MM/YYYY HH:MI:SSSS

Result set.

<Data removed at posters request>
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- -------

i Think this can be accomplished by Pl/sql Table of records.How ever i Dont have much exposure in Using this.

please let me know if you need Further Information .

Appreciating your valuable response on a Timely Manner.

[Updated on: Mon, 22 February 2010 04:38] by Moderator

Report message to a moderator

Re: Using OF Pl/sql table of Records [message #444347 is a reply to message #444346] Sat, 20 February 2010 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>Which i need to pass the Below result set after Formatting the Condition directly to the table in the database.

INSERT INTO temp1 (ccommanname,certificatetype, expirytime) VALUES (...,...,...);

I see no need for Pl/sql Table of records.
Re: Using OF Pl/sql table of Records [message #444350 is a reply to message #444347] Sat, 20 February 2010 14:32 Go to previous messageGo to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Hi Blackswan,

When i am Running the Below Mentioned Oracle Ldap Program.i am getting the expected result.How ever The result set ( after undertaking the Formatting Condition )has to be Automated in such a way the record has to be inserted directly to underlying table Temp1 in the database.

Please let me know your valuable response.

DECLARE

l_ldap_host VARCHAR2(256) := '10.1.151.3';
l_ldap_port VARCHAR2(256) := '389';
l_ldap_user VARCHAR2(256) := 'cn=Anonymous';
l_ldap_passwd VARCHAR2(256) := '';
l_ldap_base VARCHAR2(256) := 'o=rdkkeys';

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
-- Choose to raise exceptions.
DBMS_LDAP.USE_EXCEPTION := TRUE;

-- Connect to the LDAP server.
l_session := DBMS_LDAP.init(hostname => l_ldap_host,
portnum => l_ldap_port);

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

-- Get all attributes
l_attrs(1) := 'rdkuserid';
l_attrs(2):='rdkexpiretime'; -- retrieve all attributes
l_retval := DBMS_LDAP.search_s(ld => l_session,
base => l_ldap_base,
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => 'objectclass=*',
attrs => l_attrs,
attronly => 0,
res => 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
DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
END LOOP values_loop;
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);
END;


Re: Using OF Pl/sql table of Records [message #444351 is a reply to message #444350] Sat, 20 February 2010 14:36 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>The result set ( after undertaking the Formatting Condition )has to be Automated in such a way the record has to be inserted directly to underlying table Temp1 in the database.

So proceed to do so.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

DECLARE
  l_ldap_host    VARCHAR2(256) := '10.1.151.3';
  l_ldap_port    VARCHAR2(256) := '389';
  l_ldap_user    VARCHAR2(256) := 'cn=Anonymous';
  l_ldap_passwd  VARCHAR2(256) := '';
  l_ldap_base    VARCHAR2(256) := 'o=rdkkeys';
  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
  -- Choose to raise exceptions.
  dbms_ldap.use_exception := true;
  
  -- Connect to the LDAP server.
  l_session := dbms_ldap.Init(hostname => l_ldap_host,portnum => l_ldap_port);
  
  l_retval := dbms_ldap.Simple_bind_s(ld => l_session,dn => l_ldap_user,passwd => l_ldap_passwd);
  
  -- Get all attributes
  L_attrs(1) := 'rdkuserid';
  
  L_attrs(2) := 'rdkexpiretime'; -- retrieve all attributes
  
  l_retval := dbms_ldap.Search_s(ld => l_session,base => l_ldap_base,scope => dbms_ldap.scope_subtree,
                                 filter => 'objectclass=*',attrs => l_attrs,
                                 attronly => 0,res => 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
          dbms_output.Put_line('ATTIBUTE_NAME: '
                               ||l_attr_name
                               ||' = '
                               ||Substr(L_vals(i),1,200));
        END LOOP values_loop;
        
        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);
END; 

[Updated on: Sat, 20 February 2010 14:36]

Report message to a moderator

Re: Using OF Pl/sql table of Records [message #444378 is a reply to message #444351] Sun, 21 February 2010 04:46 Go to previous message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
No Message Body

[Updated on: Mon, 22 February 2010 04:07]

Report message to a moderator

Previous Topic: Sum Daily Total to Weekly in Oracle
Next Topic: Datatype Issue
Goto Forum:
  


Current Time: Tue Sep 27 21:11:28 CDT 2016

Total time taken to generate the page: 0.13645 seconds