Call webservice from oracle database

Database Web Service

Note:

The purpose of this article is to provide a step-by-step instruction on how to install the DBWS Callout Utilities on Oracle Database Server 11g and how to verify that it works properly.

All of these environments may be placed on different machines. Anyway, it has been found convenient to have these environments on one machine for an initial installation and test of the DBWS Callout Utilities. This does rule out network problems from affecting the proper operation of the DBWS Callout Utilities.

11g Database Webservice Callout Steps

preparing the Oracle Client and Oracle Database Server

  1. Setup the appropriate environment for the Oracle Client

ORACLE_HOME = D:\app\database\product\11.2.0\dbhome_1
PATH = %PATH%;%ORACLE_HOME%\bin;%ORACLE_HOME%\jdk\bin

  1. Install 11g Release 2 (11.2) for Microsoft Windows (32-Bit) with Installation Type “Administrator”

This will install all the tools and jars required to work with the DBWS Callout Utilities.

  1. Drop The Package “UTL_DBWS” in Schema “SYS”. If existed

drop package UTL_DBWS;

  1. Check the OracleJVM for Invalid Classes

The schema “SYS” and the schema into which the DBWS Callout Utilities should be installed must not have invalid classes. The status of all the java classes installed on the Oracle Database Server instance can be checked as SYSDBA with following SQL statement:

SELECT owner, status, count(*) FROM DBA_OBJECTS
WHERE OBJECT_TYPE=’JAVA CLASS’
GROUP BY owner, status;

For a standard installation of Oracle Database Server 11.1 this SQL statement should return about 20000 valid classes for the owner/schema “SYS”.

It is very difficult to resolve invalid classes once they are in schema “SYS”. There is a huge number of classes in this schema and it is not easy to find out the dependencies. It is a lot easier to resolve invalid classes in schemas that are not system schemas of the Oracle Database Server, because in this case all uploaded jar files and classes with their dependencies should be known to the user. Re-compile all invalid classes( take maximum 100 ) in one compile.

The complete initialization of the OracleJVM is usually the most efficient approach to resolve problems with invalid classes in schema “SYS”, even though this is a risky and time consuming procedure.

  1. Configure the OracleJVM

Preparing the database consists mainly in loading the Web Services client stack into the database. Loading the client stack requires extra Java space inside the OracleJVM to resolve and store load jar files. Make sure the initialization parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are equal to or greater than 132M and 80M, i.e. or more

shared_pool_size=132M
java_pool_size=80M

Depending on the number of Java classes already loaded into the database, the two parameters may need to be increased beyond 132M and 80M.

In SQL*Plus you can check the size of this initialization parameters as SYSDBA:

show parameter SHARED_POOL_SIZE
show parameter JAVA_POOL_SIZE

alter system set SHARED_POOL_SIZE=132M scope=both;
alter system set JAVA_POOL_SIZE=80M scope=both;

  1. Download the Oracle DBWS Callout Utility 10.1.3.1: this with work with Oracle Database 10g & 11g both.
  2. Take a backup of %Oracle_Home%\sqlj\   folder first:
  3. Unzip the Content of the ZIP File to $ORACLE_HOME (Oracle Database Server Environment)

Unzip in %Oracle_Home%\sqlj\  dbws-callout-utility-10131.zip

  1. Modify the CLASSPATH in Clients_Home\ sqlj\bin\jpub

D:\dbhome_2\product\11.2.0\client_1\bin;D:\app\database\product\11.2.0\dbhome_2;D:\app\database\product\11.2.0\dbhome_1\bin;D:\DevSuiteHome_1\jdk\jre\bin\classic;D:\DevSuiteHome_1\jdk\jre\bin;D:\DevSuiteHome_1\jdk\jre\bin\client;D:\DevSuiteHome_1\jlib;D:\DevSuiteHome_1\bin;D:\DevSuiteHome_1\jre\1.4.2\bin\client;D:\DevSuiteHome_1\jre\1.4.2\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;D:\ORANT\BIN;%ORACLE_HOME%\bin;%ORACLE_HOME%\OPatch;%ORACLE_HOME%\sqlj\bin;%JAVA_HOME%\bin;D:\dbhome_2\product\11.2.0\client_1\jdk\bin

 

  1. Make JPublisher and JDK 1.5.0 available for an Oracle Client Operating System Shell

Oracle Client 11g ships with a JDK 1.5.0 already. The JDK being used for JPublisher must match with the version of the OracleJVM the generated classes will be deployed to.

e.g.:
PATH=$ORACLE_HOME\jdk\bin;%PATH %

echo %PATH%
D:\dbhome_2\product\11.2.0\client_1\bin;D:\app\database\product\11.2.0\dbhome_1;D:\app\database\product\11.2.0\dbhome_1\bin;;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;%ORACLE_HOME%\bin;%ORACLE_HOME%\OPatch;%ORACLE_HOME%\sqlj\bin;%JAVA_HOME%\bin;D:\app\database\product\11.2.0\dbhome_1\sqlj\lib;

 

  1. Load the WebService Client into Oracle Database Server

loadjava -u SYS/MiracleWorker7 -r -v -f -s –grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar

Output:

[oracle@sracanov-au2 lib]$ tail -8 loadjava.txt

Classes Loaded: 4061

Resources Loaded: 81

Sources Loaded: 0

Published Interfaces: 0

Classes generated: 63

Classes skipped: 0

Synonyms Created: 0

Errors: 0

[oracle@sracanov-au2 lib]$

 

Note:

The loadjava commands above load the Oracle JAX-RPC client into the target schema. By default this classes will only be available for the target user. Other users require execute privileges and synonyms for this classes, if such users should also be able to have web service callouts.

e.g.:
loadjava -u SYS/MiracleWorker7 -r -v -f -s –grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar

  1. Once the database webservice call out classes have been loaded, I need to run the PL/SQL wrappers that will call these classes. The scripts are found in $ORACLE_HOME\sqlj\lib folder. Since I installed the jar in scott, scott needs to run these scripts:

Load below in sys schema.

  1. sql
  2. sql

Verify.

desc utl_dbws

  1. Here is the PL/SQL code using UTL_DBWS to call an external webservice:

CREATE OR REPLACE FUNCTION celciusToFahrenheit(temperature NUMBER) RETURN VARCHAR2 AS

service_ scott.utl_dbws.SERVICE;

call_ scott.utl_dbws.CALL;

service_qname scott.utl_dbws.QNAME;

port_qname scott.utl_dbws.QNAME;

response sys.XMLTYPE;

request sys.XMLTYPE;

BEGIN

scott.utl_dbws.set_http_proxy(‘myproxy.com:80’);

service_qname := scott.utl_dbws.to_qname(null, ‘CelciusToFahrenheit’);

service_      := scott.utl_dbws.create_service(service_qname);

call_         := scott.utl_dbws.create_call(service_);

scott.utl_dbws.set_target_endpoint_address(call_, ‘http://webservices.daehosting.com/services/TemperatureConversions.wso’);

scott.utl_dbws.set_property( call_, ‘OPERATION_STYLE’, ‘document’);

request       := sys.XMLTYPE(‘<celciustofahrenheit xmlns=”http://webservices.daehosting.com/temperature”><ncelcius>&#8217;||temperature||'</ncelcius></celciustofahrenheit>’);

response      := scott.utl_dbws.invoke(call_, request);

return response.extract(‘//CelciusToFahrenheitResult/child::text()’, ‘xmlns=”http://webservices.daehosting.com/temperature”&#8216;).getstringval();

END;

Now Test:

 

SELECT celciusToFahrenheit(30) from dual;

 

CELCIUSTOFAHRENHEIT(30)

——————————————————————————–

86

 

 

—————————————————————–

Advertisements

ADF Mobile Design -> 0 Mobile Design Principals

  1. Know your end users. 
    Before designing, spend time getting to know the role of your end users and the specific needs that they will have while mobile. Understanding key characteristics of your users, their work environments, and the tasks that they perform will help ensure that your product has the right features and optimal user experience. We have created a set of mobile personas that are based on international ethnographic research for the following roles: sales representative, field service technician, manager, retail merchandiser, and Generation Y.
  2. Define the essential mobile tasks. 
    A mobile application must be able to stand alone and not depend on an understanding of a related desktop version. When assessing how to convert an existing desktop application into a mobile design, be aware that the key task for the desktop may be quite different from that of the mobile. We strongly encourage you to think through the mobile use case rather than relying on the desktop workflows. Do not hesitate to eliminate tasks that are not essential to the mobile requirements. Successful mobile applications are frequently simplified to primary tasks, such as searching for coworkers or managing task lists. 
  3. Design contextually.
    Mobile applications are used in trains, warehouses, taxis, oil platforms, and more. Designs must work in the target work environment and maximize context awareness of the mobile device. A GPS-enabled device that maps sales representatives’ locations helps them arrive at their next appointments on time. A device that scans and displays information about an asset, such as details of the last service, helps a technician bring the right parts and make the appropriate repairs.
  4. Create a flattened navigation model. 
    Users do not want to traverse deep structures to complete a task. Flatten the navigation model for quick access to key task flows. Once users navigate to begin their work, provide a clear understanding of where they are and how they can return to their starting point. Instead of having a user log in, access a section, find an object, and then perform a task, a flattened navigation model provides quick access to the mobile task immediately after login.
  5. Design for the two-minutes-to-get-it-done mobile work style. 
    Typically, mobile devices are used in short bursts, while personal computers are used for extended work sessions. Mobile users will not tolerate designs that require a lot of data entry or multistep flows because they need to get to work faster and complete their tasks more quickly. Tasks should be simple and quick to complete.
  6. Integrate analytics. 
    Analytics and business intelligence are not limited to the desktop. Mobile users need analytics that work for small screens. A regional sales manager might see a screen that highlights store locations with the biggest sales data from last quarter or last year. A color-coded grid of locations versus key metrics draws attention to good, moderate, and bad situations. The first step in determining which analytics will be useful is to understand the mobile use case and how to integrate analytics that help decision making. A needless insertion of analytics takes up valuable real estate and makes it harder for mobile workers to do their jobs.
  7. Simplify search requirements. 
    Search is an important part of mobile applications and must be quickly accessible. Because mobile data entry is more difficult, simplify search entry requirements to a single field when possible and place the field above related data. If a user is on an inventory screen within a handheld application and initiates a search for an item, all results should relate to inventory attributes. When multiple-field searches are required, load field values with default data that the user can change with choice lists and lists of values. Do not require the user to enter text in multiple fields.
  8. Embed collaboration. 
    Embed collaboration into workflows, and include triggers to call a person, connect to a social network, and text using SMS and IM. The proliferation of social networking within the work environment demonstrates the increased importance of keeping in touch with colleagues and affiliated professionals. Mobility extends this trend by keeping coworkers in contact more often and in more places.
  9. Progressively disclose information. 
    Because screen real estate is precious on handheld devices, carefully consider the type and quantity of data that you display when designing the application. Information must be concisely summarized with basic overviews and limited actions. Details and additional actions should be available in drill-down pages. An example of progressive disclosure is used by mobile email programs that simplify functions in the overview interface and require a user to select an email address and only then provide functions for reply, delete, forward, and so on from the resulting details screen.
  10. Leverage the mobile platform. 
    Mobile applications can be built to run in the browser or as native applications installed on the device. Enterprise applications should leverage mobile capabilities that enable a user to tap a phone number to call or text, touch an address to map its location, and rotate the device for an alternate view. Native enterprise applications enable more integration than those applications run in the browser and provide the ability to transfer enterprise data to local built-in applications, such as calendars and contacts, so that users can view important business information without signing in. Understanding each platform and maximizing the appropriate mobile actions will ensure a productive and natural mobile experience.

Wireless Power Transmission

Wireless power transmission has been a dream since the days when Nikola Tesla imagined a world studded with enormous Tesla coils. But aside from advances in recharging electric toothbrushes, wireless power has so far failed to make significant inroads into consumer-level gear.
What is it?
This summer, Intel researchers demonstrated a method–based on MIT research–for throwing electricity a distance of a few feet, without wires and without any dangers to bystanders (well, none that they know about yet). Intel calls the technology a “wireless resonant energy link,” and it works by sending a specific, 10-MHz signal through a coil of wire; a similar, nearby coil of wire resonates in tune with the frequency, causing electrons to flow through that coil too. Though the design is primitive, it can light up a 60-watt bulb with 70 percent efficiency.

When is it coming?

Numerous obstacles remain, the first of which is that the Intel project uses alternating current. To charge gadgets, we’d have to see a direct-current version, and the size of the apparatus would have to be considerably smaller. Numerous regulatory hurdles would likely have to be cleared in commercializing such a system, and it would have to be thoroughly vetted for safety concerns. Assuming those all go reasonably well, such receiving circuitry could be integrated into the back of your laptop screen in roughly the next six to eight years. It would then be a simple matter for your local airport or even Starbucks to embed the companion power transmitters right into the walls so you can get a quick charge without ever opening up your laptop bag.

Memristor: A Groundbreaking New Circuit

This simple memristor circuit could soon transform all electronic devices.Since the dawn of electronics, we’ve had only three types of circuit components–resistors, inductors, and capacitors. But in 1971, UC Berkeley researcher Leon Chua theorized the possibility of a fourth type of component, one that would be able to measure the flow of electric current: the memristor. Now, just 37 years later, Hewlett-Packard has built one.
What is it?
As its name implies, the memristor can “remember” how much current has passed through it. And by alternating the amount of current that passes through it, a memristor can also become a one-element circuit component with unique properties. Most notably, it can save its electronic state even when the current is turned off, making it a great candidate to replace today’s flash memory. Memristors will theoretically be cheaper and far faster than flash memory, and allow far greater memory densities. They could also replace RAM chips as we know them, so that, after you turn off your computer, it will remember exactly what it was doing when you turn it back on, and return to work instantly. This lowering of cost and consolidating of components may lead to affordable, solid-state computers that fit in your pocket and run many times faster than today’s PCs. Someday the memristor could spawn a whole new type of computer, thanks to its ability to remember a range of electrical states rather than the simplistic “on” and “off” states that today’s digital processors recognize. By working with a dynamic range of data states in an analog mode, memristor-based computers could be capable of far more complex tasks than just shuttling ones and zeroes around.
When is it coming?
Researchers say that no real barrier prevents implementing the memristor in circuitry immediately. But it’s up to the business side to push products through to commercial reality. Memristors made to replace flash memory (at a lower cost and lower power consumption) will likely appear first; HP’s goal is to offer them by 2012. Beyond that, memristors will likely replace both DRAM and hard disks in the 2014-to-2016 time frame. As for memristor-based analog computers, that step may take 20-plus years.

Sending email from PL/SQL, with attachments

if we want to be to send email from within Oracle, to multiple recipients, and with attachments?. The attachments will be taken from the OS, but this could be easily modified to take them from an Oracle table of your choosing. Some of this code I gathered from other places, and a bunch of it is mine. In the end, this is the most complete PL/SQL email sending package that I know of. When we’re done, we’ll have a package that looks like below, and we’ll execute it as so..

Begin
   send_email.send(
   ToList=>             ‘myname@myhost.com’,
   Subject=>            ‘Test email with attachments’,
   Body=>               ‘Here is the body’,
   FromEmail=>          ‘myname@myhost.com’,
   SMTPServer=>         ‘localhost’,
   AttachList=>         ‘send_email.sql,login.sql’,
   Directory=>          ‘ATTACHMENTS’);
End;
/

You can describe by issue below command.

Desc send_email;

————————————————————————————-
FUNCTION GET_MIME_TYPE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 FILENAME                       VARCHAR2                IN
PROCEDURE SEND
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 TOLIST                         VARCHAR2                IN
 SUBJECT                        VARCHAR2                IN
 BODY                           VARCHAR2                IN
 FROMEMAIL                      VARCHAR2                IN     DEFAULT
 FROMHOST                       VARCHAR2                IN     DEFAULT
 SMTPSERVER                     VARCHAR2                IN     DEFAULT
 ATTACHLIST                     VARCHAR2                IN     DEFAULT
 DIRECTORY                      VARCHAR2                IN     DEFAULT
FUNCTION SPLIT RETURNS SPLIT_TBL
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 P_LIST                         VARCHAR2                IN
 P_DEL                          VARCHAR2                IN     DEFAULT

————————————————————————————————————

Couple of prerequisites here. First of all, if you want to send attachments from the OS, you’ll need to create an Oracle DIRECTORY object to point to the OS directory. Here’s an example.

Create or replace directory ATTACHMENTS as ‘/home/oracle/attachments’;

You’ll then reference that directory ATTACHMENTS from the send_email package.

Next, we need to allow our user to contact the email server, in this case, localhost, as I’ve got a local email server running that allows connections locally. If you’re running Oracle 11.1.0.6 or greater, you’ll need to create an ACL for the user, and we’re going to use a great procedure I found for this called, you guessed it, create_acl from Metalink note# 557070.1. If you don’t add an ACL, you’ll be getting to know an error like “ORA-24247: network access denied by access control list (ACL)”

This would be run as the “SYS” user, by the way.
create or replace procedure create_acl(
  aacl       varchar2,
  acomment   varchar2,
  aprincipal varchar2,
  aisgrant   boolean,
  aprivilege varchar2,
  aserver    varchar2,
  aport      number)
is
begin 
  begin
    DBMS_NETWORK_ACL_myname.DROP_ACL(aacl);
     dbms_output.put_line(‘ACL dropped…..’);
  exception
    when others then
      dbms_output.put_line(‘Error dropping ACL: ‘||aacl);
      dbms_output.put_line(sqlerrm);
  end;
  begin
    DBMS_NETWORK_ACL_myname.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
    dbms_output.put_line(‘ACL created…..’);
  exception
    when others then
      dbms_output.put_line(‘Error creating ACL: ‘||aacl);
      dbms_output.put_line(sqlerrm);
  end; 
  begin
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
    dbms_output.put_line(‘ACL assigned…..’);        
  exception
    when others then
      dbms_output.put_line(‘Error assigning ACL: ‘||aacl);
      dbms_output.put_line(sqlerrm);
  end;  
  commit;
  dbms_output.put_line(‘ACL commited…..’);
end;
/
show error

And then it’s executed via the following. This example gives the user myname privileges to make an outbound connection to port 25 on any server.

begin
  create_acl(
    ‘adam_smtp_acl.xml’,
    ‘ACL for myname to allow SMTP outbound’,
    ‘myname’,
    TRUE,
    ‘connect’,
    ‘*’,
    25);
end;
/

We need to create a type to support the below package

Create or replace TYPE “SPLIT_TBL”  AS TABLE OF VARCHAR2(32767);
/

—————————————-

 

OK then, let’s create the send_email package!

CREATE OR REPLACE package send_email as

   Function get_mime_type
   (
      FileName          in      varchar2
   ) return varchar2;

   Function split
   (
      p_list            in      varchar2,
      p_del             in      varchar2 := ‘,’
   ) return split_tbl pipelined;

   Procedure send (
      Tolist            in      varchar2,
      Subject           in      varchar2,
      Body              in      varchar2,
      FromEmail         in      varchar2 default ‘DoNotReply@Nowhere.Com’,
      SmtpServer        in      varchar2 default ‘localhost’,
      AttachList        in      varchar2 default null,
      Directory         in      varchar2 default null
   );

End send_email;
/

Show error;

CREATE OR REPLACE package body send_email as

   Function get_mime_type
   (
      FileName          in      varchar2
   ) return varchar2
   is
      NewFileName               varchar2(250);
      type mime_type_arr        is table of varchar2(250) index by varchar2(20);
      mime_types                mime_type_arr;
      mime_type                 varchar2(250);
      extension                 varchar2(250);
     
   begin
      —
      — Populate the mime_types array
      —
      mime_types(‘323’)         := ‘text/h323’;
      mime_types(‘acx’)         := ‘application/internet-property-stream’;
      mime_types(‘ai’)          := ‘application/postscript’;
      mime_types(‘aif’)         := ‘audio/x-aiff’;
      mime_types(‘aifc’)        := ‘audio/x-aiff’;
      mime_types(‘aiff’)        := ‘audio/x-aiff’;
      mime_types(‘asf’)         := ‘video/x-ms-asf’;
      mime_types(‘asr’)         := ‘video/x-ms-asf’;
      mime_types(‘asx’)         := ‘video/x-ms-asf’;
      mime_types(‘au’)          := ‘audio/basic’;
      mime_types(‘avi’)         := ‘video/x-msvideo’;
      mime_types(‘axs’)         := ‘application/olescript’;
      mime_types(‘bas’)         := ‘text/plain’;
      mime_types(‘bcpio’)       := ‘application/x-bcpio’;
      mime_types(‘bin’)         := ‘application/octet-stream’;
      mime_types(‘bmp’)         := ‘image/bmp’;
      mime_types(‘c’)           := ‘text/plain’;
      mime_types(‘cat’)         := ‘application/vnd.ms-pkiseccat’;
      mime_types(‘cdf’)         := ‘application/x-cdf’;
      mime_types(‘cer’)         := ‘application/x-x509-ca-cert’;
      mime_types(‘class’)       := ‘application/octet-stream’;
      mime_types(‘clp’)         := ‘application/x-msclip’;
      mime_types(‘cmx’)         := ‘image/x-cmx’;
      mime_types(‘cod’)         := ‘image/cis-cod’;
      mime_types(‘cpio’)        := ‘application/x-cpio’;
      mime_types(‘crd’)         := ‘application/x-mscardfile’;
      mime_types(‘crl’)         := ‘application/pkix-crl’;
      mime_types(‘crt’)         := ‘application/x-x509-ca-cert’;
      mime_types(‘csh’)         := ‘application/x-csh’;
      mime_types(‘css’)         := ‘text/css’;
      mime_types(‘dcr’)         := ‘application/x-director’;
      mime_types(‘der’)         := ‘application/x-x509-ca-cert’;
      mime_types(‘dir’)         := ‘application/x-director’;
      mime_types(‘dll’)         := ‘application/x-msdownload’;
      mime_types(‘dms’)         := ‘application/octet-stream’;
      mime_types(‘doc’)         := ‘application/msword’;
      mime_types(‘dot’)         := ‘application/msword’;
      mime_types(‘dvi’)         := ‘application/x-dvi’;
      mime_types(‘dxr’)         := ‘application/x-director’;
      mime_types(‘eps’)         := ‘application/postscript’;
      mime_types(‘etx’)         := ‘text/x-setext’;
      mime_types(‘evy’)         := ‘application/envoy’;
      mime_types(‘exe’)         := ‘application/octet-stream’;
      mime_types(‘fif’)         := ‘application/fractals’;
      mime_types(‘flr’)         := ‘x-world/x-vrml’;
      mime_types(‘gif’)         := ‘image/gif’;
      mime_types(‘gtar’)        := ‘application/x-gtar’;
      mime_types(‘gz’)          := ‘application/x-gzip’;
      mime_types(‘h’)           := ‘text/plain’;
      mime_types(‘hdf’)         := ‘application/x-hdf’;
      mime_types(‘hlp’)         := ‘application/winhlp’;
      mime_types(‘hqx’)         := ‘application/mac-binhex40’;
      mime_types(‘hta’)         := ‘application/hta’;
      mime_types(‘htc’)         := ‘text/x-component’;
      mime_types(‘htm’)         := ‘text/html’;
      mime_types(‘html’)        := ‘text/html’;
      mime_types(‘htt’)         := ‘text/webviewhtml’;
      mime_types(‘ico’)         := ‘image/x-icon’;
      mime_types(‘ief’)         := ‘image/ief’;
      mime_types(‘iii’)         := ‘application/x-iphone’;
      mime_types(‘ins’)         := ‘application/x-internet-signup’;
      mime_types(‘isp’)         := ‘application/x-internet-signup’;
      mime_types(‘jfif’)        := ‘image/pipeg’;
      mime_types(‘jpe’)         := ‘image/jpeg’;
      mime_types(‘jpeg’)        := ‘image/jpeg’;
      mime_types(‘jpg’)         := ‘image/jpeg’;
      mime_types(‘js’)          := ‘application/x-javascript’;
      mime_types(‘latex’)       := ‘application/x-latex’;
      mime_types(‘lha’)         := ‘application/octet-stream’;
      mime_types(‘lsf’)         := ‘video/x-la-asf’;
      mime_types(‘lsx’)         := ‘video/x-la-asf’;
      mime_types(‘lzh’)         := ‘application/octet-stream’;
      mime_types(‘m13’)         := ‘application/x-msmediaview’;
      mime_types(‘m14’)         := ‘application/x-msmediaview’;
      mime_types(‘m3u’)         := ‘audio/x-mpegurl’;
      mime_types(‘man’)         := ‘application/x-troff-man’;
      mime_types(‘mdb’)         := ‘application/x-msaccess’;
      mime_types(‘me’)          := ‘application/x-troff-me’;
      mime_types(‘mht’)         := ‘message/rfc822’;
      mime_types(‘mhtml’)       := ‘message/rfc822’;
      mime_types(‘mid’)         := ‘audio/mid’;
      mime_types(‘mny’)         := ‘application/x-msmoney’;
      mime_types(‘mov’)         := ‘video/quicktime’;
      mime_types(‘movie’)       := ‘video/x-sgi-movie’;
      mime_types(‘mp2’)         := ‘video/mpeg’;
      mime_types(‘mp3’)         := ‘audio/mpeg’;
      mime_types(‘mpa’)         := ‘video/mpeg’;
      mime_types(‘mpe’)         := ‘video/mpeg’;
      mime_types(‘mpeg’)        := ‘video/mpeg’;
      mime_types(‘mpg’)         := ‘video/mpeg’;
      mime_types(‘mpp’)         := ‘application/vnd.ms-project’;
      mime_types(‘mpv2’)        := ‘video/mpeg’;
      mime_types(‘ms’)          := ‘application/x-troff-ms’;
      mime_types(‘mvb’)         := ‘application/x-msmediaview’;
      mime_types(‘nws’)         := ‘message/rfc822’;
      mime_types(‘oda’)         := ‘application/oda’;
      mime_types(‘p10’)         := ‘application/pkcs10’;
      mime_types(‘p12’)         := ‘application/x-pkcs12’;
      mime_types(‘p7b’)         := ‘application/x-pkcs7-certificates’;
      mime_types(‘p7c’)         := ‘application/x-pkcs7-mime’;
      mime_types(‘p7m’)         := ‘application/x-pkcs7-mime’;
      mime_types(‘p7r’)         := ‘application/x-pkcs7-certreqresp’;
      mime_types(‘p7s’)         := ‘application/x-pkcs7-signature’;
      mime_types(‘pbm’)         := ‘image/x-portable-bitmap’;
      mime_types(‘pdf’)         := ‘application/pdf’;
      mime_types(‘pfx’)         := ‘application/x-pkcs12’;
      mime_types(‘pgm’)         := ‘image/x-portable-graymap’;
      mime_types(‘pko’)         := ‘application/ynd.ms-pkipko’;
      mime_types(‘pma’)         := ‘application/x-perfmon’;
      mime_types(‘pmc’)         := ‘application/x-perfmon’;
      mime_types(‘pml’)         := ‘application/x-perfmon’;
      mime_types(‘pmr’)         := ‘application/x-perfmon’;
      mime_types(‘pmw’)         := ‘application/x-perfmon’;
      mime_types(‘pnm’)         := ‘image/x-portable-anymap’;
      mime_types(‘pot,’)        := ‘application/vnd.ms-powerpoint’;
      mime_types(‘ppm’)         := ‘image/x-portable-pixmap’;
      mime_types(‘pps’)         := ‘application/vnd.ms-powerpoint’;
      mime_types(‘ppt’)         := ‘application/vnd.ms-powerpoint’;
      mime_types(‘prf’)         := ‘application/pics-rules’;
      mime_types(‘ps’)          := ‘application/postscript’;
      mime_types(‘pub’)         := ‘application/x-mspublisher’;
      mime_types(‘qt’)          := ‘video/quicktime’;
      mime_types(‘ra’)          := ‘audio/x-pn-realaudio’;
      mime_types(‘ram’)         := ‘audio/x-pn-realaudio’;
      mime_types(‘ras’)         := ‘image/x-cmu-raster’;
      mime_types(‘rgb’)         := ‘image/x-rgb’;
      mime_types(‘rmi’)         := ‘audio/mid’;
      mime_types(‘roff’)        := ‘application/x-troff’;
      mime_types(‘rtf’)         := ‘application/rtf’;
      mime_types(‘rtx’)         := ‘text/richtext’;
      mime_types(‘scd’)         := ‘application/x-msschedule’;
      mime_types(‘sct’)         := ‘text/scriptlet’;
      mime_types(‘setpay’)      := ‘application/set-payment-initiation’;
      mime_types(‘setreg’)      := ‘application/set-registration-initiation’;
      mime_types(‘sh’)          := ‘application/x-sh’;
      mime_types(‘shar’)        := ‘application/x-shar’;
      mime_types(‘sit’)         := ‘application/x-stuffit’;
      mime_types(‘snd’)         := ‘audio/basic’;
      mime_types(‘spc’)         := ‘application/x-pkcs7-certificates’;
      mime_types(‘spl’)         := ‘application/futuresplash’;
      mime_types(‘src’)         := ‘application/x-wais-source’;
      mime_types(‘sst’)         := ‘application/vnd.ms-pkicertstore’;
      mime_types(‘stl’)         := ‘application/vnd.ms-pkistl’;
      mime_types(‘stm’)         := ‘text/html’;
      mime_types(‘svg’)         := ‘image/svg+xml’;
      mime_types(‘sv4cpio’)     := ‘application/x-sv4cpio’;
      mime_types(‘sv4crc’)      := ‘application/x-sv4crc’;
      mime_types(‘swf’)         := ‘application/x-shockwave-flash’;
      mime_types(‘t’)           := ‘application/x-troff’;
      mime_types(‘tar’)         := ‘application/x-tar’;
      mime_types(‘tcl’)         := ‘application/x-tcl’;
      mime_types(‘tex’)         := ‘application/x-tex’;
      mime_types(‘texi’)        := ‘application/x-texinfo’;
      mime_types(‘texinfo’)     := ‘application/x-texinfo’;
      mime_types(‘tgz’)         := ‘application/x-compressed’;
      mime_types(‘tif’)         := ‘image/tiff’;
      mime_types(‘tiff’)        := ‘image/tiff’;
      mime_types(‘tr’)          := ‘application/x-troff’;
      mime_types(‘trm’)         := ‘application/x-msterminal’;
      mime_types(‘tsv’)         := ‘text/tab-separated-values’;
      mime_types(‘txt’)         := ‘text/plain’;
      mime_types(‘uls’)         := ‘text/iuls’;
      mime_types(‘ustar’)       := ‘application/x-ustar’;
      mime_types(‘vcf’)         := ‘text/x-vcard’;
      mime_types(‘vrml’)        := ‘x-world/x-vrml’;
      mime_types(‘wav’)         := ‘audio/x-wav’;
      mime_types(‘wcm’)         := ‘application/vnd.ms-works’;
      mime_types(‘wdb’)         := ‘application/vnd.ms-works’;
      mime_types(‘wks’)         := ‘application/vnd.ms-works’;
      mime_types(‘wmf’)         := ‘application/x-msmetafile’;
      mime_types(‘wps’)         := ‘application/vnd.ms-works’;
      mime_types(‘wri’)         := ‘application/x-mswrite’;
      mime_types(‘wrl’)         := ‘x-world/x-vrml’;
      mime_types(‘wrz’)         := ‘x-world/x-vrml’;
      mime_types(‘xaf’)         := ‘x-world/x-vrml’;
      mime_types(‘xbm’)         := ‘image/x-xbitmap’;
      mime_types(‘xla’)         := ‘application/vnd.ms-excel’;
      mime_types(‘xlc’)         := ‘application/vnd.ms-excel’;
      mime_types(‘xlm’)         := ‘application/vnd.ms-excel’;
      mime_types(‘xls’)         := ‘application/vnd.ms-excel’;
      mime_types(‘xlt’)         := ‘application/vnd.ms-excel’;
      mime_types(‘xlw’)         := ‘application/vnd.ms-excel’;
      mime_types(‘xof’)         := ‘x-world/x-vrml’;
      mime_types(‘xpm’)         := ‘image/x-xpixmap’;
      mime_types(‘xwd’)         := ‘image/x-xwindowdump’;
      mime_types(‘z’)           := ‘application/x-compress’;
      mime_types(‘zip’)         := ‘application/zip’;
      —
      — Determine the file extension
      —
      dbms_output.put_line(‘Splitting file ‘||FileName||’ to get extension.’);
      for i in (Select column_value as extension from table(split(FileName,’.’))) loop
         extension := i.extension;
      end loop;
      —
      — use the extension to determine the MIME type
      —
      if mime_types.exists(extension) then
         mime_type := mime_types(extension);
      else
         —
         — Default to ‘text/plain’ if not found
         —
         mime_type := ‘text/plain’;
      end if;

      dbms_output.put_line(‘Extension is ‘||extension);
      dbms_output.put_line(‘mime_type is ‘||mime_type);
       
      return mime_type;

   end get_mime_type;

   Function split
   (
      p_list varchar2,
      p_del varchar2 := ‘,’
   ) return split_tbl pipelined
   is
      l_idx    pls_integer;
      l_list    varchar2(32767) := p_list;
      l_value    varchar2(32767);
   begin
      loop
         l_idx := instr(l_list,p_del);
         if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));
         else
            pipe row(l_list);
            exit;
         end if;
      end loop;
      return;
   end split;

   Procedure send (
      ToList            in      varchar2,
      Subject           in      varchar2,
      Body              in      varchar2,
      FromEmail         in      varchar2 default ‘DoNotReply@Nowhere.Com’,
      SmtpServer        in      varchar2 default ‘localhost’,
      AttachList        in      varchar2 default null,
      Directory         in      varchar2 default null
    ) is

      smtp            utl_smtp.CONNECTION;
      reply           utl_smtp.REPLY;
      file_handle     bfile;
      file_exists     boolean;
      block_size      number;
      file_len        number;
      pos             number;
      total           number;
      read_bytes      number;
      data            raw(200);
      my_code         number;
      my_errm         varchar2(32767);
      mime_type       varchar2(50);
      myhostname      varchar2(255);
      att_table       dbms_utility.uncl_array;
      att_count       number;
      tablen          binary_integer;
      loopcount       number;

       Procedure WriteLine(
          line          in      varchar2 default null
       ) is

       Begin
          utl_smtp.Write_Data( smtp, line||utl_tcp.CRLF );
       End;

    Begin

      Select sys_context(‘USERENV’,’SERVER_HOST’) into myhostname from dual;

      dbms_output.put_line(‘ToList:     ‘||ToList);
      dbms_output.put_line(‘Subject:    ‘||Subject);
      dbms_output.put_line(‘Body:       ‘||Body);
      dbms_output.put_line(‘FromEmail:  ‘||FromEmail);
      dbms_output.put_line(‘SmtpServer: ‘||SmtpServer);
      dbms_output.put_line(‘AttachList: ‘||AttachList);
      dbms_output.put_line(‘Directory:  ‘||Directory);
      dbms_output.put_line(‘ToList:     ‘||ToList);
      dbms_output.put_line(‘myhostname: ‘||myhostname);

      dbms_output.put_line(‘Opening connection to ‘||SmtpServer);
      smtp := utl_smtp.OPEN_CONNECTION( SmtpServer, 25 );
      reply := utl_smtp.HELO( smtp, myhostname);
      reply := utl_smtp.MAIL( smtp, FromEmail );

      —
      — Split up the recipient list
      —
      for i in (Select column_value as recipient from table(split(ToList))) loop
         dbms_output.put_line(‘Recipient: ‘||i.recipient);
         reply := utl_smtp.RCPT( smtp, i.recipient);
      end loop;

      reply := utl_smtp.open_data( smtp );

      WriteLine( ‘From: ‘||FromEmail);

      —
      — Split up the recipient list (again)
      —
      for i in (Select column_value as recipient from table(split(ToList))) loop
         WriteLine( ‘To: ‘||i.recipient);
      end loop;

      WriteLine( ‘Subject: ‘||Subject );
      WriteLine( ‘Content-Type: multipart/mixed; boundary=”gc0p4Jq0M2Yt08jU534c0p”‘ );
      WriteLine( ‘MIME-Version: 1.0’ );
      WriteLine;

      — start of boundary payloads
      WriteLine( ‘–gc0p4Jq0M2Yt08jU534c0p’ );
      WriteLine( ‘Content-Type: text/plain’ );
      WriteLine;
      WriteLine(  Body );
      WriteLine;
      WriteLine( ‘–gc0p4Jq0M2Yt08jU534c0p’ );

      dbms_output.put_line(‘Starting attachment segment’);
      dbms_output.put_line(‘Directory: ‘||Directory);
      dbms_output.put_line(‘AttachList: ‘||AttachList);

      —
      — Split up the attachment list
      —
      loopcount := 0;
      Select count(*) into att_count from table(split(AttachList));
      if AttachList is not null and Directory is not null then
         for i in (Select ltrim(rtrim(column_value)) as attachment from table(split(AttachList))) loop
            loopcount := loopcount +1;
            dbms_output.put_line(‘Attaching: ‘||Directory||’/’||i.attachment);
            utl_file.fgetattr(Directory, i.attachment, file_exists, file_len, block_size);
            if file_exists then
               dbms_output.put_line(‘Getting mime_type for the attachment’);
               Select get_mime_type(i.attachment) into mime_type from dual;
               WriteLine( ‘Content-Type: ‘||mime_type );
               WriteLine( ‘Content-Transfer-Encoding: base64’);
               WriteLine( ‘Content-Disposition: attachment; filename=”‘||i.attachment||'”‘ );
               WriteLine;
               file_handle := bfilename(Directory,i.attachment);
               pos := 1;
               total := 0;
               file_len := dbms_lob.getlength(file_handle);
               dbms_lob.open(file_handle,dbms_lob.lob_readonly);

               loop
                  if pos + 57 – 1 > file_len then
                     read_bytes := file_len – pos + 1;
                     –dbms_output.put_line(‘Last read – Start: ‘||pos);
                  else
                     –dbms_output.put_line(‘Reading – Start: ‘||pos);
                     read_bytes := 57;
                  end if;
                  total := total + read_bytes;
                  dbms_lob.read(file_handle,read_bytes,pos,data);
                  utl_smtp.write_raw_data(smtp,utl_encode.base64_encode(data));
                  –utl_smtp.write_raw_data(smtp,data);
                  pos := pos + 57;
                  if pos > file_len then
                     exit;
                  end if;
               end loop;
               dbms_output.put_line(‘Length was ‘||file_len);
               dbms_lob.close(file_handle);
               if (loopcount < att_count) then
                  WriteLine;
                  WriteLine( ‘–gc0p4Jq0M2Yt08jU534c0p’ );
                else
                  WriteLine;
                  WriteLine( ‘–gc0p4Jq0M2Yt08jU534c0p–‘ );
                  dbms_output.put_line(‘Writing end boundary’);
                end if;
            else
               dbms_output.put_line(‘Skipping: ‘||Directory||’/’||i.attachment||’Does not exist.’);
            end if;
         end loop;
      end if;

      reply := utl_smtp.close_data( smtp );
      reply := utl_smtp.quit( smtp );

   exception
      when others then
         my_code := sqlcode;
         my_errm := sqlerrm;
         dbms_output.put_line(‘Error code ‘ || my_code || ‘: ‘ || my_errm);
  end;

end send_email;

Upload a csv or excel file from clients machine in Oracle Table with oracle Forms

WebUtil: How to Read an Excel file into an Oracle Form

let’s get down to business and create a form that reads data from Excel into a Forms Data Block.

Do before proceed.

  1.  Table PLANETS :- script to create the table used by this sample code

DROP TABLE PLANETS;
CREATE TABLE PLANETS (
 PID                     NUMBER(4),
 NAME             VARCHAR2(15),
 FEATURES         VARCHAR2(15));
/

  1. planets.xls – Excel (version 2003) sample spreadsheet
1 Mercury Hot!
2 Venus Bright!
3 Earth Life!
4 Mars Water!
5 Jupiter Big!
6 Saturn Rings!
eod    

Setup steps:

  1. Log into your database and run the script
  2. Ensure your environment is properly configured to run WebUtil. These steps does not cover this configuration – there is plenty of information available in the OTN forums as well on the Internet that covers configuring WebUtil so I will not included it here.
  3. Place “planets.xls’ some place on the client computer. I have modified Oracle’s original form to display a “File Select dialog” rather than hard code the file name and location in the code.
  4. Ensure you have configured the Forms Builder to allow running a form from the Forms Builder. This document does not cover how to perform this configuration.
  5. Create a simple form for PLANETS table data block.
  6. Palace a button on form canves and name it “Read from Excell File”.
  7. on Button Press trigger paste below code.

Code:

DECLARE
 application   Client_OLE2.Obj_Type;
 workbooks     Client_OLE2.Obj_Type;
 workbook      Client_OLE2.Obj_Type;
 worksheets    Client_OLE2.Obj_Type;
 worksheet     Client_OLE2.Obj_Type;
 worksheet2    Client_OLE2.Obj_Type; 
 cell      Client_OLE2.OBJ_TYPE;
 args      Client_OLE2.OBJ_TYPE;
 cell_value   varchar2(100);
 num_wrkshts  NUMBER;
 wksht_name  VARCHAR2(250);
 eod      boolean:=false;
 j        integer:=1;
 v_fName    VARCHAR2(250);
BEGIN
 — Get the name of the file to open
 –v_fName := ‘D:\MyDevelopment\Forms\Samples\WebUtil\Read_Excel\planets3.xls’;
 v_fName := WebUtil_File.File_Open_Dialog(
       directory_name => ‘C:\’
       –,file_name => Get_Form_Property(:System.Current_form,Form_Name)||’.xls’
       ,File_Filter => null
       ,Title => ‘Select Client filename to Open.’
     );

 IF ( v_fName IS NOT NULL ) THEN
 
  — The following sets up communication with the excel spreadsheet
  — ————————————————————–
  — Open the OLE application
  application := Client_OLE2.create_obj(‘Excel.Application’);
  — Keep the application hidden
  Client_OLE2.set_property(application,’Visible’,’false’);
 
  workbooks := Client_OLE2.Get_Obj_Property(application, ‘Workbooks’);
  args := Client_OLE2.CREATE_ARGLIST;
  
  — Open the selected File
  — ———————-
  Client_OLE2.add_arg(args,v_fName);
  workbook := Client_OLE2.GET_OBJ_PROPERTY(workbooks,’Open’,args);
  Client_OLE2.destroy_arglist(args);
 
  worksheets := Client_OLE2.GET_OBJ_PROPERTY(workbook, ‘Worksheets’);
  
  — Get number of worksheets
  — ————————
  num_wrkshts := Client_OLE2.GET_NUM_PROPERTY(worksheets, ‘Count’);
  worksheet := Client_OLE2.GET_OBJ_PROPERTY(application,’activesheet’);
    
  –Go to the first record
  go_block(‘planets’);
  first_record;
    
  loop
    If :system.record_status <> ‘NEW’ then
           create_record;
    end if;
    
  exit when eod;
   
   for k in 1..3 loop  –3 fields per record
    args:= Client_OLE2.create_arglist;
    Client_OLE2.add_arg(args, j);
    Client_OLE2.add_arg(args, k);
    cell := Client_OLE2.get_obj_property(worksheet, ‘Cells’, args);
    Client_OLE2.destroy_arglist(args);
    cell_value :=Client_OLE2.get_char_property(cell, ‘Value’);
     
    
     
    if upper(cell_value) = ‘EOD’ then
     eod:=true;
     Message(‘End of Data’);
     exit;
    end if;
  
  –Could be done this way also ->
  
  /*if k =1 then
   :dept.deptno:=cell_value;
  end if;
  
  if k =2 then
   :dept.dname:=cell_value;
  end if;
  
  if k =3 then
   :dept.loc:=cell_value;
  end if; 
  */
 
    –Less code this way ->
    copy(cell_value,name_in(‘system.cursor_item’));
    next_item;
  
   end loop; –for
   
   j:=j+1;
  end loop;–main loop
  
  — Release the Client_OLE2 object handles
  IF (cell IS NOT NULL) THEN
   Client_OLE2.release_obj(cell);
  END IF;
  IF (worksheet IS NOT NULL) THEN
   Client_OLE2.release_obj(worksheet);
  END IF;
  IF (worksheets IS NOT NULL) THEN
   Client_OLE2.release_obj(worksheets);
  END IF;
  IF (worksheet2 IS NOT NULL) THEN
   Client_OLE2.release_obj(worksheet2);
  END IF;
  IF (workbook IS NOT NULL) THEN
   Client_OLE2.release_obj(workbook);
  END IF;
  IF (workbooks IS NOT NULL) THEN
   Client_OLE2.release_obj(workbooks);
  END IF;
  Client_OLE2.invoke(application,’Quit’);
  Client_OLE2.release_obj(application);
 ELSE
  Message(‘No File selected.’);
  message(‘ ‘);
  RAISE Form_Trigger_Failure;
 END IF;
END;

Now enjoy!

 

 

 

 

 

Oracle 12c New Features.

The Oracle 12C means different things to different people.It all depends on which areas you are looking at, as there are improvements in many areas. Summarized below is the list of Top 12 Features of Oracle 12C as I see it. I have summarized below, the top 12 which I found interesting.

01. Pluggable Databases Through Database Consolidation:

Oracle is doing every thing to jump into the cloud bandwagon. With 12C, Oracle is trying to address the problem of Multitenancy through this feature. There is a radical change and a major change in the core database architecture through the introduction of Container Databases also called CBD and Pluggable Databases (PDB). The memory and process is owned by the Container Database. The container holds the metadata where the PDBs hold the user data. You can create upto 253 PDBs including the seed PDB.

In a large setup, it is common to see 20 or 30 different instances running in production environment. With these many instances, it is a maintenance nightmare as all these instances have to be separately

•Upgraded
•Patched
•Monitored
•Tuned
•RAC Enabled
•Adjusted
•Backed up and
•Data Guarded.

With Pluggable Databases feature, you just have to do all this for ONE single instance. Without this feature, prior to 12C, you would have to create separate schemas and there is always a thread of security how much ever the isolation we build into it. There are problems with namespace conflicts, there is always going to be one public synonym that you can create. With PDBs you can have a separate HR or Scott schema for each PDB, separate Emp, Dept Tables and separate public synonyms. Additionally, 2 PDBs can talk to each other through the regular DB Link feature. There is no high startup cost of creating a database any more. Instead of one instance per day, the shift is into one instance per many databases. For the developer community, you can be oblivious of all this and still continue to use the PDBs as if it were a traditional database, but for the DBAs the world would look like it has changed a lot.

Another cool feature is, you can allocate a CPU percentage for each PDB.

Another initiative being, it has announced a strategic tieup with salesforce.com during the first week of July 2013.

 

02. Redaction Policy:

This is one of the top features in Oracle 12C. Data Redaction in simple terms means, masking of data. You can setup a Data Redaction policy, for example SSN field in a Employee table can be masked. This is called redaction. From Sql Develop you can do this by going to the table: Employee->Right click on Security Policy->click on New->click on Redaction Policy->Enter SSN.
When you do a select * from employee, it will show that the SSN is masked.
The new data masking will use a package called DBMS_REDACT. It is the extension to the FGAC and VPD present in earlier versions.
By doing this, whoever needs to view the data will be able to see it where as the other users will not be able to view it.

 

03. Top N Query and Fetch and offset Replacement to Rownum:

With the release of Oracle Database 12c, Oracle has introduced this new SQL syntax to simplify fetching the first few rows. The new sql syntax “Fetch First X Rows only” can be used.

 

04. Adaptive Query Optimization and Online Stats Gathering:

With this feature, it helps the optimizer to make runtime adjustments to execution plan which leads to better stats. For statements like CTAS (Create Table As Select) and IAS (Insert As Select), the stats is gathered online so that it is available immediately.

 

05. Restore a Table easily through RMAN:

Earlier if you had to restore a particular table, you had to do all sorts of things like restoring a tablespace and or do Export and Import. The new restore command in RMAN simplifies this task.

 

06. Size Limit on Varchar2, NVarchar2, Raw Data Types increased:

The previous limit on these data types was 4K. In 12C, it has been increased to 32,767 bytes. Upto 4K, the data is stored inline. I am sure everyone will be happy with this small and cute enhancement.

 

07. Inline PL/SQL Functions and Procedures:

The in line feature is extended in Oracle 12C. In addition to Views, we can now have PL/SQL Procedures and Functions as in line constructs. The query can be written as if it is calling a real stored procedure, but however the functions do not actually exist in the database. You will not be able to find them in ALL_OBJECTS. I think this will be a very good feature for the developers to explore as there is no code that needs to be compiled.

 

08. Generated as Identity/Sequence Replacement:

You can now create a col with ‘generated as identity’ clause. Thats it. Doing this is equivalent to creating a separate sequence and doing a sequence.nextval for each row. This is another handy and a neat feature which will help developer community. This is also called No Sequence Auto Increment Primary Key.

 

09. Multiple Indexes on a Single Column:

Prior to 12C, a column cant be in more than one index. In 12C, you can include a column in B-tree index as well as a Bit Map index. But, please note that only one index is usable at a given time.

 

10. Online Migration of Table Partition or Sub Partition:

You can very easily migrate a partition or sub partition from one tablespace to another. Similar to how the online migration was achieved for a non-partitioned table in prior releases, a table partition or sub partition can be moved to another tablespace online or offline. When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.

 

11. Temporary UNDO:

Prior to 12C, undo records generated by TEMP Tablespace is stored in the undo tablespace. With Temp undo feature in 12C, temp undo records can be stored in temporary table instead of UNDO TS. The benefit is – reduced undo tablespace and reduced redo log space used.

SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=2G;
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0; –disables the hard limit

 

12. In Database Archiving:

This feature enables archiving rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression but are not visible to the application. These records are skipped during FTS (Full Table Scan).

ADF Mobile Design -> 10 Mobile Design Principals

  1. Know your end users. 
    Before designing, spend time getting to know the role of your end users and the specific needs that they will have while mobile. Understanding key characteristics of your users, their work environments, and the tasks that they perform will help ensure that your product has the right features and optimal user experience. We have created a set of mobile personas that are based on international ethnographic research for the following roles: sales representative, field service technician, manager, retail merchandiser, and Generation Y.
  2. Define the essential mobile tasks. 
    A mobile application must be able to stand alone and not depend on an understanding of a related desktop version. When assessing how to convert an existing desktop application into a mobile design, be aware that the key task for the desktop may be quite different from that of the mobile. We strongly encourage you to think through the mobile use case rather than relying on the desktop workflows. Do not hesitate to eliminate tasks that are not essential to the mobile requirements. Successful mobile applications are frequently simplified to primary tasks, such as searching for coworkers or managing task lists.
  3. Design contextually.
    Mobile applications are used in trains, warehouses, taxis, oil platforms, and more. Designs must work in the target work environment and maximize context awareness of the mobile device. A GPS-enabled device that maps sales representatives’ locations helps them arrive at their next appointments on time. A device that scans and displays information about an asset, such as details of the last service, helps a technician bring the right parts and make the appropriate repairs.
  4. Create a flattened navigation model.
    Users do not want to traverse deep structures to complete a task. Flatten the navigation model for quick access to key task flows. Once users navigate to begin their work, provide a clear understanding of where they are and how they can return to their starting point. Instead of having a user log in, access a section, find an object, and then perform a task, a flattened navigation model provides quick access to the mobile task immediately after login.
  5. Design for the two-minutes-to-get-it-done mobile work style. 
    Typically, mobile devices are used in short bursts, while personal computers are used for extended work sessions. Mobile users will not tolerate designs that require a lot of data entry or multistep flows because they need to get to work faster and complete their tasks more quickly. Tasks should be simple and quick to complete.
  6. Integrate analytics. 
    Analytics and business intelligence are not limited to the desktop. Mobile users need analytics that work for small screens. A regional sales manager might see a screen that highlights store locations with the biggest sales data from last quarter or last year. A color-coded grid of locations versus key metrics draws attention to good, moderate, and bad situations. The first step in determining which analytics will be useful is to understand the mobile use case and how to integrate analytics that help decision making. A needless insertion of analytics takes up valuable real estate and makes it harder for mobile workers to do their jobs.
  7. Simplify search requirements. 
    Search is an important part of mobile applications and must be quickly accessible. Because mobile data entry is more difficult, simplify search entry requirements to a single field when possible and place the field above related data. If a user is on an inventory screen within a handheld application and initiates a search for an item, all results should relate to inventory attributes. When multiple-field searches are required, load field values with default data that the user can change with choice lists and lists of values. Do not require the user to enter text in multiple fields.
  8. Embed collaboration. 
    Embed collaboration into workflows, and include triggers to call a person, connect to a social network, and text using SMS and IM. The proliferation of social networking within the work environment demonstrates the increased importance of keeping in touch with colleagues and affiliated professionals. Mobility extends this trend by keeping coworkers in contact more often and in more places.
  9. Progressively disclose information.
    Because screen real estate is precious on handheld devices, carefully consider the type and quantity of data that you display when designing the application. Information must be concisely summarized with basic overviews and limited actions. Details and additional actions should be available in drill-down pages. An example of progressive disclosure is used by mobile email programs that simplify functions in the overview interface and require a user to select an email address and only then provide functions for reply, delete, forward, and so on from the resulting details screen.
  10. Leverage the mobile platform. 
    Mobile applications can be built to run in the browser or as native applications installed on the device. Enterprise applications should leverage mobile capabilities that enable a user to tap a phone number to call or text, touch an address to map its location, and rotate the device for an alternate view. Native enterprise applications enable more integration than those applications run in the browser and provide the ability to transfer enterprise data to local built-in applications, such as calendars and contacts, so that users can view important business information without signing in. Understanding each platform and maximizing the appropriate mobile actions will ensure a productive and natural mobile experience.

WebLogic as a Windows Service!

WebLogic as a Windows Service

The below post describes the installation of WebLogic server as a windows service, we will look at how we can install the Admin Server as well as Managed server as a service.
Steps:

1. Create an install batch script that contains the domain, server details.

Installing Admin Server as a service.

Create an install script as below.

—————————————————

SETLOCAL

set DOMAIN_NAME=Wonders_Domain

set USERDOMAIN_HOME=C:\BEA103\user_projects\domains\Wonders_Domain

set SERVER_NAME=AdminServer

set WL_HOME=C:\BEA103\wlserver_10.3

set WLS_USER=weblogic

set WLS_PW=weblogic

set PRODUCTION_MODE=true

set MEM_ARGS=-Xms512m –Xmx512m

call “%WL_HOME%\server\bin\installSvc.cmd”

ENDLOCAL

—————————————————

Installing Managed  Server as Windows Service:

If you want to install a Managed Server as a Windows service, you must include a variable that specifies the location of the domain’s Administration Server. The Managed Server must contact the Administration Server to retrieve its configuration data.

The Administration Server (which is not a service) must be started before installing and starting Managed Server as a Windows service.

Create an install script as below.

—————————————————

echo off
SETLOCAL

set DOMAIN_NAME= Wonders_Domain
set USERDOMAIN_HOME= C:\BEA103\user_projects\domains\Wonders_Domain
set SERVER_NAME=ManagedServer1
set PRODUCTION_MODE=true

set WL_HOME=C:\BEA103\wlserver_10.3

set ADMIN_URL=http://<adminserver-host>:7501

set MEM_ARGS=-Xms40m -Xmx250m

call “%WL_HOME%\server\bin\installSvc.cmd”

—————————————————

NOTE: If you set up both an Administration Server and a Managed Server to run as Windows services on the same computer, you can specify that the Managed Server starts only after the Administration Server. In that scenario we need to pass the below argument while running the script.

-delay:delay_milliseconds

For example:

“%WL_HOME%\server\bin\beasvc” -install
-svcname:”%DOMAIN_NAME%_%SERVER_NAME%” -delay:120000

2. After running this script in the command prompt you will observe a log statement such as

“beasvc <Server> installed”

You can check the same from the services list as well using the services.msc utility.

3.  Verify whether the WebLogic Server is successfully installed as a service as below.

Open a command window and enter the following command:

set PATH=WL_HOME\server\bin;%PATH%

Navigate to the WLS_SERVER_HOME/server/bin

Enter:   beasvc -debug “yourServiceName

For Example:

C:\BEA103\wlserver_10.3\server\bin>beasvc -debug “beasvc Wonders_Domain_AdminServer”

4. You can start / stop the server from the services.msc window or you can also use the below commands.

Starting the service:

net start “beasvc Wonders_Domain_AdminServer”

Stopping the service:

net stop “beasvc Wonders_Domain_AdminServer”

Note: For each server instance that you set up as a Windows service, WebLogic Server creates a key in the Windows Registry under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services. The registry entry contains such information as the name of the server and other startup arguments.

Uninstall the WebLogic server as a windows service.

1. Create an Uninstall script as below.

******************************************************

echo off

SETLOCAL

set DOMAIN_NAME= Wonders_Domain
set SERVER_NAME=AdminServer
set WL_HOME=G:\BEA103\wlserver_10.3

call “%WL_HOME%\server\bin\uninstallSvc.cmd”

ENDLOCAL

******************************************************

2. Execute the script from the command prompt and you will see the below massage.

beasvc <service-name> removed.

 

How to create Entity Object for Oracle ADF Components Layer

How to create Entity Object for Oracle ADF Components Layer in Application

Lets look how we accutaly built Oracle ADF Components layer for our application. The first step is usually creating Entity Objects and this involves following steps.1) Mapping to database tables
2) Choosing specific attributes
3) Set attribute propertiesStart Oracle JDeveloper, Click on New Application and from Categories select Application and from Item select Fusion Web Application (ADF)

Enter Application Name and than click on Finish

Right Click on Model and choose New

From the Business Tire category select ADF Business Components and from items select Business Components from Tables

Create the Connection to the Oracle Database

Query the Database Schema and move the Entities from Available to Selected window. In my case I am moving Department and Employees Entities. Rename the Package model from model.eo (for ease to remember)

Create the Entities based Views and rename the package as shown and than click on Finish

We can define two types of attribute

a) Control Functionality and behaviour such as Mandatory fields, Default values of the fields and also set the database interaction properties etc.
b) UI Hints – default UI representation such as Label of the fields, Tooltip, Format mask and control types etc

Expand the model.eo package and open the Employees entity. Click on Attributes and select the hiredate field from Employees entity. Now we will set the following attributes on the hiredate field.

1) Setting the default date value using the Groovy Expression
2) Setting the Label, Tool Tip, Format Type and Format on hiredate field

Save the Project. Expand model.vo package, right click on AppModule and choose run.

Oracle ADF provides swing based interface to interact our View Object. Double Click on EmployeesView1 and than click on plus sign to create a record. You will see that the default value of the hiredate date filed, Label is also set as we did. Move the mouse cursor to the hiredate field, Tool Tip will also appear automatically.