Setting Bind Variables in ADF

In this post we will see some of the ways to programmatically set bind variables in ADF ViewObject.

Setting Value to Bind Variable

The following code sets the value for the the bind variable defined in the whereClause of the ViewObject.

ViewObject viewobj = am.findViewObject(“OrdersVO”);
viewobj.setNamedWhereClauseParam(“BndOrderNumber”, 1001);
viewobj.executeQuery();

Bind Variable in View Criteria

But most often we use ViewCriterias to define the whereClause of the viewObject so that the ViewObject definition could be reused in multiple screens. The following code sets the value for the bind variable defined in the whereClause.

ViewObject vo = getOrdersVO();
ViewCriteria vc = vo.getViewCriteriaManager().getViewCriteria("OrdersViewCriteria");
vc.resetCriteria();
vo.ensureVariableManager().setVariableValue(“BndOrderNumber”, order_number );
vo.applyViewCriteria(vc);
vo.executeQuery();

Dynamic Bind Variable

In some scenarios we would have to set whereClause dynamically. In this case we could create a bind variable on the viewObject and then set its value.

viewobj.setWhereClause(“order_no = :BndOrderNumber”);
viewobj.defineNamedWhereClauseParam(“BndOrderNumber”,null ,null);
viewobj.setNamedWhereClauseParam(“BndOrderNumber”,234)
viewobj.executeQuery();

SQL Notes and Tips

SQL Tips and Notes

Some SQL queries and useful sql statements for ADF/SOA Developers or at least I use them so often.

Database size

Find out table sizes

select table_name,(num_rows*avg_row_len)/(1024*1024) MB from user_tables order by 2 desc;

Gather Schema status

EXEC DBMS_STATS.gather_schema_stats('DEV_SOAINFRA');

Find out the schema sizes

SELECT *

FROM

( SELECT

   c.tablespace_name,

   ROUND(a.bytes/1048576,2)                    MB_Allocated,

   ROUND(b.bytes/1048576,2)                    MB_Free,

   ROUND((a.bytes-b.bytes)/1048576,2)          MB_Used,

   ROUND(b.bytes/a.bytes * 100,2)              tot_Pct_Free,

   ROUND((a.bytes-b.bytes)/a.bytes,2) * 100    tot_Pct_Used

 FROM

   ( SELECT

       tablespace_name,

       SUM(a.bytes) bytes

     FROM

       sys.DBA_DATA_FILES a

     GROUP BY

       tablespace_name

   ) a,

   ( SELECT

       a.tablespace_name,

       NVL(SUM(b.bytes),0) bytes

     FROM

       sys.DBA_DATA_FILES a,

       sys.DBA_FREE_SPACE b

     WHERE

       a.tablespace_name = b.tablespace_name (+)

       AND a.file_id = b.file_id (+)

     GROUP BY

       a.tablespace_name

   ) b,

   sys.DBA_TABLESPACES c

 WHERE

   a.tablespace_name = b.tablespace_name(+)

   AND a.tablespace_name = c.tablespace_name

 )

WHERE

 tot_Pct_Used >=0

ORDER BY

 tablespace_name;

Database Back and Restore

Cold Backup

Find out files:

select name from sys.v_$datafile;

select member from sys.v_$logfile;

select name from sys.v_$controlfile;

Shutdown the Database

alter system checkpoint;

shutdown abort

startup restrict

shutdown immediate

Change DB password

select username, account_status from dba_users;

alter user dev_soainfra identified by welcome1;

alter user test2_soainfra account unlock;

ALTER PROFILE DEFAULT LIMIT

FAILED_LOGIN_ATTEMPTS UNLIMITED

PASSWORD_LIFE_TIME UNLIMITED;

change password for all expired users

set hea off

select 'alter user '||username||' identified by welcome1;' from dba_users where account_status like 'EXPIRED%';

select 'alter user '||username||' account unlock;' from dba_users where account_status like 'LOCK%';

-- To find out the profiles

select profile, username from dba_users;

-- To find out LOGIN ATTEMPT LIMIT for the profiles

select LIMIT, RESOURCE_NAME from dba_profiles

where PROFILE = (select profile

                 from dba_users

                where username = 'DEV_SOAINFRA');

Logging in SQL

drop table test_log;

create table test_log (a number(10), b varchar2(4000));

create sequence test_seq;

create or replace PROCEDURE test_logger(p_text IN varchar2)

as

PRAGMA AUTONOMOUS_TRANSACTION;

begin

 insert into test_log values (test_seq.nextval, p_text);

 commit;

END;

/

CALL TEST_LOGGER('HELLO');

SELECT * FROM TEST_LOG ORDER BY A DESC;

TRUNCATE TABLE TEST_LOG;

Redo Log generation rate

SELECT TRUNC(NEXT_TIME,'HH24'),ROUND(SUM(BLOCKS*BLOCK_SIZE/1024/1024/1024),2) REDO_GB

FROM GV$ARCHIVED_LOG

where next_time > sysdate -4

GROUP BY TRUNC(NEXT_TIME,'HH24')

ORDER BY TRUNC(NEXT_TIME,'HH24');

Creating procedure objects to use with SOA

CREATE TYPE security_challenge_obj

AS

 OBJECT

 (user_name varchar2(254),

  max_val_attempts number(3),

  val_attempts_period varchar2(3),

  activity_type varchar2(30));   

 /

set serveroutput on;

DECLARE

 P_SECURITY_CHALLENGE AR_SOA.SECURITY_CHALLENGE_OBJ;

 P_PERMIT_VALIDATION VARCHAR2(200);

 P_STATUS VARCHAR2(200);

 P_ERROR_MESSAGE VARCHAR2(200);

 p_count number;

BEGIN

 -- Modify the code to initialize the variable

 p_security_challenge := ar_soa.SECURITY_CHALLENGE_OBJ(null,null,null,null);

 P_SECURITY_CHALLENGE.user_name := 'helasdfasdfasdfasdfasdfasdfasdfasdflo';

 P_SECURITY_CHALLENGE.max_val_attempts := '3';

 P_SECURITY_CHALLENGE.val_attempts_period := '24';

 P_SECURITY_CHALLENGE.activity_type := 'accounter';

  USER_VALIDATION_PKG.CHECK_VALIDATION_ATTEMPTS(

   P_SECURITY_CHALLENGE => P_SECURITY_CHALLENGE,

   P_PERMIT_VALIDATION => P_PERMIT_VALIDATION,

   p_validation_count => p_count

  );

  --:P_PERMIT_VALIDATION := P_PERMIT_VALIDATION;

DBMS_OUTPUT.PUT_LINE('P_STATUS = ' || P_STATUS);

  --:P_STATUS := P_STATUS;

DBMS_OUTPUT.PUT_LINE('P_ERROR_MESSAGE = ' || P_ERROR_MESSAGE);

DBMS_OUTPUT.PUT_LINE('P_count = ' || p_count);

DBMS_OUTPUT.PUT_LINE('P_PERMIT_VALIDATION  = ' || P_PERMIT_VALIDATION);

--  :P_ERROR_MESSAGE := P_ERROR_MESSAGE;

END;

/

Find and Kill Table Locks

SELECT s.username dbuser,owner || '.' || object_name "Object",

s.osuser,

s.PROGRAM,

lk.sid,

lk.type lock_type,

decode(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.lmode)) lockmode,

decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.request)) requestmode

FROM v$lock lk,

v$session s,

dba_objects ao

WHERE lk.lmode > 1

AND object_name LIKE 'USER%'

AND s.username IS NOT NULL

AND lk.sid = s.sid

AND ao.object_id(+) = lk.id1;

select session_id from dba_dml_locks where name = 'USER_REMINDER_STATUS' ;

select sid, serial# from v$session where sid in ( 9);

alter system kill session '9,10679';

Monitor JDBC connections

select

   inst_id,

   machine,

   program,

   username,

   osuser,

   failed_over,

   to_char(logon_time,'DD/MM/YYYY HH:MI'),

   count(*) COUNT

from gv$session

where username IN ('PERF_SOAINFRA','AR_SOA_RW','PERF_MDS')

group by inst_id,

   machine,

   program,

   username,

   osuser,

   failed_over,

   to_char(logon_time,'DD/MM/YYYY HH:MI')

order by inst_id,

   machine,

   program,

   username,

   osuser,

   failed_over,

   to_char(logon_time,'DD/MM/YYYY HH:MI')

  INST_ID MACHINE                   PROGRAM                        USERNAME        OSUSER          FAI  COUNT

---------- ------------------------- ------------------------------ --------------- --------------- --- ------

        1 fang                      JDBC Thin Client               AR_SOA_RW       oracle          NO       2

        1 feral.ref.london.02.net   JDBC Thin Client               AR_SOA_RW       oracle          NO       2

        2 fang                      JDBC Thin Client               AR_SOA_RW       oracle          NO       2

        2 feral.ref.london.02.net   JDBC Thin Client               AR_SOA_RW       oracle          NO       2

         1 fang                      JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29

        1 feral.ref.london.02.net   JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29

        2 fang                      JDBC Thin Client               PERF_SOAINFRA   oracle          NO      28

        2 feral.ref.london.02.net   JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29

Sleep Function using Java

CREATE OR REPLACE PROCEDURE Sleep_fn(sec1 NUMBER)

AS LANGUAGE JAVA

NAME 'java.lang.Thread.sleep(long)';

/

Find Database processes limit

select resource_name, current_utilization, max_utilization, limit_value

   from v$resource_limit

   where resource_name in ('sessions', 'processes');

Purge AQ table

DECLARE

 po_t dbms_aqadm.aq$_purge_options_t;

BEGIN

  dbms_aqadm.purge_queue_table('XXCUST_TABLE', NULL, po_t);

END;

/

How to get current userId in ADF

How to get username from ADF Bc - EOs and VOs

The current username can be set to the default expressions and BindVariables using the groovy expressions like this

adf.context.securityContext.getUserPrincipal().getName()
or
adf.context.securityContext.getUserName()

Another way to get User ID in Application Module 

String userName = this.getUserPrincipalName();

How to get Logged in User Roles  in Application Module ?

List userRoles = Arrays.asList(ADFContext.getCurrent().getSecurityContext().getUserRoles());

How to get username from ADF Managed Beans

SecurityContext object in ADF can be used to get the current usename from the managed beans and business components implementation classes (AMImpl, VOImpl, RowImpl etc).

    ADFContext adfCtx = ADFContext.getCurrent();
    SecurityContext secCntx = adfCtx.getSecurityContext();
    String user = secCntx.getUserPrincipal().getName();
    String _user = secCntx.getUserName();

How to get current user using EL Expressions on the pages

SecurityContext object can be accessed from EL expressions on pages as follows

#{securityContext.userName}