Setup PowerShell Remote (WinRM) on Azure public Virtual Machine using HTTPS on Port 443

Create a public accessable Azure Windows Server VM. In this example I am using Windows Server 2016. 

Make sure that the VM:

• has a public IP Address.
• has a valid DNS name ( e.g. xxx.eastus.cloudapp.azure.com)

Configure the Network Security Group to accept HTTPS request from port 443

Make sure your Windows Server firewall does not block inbound request from 443. The default Azure Windows Server 2016 should have this port opened.

Configure the Windwos Server WinRM (PowerShell remote service)

Open a PowerShell console and execute the following commands:

1. Enable WinRM remote
    enable-psremoting -force
2. Set Trusted Hosts. If you do not know your local PC public IP range, you may have to use "*" to allow all ips.
   Allow a IP range:
   set-item wsman:\localhost\Client\TrustedHosts -value <Your Local Computer Public IP Address range>
   
   Allow all IPs: 
   set-item wsman:\localhost\Client\TrustedHosts -value *
   
3. Enable HTTPS via 443 port. (As default the WinRM uses 5986 port for HTTPS requests)
   
   Set-Item WSMan:\localhost\Service\EnableCompatibilityHttpsListener -Value true
4. Restart WinRM
   
   restart-service WinRM
5. Create a new self signed certifcate based on your virtual machine DNS name
   
   PS C:\Users\Test > new-selfsignedcertificate -DnsName <Your VM Name>.eastus.cloudapp.azure.com -CertStoreLocation Cert:\LocalMachine\My
   
   
   
6. Configure WinRM to use self signed certificate for HTTPS connection
   
   PS C:\Users\Test> winrm create winrm/config/Listener?Address=*+Transport=HTTPS '@{Hostname="<VMName>.eastus.cloudapp.azure.com";CertificateThumbprint="876D6XXXXXXXXX12EXXXXXXXXXXXXXX"}'
   
   
   
   
7. This complete the Server side configuration.

Connect your Azure VM via local PC

Open a powershell command line and execute the following scripts:

$so = New-PSSessionOption -SkipCACheck -SkipCNCheck

Enter-PSSession -ComputerName <Azure VM Public IP Address> -port 443 -Credential <UserName> -UseSSL -SessionOption $so

Pega 7 External JDBC Connection Pool Configuration - Oracle

How to Configure Pega External JDBC Connection Pool

Environment: Tomcat 7, Oracle XE, Pega 7

Oracle JDBC Driver

Copy "ojdbc6.jar" and "ucp.jar" to apache-tomcat-7.0.62\lib

Tomecat Configuration

Add resource element into \apache-tomcat-7.0.62\conf\context.xml

.....................
<Resource
       name="jdbc/testucp"
       auth="Container"
       factory="oracle.ucp.jdbc.PoolDataSourceImpl"
       type="oracle.ucp.jdbc.PoolDataSource"
       description="Oracle UCP Pool in Tomcat"
       connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
       minPoolSize="2"
       maxPoolSize="5"
       inactiveConnectionTimeout="20"
       user=""
       password=""
       url="jdbc:oracle:thin:@localhost:1521:xe"
       connectionPoolName="testucp"
       validateConnectionOnBorrow="true"
       sqlForValidateConnection="select 1 from DUAL"
/>

Pega  prweb.war "web.xml" configuration

Add "resource-ref" element into "web.xml"

<resource-ref> <res-ref-name>jdbc/paceucp</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref>

Pega external Database connection setting

Record Page-> SystemAdmin->Database->+Create

Note:

1. Do not just use "jdbc/testucp", as Pega will add this value directly into "cxt.lookup()" method. so you have to put the entire lookup string : cxt.lookup( "java:/comp/env/jdbc/testucp" );
2. You have to provide database username and password in this configuration form. Pege will not use the username/password in the context.xml file.

Linux Configuration

Install ActiveMQ (Linux 64 bit)

   install app:

tar zxvf activemq-x.x.x.tar.gz

cd [activemq_install_dir]/bin

chmod 755 activemq

Start app:

cd [activemq_install_dir]/linux-x86-64

activemq start

Check app:

Check status: netstat -an | grep 61616

Configure iptables:

vi /etc/sysconfig/iptables

add the following lines and save the file:

# For ActiveMQ
-A INPUT -m state --state NEW -m tcp -p tcp --dport 61616 ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 8161 ACCEPT

     service iptables restart

JBoss 7 EJB Spring Interceptor (SpringBeanAutowiringInterceptor) is not working (Igonred)

I have setup the modules and interceptor configurations correctly, but the SpringBeanAutowiringInterceptor has not been triggered. At the end I found out that I need to add the spring-context-3.x.x.RELEASE.jar in my EAR lib. It looks like the JBoss 7 will only search interceptor class from the local EAR. It won’t load it from the modules.

Fedora 17 Linux Knowledge

1. Login GUI as Supperuser 

Open your Terminal from Applications -> System Tools -> Terminal
Step 1 :- Now Login as a root from your terminal
[fedora17@xxx]$ su
 Password:-

Step 2:- Now go to your /etc/pam.d/ directory.
[root@xxx]# cd /etc/pam.d/
Then first take a backup of gdm file
cp gdm-password gdm-password.bkp ( always take backup if anything goes wrong you can correct it by original file)

Step 3 :- Now Open gdm file in your favourite editor. I am using vi as my editor.
[root@ tejas-barot-ahmedabad-linux pam.d]# vi gdm-password

Find line auth required pam_succeed_if.so user != root quiet
And change it to ## auth required pam_succeed_if.so user != root quiet

Step 4 :- Save & Exit From that File.
Step 6 :- Save & Exit from File. Now Logout and Try to Login as a root user. Now you are able to Login as a root user from GUI in Fedora 17.

2. Add script to start up

Your script should be in /etc/init.d/ directory.
Then you have to make a symlink from your run-level directory.
I.e you're in run-level 5 (multiuser with X)

Code:

cp   /etc/init.d
ln -s /etc/init.d/[script file name] /etc/rc.d/rc5.d/S50[script file name]
ln -s /etc/init.d/[script file name] /etc/rc.d/rc5.d/K50[script file name]

The S50 is to tell the system to start the script when it boots up,
the K50 is to tell the system to shut down cleanly when you do a shut down.
The number representates in which order the script should start/shut down in. This way you have the possibility to explicity start your server after like when you started networking and other servers that is required by your server.

Change my example obove with your scriptfile name.
Ah, and remember to do "chmod +x " 

3. Fedora 17 install JDK

1. Download Sun/Oracle Java JDK/JRE 7u9 32-bit/64-bit RPM packages
Download Sun/Oracle Java 7u9 from here http://www.oracle.com/technetwork/java/javase/downloads/index.html. Select rpm package (jdk-7u9-linux-i586.rpm, jdk-7u9-linux-x64.rpm, jre-7u9-linux-i586.rpm or jre-7u9-linux-x64.rpm).


2. Change to root user.

sudo -i
## OR ##
su -

3. Install Java JDK or JRE package

## JDK 32-bit ##
rpm -Uvh /path/to/binary/jdk-7u9-linux-i586.rpm

## JDK 64-bit ##
rpm -Uvh /path/to/binary/jdk-7u9-linux-x64.rpm

## JRE 32-bit ##
rpm -Uvh /path/to/binary/jre-7u9-linux-i586.rpm

## JRE 64-bit ##
rpm -Uvh /path/to/binary/jre-7u9-linux-x64.rpm

4a. Install Sun/Oracle JDK java, javaws, libjavaplugin.so (for Firefox/Mozilla) and javac with alternatives –install command
Use Java JDK latest version (/usr/java/latest)

## java ##
alternatives --install /usr/bin/java java /usr/java/latest/jre/bin/java 20000
## javaws ##
alternatives --install /usr/bin/javaws javaws /usr/java/latest/jre/bin/javaws 20000

## Java Browser (Mozilla) Plugin 32-bit ##
alternatives --install /usr/lib/mozilla/plugins/libjavaplugin.so libjavaplugin.so /usr/java/latest/jre/lib/i386/libnpjp2.so 20000

## Java Browser (Mozilla) Plugin 64-bit ##
alternatives --install /usr/lib64/mozilla/plugins/libjavaplugin.so libjavaplugin.so.x86_64 /usr/java/latest/jre/lib/amd64/libnpjp2.so 20000

## Install javac only if you installed JDK (Java Development Kit) package ##
alternatives --install /usr/bin/javac javac /usr/java/latest/bin/javac 20000
alternatives --install /usr/bin/jar jar /usr/java/latest/bin/jar 20000

## java ##
alternatives --install /usr/bin/java java /usr/java/jdk1.7.0_09/jre/bin/java 20000
## javaws ##
alternatives --install /usr/bin/javaws javaws /usr/java/jdk1.7.0_09/jre/bin/javaws 20000

## export JAVA_HOME JDK ##
export JAVA_HOME="/usr/java/jdk1.7.0_09"

4. Setup "sudo"

* Modifying sudoers file

Normally if you want to run a command which requires root privileges you first have to type 'su -', type your root password and then type the command. However you can bypass this by using the 'sudo' command. But first you have to configure it like this

su --login -c 'visudo'

Now go below the line:

root    ALL=(ALL)       ALL

press a, and type the following

username ALL=(ALL)      ALL

where username is the username you use to login. Next press Escape. Now, if you want to be prompted for your root password each time you use the sudo command go to this line:

# %wheel        ALL=(ALL)       ALL

and with your cursor pointing on # press x If you don't want to be prompted for your root password each time you use the sudo command go to this line:

# %wheel        ALL=(ALL)       NOPASSWD: ALL

and with your cursor pointing on # press x Next, press :wq to save and exit. (type the : as well) * Adding your user to the wheel groupJust type: su -c 'gpasswd -a username wheel'

* Testing sudo

To test if you have done it correctly as a simple user type:

$ sudo whoami

If everything is working properly the command should return the word 'root'.
 

JBoss SOAP http basic auth

package security.test;

import java.io.UnsupportedEncodingException;
import java.util.Map;

import javax.annotation.Resource;
import javax.annotation.security.RolesAllowed;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.ejb.TransactionManagement;
import javax.ejb.TransactionManagementType;
import javax.jws.WebMethod;
import javax.jws.WebService;
import javax.jws.soap.SOAPBinding;
import javax.xml.ws.WebServiceContext;
import javax.xml.ws.handler.MessageContext;

import org.jboss.ejb3.annotation.SecurityDomain;
import org.jboss.util.Base64;
import org.jboss.wsf.spi.annotation.WebContext;

/**
* Session Bean implementation class ServiceCall
*/
@Stateless
@TransactionManagement(TransactionManagementType.BEAN)
@WebService(name="MetaDataRegistry",
targetNamespace = "http://csiro.au/webservice/registry",
serviceName = "RegistrationService")
@SecurityDomain(value = "JBossWS")
@WebContext(contextRoot = "/MetaDataRegistry", transportGuarantee="NONE", authMethod="BASIC", secureWSDLAccess=false)

@Remote(ServiceCallRemote.class)
@SOAPBinding(style=SOAPBinding.Style.DOCUMENT)
public class ServiceCall implements ServiceCallRemote, ServiceCallLocal {

@Resource
WebServiceContext wsContext;
/**
* Default constructor.
*/
public ServiceCall() {
// TODO Auto-generated constructor stub
}

@Override
@RolesAllowed("Guest")
@WebMethod()
public void CallService1(String username) throws UnsupportedEncodingException
{
MessageContext context = wsContext.getMessageContext();
Map requestHeaders = (Map) context.get(MessageContext.HTTP_REQUEST_HEADERS) ;
Object obj = requestHeaders.get("Authorization");
String output = obj.toString();
String result = output.replace("Basic ", "");
result = result.replace("[", "");
result = result.replace("]", "");
byte[] p = Base64.decode(result);
String f = new String(p, 0, p.length, "UTF-8" );
System.out.println(f);
}

}

Setup Eclipse for JBOSS

# Download and Install Eclipse

1. Download Eclipse 3.5 from Eclipse IDE for Java EE Developers (189 MB) extract to a convinient folder say c:\eclipse
2. From within eclipse, Install new software (form help menu) and point to http://download.jboss.org/jbosstools/updates/development to install JBoss tools for eclipse. This will install JBoss 5.1 Runtime Environment server for eclipse.

# Download and Install JBoss

1. Download http://www.jboss.org/jbossas/downloads/ and extract it to a convinient folder. say C:\jboss-5.1.0.GA
2. Set JBOSS_HOME environment variable to point to installed C:\jboss-5.1.0.GA folder

Configure JBoss AS 5

1. Download jboss native windows service
http://labs.jboss.com/jbossweb/downloads/jboss-native-2-0-9.html
unzip the package to jboss/bin folder


2.Create the following system variables

Variable Name: JAVA_HOME

Variable value: C:\Program Files\Java\jdk1.6.0_12

Variable Name: JBOSS_BIN

Variable value: C:\jboss-5.1.0.GA-JDK6\bin

Variable Name: JBOSS_HOME

Variable Value: C:\jboss-5.1.0.GA-JDK6

3.Setup jboss binding address

Open {JBoss}\bin\service.bat

change the ipaddress in line

"call run.bat -c default -b xx.xx.xx.xx"

4. To avoid "java.lang.OutOfMemoryError: PermGen space" error

Open file jboss/bin/run.conf.bat

replace set "JAVA_OPTS=.............' with the following settings:

set "JAVA_OPTS=-Xms512m -Xmx512m -XX:PermSize=128m -XX:MaxPermSize=512m -XX:+UseConcMarkSweepGC -XX:+CMSPermGenSweepingEnabled -XX:+CMSClassUnloadingEnabled"

5. Open a command line window.

Direct to Jboss

Run: service.bat install

6. Change listening port

{JBoss}\server\default\deploy\jbossweb.sar\service.xml

{JBoss}\server\default\conf\bindingservice.beans\META-INF\bindings-jboss-beans.xml

Subversion Administration

Add user

If you already have some users, the password file [filename] will already exist.

htpasswd2 /var/svn/conf/[filename] [username]

The -c flag will create a new file, so don't use it if you only want to add a user to an existing file otherwise you'll overwrite the entire file (hence delete all existing users).

htpasswd2 -c /var/svn/conf/[filename] [username]

Edit user

This is the same as creating a new one.

htpasswd2 /var/svn/conf/[filename] [username]

Delete user

htpasswd2 -D /var/svn/conf/[filename] [username]

Specifying password on the command line as an argument

In all examples, you can use the -b to allow you to specify the password as an argument rather than being prompted for it. e.g:

htpasswd2 -b /var/svn/conf/[filename] [username] [password]

Oracle Delete all uesr table query

select 'DROP TABLE ' || table_name || ' cascade constraints; ' from user_tables

C++ String Conversion

BSTR -> string
BSTR bstrString;
string stlString;

stlString = (LPCSTR)(_bstr_t)bstrString;

string -> char*
String sTemp = "xxxx";
char* lpszTemp;
lpszTemp = (char*)sTemp.c_str();

int -> string
#include
using namespace std;

int i = 100;
ostringstream os;
os< string s = os.str();

VARIANT.bstrVAL -> string

VARIANT variantData;
variantData.vt = VT_BSTR;
variantData.bstrVal = _bstr_t("test");

string sData;
sData = string((const char*)(_bstr_t(variantData.bstrVal))));

Trim For STL string

string Trim( string s )
{
return s.erase( s.find_first_of(" "), s.find_last_of(" ") );
}

string -> bstr
string sTemp
_bstr_t bstrTemp = _bstr_t((char*)(sTemp.c_str()));

float -> string
char buffer[30];
sprintf( buffer, "%3.15f", 3.1592646 )
string sResult = (string)buffer;

Stand Alone Client call EJB ( JBoss 5)

Hashtable environment = new Hashtable();
environment.put(Context.INITIAL_CONTEXT_FACTORY, "org.jnp.interfaces.NamingContextFactory");
environment.put(Context.URL_PKG_PREFIXES, "org.jboss.naming:org.jnp.interfaces");
environment.put(Context.PROVIDER_URL, "localhost:1099");
InitialContext context = new InitialContext(environment);
RuleEngineRemote mrs = (RuleEngineRemote)context.lookup("RuleEngineEAR/RuleEngine/remote");

Stand Alone Client call EJB ( JBoss 4.2.3)

System.setProperty("java.security.policy", "D:\\Work\\NEW MRS Online\\Code\\Test_RuleEngine\\bin\\no.policy");
if (System.getSecurityManager() == null)
System.setSecurityManager(new RMISecurityManager());
Properties env = new Properties();
env.setProperty("java.naming.factory.initial", "org.jnp.interfaces.NamingContextFactory");
env.setProperty("java.naming.provider.url", "localhost:1099");
env.setProperty("java.naming.factory.url.pkgs", "org.jboss.naming");
InitialContext context = new InitialContext(env);
RuleEngineRemote mrs = (RuleEngineRemote)context.lookup("RuleEngineEAR/RuleEngine/remote");

Content of no.policy file:

grant { permission java.security.AllPermission; };

JPA error: Attempt to update the sequence table "OPENJPA_SEQUENCE_TABLE" failed

OpenJPA uses OPENJPA_SEQUENCE_TABLE to generate primary keys for entities that use datastore identity or application identity with auto-generation strategy. The table is created automatically when persistence.xml configuration specifies to build the schema. The other option is to create the table directly via DDL CREATE TABLE openjpa_sequence_table (ID tinyint(4) NOT NULL, SEQUENCE_VALUE bigint(20) default NULL, PRIMARY KEY (ID))

http://www.oracle.com/technology/products/ias/toplink/jpa/howto/id-generation.html

Get the latest executed query from oracle db

select * from v$sqlarea

Weblogic Server Admin Console Default username password

Username: weblogic
Password: weblogic

Use Drool in Eclipse

The following library are required to run Drool in Eclipse:

1. Drool Library which is added automatically by plugin
2. org.eclipse.jdt_3.2.1.r321_v20060823.jar (Can be find in Eclipse\Plugins filder)
3. org.eclipse.jdt.core_3.2.3.v_686_R32x.jar (Can be find in Eclipse\Plugins filder)

Weblogic Database URL for Oracle

Database URL: jdbc:oracle:thin:@192.168.5.2:1821:WSD jdbc:oracle:thin:@::

Weblogic Database URL for Oracle [SEC=UNCLASSIFIED]

Database URL:
        jdbc:oracle:thin:@192.168.5.2:1821:WSD
       
        jdbc:oracle:thin:@<servername/ip>:<port>:<DB Name(The name displayed in the Tnsnames.ora file)>

_______________ This email message and any accompanying attachments may contain information that  is confidential and intended only for the use of the addressee named above.  It may also be privileged. If you are not the intended recipient do not read,  use, disseminate, distribute or copy or take any action in reliance on it.  If you have received this message in error please notify the sender immediately  and delete this message. Before opening any attachments, check them for viruses or defects.  _______________ 

Oracle Update Select statement [SEC=UNCLASSIFIED]

Oracle Update Statements               
Version 11.1           
               
Basic Update Statements        

Update all records      UPDATE <table_name>
SET <column_name> = <value>    
        CREATE TABLE test AS
SELECT object_name, object_type
FROM all_objs;

SELECT DISTINCT object_name
FROM test;

UPDATE test
SET object_name = 'OOPS';

SELECT DISTINCT object_name
FROM test;

ROLLBACK;      

Update a specific record        UPDATE <table_name>
SET <column_name> = <value>
WHERE <column_name> = <value>  
        SELECT DISTINCT object_name
FROM test;

UPDATE test
SET object_name = 'LOAD'
WHERE object_name = 'DUAL';

COMMIT;

SELECT DISTINCT object_name
FROM test      

Update based on a single queried value  UPDATE <table_name>
SET <column_name> = (
SELECT <column_name>
FROM <table_name
WHERE <column_name> <condition> <value>)
WHERE <column_name> <condition> <value>;       
        CREATE TABLE test AS
SELECT table_name, CAST('' AS VARCHAR2(30)) AS lower_name
FROM user_tables;

desc test

SELECT *
FROM test
WHERE table_name LIKE '%A%';

SELECT *
FROM test
WHERE table_name NOT LIKE '%A%';

-- this is not a good thing ...
UPDATE test t
SET lower_name = (
SELECT DISTINCT LOWER(table_name)
FROM user_tables u
WHERE u.table_name = t.table_name
AND u.table_name LIKE '%A%');
-- look at the number of rows updated

SELECT * FROM test;

-- neither is this
UPDATE test t
SET lower_name = (
SELECT DISTINCT LOWER(table_name)
FROM user_tables u
WHERE u.table_name = t.table_name
AND u.table_name NOT LIKE '%A%');

SELECT * FROM test;

UPDATE test t
SET lower_name = (
SELECT DISTINCT LOWER(table_name)
FROM user_tables u
WHERE u.table_name = t.table_name
AND u.table_name LIKE '%A%')
WHERE t.table_name LIKE '%A%';

SELECT * FROM test;    

Update based on a query returning multiple values       UPDATE <table_name> <alias>
SET (<column_name>,<column_name> ) = (
SELECT (<column_name>, <column_name>)
FROM <table_name>
WHERE <alias.column_name> = <alias.column_name>)
WHERE <column_name> <condition> <value>;       
        CREATE TABLE test AS
SELECT t. table_name, t. tablespace_name, s.extent_management
FROM user_tables t, user_tablespaces s
WHERE t.tablespace_name = s. tablespace_name
AND 1=2;

desc test

SELECT * FROM test;

-- does not work
UPDATE test
SET (table_name, tablespace_name) = (
SELECT table_name, tablespace_name
FROM user_tables);

-- works
INSERT INTO test
(table_name, tablespace_name)
SELECT table_name, tablespace_name
FROM user_tables;

COMMIT;

SELECT *
FROM test
WHERE table_name LIKE '%A%';

-- does not work
UPDATE test t
SET tablespace_name, extent_management = (
SELECT tablespace_name, extent_management
FROM user_tables a, user_tablespaces u
WHERE t.table_name = a.table_name
AND a.tablespace_name = u.tablespace_name
AND t.table_name LIKE '%A%');

-- works but look at the number of rows updated
UPDATE test t
SET (tablespace_name, extent_management) = (
SELECT DISTINCT u.tablespace_name, u.extent_management
FROM user_tables a, user_tablespaces u
WHERE t.table_name = a.table_name
AND a.tablespace_name = u.tablespace_name
AND t.table_name LIKE '%A%');

ROLLBACK;

-- works properly
UPDATE test t
SET (tablespace_name, extent_management) = (
SELECT DISTINCT (u.tablespace_name, u.extent_management)
FROM user_tables a, user_tablespaces u
WHERE t.table_name = a.table_name
AND a.tablespace_name = u.tablespace_name)
WHERE t.table_name LIKE '%A%';

SELECT * FROM test;    

Update the results of a SELECT statement        UPDATE (<SELECT Statement>)
SET <column_name> = <value>
WHERE <column_name> <condition> <value>;       
        SELECT *
FROM test
WHERE table_name LIKE '%A%';

SELECT *
FROM test
WHERE table_name NOT LIKE '%A%';

UPDATE (
SELECT *
FROM test
WHERE table_name NOT LIKE '%A%')
SET extent_management = 'Unknown'
WHERE table_name NOT LIKE '%A%';

SELECT * FROM test;    
               
Correlated Update              

Single column   UPDATE TABLE(<SELECT STATEMENT>) <alias>
SET <column_name> = (
SELECT <column_name>
FROM <table_name> <alias>
WHERE <alias.table_name> = <alias.table_name>);
        conn hr/hr

CREATE TABLE empnew AS
SELECT * FROM employees;

UPDATE empnew
SET salary = salary * 1.1;

UPDATE employees t1
SET salary = (
SELECT salary
FROM empnew t2
WHERE t1.employee_id = t2.employee_id);

drop table empnew;     

Multi-column    UPDATE <table_name> <alias>
SET (<column_name_list>) = (
SELECT <column_name_list>
FROM <table_name> <alias>
WHERE <alias.table_name> <condition> <alias.table_name>);      
        CREATE TABLE t1 AS
SELECT table_name, tablespace_name
FROM user_tables
WHERE rownum < 11;

CREATE TABLE t2 AS
SELECT table_name,
TRANSLATE(tablespace_name,'AEIOU','VWXYZ') AS TABLESPACE_NAME
FROM user_tables
WHERE rownum < 11;

SELECT * FROM t1;

SELECT * FROM t2;

UPDATE t1 t1_alias
SET (table_name, tablespace_name) = (
SELECT table_name, tablespace_name
FROM t2 t2_alias
WHERE t1_alias.table_name = t2_alias.table_name);

SELECT * FROM t1;      
               
Nested Table Update            
        See Nested Tables page 
               
Update With Returning Clause           

Returning Clause demo   UPDATE (<SELECT Statement>)
SET ....
WHERE ....
RETURNING <values_list>
INTO <variables_list>; 
        conn hr/hr

var bnd1 NUMBER
var bnd2 VARCHAR2(30)
var bnd3 NUMBER

UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000,
department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;

print bnd1
print bnd2
print bnd3

rollback;      
        conn hr/hr

variable bnd1 NUMBER

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 100
RETURNING SUM(salary) INTO :bnd1;

print bnd1

rollback;      
               
Update Object Table            

Update a table object   UPDATE <table_name> <alias>
SET VALUE (<alias>) = (
<SELECT statement>)
WHERE <column_name> <condition> <value>;       
        CREATE TYPE people_typ AS OBJECT (
last_name VARCHAR2(25),
department_id NUMBER(4),
salary NUMBER(8,2));
/

CREATE TABLE people_demo1 OF people_typ;

desc people_demo1

CREATE TABLE people_demo2 OF people_typ;

desc people_demo2

INSERT INTO people_demo1
VALUES (people_typ('Morgan', 10, 100000));

INSERT INTO people_demo2
VALUES (people_typ('Morgan', 10, 150000));

UPDATE people_demo1 p
SET VALUE(p) = (
SELECT VALUE(q) FROM people_demo2 q
WHERE p.department_id = q.department_id)
WHERE p.department_id = 10;

SELECT * FROM people_demo1;    
               
Record Update          

Update based on a record
Note: This construct updates every column so use with care. May cause increased redo, undo, and foreign key locking issues.     UPDATE <table_name>
SET ROW = <record_name>
WHERE <column_name> <condition> <value>;       
        CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;

SELECT DISTINCT tablespace_name
FROM t;

DECLARE
trec t%ROWTYPE;
BEGIN
trec.table_name := 'DUAL';
trec.tablespace_name := 'NEW_TBSP';

UPDATE t
SET ROW = trec
WHERE table_name = 'DUAL';

COMMIT;
END;
/

SELECT DISTINCT tablespace_name
FROM t;
               
Update Partitioned Table               

Update only records in a single partition       UPDATE <table_name> PARTITION (<partition_name>)
SET <column_name> = <value>
WHERE <column_name> <condition> <value>;       
        conn sh/sh

UPDATE sales PARTITION (sales_q1_2005) s
SET s.promo_id = 494
WHERE amount_sold > 9000;      
               
Related Topics         
Delete         
Error Logging          
Insert         
Nested Tables          
Select         
Types          
               
Contact Us ? Legal Notices and Terms of Use ? Privacy Statement        

_______________ This email message and any accompanying attachments may contain information that  is confidential and intended only for the use of the addressee named above.  It may also be privileged. If you are not the intended recipient do not read,  use, disseminate, distribute or copy or take any action in reliance on it.  If you have received this message in error please notify the sender immediately  and delete this message. Before opening any attachments, check them for viruses or defects.  _______________