Oracle PL/SQL Cheat Sheet

These Codes are Tested on: Oracle Database 11g Enterprise Edition Release

DEFINITIONS:

TBL.  = TABLE
FUNC. = FUNCTION
PROC. = PROCUDURE
PKG.  = PACKAGE

-- CREATE LIKE TABLE WITH DATA
CREATE TABLE BISKINLER_TBL_NEW AS
    SELECT * FROM BISKINLER_TBL_OLD;


-- DELETE ALL DATA FROM TABLE
TRUNCATE TABLE BISKINLER_TBL_OLD;

-- COPY DATA FROM OLD TABLE -> NEW TABLE
INSERT INTO BISKINLER_TBL_NEW
    SELECT * FROM BISKINLER_TBL_OLD;

-- SIMPLE FUNCTION CALL
SELECT FUNCTION_NAME ('VARHCHAR PARAM', 10) FROM DUAL;


-- SIMPLE PROCEDURE CALL
BEGIN
    PROCEDURE_NAME();
END;


-- CHANGE DATABASE LANGUAGE - PL/SQL
EXECUTE IMMEDIATE ('ALTER SESSION SET NLS_LANGUAGE = "AMERICAN"');

-- CHANGE DATABASE LANGUAGE - SQL
ALTER SESSION SET NLS_LANGUAGE = "AMERICAN";

-- LEARN ORACLE/ PL/SQL.. VERSION
SELECT * FROM v$version;

-- SEARCH IN YOUR SOURCE CODE (PROC., FUNC., PACKAGE, )
-- EX: SEARCH FOR COMMENTS ADDED BY AFBISKINLER
  SELECT *
    FROM user_source C
   WHERE C.TYPE = 'PACKAGE' AND C.TEXT LIKE ('%--%AFBISKINLER%')
ORDER BY C.NAME;




 

		
		<dependency>
			<groupId>com.oracle.jdbc</groupId>
			<artifactId>ojdbc5</artifactId>
			<version>11.1.0.7.0</version>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>weblogic-server</groupId>
			<artifactId>weblogic</artifactId>
			<version>8.1.4.0</version>
			<optional>true</optional>
		</dependency>

		
DROP TABLE BISKINLER.LOG CASCADE CONSTRAINTS;

CREATE TABLE BISKINLER.LOG
(
  LOG_ID      NUMBER(19) NOT NULL,
  LOG_DATE    TIMESTAMP(6),
  LOG_FILE    VARCHAR2(255 CHAR),
  LOG_METHOD  VARCHAR2(255 CHAR),
  LOG_TEXT    CLOB,
  LOG_TYPE    VARCHAR2(255 CHAR)
)

package com.biskinler.lo4j.appander;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.jdbc.JDBCAppender;
import org.apache.log4j.spi.ErrorCode;

/**
 * Extension of the log4j {@link JDBCAppender} to allow using a JNDI data source
 * rather than a direct connection.
 * 
 * <p>
 * Example configuration:
 * 
 * log4j.appender.JNDI=com.biskinler.lo4j.appander.JndiCapableJdbcAppender
 * log4j.appender.JNDI.factoryInitial=weblogic.jndi.WLInitialContextFactory
 * log4j.appender.JNDI.jndiDataSource=dataSoruce
 * log4j.appender.JNDI.jndiProviderURL=t3://10.11.12.13:10000
 * 
 * @author Bart Bakker
 * @author Ahmet Faruk Bişkinler
 * @email ahmet @ biskinler.com
 */
public class JndiPoolAppender extends JDBCAppender {

	private static final Pattern SQL_VALUE_PATTERN = Pattern.compile( "'(.*?)'(?=\\s*[,)])", Pattern.MULTILINE);
	private String factoryInitial;
	private String jndiProviderURL;
	private String jndiDataSource;

	/** {@inheritDoc} */
	@Override
	protected Connection getConnection() throws SQLException {
		if (jndiDataSource == null) {
			return super.getConnection();
		} else {
			return lookupDataSource().getConnection();
		}
	}
	
	/**
	 * Looks up the datasource in the naming context specified by the
	 * {@link #jndiDataSource}.
	 * 
	 * @return the datasource.
	 */
	private DataSource lookupDataSource() {
		try {
			Hashtable<String, String> props = new Hashtable<String, String>();
			props.put("java.naming.factory.initial", factoryInitial);
			props.put("java.naming.provider.url", jndiProviderURL);
			Context ctx = new InitialContext(props);
			DataSource ds = (DataSource) ctx.lookup(jndiDataSource);
			
			return ds;
		} catch (NamingException e) {
			throw new RuntimeException("Cannot find JNDI DataSource: " + jndiDataSource, e);
		}
	}

	@Override
	protected void closeConnection(Connection con) {
		try {
			con.close();
		} catch (SQLException e) {
			errorHandler.error("Failed to close connection", e, ErrorCode.CLOSE_FAILURE);
		}
	}

	/**
	 * Executes the specified SQL statement, by parsing its values and turning
	 * them into parameters, so that characters that must be escaped in a SQL
	 * statement are supported.
	 */
	@Override
	protected void execute(String sql) throws SQLException {
		String statement = sql;
		ArrayList<String> args = new ArrayList<String>();
		Matcher m = SQL_VALUE_PATTERN.matcher(sql);
		while (m.find()) {
			args.add(m.group(1));
			statement = statement.replace(m.group(), "?");
		}

		executeStatement(statement, args.toArray(new String[args.size()]));
	}

	/**
	 * Executes the statement settings its parameters to the specified
	 * arguments.
	 * 
	 * @param statement the statement to execute.
	 * @param args the parameter values.
	 */
	protected void executeStatement(String statement, String[] args)throws SQLException {
		Connection con = getConnection();
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(statement);
			for (int i = 0; i < args.length; i++) {
				stmt.setString(i + 1, args[i]);
			}
			stmt.executeUpdate();
		} catch (SQLException e) {
			if (stmt != null) {
				stmt.close();
			}
			throw e;
		}
		stmt.close();
		closeConnection(con);
	}
	

	public String getFactoryInitial() {
		return factoryInitial;
	}

	public void setFactoryInitial(String factoryInitial) {
		this.factoryInitial = factoryInitial;
	}

	public String getJndiProviderURL() {
		return jndiProviderURL;
	}

	public void setJndiProviderURL(String jndiProviderURL) {
		this.jndiProviderURL = jndiProviderURL;
	}

	public String getJndiDataSource() {
		return jndiDataSource;
	}

	public void setJndiDataSource(String jndiDataSource) {
		this.jndiDataSource = jndiDataSource;
	}
	
}

# JNDI Appender Log Configuration
log4j.appender.JNDI=com.biskinler.testapp.log.JndiPoolAppender
log4j.appender.JNDI.Threshold=INFO
log4j.appender.JNDI.jndiProviderURL=t3://10.11.12.13:10000

#Weblogic
	#log4j.appender.JNDI.factoryInitial=weblogic.jndi.WLInitialContextFactory
	#log4j.appender.JNDI.jndiDataSource=testapp10g
#Tomcat
	log4j.appender.JNDI.factoryInitial=org.apache.naming.java.javaURLContextFactory
	log4j.appender.JNDI.jndiDataSource=java:comp/env/testapp10g

log4j.appender.JNDI.layout=org.apache.log4j.PatternLayout
log4j.appender.JNDI.layout.ConversionPattern=insert into log (LOG_DATE, LOG_TYPE, LOG_FILE, LOG_METHOD, LOG_TEXT) values (sysdate, '%p', '%F', '%l', '%m')

 
 Bu e-Posta adresi istek dışı postalardan korunmaktadır, görüntülüyebilmek için JavaScript etkinleştirilmelidir
 
27.04.2011

Calling WS with PHP using HessianPHP
WS is writen with Hessian in Java.
*/
try {
	
	include_once 'HessianPHP_v2.0.3/src/HessianClient.php';
	$testurl = 'http://biskinler.com/currency/crudServicesGateway';
	$proxy = &new HessianClient($testurl);


	$hashMap=array("foo" => "bar", "foo2" => "bar2");
	print "HashMap: ";
	echo "< pre >";
		print_r($hashMap);
	echo "< /pre >";
	
	//$result = $proxy->execute("currencyService", "get", 51, $hashMap); 
	$result = $proxy->execute("currencyService", "getAllByCrits", null, $hashMap); 
	
	echo "Result: ";
		echo "< pre >";
			print_r($result);
		echo "< /pre >";

} catch (Exception $e) {
	echo "Exception: $e";
}

?>

JSF
JSP
Facelets
JPA(Hibernate)
Spring
Maven
RichFaces
PrimeFaces
AJAX
AOP
ClearCase
Eclipse
EL
JBoss
Log4J
Oracle
Toad
Tomcat
XML

00 01 Download Java
00 02 Download Eclipse
00 03 Download JBossTools
00 04 Download Tomcat
00 05 Download Maven
00 05 Download Notepad++
00 06 Download Oracle Client
00 07 Download Toad
01 01 Uninstall Prev Java
01 02 Install Java
01 03 Install Tomcat
01 04 Install Maven
01 05 Install Notepad++
01 06 Install Oracle Client
01 07 Install Toad
01 08 00 Eclipse Extract
01 08 01 Run Eclipse
01 08 02 Eclipse Conf JavaJDK
02 01 Maven Repo
02 01 Maven Repo 2
02 02 Toad Conf
03 Eclipse JBoss Install
04 01 Apache Server
04 02 Maven Settings
05 Dynamic Web Project Create
06 00 Maven POM to Project
06 01 Maven Add Jar
06 02 Add JSF Support to Project Web.xml FaceConfig.xml Configurations
06 03 01 Copy provided files ex index.html
07 01 Run Project
07 02 Run on firefox
08 01 Add RichFaces Jar to web.xml
08 02 RichFaces 1
09 01 Entity and EntityController ManageBean
09 02 Run Richfaces
10 01 Spring service class interface
10 02 Spring Conf
10 03 Spring Run
11 Oracle POM Test Conn
12 01 JPA Conf
12 02 JPA Entity
12 03 JPA Persistance
13 01 Oracle TNSNames
13 02 Toad Setup
14 Hibernate Tools SQL Result
15 Oracle
16 JPA Connect Country
17 Log4J
18 ClearCase Setup
19 ClearCase Join Project
20 Download ClearCase Eclipse Plugin
21 Install Eclipse ClearCase Plugin 1
21 Install Eclipse ClearCase Plugin 2
22 CC Eclipse Installed
23 Eclipse ClearCase Import Project
24 AcenteRisk appconfig system env
25 Eclipse Tomcat Appconfig settings
26 Run AR Project

Change Alert Popup Email Notifications in Outlook 2007