BIP: Report Usernames

 

There was a simple requirement to print the login name of the user running a BIPublisher report on that report itself. This got me hunting. There are tonnes of ways to get the login name from an oracle database, but because of the way BIP uses the DB and runs the SQL, not all of those ways would give the right answers. cleartext.blogspot.com

Simplest way, use the  <?$XDO_USER_NAME?>  parameter n your template.

But my requirement was to enforce data security on the report, to ensure the logged in user sees only the records he should see. This meant fetching the username at the SQL itself.

One sure shot way to do this is to use :XDO_USER_NAME in the SQL. This will fetch the userid of the logged in user. cleartext.blogspot.com

And there is one more way.

Use the sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER function in the SQL. cleartext.blogspot.com

 

To get all the sys_context parameters, this SQL can be used. cleartext.blogspot.com

select
  sys_context ('userenv','ACTION') ACTION,
  sys_context ('userenv','AUDITED_CURSORID') AUDITED_CURSORID,
  sys_context ('userenv','AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,
  sys_context ('userenv','AUTHENTICATION_DATA') AUTHENTICATION_DATA,
  sys_context ('userenv','AUTHENTICATION_METHOD') AUTHENTICATION_METHOD,
  sys_context ('userenv','BG_JOB_ID') BG_JOB_ID,
  sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
  sys_context ('userenv','CLIENT_INFO') CLIENT_INFO,
  sys_context ('userenv','CURRENT_BIND') CURRENT_BIND,
  sys_context ('userenv','CURRENT_EDITION_ID') CURRENT_EDITION_ID,
  sys_context ('userenv','CURRENT_EDITION_NAME') CURRENT_EDITION_NAME,
  sys_context ('userenv','CURRENT_SCHEMA') CURRENT_SCHEMA,
  sys_context ('userenv','CURRENT_SCHEMAID') CURRENT_SCHEMAID,
  sys_context ('userenv','CURRENT_SQL') CURRENT_SQL,
  sys_context ('userenv','CURRENT_SQLn') CURRENT_SQLn,
  sys_context ('userenv','CURRENT_SQL_LENGTH') CURRENT_SQL_LENGTH,
  sys_context ('userenv','CURRENT_USER') CURRENT_USER,
  sys_context ('userenv','CURRENT_USERID') CURRENT_USERID,
  sys_context ('userenv','DATABASE_ROLE') DATABASE_ROLE,
  sys_context ('userenv','DB_DOMAIN') DB_DOMAIN,
  sys_context ('userenv','DB_NAME') DB_NAME,
  sys_context ('userenv','DB_UNIQUE_NAME') DB_UNIQUE_NAME,
  sys_context ('userenv','DBLINK_INFO') DBLINK_INFO,
  sys_context ('userenv','ENTRYID') ENTRYID,
  sys_context ('userenv','ENTERPRISE_IDENTITY') ENTERPRISE_IDENTITY,
  sys_context ('userenv','FG_JOB_ID') FG_JOB_ID,
  sys_context ('userenv','GLOBAL_CONTEXT_MEMORY') GLOBAL_CONTEXT_MEMORY,
  sys_context ('userenv','GLOBAL_UID') GLOBAL_UID,
  sys_context ('userenv','HOST') HOST,
  sys_context ('userenv','IDENTIFICATION_TYPE') IDENTIFICATION_TYPE,
  sys_context ('userenv','INSTANCE') INSTANCE,
  sys_context ('userenv','INSTANCE_NAME') INSTANCE_NAME,  cleartext.blogspot.com
  sys_context ('userenv','IP_ADDRESS') IP_ADDRESS,
  sys_context ('userenv','ISDBA') ISDBA,
  sys_context ('userenv','LANG') LANG,
  sys_context ('userenv','LANGUAGE') LANGUAGE,
  sys_context ('userenv','MODULE') MODULE,
  sys_context ('userenv','NETWORK_PROTOCOL') NETWORK_PROTOCOL,
  sys_context ('userenv','NLS_CALENDAR') NLS_CALENDAR,
  sys_context ('userenv','NLS_CURRENCY') NLS_CURRENCY,
  sys_context ('userenv','NLS_DATE_FORMAT') NLS_DATE_FORMAT,
  sys_context ('userenv','NLS_DATE_LANGUAGE') NLS_DATE_LANGUAGE,
  sys_context ('userenv','NLS_SORT') NLS_SORT,
  sys_context ('userenv','NLS_TERRITORY') NLS_TERRITORY,
  sys_context ('userenv','OS_USER') OS_USER,
  sys_context ('userenv','POLICY_INVOKER') POLICY_INVOKER,
  sys_context ('userenv','PROXY_ENTERPRISE_IDENTITY') PROXY_ENTERPRISE_IDENTITY,
  sys_context ('userenv','PROXY_USER') PROXY_USER,
  sys_context ('userenv','PROXY_USERID') PROXY_USERID,
  sys_context ('userenv','SERVER_HOST') SERVER_HOST,
  sys_context ('userenv','SERVICE_NAME') SERVICE_NAME,
  sys_context ('userenv','SESSION_EDITION_ID') SESSION_EDITION_ID,
  sys_context ('userenv','SESSION_EDITION_NAME') SESSION_EDITION_NAME,
  sys_context ('userenv','SESSION_USER') SESSION_USER,
  sys_context ('userenv','SESSION_USERID') SESSION_USERID,
  sys_context ('userenv','SESSIONID') SESSIONID,
  sys_context ('userenv','SID') SID,
  sys_context ('userenv','STATEMENTID') STATEMENTID, cleartext.blogspot.com
  sys_context ('userenv','TERMINAL') TERMINAL
from dual
     
Try running this on Oracle Live SQL

SQLFiddle (for Oracle) is broken. Try Oracle Live SQL

 

BI Publisher reporting work involves a lot of SQL. And often (in the Oracle world) one needs a quick way to try an Oracle function in a quick sandbox like system. For  long time, I have been using SQLFiddle, which provides a simple browser UI to create schema and run SQLs on them. But for many months now, the Oracle support on SQLFiddle has been broken. And I was looking for alternatives.

Turns out, Oracle themselves provides an excellent replacement tool to create schemas and run SQLs. Free for anyone who has an oracle.com login. That includes all customers and vendors working on their products. cleartext.blogspot.com

Go to Oracle Live SQL. The SQL worksheet page there can be used to run any SQLs, and its on a 12c DB server. cleartext.blogspot.com

 

There is a simple schema designer to create schema. Or you can also use standard DDL functions. Turns out , this is actually a simple Apex application. cleartext.blogspot.com

 

cleartext.blogspot.com

Understanding SQL's Null

 

Tony Hoare invented the null reference in 1965, and he considers it his "billion-dollar mistake" for the amount of trouble it has caused. Even today, SQL's null value is the cause of several common mistakes.

Let’s go over the most egregious.

Equals Null

These two queries return the exact same result on a users table with many rows:

select * from users where deleted_at = null;
-- result: 0 rows

select * from users where deleted_at != null;
-- result: 0 rows

How can that be? It's because null represents an "unknown" type. This means it doesn't make sense to compare null to anything else with normal conditional operators. Null isn't even equal to itself:

select null > 0;
-- result: null

select null < 0;
-- result: null

select null = 0;
-- result: null

select null = null;
-- result: null

The right way to compare values with null is with the is, and is not operators:

select * from users 
where deleted_at is null;
-- result: all non-deleted users

select * from users
where deleted_at is not null;
-- result: all deleted users

If you want to check if two columns are different, you can use is distinct from:

select * from users
where has_address is distinct from has_photo
-- result: users with an address or 
-- photo, but not both

Not in Null

One handy way to filter rows is with a subselect. For example, if you wanted the userswho did not have any packages, you could write a query like this:

select * from users 
where id not in (select user_id from packages)

But if one of the rows in packages has a null user_id, this query will return no results! To understand why this happens we need to factor the query like the SQL compiler does. Here's a simpler example:

select * from users 
where id not in (1, 2, null)

Which translates to:

select * from users 
where id != 1 and id != 2 and id != null

As we now know, id != null is an unknown value, null. Using and on any value withnull becomes null, so all of the other and conditions fall away. No rows match the resulting query since null is not equal to true.

If the condition is inverted, the query works fine. This time we'll look for users withpackages.

select * from users 
where id in (select user_id from packages)

Which we can simplify for the example:

select * from users 
where id in (1, 2, null)

This query translates to:

select * from users 
where id = 1 or id = 2 or id = null

Since the where clause is a list of or conditions, it doesn't matter that one of them is null. That condition is simply ignored because non-true values do not change the evaluation of the rest of the clause.

Sorting Nulls

When it comes to sorting, nulls are considered the largest possible value. This can lead to some frustrating queries when trying to sort values descending, since all the nulls will be on top.

This query is meant to show the users ranked by their points, but it's putting users without any points first!

select name, points
from users
order by 2 desc;
-- null points sort above 
-- any number of points!

There are two ways to deal with this. The easiest way is to get rid of those nulls in the output or the comparison using coalesce:

-- treat nulls as 0 in output
select name, coalesce(points, 0)
from users
order by 2 desc;

-- keep nulls in output, but sort as 0
select name, points
from users
order by coalesce(points, 0) desc;

And if your database supports it, you can instead tell it where to put nulls when sorting with nulls first or nulls last:

select name, coalesce(points, 0)
from users
order by 2 desc nulls last;

Of course, nulls can also be used to prevent errors. One great use of nulls is in dealing with divide by zero errors.

Divide by Zero

Divide by zero errors are especially painful. Queries that ran fine yesterday all of a sudden are failing with divide by zero errors. One common solution is to check if the denominator is 0 before dividing with a case statement:

select case when num_users = 0 then 0 
else total_sales/num_users end;

The case statement approach is verbose and duplicates the denominator. That's OK if the denominator is simple, but if it's an expression, you're likely to get more bugs if you change the query later.

Here we can use null to our advantage. Use nullif on the denominator to make the denominator null instead of zero. Rather than failing, the query will return null on days where num_users = 0.

select total_sales/nullif(num_users, 0);

If you prefer the result to be 0 or anything else instead of null, use coalesce on the previous query:

select coalesce(total_sales/nullif(num_users, 0), 0);
-- nulls results become 0

Conclusion

Tony Hoare may regret his mistake, but at least it’s easy to work around the issues that null presents. Now go forth with your new knowledge and keep null from nullifying your future query results!

Alter Session Parameter for Siebel Query

 

For Oracle 10g

alter session set optimizer_mode = first_rows_10
alter session set "_optimizer_sortmerge_join_enabled" = false
alter session set "_optimizer_join_sel_sanity_check" = true

Siebel 8 Local DB Encryption & Backend Access

 

Anyone who has worked on the Siebel version 8 flavours is bound to have run into problems with its new local db encryption feature. As a default setting, Siebel encrypts the local db with an RSA encryption key, which means the local db thus extracted, cannot be accessed via the backend. Tools and the locally installed client will continue to work, but if you need to actually debug data entry issues in the database, the system will just respond saying incorrect password.

Traditionally, there is a tool called dbisqlc, an executable present in the Tool\Bin folder, which is mean to access Sybase databases. I have used this tool numerous times in the past to debug data entry issues and run spooled SQL statements. But if your localdb is encrypted, you will need to provide some more inputs to dbisqlc so that it can open up the dbf file.

Thankfully, there are helpful documents on suppportweb here and here which explains everything in detail.

 

1. Determine the location of the local database that user wants to connect to and note down the full path of the database file for example %SIEBEL_CLIENT_ROOT%\local\sse_data.dbf.

2. Launch dbisqlc.exe from the %SIEBEL_CLIENT_ROOT%\bin directory to bring up the Connect to Adaptive Server Anywhere dialog. On the Login tab fill out the following fields.NOTE: The values for the User ID and Password have to be in upper case:

Field	Value
User ID	DBA
Password	<Enterprise Name> in ALL CAPITALS

NOTE: The default DBA password value is the enterprise name. If the enterprise name has less than 8 characters, the name is padded with consecutive digits 1234. For example:

o If the Enterprise Name is Siebel2005, then the DBA password is SIEBEL2005.

o If the Enterprise Name is Siebel, then the DBA password is SIEBEL12.

o

On the Database tab fill out the following field:

Field	Value
Database File	C:\Sea\local\sse_data.dbf

NOTE: Starting in Siebel version 8.0, you will need to run the following query to retrieve the encrypted value for the local database owner and enter this value on the Encryption Key field. 

SELECT PREF_CD, VAL FROM S_NODE_PREF s1, S_NODE s2 WHERE s1.PREF_CD ='RemLocSec:PlainKey' AND s1.NODE_ID = s2.ROW_ID AND s2.NAME='<mobile client name of the local database';

3. After clicking on the OK button, the user will connect to the local database. A message "Connected to database" should appear in the Statistics sub-window.

 

Knowing the DBA password for your local db can also be useful when the tables need to be extended.  Sometimes in Siebel tools, when you do an apply activate, the tools wizard goes into a hang and refuses to come out. I found that when I entered the userid as DBA and corresponding password generated as in the steps above, the tools system was able to complete the apply DDL step easily.

 

Also refer to this excellent post which explains how to reset forgotten passwords.

SQL Spools in Siebel and Trace commands

We have all been there; at some point or other, Siebel starts throwing SQL errors, or the application slows down due to some long running SQL statements.

“An error has occurred executing a Sql statement. Please continue or ask your systems administrator to check your application configuration if the problem persists.(SBL-DBC-00105)”

The best way to figure out what went wrong is to spool out the actual SQL statement (could be query or insert/update or delete) which ran in the application.

You could turn on SQL spooling when the client starts using the /s option.

Or you could increase Log levels

Now both these options enables SQL spooling when the client itself starts; and the resulting file will contain every SQL statement which ran up until the error can be reproduced. Is there any way we can turn SQL logging on the fly, to spool up only the SQLs we want and ignore the rest ?

Turns out, there are script commands TraceOn and TraceOff, which can be used the spool out the SQL statements. Documentation can be found here.

We will have to start SQL tracing using script at the beginning of the even,and then stop it after the spooling is done. I found that these commands can also be used in COM controls.

So I have created a simple SQL instant spooler in Excel macros, which will do this for you, without the need for increasing log levels or writing scripts. I have tested it on Siebel 7.8 and Siebel 8.1 local and dedicated clients, and it works fine. All you need to do is

1) Open the Siebel local or dedicated client (does not work on the thin client)

2) Use the start and stop buttons on the sheet to enable SQL tracing.

3) The SQL which was run in Siebel between start and stop will be opened in notepad

You can download the SQL instant spooler here and try it out. You need to have Siebel installed properly and enable the macros on the excel when it is opened.

Cheerios !!

sql update insert space before caps and capitalize first charachter

I was loading up EAILoolup Values for use in a interation module I had. The external values where strings without spaces and first letter of each word was capitalized.

 

Like this

 

reopenedOnClientRequest.

 

Its equivalent Siebel value to be show was

 

Reopened On Client Request.

 

So I figured all I needed was an SQL update statement to replace a space before each capitalized letter and make the first letter caps…

 

select initcap(regexp_replace(EXT_VALUE,'([^^])([A-Z])','\1 \2')) from S_EAI_LOOKUPMAP where LOOKUP_TYPE = 'ClaimStatus’

 

This did the trick !

See all developer logins in Siebel

I wanted to see all users who had localdbs on our server.

 

select * from user_role_privs

 

..will list all the users roles. The user with SSE_ROLE permissions are registered developers, and they could have local dbs.