ORA-06550: line 1, column 91: PLS-00201: identifier 'NameFromLastDDL' must be declared

You may face below error while executing loadjava command:

ORA-06550: line 1, column 91: PLS-00201: identifier 'NameFromLastDDL' must be declared

This is majorly because of java is not enabled.

So, Check Java Enabled on Database.

SQL> select owner, object_name, object_type from all_objects where object_name like '%NameFromLastDDL%' ;
no rows selected
SQL> select comp_name, status from DBA_REGISTRY where upper(comp_name) like '%JAVA%' ;
no rows selected

and then install java on exist database by initjvm.sql script.
http://download.oracle.com/docs/cd/B19306_01/install.102/e10319/java.htm#DFSIG276http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/scripts005.htm#sthref2935
run $ORACLE_HOME/javavm/install/initjvm.sql script.

SQL>@?/javavm/install/initjvm.sql
.
.
.

SQL> select owner, object_name, object_type from all_objects where object_name like '%NameFromLastDDL%' ;
OWNER OBJECT_NAME OBJECT_TYPE--------------- ------------------------------ -------------------SYS NameFromLastDDL FUNCTIONPUBLIC NameFromLastDDL SYNONYM
SQL> select comp_name, status from DBA_REGISTRY where upper(comp_name) like '%JAVA%' ;
COMP_NAME STATUS------------------------------------------------JServer JAVA Virtual Machine VALID

Reference: http://www.ora600.be/node/4628

SQL Update with select

Here is example of the update query syntax which will update mutiple columns of the table with values from select statment.

UPDATE tgt

SET ( tgt.x, tgt.y ) = ( SELECT src.x, src.y FROM src WHERE src.id = tgt.id )

Using java class in PL/SQL function. Example: password encryption with SHA1

We can use java class in PL/SQL function in Oracle. Here is example of the java class which will accept string and it will encrypt string with SHA1 algorithm.

1. Create java file e.g SHA1Converter.java with below code:

import java.io.UnsupportedEncodingException;

import java.security.MessageDigest;

import java.security.NoSuchAlgorithmException;

import sun.misc.BASE64Encoder;

public class SHA1Converter

{

public static String SHA1(String text) throws NoSuchAlgorithmException,

UnsupportedEncodingException

{

MessageDigest md = MessageDigest.getInstance("SHA");

md.update(text.getBytes("UTF-8"));

byte raw[] = md.digest(); //step 4

String hash = (new BASE64Encoder()).encode(raw); //step 5

return "{SHA-1}"+hash;

}

}

2. Compile java file

javac SHA1Converter.java

3. Load compile class file using openjava command. Mention schema for which you want this java class to be made available

loadjava -u -schema -v -resolve SHA1Converter.class

password:

arguments: '-u' 'dgmt' '-schema' 'dgmt' '-v' '-resolve' 'SHA1Converter.class'

creating : class DGMT.SHA1Converter

loading : class DGMT.SHA1Converter

resolving: class DGMT.SHA1Converter

Classes Loaded: 1

Resources Loaded: 0

Sources Loaded: 0

Published Interfaces: 0

Classes generated: 0

Classes skipped: 0

Synonyms Created: 0

Errors: 0

4. Written function to use java class and return string encrypted using SHA1

create or replace

FUNCTION gnuhash_sha1 (string IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'SHA1Converter.SHA1(java.lang.String) return java.lang.String';

5. Use above function

select gnuhash_sha1('anand') from dual;

Result : {SHA-1}uXP3dL/qtTIztPNHvhFOnKey0A8=

Deleting childeren in Hibernate

Lets take example of One to many relationship between Author and Book.

If you want to delete book without deleting parent i.e author you could simply delete using

delete(book)

which will cause :

org.springframework.dao.InvalidDataAccessApiUsageException: deleted object would be re-saved by cascade (remove deleted object from associations): [Book#1]; ...  

at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:657)  

at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)  

at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)  

at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)  

at org.springframework.orm.hibernate3.HibernateTemplate.flush(HibernateTemplate.java:881)  

at ConsoleScript7.run(ConsoleScript7:3)

Caused by: org.hibernate.ObjectDeletedException: deleted object would be re-saved by cascade (remove deleted object from associations): [Book#1] 

The problem is that the books are still in the author's collection, so when the session is flushed, they will be recreated. Solution will be remove the book from the collection itself.

author.getBooks().remove(bookId) 

and then save author object.