Category Archives: Tech

Oracle, SQL, Operating Systems

Active Directory kullanıcılarını bir tablodan SELECT etmek:

Tech, , ,

Oracle veri tabanından domain sunucunuza erişip kullanıcıları görme konusunu detaylı örneklerle anlatan sayısız doküman var.

Bu örnekler genel anlamda DBMS_LDAP paketini kullanarak domain sunucunuza nasıl erişeceğinizi, eriştikten sonra nasıl browse edeceğinizi gösteriyor.

Test etmek için domain sunucunuzun adres ve port bilgilerini edinmelisiniz. Tabii bir de username/password.

Ben bu örneklerde tarif edilen yöntemi kendi tablomu doldurmasını sağlayacak şekilde biraz değiştirdim. Bunu yaparken de neredeyse tamamen Billy~Verreynne ‘in https://community.oracle.com/thread/4064849 adresindeki önerilerini kullandım.

Eklediğim bir kaç küçük şey oldu:

  • ldap_host, ldap_user, ldap_passw gibi değişkenleri plain-text göndermek yerine encrypt etmek istedim.
  • bu örnek attribute_name ve attribute_value isimli iki kolonda alt alta sıralıyordu oysa ben isim, email, departman, title, telefon gibi bilgileri de içeren bir tablom olsun, bu tablo ben her sorguladığımda canlı olarak LDAP üzerinden beslensin istiyordum.

Yaptıklarımsa şunlar;

  • önce objelerimi hazırladım.
    • TLDAPATTR için kullandığım kolonları özelleştirebilirsiniz. Ama tabii bu durumda DBMS_LDAP kullandığımız fonksiyonda da uygun değişiklikler yapmamız gerekecek.
CREATE OR REPLACE TYPE tstrings IS TABLE OF VARCHAR2(4000);
 
CREATE OR REPLACE TYPE tldapattr IS object 
(
attribute_name VARCHAR2(50), 
attribute_value VARCHAR2(4000), 
dispname VARCHAR2(255), 
email VARCHAR2(255), 
department VARCHAR2(255), 
memberof VARCHAR2(255), 
manager VARCHAR2(255), 
extension VARCHAR2(255
);
 
CREATE OR REPLACE TYPE tldapattrlist IS TABLE OF tldapattr;
  • sonra type objemde görmek istediğim kolonları besleyecek şekilde değiştirerek fonksiyonumu create ettim.
CREATE OR REPLACE FUNCTION ldapattr (filter VARCHAR2, attributes tstrings DEFAULT NULL) 
RETURN tldapattrlist pipelined AUTHID definer IS
 
ldap_host    VARCHAR2(256);
ldap_port    VARCHAR2(256) := '389';
ldap_user    VARCHAR2(256); 
ldap_passw   VARCHAR2(256);
ldap_base    VARCHAR2(256) := 'OU=buraya_Organization_Unit_bilgisi_yaz,dc=sizin_domain_controlleriniz,dc=muhtemelen_com'; 
LDAP_ATTR    CONSTANT tstrings := NEW tstrings('displayname' ,'mail', 'department', 'memberof', 'manager','telephonenumber');
 
retVal          INTEGER;  
ldapSession     DBMS_LDAP.SESSION;  
attrList        DBMS_LDAP.string_collection;  
valList         DBMS_LDAP.string_collection;  
ldapMessage     DBMS_LDAP.message;  
berElement      DBMS_LDAP.ber_element;  
ldapTimeout     DBMS_LDAP.timeval;  
 
attrName        VARCHAR2(256);  
attrDisplay     VARCHAR2(4000);  
name            VARCHAR2(256);  
attrNum         INTEGER;  
i               INTEGER; 
 
v_dispname     VARCHAR2(255);
v_email        VARCHAR2(255);  
v_department   VARCHAR2(255);
v_memberof     VARCHAR2(255);
v_manager      VARCHAR2(255);
v_extension    VARCHAR2(255);
BEGIN
SELECT 'foo' INTO ldap_host FROM dual;
SELECT 'goo' INTO ldap_user FROM dual;
SELECT 'hoo' INTO ldap_passw FROM dual;
 
pipe ROW(TLDAPATTR('PARAM.<filter>', filter, v_dispname, v_email, v_department, v_memberof, v_manager, v_extension));  
         DBMS_LDAP.USE_EXCEPTION := TRUE;
 
-- create LDAP session  
         ldapSession := DBMS_LDAP.init(hostname => LDAP_HOST, portnum  => LDAP_PORT);  
         retval := DBMS_LDAP.simple_bind_s(ld=> ldapSession,dn=> LDAP_USER,passwd=> LDAP_PASSW);  
 
-- build a distinct ordered list of attributes  
         i := 0;  
         FOR c IN (SELECT DISTINCT column_value AS ATTR FROM TABLE(NVL(attributes,LDAP_ATTR)) ORDER BY 1) LOOP  
                 i := i + 1;  
                 attrList(i) := c.attr;  
                 attrDisplay := attrDisplay || c.attr || ' ';  
         END LOOP;  
pipe ROW(TLDAPATTR('PARAM:<attribute list>', TRIM(attrDisplay), v_dispname, v_email, v_department, v_memberof, v_manager, v_extension));  
 
-- use supplied filter to search  
        ldapTimeout.seconds := 5;  
        ldapTimeout.useconds := 0;
 
        retval := DBMS_LDAP.search_st(
		ld => ldapSession,
		base => ldap_base,
		scope => DBMS_LDAP.SCOPE_SUBTREE,
		filter => filter,
		attrs => attrList,
		attronly => 0,
		tv => ldapTimeout,
		res => ldapMessage);
 
-- is the search successful?  
        retval := DBMS_LDAP.count_entries(ld => ldapSession, msg => ldapMessage);  
        IF retval = 0 THEN  
pipe ROW(TLDAPATTR('Error', 'No matches found for filter ['||filter||'].',v_dispname, v_email, v_department, v_memberof, v_manager, v_extension));  
retVal := DBMS_LDAP.unbind_s(ld => ldapSession);  
RETURN;  
        END IF;  
-- Get all the entries returned by our search.  
        ldapMessage := DBMS_LDAP.first_entry(ld  => ldapSession, msg => ldapMessage);  
 
        WHILE ldapMessage IS NOT NULL  
        LOOP  
-- Get all the attributes for this entry.  
        attrName := DBMS_LDAP.first_attribute(ld => ldapSession, ldapentry => ldapMessage, ber_elem  => berElement);  
--degerleri her defasinda resetle
v_dispname   := NULL;
v_email      := NULL;
v_department := NULL;
v_memberof   := NULL;
v_manager 	 := NULL;
v_extension  := NULL;
-- output a null row for formatting an empty line between records  
                pipe ROW( NULL );  
-- output DN as unique record identifier  
                pipe ROW(TLDAPATTR('distinguishedName', DBMS_LDAP.get_dn(ldapSession, ldapMessage), v_dispname, v_email, v_department, v_memberof, v_manager, v_extension));   
-- now output attribute name-values found for the DN  
attrNum := 1;
		LOOP  
		EXIT WHEN attrName IS NULL;
		EXIT WHEN attrNum > attrList.COUNT;   
-- Get all the values for this attribute.  
		valList := DBMS_LDAP.get_values (ld => ldapSession, ldapentry => ldapMessage, attr => attrName);
			FOR i IN valList.FIRST .. valList.LAST
			LOOP
				IF attrName    = 'displayName' THEN v_dispname := SUBSTR(valList(i),1,200);
				ELSIF attrName = 'mail' THEN v_email := SUBSTR(valList(i),1,200);
				ELSIF attrName = 'department' THEN v_department := SUBSTR(valList(i),1,200);
				ELSIF attrName = 'memberOf' THEN v_memberof := SUBSTR(valList(i),1,200);
				ELSIF attrName = 'manager' THEN v_manager := SUBSTR(valList(i),1,200);
				ELSIF attrName = 'telephoneNumber' THEN v_extension := SUBSTR(valList(i),1,200);
				END IF ;
			END LOOP values_loop;
name := valList.FIRST;  
WHILE name IS NOT NULL LOOP  
pipe ROW(TLDAPATTR(attrName,valList(name),v_dispname, v_email, v_department, v_memberof, v_manager, v_extension));  
name := valList.Next(name);  
END LOOP;  
 
attrName := DBMS_LDAP.next_attribute(ld => ldapSession, ldapentry => ldapMessage, ber_elem  => berElement);
attrNum := attrNum + 1;  
END LOOP;  
 
ldapMessage := DBMS_LDAP.next_entry(ld  => ldapSession, msg => ldapMessage);  
END LOOP;  
 
-- Disconnect from the LDAP server.  
retVal := DBMS_LDAP.unbind_s(ld => ldapSession);  
RETURN;  
END;

bunları yaptıktan sonra şöyle test edebiliyor olmalısınız:

SELECT * 
FROM TABLE(ldapattr(filter => '(&(objectclass=user) (!(useraccountcontrol=514)))'))

514 pasif kullanıcıları dışarıda bırakmak için. Search stringi kendi ihtiyaçlarınıza göre değiştirebilirsiniz. Son olarak datayı derli toplu görmek için bir VIEW oluşturun.

Böylece a/d kullanıcılarınız, bu kullanıcıların bağlı olduğu yöneticiler, departmanları, masa telefonları gibi bilgilere dilediğiniz zaman gerçek zamanlı olarak erişebilirsiniz.

APEX import. Bad Request?

Tech, , , ,

The request could not be understood by server due to malformed syntax.

I needed to export one of my APEX application page to deploy it on another APEX environment.

But it raised this error: BAD REQUEST, malformed syntax.

It’s not self explanatory. Hard to understand what’s wrong at first glance. I checked .sql file to see if there is an ascii character, a non-unicode string and so on.

Better check your application logs

If you see an error like this and have used WIZARD template, perhaps the easiest work-around is: remove wizard region, export again, then import.

Finally!

APEX Page Validation: a global check for special characters

Tech, , , , , , ,

I’ve been struggling with a long form which has tens of text areas on my APEX env.

At some point I needed to apply some restrictions on input fields. Basically to refrain any unwanted non-unicode characters to be inserted into table.

APEX has great Built-in restrictions and they work like a charm. However, it has only a few options. It didn’t help my case when I had to set a restriction to reject all characters but a-Z letters, all the numbers, spaces AND punctuations.

Perhaps there are easier ways to do that. Actually I’m sure there are easier ways to do that. But here is what I ‘ve done and feel free to use & feedback.

DECLARE
l_query VARCHAR2(100);
l_res clob;
v_out clob;
v_special NUMBER;
CURSOR cur_main IS
SELECT item
FROM (
SELECT item_name item 
FROM APEX_APPLICATION_PAGE_ITEMS 
WHERE application_id = :app_id AND page_id = :app_page_id 
AND LOWER(display_as) LIKE '%text%'
);
BEGIN
v_out:= '';
   FOR rec_main IN cur_main LOOP
   l_query := 'select v('''||NVL(rec_main.item, 'null')||''') from dual';
   EXECUTE IMMEDIATE l_query INTO l_res;
   v_out := v_out||l_res;
   END LOOP;
SELECT COUNT(1) 
INTO v_special 
FROM dual 
WHERE regexp_like(REPLACE(v_out, CHR(13)||CHR(10), '_carriegeReturn_'), 
                 '[^a-zA-Z0-9 .,:;?%&+-_]');
   IF v_special >0 THEN
   RETURN FALSE;
   ELSE
   RETURN TRUE;
  END IF;
END;

let’s unwrap it a bit:

  1. cur_main is the cursor where I get all my text-based input fields on the page.
  2. l_query holds my dynamic sql string.
  3. v_out is concatenated string which consists of each item’s value on the screen for current page. note that it won’t work based on the data from session state.
  4. I simply hate being have to use regular expressions. So I copied a simple syntax and modified for my scenario.

Rest are just true/false conditions.

In my case it’s a Page Validation Process. To pass this validation no rows should be returned. Otherwise it will raise the error message.

Cheers!

Apex 5 Universal Theme – Navigation Experiment #4

Tech

His blog is a gem :)

svenweller's avatarsvenweller

Coloring side menu entries

Sometimes you want to add a little more color to the navigation menu. The idea in one project was to use colors for certain areas in an application. The color would show the user where he is currently working. Whether such colors are a feature that end users like, is still part of an ongoing discussion. But here are the technical details how to implement it.

The example uses a very small apex application that I’ll be using for a DOAG2015 presentation “Exotic SQL” (http://syntegris-doag.de/#vortraege).

Before / After
apex5_sidebar_colored_beforeapex5_sidebar_colored_after

Steps to create this

I explain the changes in detail a little further down.

  1. Create a new List template based upon the current one.
  2. Switch the user interface from the old to the new template
  3. Edit the template
  4. Add colors to the parent list entries
  5. Update alls sub menus to match the parent color

Special Features:

  • can be…

View original post 902 more words

10 Oracle SQL features you probably didn’t know

Tech

svenweller!

svenweller's avatarsvenweller

10 Oracle SQL features you probably didn’t know

I can not guarantee that you never heared of this. But the majority of developers doesn’t know or doesn’t seem to know. And honestly –  most of this information is fairly useless – and mostly harmless.

10. Why we have the CONCAT function

There is a concat function that can be used instead of the concat operator ||.

Nobody uses that! But there is a reason why it exists.

This is from the 8.1.7 Oracle documention

On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function…

View original post 2,420 more words

Why APEX running inside the database is a GOOD thing!

Tech

John Scott's avatarJohns Blog

I’ve had quite a few discussions with people regarding whether APEX running inside the database is a good thing or a bad thing. Personally I believe it is definitely a good thing, I see no downsides really. However a recent forum thread

For me, the fact that APEX runs inside the database means that one of the major positives is you are able to take full advantage of each and every database available to you. A recent forum thread illustrates this very nicely. The poster in the thread has deleted some files and has unfortunately performed a commit before realising their mistake.

Here is where the features of the database can help you, for example we are able to take advantage of the Flashback feature to get the database back to the state it was in before the files were deleted.

The user in the forum deleted some files from…

View original post 838 more words

note to mezelf: chunk CLOB?

Tech,

Ever had a trouble while you were trying to print a variable, got an ORA-06502 error?

ORA-06502: PL/SQL: numeric or value error string

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Here is what I came across to. A big and special thanks to oracle forums, of course :)


PROCEDURE chunk_clob(p_clob CLOB) IS
v_buf_size VARCHAR2(32000);
v_offset PLS_INTEGER DEFAULT 1;
v_size PLS_INTEGER;
v_len PLS_INTEGER;
v_chunk PLS_INTEGER;
v_clob CLOB := p_clob;
BEGIN
v_chunk := dbms_lob.getchunksize(v_clob);
v_len := dbms_lob.getlength(v_clob);
WHILE v_offset v_chunk
THEN v_size := v_chunk;
ELSE v_size := v_len - (v_offset - 1);
END IF;
v_buf_size := NULL;
dbms_lob.READ(v_clob, v_size, v_offset, v_buf_size);
htp.prn(v_buf_size);
v_offset := v_offset + v_size;
END LOOP;
END;

– OKAY what is this and what am I going to do with this?

It ‘ll take care of your oversized strings so you get to see what your VAR has in it.


my_pck.chunk_clob(l_show_html);

Oracle Database In-Memory and Hash Keys

Tech

Dani Schnider's avatarData Warehousing with Oracle

Hash keys are often used as primary keys in Data Vault environments. When loading dimensional data marts from a Data Vault schema, it seems to be obvious to use them as dimension keys, too. At least in combination with Oracle Database In-Memory, this is not a good idea.

View original post 14,906 more words