Execute an SQL script file in SQLPlus

To execute a script file in SQLPlus, type @ and then the file name.

SQL > @{file}

e.g if your file name is script.sql

then

SQL > @script.sql

If script is not in current directory you can specify the path

SQL > @{path}{file}

e.g

SQL >@/opt/oracle/script.sql

Oracle database backup and restore

Here is command to take backup from the oracle database:

exp userid=system/{system_passwd}@{schema} owner={schema_owner} file={backup_file}.dmp buffer=102400 statistics=none grants=n log={backup_log_file}.log

Where

system_passwd is password for system user

schema is database schema name

schema_owner is user name for the database

backup_file is backup file name

backup_log_file is log file name for backup logs

To restore database from the backup (dmp file) use following command

imp system/{system_passwd}@{schema} file={backup_file}.dmp log={import_log_file}.log fromuser={existing_user} touser={new_user}


Where
system_passwd is password for system user

schema is database schema name

backup_file is backup file name

import_log_file is log file name where all the logs will be written

existing_user is database user name from the dmp file

new_user is database user name which is being restored.

Creating DBLink in Oracle

Login to database execute below command:

create public database link {link_name} connect to {user_name} identified by using '{connect_descriptor}'

Where

1. link_name is the name of the database link

2. user_name is the username of the database to get connected

3. connect_descriptor is the tns entry ($OARCLE_HOME/network/admin/tnsname.ora)

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.

Reading file from URL into byte array output stream

Here is code which reads file from URL and writes to ByteArrayOutPutStream

try

{

InputStream is = new URL("http://localhost:8080/pdf/test.pdf").openStream();

ByteArrayOutputStream outputDoc = new ByteArrayOutputStream();

byte buf[]=new byte[1024];

int len;

while((len=is.read(buf))>0)

{

outputDoc.write(buf,0, len);

}

outputDoc.close();

}

catch(Exception e) { e.printStackTrace(); }

Reading file from URL and writing to file

File file = new File("D:/test123.pdf"); FileOutputStream fos = new FileOutputStream(file); BufferedOutputStream outBuf = new BufferedOutputStream(fos);

InputStream is = new URL("http://localhost:8080/pdf/test.pdf").openStream();

byte buf[]=new byte[1024]; int len; while((len=is.read(buf))>0) { outBuf.write(buf,0, len); } outBuf.close(); fos.close();

Resolving java.lang.OutOfMemoryError: PermGen space in Weblogic

Hi,

To resolve the java.lang.OutOfMemoryError: PermGen space in Weblogic please add the following line

set USER_MEM_ARGS=-Xms512m -Xmx1024m -XX:MaxPermSize=128m

in :\Oracle\Middleware\user_projects\domains\dgmt_domain\bin\setDomainEnv.cmd

Thanks,

Enable Remote Debugging in Weblogic

Hi,

Remote debugging in Weblogic with Eclipse can be configured as follows:

In startWebLogic.cmd file, can be found at :\Oracle\Middleware\user_projects\domains\dgmt_domain\bin\startWebLogic.cmd,

Replace

set JAVA_OPTIONS =%SAVE_JAVA_OPTIONS%

With

set JAVA_OPTIONS=-Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,address=1044,server=y,suspend=n %SAVE_JAVA_OPTIONS%

While starting the Weblogic you will see “Listening for transport dt_socket at address: 1044” then we are sure that debug is enabled on weblogic.

Thanks,

Spring MVC Training

Check out this SlideShare Presentation:

Spring MVC Training

View more presentations from Intertech Training.