Follow by Email

Wednesday, April 9, 2014

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.
 

Wednesday, March 12, 2014

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) ;

 

Sunday, February 16, 2014

Maven error occurred during initialization of VM Could not reserve enough space for object heap

Today I faced the same problem with same error message but this time it was when I was trying to run Maven for my project to clean and build , eclipse was showing me below message again

Error occurred during initialization of VM Could not reserve enough space for object heap

The reason for this problem was that I am using a 32 bit JDK and in my VM parameters I was initiating the head size like

-Xmx1024m equal to 1GB

when you do that on a 64bit windows platform the windows can't alocate this uch memory and you will see the above error message , so I just reduced the size to less then a GB like below

-Xmx1000m

and now I can run maven.

Sunday, February 9, 2014

Error occurred during initialization of VM; Could not reserve enough space for object heap ,

I am running tomcat/weblogic in eclipse and when I was trying to start server from within eclipse I was facing this problem.

I found that I was not adding the tns VM parameter in server enviorment variable.

By adding the below VM argument to the JEE server (tomcat/weblogic) I was able to run my server from eclipse.
 
-Doracle.net.tns_admin=The location of you tns


 

Thursday, February 6, 2014

ORA-31061: XDB error: special char to escaped char conversion failed.


I encountered this exception while trying to get the XML data for a query while I was using
dbms_xmlgen.getXML function in oracle.

Problem :
I found that one of the varchar columns in my table had invalid xml characters in it , so whenever the query was trying to fetch a record having that column with invalid data oracle was showing me the error as

ORA-31061: XDB error: special char to escaped char conversion failed.

For a list of invalid xml characters please see below





List of invalid XML chars



Solution :
The best solution I can think of is to update that column and remove the invalid xml characters , and make sure by adding a trigger rule that nobody can enter those characters again in any similar field.

Thursday, January 16, 2014

java.sql.SQLDataException: ORA-01405: fetched column value is NULL



Exception Description

ORA-01405 is thrown when you attempt to execute FETCH, which was returned as a NULL value, but there was no indicator in use. If you are using Oracle

I have a databse function to which if you pass a query it will give you back a CLOB containing XML output of that query , I encountered this error today when I try to pass a query which returns null as result there I got this exception.



Handling

Once I understood the exception and it's cause the handling code aws easy to write , in my catch block I will retun an error message like "No Record Found ,You may want to revise your query to get some data satifying your conditions".


Solution

You might try resolving ORA-01405 by substituting a value with NVL function when a NULL value appears, or simply construct your cursor so that NULL values are not returned (you may also specify that columns with NULL values are not received).

 

Tuesday, December 10, 2013

ORA-03115: unsupported network datatype or representation

In a code written by another developer I was trying to replace JDBC statement code with prepared statements

So I was trying to do something like
   
PreparedStatement preparedStatement=con.prepareStatement(sql);
preparedStatement.setString(1, START_DT);

preparedStatement.setString(2, END_DT);

    
ResultSet set = preparedStatement.executeQuery(sql);

but all the queries being executed by above code were giving me the below exception


"ORA-03115: unsupported network datatype or representation"

I found that mistakenly I was calling the executeQuery method and passing it the sql again (this happend because I changed the code to use PreparedStatements instead of Statement , and if you are using statement you need to pass sql to execute query) , which is wrong I needed to call the function like

ResultSet set = preparedStatement.executeQuery();

and this resolved the problem :).