APEX: Create and Parse Arrays
It's been awhile since I've been able to work with APEX extensively, so I am rusty. A question came up today whether we could get multiple values into a single variable (Item in APEX). Yes we can! APEX_UTILSNeed some data first: CREATE TABLE t ( some_text VARCHAR2(10) );
INSERT INTO t ( some_text ) SELECT dbms_random.string( 'a', 10 ) some_text FROM dual CONNECT BY LEVEL <= 5;
CJUSTICE@TESTING>SELECT * FROM t;
SOME_TEXT ---------- thrFXviVWJ kpfGRRwctv EVxNrcmBHC gcBlHaKrLa irYduOZfkS I want that table data to be in a single item. TABLE_TO_STRING is your function. VAR C VARCHAR2(100);
DECLARE l_table APEX_APPLICATION_GLOBAL.VC_ARR2; BEGIN SELECT some_text BULK COLLECT INTO l_table FROM t;
:c := apex_util.table_to_string( p_table => l_table ); END; /
PL/SQL procedure successfully completed.
C ----------------------------------------------------------- thrFXviVWJ:kpfGRRwctv:EVxNrcmBHC:gcBlHaKrLa:irYduOZfkS Easy enough. How about converting it back to a table? STRING_TO_TABLE is your answer. DECLARE l_table APEX_APPLICATION_GLOBAL.VC_ARR2; BEGIN l_table := apex_util.string_to_table( p_string => :c ); FOR i IN 1..l_table.COUNT LOOP d( 'value ' || i || ': ' || l_table(i) ); END LOOP; END; /
value 1: thrFXviVWJ value 2: kpfGRRwctv value 3: EVxNrcmBHC value 4: gcBlHaKrLa value 5: irYduOZfkS
PL/SQL procedure successfully completed. Done. Labels: apex, apex_util, howto
APEX: LDAP Authentication
I got called into a discussion about an existing APEX application. The custom LDAP functionality wasn't working as they expected. I knew APEX had an LDAP authentication scheme (and don't know the full history of the project so I can't (won't) comment on why it wasn't used). So I fired up my local sandbox just to see how easy or hard it was. Admittedly, I have always avoided anything to do with LDAP...not sure why (plate is full?). I used this as a guide. Anyway, it was remarkably easy. SetupAPEX: 3.2.1 Web Server: Apache (OHS) Database: BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for 32-bit Windows: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production First I fired up the web server: C:\oracle\http\opmn\bin>opmnctl start opmnctl: opmn started
C:\oracle\http\opmn\bin>opmnctl startproc process-type=HTTP_Server opmnctl: starting opmn managed processes... Opened up APEX, and created a new application. For authentication schemes I chose "No Authentication." After I had created the application, I went into Shared Components --> Authentication Schemes --> Create Select the default and click Next  Select "Show Login Page and Use LDAP Directory Credentials" and click Next  I've already done this so I'm selecting my current Login page, 11, click Next  Enter your LDAP Host and your DN:  Your DN String should look something like this (from article above): cn=%LDAP_USER%,l=amer,dc=oracle,dc=com Make sure you use the %LDAP_USER% after the cn= portion of the string. Name it ldap_test, click Create Scheme:  You will then be redirected back to the list of Authentication Schemes, ldap_test should now be current  To test it just run your application and login using your LDAP (AD) credentials  Success!  Labels: apex, howto
SQL Developer: Install Unit Testing Repository
Get the latest SQL Developer release here. I'm not a big tools guy, I prefer SQL*Plus to anything else. I especially don't like paying for tools (yes, the database is a tool and costs a lot of money...I do realize the hypocrisy). After Syme Kutz's presentation at SOUG, I've been looking more closely at SQL Developer. I've been using it (and JDeveloper) since they were both made freely available a few years ago. Mostly for the schema browsing, looking around, importing and exporting data. I do use it (SQL Developer) to write reports that I can share with the Business folks as well. Syme's presentation was primarily on Unit Testing (which I begged for). First step to using Unit Testing is to install the repository, a set of tables the application uses to build and store tests and their results. You need to have version 2.1 or greater. First up, go to Tools --> Unit Test --> Select Current Repository  You'll be prompted to select a connection (i.e. database) to use  Would you like to create one now? Select Yes.  You're then told the the required roles do not exist, select OK.  Confirm running SQL  Running...will take just a few seconds  Success!  That's it. Easy right? Future posts will detail managing users and creating tests. Labels: howto, sql developer, testing
OBIEE: XML File as Data Source
I received a question in the form of a comment on using XML files here. Fittingly, we were discussing this exact topic yesterday and I decided I would try it out. So here goes. You'll need 2 files: a XML file and a XSL stylesheet (which defines the XML file). I found said files here. I'll also provide them here (cdcatalog.xml) and here (cdcatalog.xls). In the Administration tool, go to File --> Import --> from Database  When you prompted to select a datasource, select XML and the window should look like this  For URL, browse to the location you saved cdcatalog.xml, do the same for XSLT  Hit OK and you'll be prompted with the Import dialog  Import the "table" and you're done.  Labels: howto, obiee, rpd
OBIEE: Dynamic Variables
Apparently this is a 3 part series, Part I, OBIEE: Text File as Data Source and Part II, OBIEE: Create Repository Init Block. Finally, to the ultimate goal, dynamic session variables. Ideally, these would be repository variables, but since I'm using the row-wise variety, that is not possible (as of 10.1.3.4). Once a day, or some other relatively long time frame would be sufficient. Anyway, there's an occasion where dynamically creating variables might be needed or necessary. To recap the problem Due to certain restrictions in our environment, the tnsnames entries in our separate environments (Dev/QA/Stage/Prod) are exactly the same. I would prefer to have one entry in each environment that points to that environments database, but alas, I cannot. What this does is forces us to change the password in the connection pools when migrating. I know it's not ideal, but it is what it is.
The proof of concept I am embarking on will allow us to lookup the tnsname entry and the associated password for that environment, theoretically, requiring no changes between environments.
In that respect, it was decided that we would have a single service account. From that service account, we could look up the values we need for each environment. Here's the other fun problem...what comes first? The chicken or the egg? This service account password will be stored in a simple text file that will be locked down by the system administrators. Don't ask what happens when the password changes either. At this point, it's just a proof of concept. So be gentle. If you have an established way to manage passwords...please share. A few people did chime in, Pete Scott suggesting using the a command line utility detailed here. Christian chimed in that I should use it at my own risk and rnm1978 said this problem has not been truly solved yet. The whole issue of migration OBIEE deployments through the environments is a bit of a pig's ear with no foolproof method that I've seen I need to look up that phrase as I'm certain it's peculiar to my brethren across the pond. Finally, the meat of the post. Here's my table: OBI@TESTING>@desc obi_variables Name Null? Type ------------------------------ -------- ------------- VARIABLE_NAME NOT NULL VARCHAR2(50) VARIABLE_VALUE NOT NULL VARCHAR2(250) VARIABLE_TYPE_CODE NOT NULL VARCHAR2(30) I'll throw a few records in there for fun (and to obviously demonstrate). INSERT INTO obi_variables ( variable_name, variable_value, variable_type_code ) VALUES ( 'TNS_TESTING', 'TESTING', 'TNSNAME' );
INSERT INTO obi_variables ( variable_name, variable_value, variable_type_code ) VALUES ( 'PW_TESTING', 'TESTING', 'PASSWORD' );
INSERT INTO obi_variables ( variable_name, variable_value, variable_type_code ) VALUES ( 'URL_TESTING', 'http://localhost:7777/pls/apex/f?p=101:1', 'URL' );
TYPE VARIABLE_NAM VARIABLE_VALUE ---------- ------------ ------------------------------------------ PASSWORD PW_TESTING TESTING URL URL_TESTING http://localhost:7777/pls/apex/f?p=101:1 TNSNAME TNS_TESTING TESTING For demonstration purposes, I am going to create a new database in the Physical layer and add a connection pool called obi variables pool. From the previous post, I'll be using the values that I brought in through the text file. The only difference is that I added username...just in case. To sum that up, I'm bringing in the TNSNAME entry, the USERNAME and the PASSWORD. Your connection pool should look like this:  The password column is the same format as the others, VALUEOF( INIT_PASSWORD ) You'll have to re-enter that when you save. Now I'll be creating an init block using this connection pool. I won't drag you through that entire process again (your welcome). This is a Session Init Block as opposed to the Repository Init Block in the previous example. The only difference is that I will be using a row-wise variable. I'll get to that in a second. After selecting the obi variable pool connection pool, entering the following SQL statement into the area provided: SELECT variable_name, variable_value FROM obi_variables .In the Edit Data Target area, select the row-wise initialization variable. Select OK. Test it and voila! You now have dynamic variables in OBIEE. Labels: howto, obiee
OBIEE: Single/Detail Record View
Recently, I've been searching for a Single Row/Detail type report in Answers. Sadly, there isn't one so named.
Here's what I could find in Answers:
Nothing jumps out at me, so, Twitter to the rescue again
Within minutes...I had an answer from Mr. Christian Berg. What was that answer? The Narrative View.
Here's the screen for the Narrative View:
According to Mr. Berg, you reference the columns using the @1 (@2, @3, etc) syntax, the number representing the column number. So I tried that
Run it...
That's not what I want...
OK, so let's see if I can add some HTML to it. First I check the Contains HTML Markup box and then I enter in the following in the Narrative Text Area: @1<br>
@2<br>
@3<br>
@4<br>
@5<br>
@6<br>
@7<br>
@8<br>
@9<br> Here's what it looks like:
Better, but not exactly what I want. Now that I know I can use HTML though, it should be very easy.
Voila! Labels: howto, obiee, reports
OBIEE: Create Repository Init Block
My post earlier today, OBIEE: Text File as Data Source , was the first part of my overall goal. Here's what I'm trying to do. Due to certain restrictions in our environment, the tnsnames entries in our separate environments (Dev/QA/Stage/Prod) are exactly the same. I would prefer to have one entry in each environment that points to that environments database, but alas, I cannot. What this does is forces us to change the password in the connection pools when migrating. I know it's not ideal, but it is what it is. The proof of concept I am embarking on will allow us to lookup the tnsname entry and the associated password for that environment, theoretically, requiring no changes between environments. In that respect, it was decided that we would have a single service account. From that service account, we could look up the values we need for each environment. Here's the other fun problem...what comes first? The chicken or the egg? This service account password will be stored in a simple text file that will be locked down by the system administrators. Don't ask what happens when the password changes either. At this point, it's just a proof of concept. So be gentle. If you have an established way to manage passwords...please share. We've pulled the text file into the RPD already. Now, I need to create 2 repository variables to hold these values. I open up the Administration tool, go to Manage then variables. You should see this:  Then follow this picture to create a new Initialization Block  Name your Initialization block init_block_test  Leave the scheduler stuff alone for now, click on Edit Data Source  First, browse for the connection pool  Select the get_local_password (yes, the name mysteriously changed from the default Connection Pool to "get_local_password," it makes sense doesn't it?).  In the Default Initialization String text box, enter: SELECT username, password FROM test.txt You can test it if you want, but I'm saving it for the final step. Your Initialization Block Data Source should look like this  Now select Edit Data Target  Which looks like this  Select New and enter INIT_USERNAME in the Name text box and make the Default Initializer 'USERNAME' (in single quotes).  Do the same thing for the password column, INIT_PASSWORD and 'PASSWORD' Now you are back at the main screen and the Test button (lower left hand corner) should be enabled. Select it.  One final test...let's see if we can access these in the presentation layer.  So it's a success right? After I was done, I began to think..."You can access the repository variable from the presentation server" Ummm...that's not good. Double checked the properties of both the Initialization Block and the Variables to see if there's a way to lock it down...and there's not. Because of this "small" little security issue, I'm not completely sold. It is a proof of concept, perhaps it should stay there. Labels: howto, obiee
OBIEE: Text File as Data Source
I have a requirement to pull data from a text file. OBI has the ability to pull from (just about) any database, but can it pull from a text file? Of course. First I'll create a simple file, call it test.txt. In the file I put cjustice testing that "space" between cjustice and testing is actually a tab character (usually represented as -> if you have view formatting turned on). Knowing that it is a tab will be important shortly. Save the file to your root folder, i.e. c:\test.txt Next up, go to your ODBC Datasources: Control Panel --> Administrative Tools --> Data Sources (ODBC) When that opens up, navigate to the System DSN tab and click on Add. You'll be prompted to choose a driver, select the Microsoft Text Driver (*.txt, *.csv)  Click on Finish. Next up, you should see the ODBC Text Setup screen, click on the Options tab to expand the window.  It should look like this:  Uncheck the Use Current Directory checkbox (default is usually c:\windows\system32):  Then select the Select Directory button and navigate to your root folder, or the place where you saved the test.txt file. You should see your file, greyed out, in the left hand pane.  Now, at the bottom of the window, uncheck Default (*.*) which should enable the Define Format button  Select the Define Format. For the Format drop down, choose Tab Delimited. Rows to Scan, in my case, will only be 1. You can leave it at the default if you want though. After you have done that, the Guess button should be enabled for you, go ahead and click it.  See F1 and F2? Those are your columns. Let's name them something meaningful so we can easily reference them later. In the Name box, it says F1, enter in USERNAME and click on Modify (the button to the right):  Do the same thing for the F2 column, name it PASSWORD. It should end up looking like this:  Select OK and then select OK again in the ODBC Text Setup screen. You should now see your new data source in the System DSN window. OBI EE Admin ToolNow, we need to pull this new data source into the RPD. Open up your RPD, go to File --> Import --> from Database You'll see this window, go ahead and select your new data source:  You'll be prompted with the Import window, expand the c:\ drive and then find your file  Select Import (at the bottom), wait for it to complete and then close the window. In your physical layer you should see a new "database" with the name "text_test" (or whatever you named your data source). Go ahead and expand that to see what you've got:  To verify, right click on the table "test.txt" and select View Data.  All done. Labels: howto, obiee
|