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

  ¶ 3/10/2010 01:23:00 AM   0 Comments Links to this post

  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.

Setup
APEX: 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

  ¶ 3/09/2010 01:09:00 AM   0 Comments Links to this post

  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

  ¶ 3/08/2010 08:41:00 AM   0 Comments Links to this post

  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

  ¶ 3/03/2010 03:48:00 PM   1 Comments Links to this post

  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

  ¶ 3/01/2010 10:30:00 PM   0 Comments Links to this post

  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

  ¶ 2/28/2010 11:55:00 PM   0 Comments Links to this post

  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

  ¶ 2/24/2010 10:17:00 PM   6 Comments Links to this post

  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 Tool
Now, 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

  ¶ 2/24/2010 05:30:00 PM   5 Comments Links to this post