ORA-01858: a non-numeric character was found where a numeric was expected

I was getting this problesm while I was trying to debug PLSQL code written by someone else . And I found the sql statement like below where he was trying to apply to_date function on a date type column ,

The format of MY_DATE_COLUMN is like "09-JULY-2014" , so when to-date function was applied on this date format to convert it to MM/DD/YYYY oracle was throwing this error

SELECT TO_DATE(MY_DATE_COLUMN, 'MM/DD/YYYY') - INTERVAL '5' YEAR FROM DUAL

The solutionw was simple , to get rid of the to_date function , so the below query is working fine for me now.

SELECT MY_DATE_COLUMN - INTERVAL '5' YEAR FROM DUAL

Using Oracle PL/SQL how do I know to which database instance I am currently connected

Below query will give you the name of the instance you are currently connected with

SELECT sys_context('USERENV','DB_NAME') AS Instance FROM dual;

 Also we can use the below query to use the database name

select name from v$database

Spring Roo add an empty option to a combo

Spring roo by default creates a combo box with widgetType "dijit.form.FilteringSelect" applied on it , this combo box doesn't contain an empty option.

To add an empty option to it please follow the below steps

In your java code when you are preparing a List for that combo box try to add first option with

code =0 and value = -- Not Selected

and then add all the other options in that list.

A simple java code snipet I am using is

ComboDTO comboDTO=new ComboDTO();
comboDTO.setCode("0");
comboDTO.setDescription("--Not Selected--");
list.add(comboDTO); //This is the list which will be used to populate values in combo box

The option having the code = 0 will be shown as the first element in your combo box. Now if you want it to be mendatory field implement your Validator in and check if the option slected in combo has code=0 send an error back

Sample code for checking the value and sending error back from validator is like below

 if(myObject.getFieldCode().equals("0")){
  errors.rejectValue("fieldCode", "required.fieldCode");
 }
 

 This way you can have a combo box with an empty option and can check the validity at server side.

Unfortunatly I couldn't find a way to show dojo type notification at client end if the user has not selected a valid option in the combo box. But this is working for me now.


 

Documentum getting list of workflow performers

Below queries can be used to get the details about

• What happend during a wrokflow
• Which user performed which task
• How long a task was waiting to be processed e.t.c.

1. Use the below query to get the process ids related to your templete

            select * from dm_process where object_name like '%DBP%';

 

2. Use the Below query to get  the completed workflow ids related to a process id

select * from dmc_completed_workflow where process_id = 'my_process_id';

 3. Use the below query to get the information related to a completed workflow

select * from dmc_completed_workitem where workflow_id in (select workflow_id from dmc_completed_workflow where process_id = 'my_process_id' )

order by workflow_id, act_seqno;

Spring roo Customize Dojo Editor

I wanted to add more extensions to the dojo editor in my spring roo application , below is what I did

 

IN editor.tagx

I added the required plugins to be added to Editor like below

<script type="text/javascript">

 dojo.require("dijit.Editor");

<!-- Added by sajjad as recomeneded in http://forum.spring.io/forum/spring-projects/web/web-flow/85188-customize-dojo-dijit-editor -->

dojo.require('dijit._editor.plugins.ViewSource');
dojo.require('dijit._editor.plugins.FullScreen');

</script>

and I added the decoraiton like below

//**Added by sajjad as recomended in http://forum.spring.io/forum/spring-projects/web/web-flow/85188-customize-dojo-dijit-editor

Spring.addDecoration(

new Spring.ElementDecoration({elementId : '_${sec_field}_id_', widgetType : 'dijit.Editor', widgetAttrs : {disabled: ${disabled},extraPlugins: ['viewsource','fullscreen']}}));

pls-00201 identifier must be declared Oracle

I Created a new package in a schema let's call it "A" and was trying to run a function in that package using my own ID.

Oracle was showing me this error  "pls-00201 identifier must be declared " , after a little research I found that I had to grant execute on this package to my user ID otherwise Oracle will show me the above error so I executed below command as the Schema owner of  "A"

grant execute on A to My_User_id

and only then I was able to run the functions in the package "A"

Thanks to the below link

http://www.dbasupport.com/forums/showthread.php?53493-DBMS_JOB-errors-PLS-00201-identifier-package-proc_name-must-be-declared


Let's define the steps to be done here to avoid error "pls-00201 identifier must be declared "

1. Create a package
2. Grant execute access to your user or role to that package
3. Try to run any pricedure in that package , you should be able to do it now as you have been granted the execute rights on it.

 

Useful Documentum Queries

---getting the work flow using workflow name
select * from dm_workflow where object_name like '3333%';
--Getting the workflow using id
select * from dm_workflow where r_object_id='abc'

--getting user name in documentum using network ID in small
select user_name from dm_user where LOWER(user_login_name)='abc'
----Getiing inbox

EXECUTE get_inbox WITH name='abc'
EXECUTE get_inbox WITH category=1,name='Weekse Alexander E',order_by=date_sent desc

--Get the completed work flow information
select * from dmc_completed_workflow where workflow_id='4d00eb438002ba00';
---Get all the completed work items
select * from dmc_completed_workitem where workflow_id='4d00eb438002ba00';

---Get User Inbox
SELECT router_id,item_id,read_flag,stamp,task_name,sent_by, date_sent,task_state,due_date,name  FROM dm_queue WHERE name = abc' AND task_name !='event' order by date_sent DESC;
--Get user status (if 1 it means locked)
select user_name,user_state from dm_user where user_os_name in ('a','b','c')


 Check the Documentum groups for a user -
select i_all_users_names, group_name from dm_group where any i_all_users_names in (select user_name from dm_user where upper(user_os_name) = upper('<<network id>>'))
 Is a user disabled - 
select * from ep_user where upper(user_os_name)=upper('<<network id>>') and user_state=1
 Check all users in a group -
select i_all_users_names, group_name from dm_group where group_name =’<<Documentum group>>’
 The below query will check the status of user on Documentum 0 = active otherwise inactive
select user_os_name,user_state from ep_user where user_os_name in ('a','b','c')
---Query to check users in a group
select i_all_users_names from dm_group where group_name like '%a%'
---checking if a user is in a documentum group
select i_all_users_names from dm_group where group_name like '%b%' and any "i_all_users_names" = 'a'

---Get the list of performer names of completed workitems for a live workflow
select r_performer_name from dmi_workitem where r_workflow_id ='4d00ea968006f2d6' and r_runtime_state in (2) ;

---Get the performer name of current workitem for a live workflow
select r_performer_name from dmi_workitem where r_workflow_id ='4d00ea968006f2d6' and r_runtime_state in (0) ;


--Get work item performer names and action names for a given workflow
---the first two columns will have comma separated values with same order

select all r_performers,r_perf_act_name,r_object_id from dm_workflow where r_object_id in ('Workflowid1','Workflowid2');
---Get the current workitem for a live workflow (router_id = workflow id , item_id  = workitem id )

Select item_id as workitem_id from dmi_queue_item a where a.router_id = 'workflow_id' and delete_flag = 0;