Connection scripts in OBIEE11g with real time example / connection scripts in connection pool properties

We can use this option to execute a command script.

There are four possibilities where we can use to execute command script in RPD level.

1) Execute on connect

2) Execute before query

3) Execute after query

4) Execute on disconnect

For example we will discuss about Execute on connect

We will get few common requirements like .... changing NLS_DATE_FORMAT.
or client may ask some times what are the users loggged in the dashboard side.

 For example we have a requirement whenever user logis in ...it should insert one record with user name and login time.

This requirement we can do in simple way by using connection scripts.

Follow below steps:

1) Open connection scripts tab.

2) Click on Execute on connect




3) Click on new button.



4) Enter below command and click on OK.



 


 5) Before that create LOGIN_TABLE in database by using below sql command.

CREATE TABLE LOGIN_TABLE

  "USER" VARCHAR2(20 BYTE),
  "DATE" TIMESTAMP(6)



 



5) Now login with weblogic account and run any report.













 6) Login with other account ... Here my other account is Asia.





 And run any report.






 6) Now check the table which we created in beginning (Login_table).



 We logged in two different accounts...weblogic and Asia.
Table has updated with user and date.........

Let me know if you have any issues regarding this post.


Bye....until next post.......

 

How to change Oracle database character set WE8MSWIN1252 to AL32UTF8

Changing character set for Oracle database.

My scenario is to support Arabic data format.

My existing oracle database character set is : WE8MSWIN1252

My requirement is to change this character set to  AL32UTF8 ( Which will support Arabic format)

Step :1

Set your ORACLE_HOME and SID .

Windows :


SET  ORACLE_HOME= E:\app\7stl\product\11.1.0\db_1

SET SID=ORCL

Linux : Use export command to set ORACLE_HOME and SID.



Step 2 :

Connect sqlplus through command prompt.

Enter user name as below shown in screenshot.
          user-name:    / as sysdba




step 3:

Shutdown your database immediate or normal by using below command.

               shutdown normal
 

Step 4 :

mount your database by using below command

            startup mount



step 5:

Now execute below commands

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
System altered.





Step 6:

SQL> ALTER DATABASE OPEN;
Database altered.



Step 7:

Now change the character set by using below command.

ALTER DATABASE CHARACTER SET AL32UTF8;



If you facing this error '' ORA-12712: new character set must be a superset of old character set''
use below command to overcome from this issue.

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;




If you facing below error while executing above command then follow below steps:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30556: functional index is defined on the column to be modified

Execute this command first then followed by character set command.

SQL> alter system set "_system_trig_enabled"=FALSE

System altered.

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Database altered.

Step 9:

SQL> SHUTDOWN NORMAL;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP;





Now query your Database to confirm the change in CHARACTER SET.

Use below query :

select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET',  'NLS_NCHAR_CHARACTERSET');



Your character set has changed successfully.
Please let me know if you facing any other issues when you implementing this.

Regards,
Naga