Oracle: DROP TABLE OR SEQUENCE IF EXIST als Prozedur

CREATE OR REPLACE
PROCEDURE drop_object
(
ObjName IN VARCHAR2
) IS
counter  NUMBER := 0;
to_drp   VARCHAR2(200) := UPPER(ObjName);
drp_stmt VARCHAR2(200) := NULL;

BEGIN

  SELECT COUNT(*)
  INTO counter
  FROM user_tables
  WHERE table_name = to_drp;
  IF counter = 1
  THEN drp_stmt := 'Drop Table ' || to_drp;
  EXECUTE IMMEDIATE drp_stmt;
  END IF;

  SELECT COUNT(*) INTO counter
  FROM user_sequences
  WHERE sequence_name = to_drp;
  IF counter = 1 THEN
    drp_stmt := 'DROP SEQUENCE ' || to_drp;
    EXECUTE IMMEDIATE drp_stmt;
  END IF;

END DROP_OBJECT;
/

Der Aufruf der Prozedur könnte z.B. so erfolgen:

CALL drop_object('t_test');
CREATE TABLE t_test
(
  test_id NUMBER, uid_text VARCHAR2(80)
)

Oracle: LDAP ID als Primärschlüssel

CALL drop_object('t_test');
CREATE TABLE t_test (test_id NUMBER, uid_text VARCHAR2(80));

CREATE OR REPLACE TRIGGER insert_lid
BEFORE INSERT ON t_test FOR EACH ROW

DECLARE
l_ldap_host  VARCHAR2(256) := 'hera.leipzig.ufz.de';
l_ldap_port  VARCHAR2(256) := '389';
l_ldap_base  VARCHAR2(256) := 'dc=ufz,dc=de';
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_vals       DBMS_LDAP.string_collection;

BEGIN    
l_session := DBMS_LDAP.init
(
  hostname => l_ldap_host,
  portnum  => l_ldap_port
);

l_retval := DBMS_LDAP.simple_bind_s
(
  ld     => l_session,
  dn     => NULL,
  passwd => NULL
);
                                      
l_attrs(0) := 'uidNumber'; 
l_retval := DBMS_LDAP.search_s
(
  ld       => l_session, 
  base     => l_ldap_base, 
  scope    => DBMS_LDAP.SCOPE_SUBTREE,
  filter   => '(&
                 (nsrole=*roleself*)
                 (objectClass=ufzperson)
                 (uid=' || :new.uid_text || ')
               )',
  attrs    => l_attrs,
  attronly => 0,
  res      => l_message
);   

IF DBMS_LDAP.count_entries
(
  ld => l_session,
  msg => l_message
) = 1
THEN
  l_entry := DBMS_LDAP.first_entry
  (
    ld  => l_session,
    msg => l_message
  );
                                                                                 
  l_vals := DBMS_LDAP.get_values
  (
    ld        => l_session,
    ldapentry => l_entry,
    attr      => l_attrs(0)
  );
END IF;                                     
  
DBMS_OUTPUT.PUT_LINE
(
  l_attrs(0) || ' = ' || l_vals(0)
);
  
l_retval := DBMS_LDAP.unbind_s
(
  ld => l_session
);
  
:new.test_id := l_vals(0);

END;
/

Der Trigger könnte z.B. so ausgelöst werden:

INSERT INTO t_test (uid_text)
VALUES ('dutzend');