Learning Oracle Integration Cloud Services SOA | BPM

According to Oracle's official blog following exams are going to be expire in July 31, 2013:- 1)   1Z0-007 Introduction to Oracle9i: SQL 2)   1Z0-031 Oracle9i Database: Fundamentals I 3)   1Z0-032 Oracle9i Database: Fundamentals II 4)   1Z0-033 Oracle9i Database: Performance Tuning 5)   1Z0-141 Oracle Forms: Build Internet Applications 6)   1Z0-147 Program with PL/SQL

Oracle provides sample HR schema in all versions of its databases which is locked by default. For working with tutorial of Introduction to Oracle you need to work on HR schema. Follow the given below steps to unlock HR schema in Oracle Database XE 11gR2 by using PL/SQL Developer. Login in PL/SQL Developer as a user “system or sysdba” with respective password Click on New button and open Command Window

What are Packages in Oracle Database A package is a group of procedures, functions, variables and SQL statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification and Package Body. Package Specification Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code. Package Body Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification.

In a database management system, a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data, preserving data integrity and improving productivity. The Oracle PL/SQL language allows you to write procedure to centralize the business logic and store the code in the database. I have created the following Stored Procedure on Oracle using TOAD. This particular Stored Procedure will Insert a Customer to the Customer Table. For the sake of simplicity, Customer Table contains only 4 columns (Customer ID, Customer Name, City and Contact No). In this post I will show you how to create Oracle stored Procedure using TOAD.  Connect to Oracle Database using TOAD. Click on Database menu and open Schema Browser.

The Oracle PL/SQL language allows you to write functions and procedure to centralize the business logic and store the code in the database.  A stored function is similar to a stored procedure with one key difference, a stored function returns a single value. This makes a stored function available in your SQL SELECT statements, unlike stored procedures that you must call within an anonymous PL/SQL block or another stored procedure. In this post I will show you how to create Oracle stored Functions using TOAD.  Connect to Oracle Database using TOAD. Click on Database menu and open Schema Browser.

A synonym is a database object that provides a better name to refer to objects. You can use synonyms to  shorten lengthy names or to ease the reference to an object owned by another user. You can create  synonyms for a table, view, sequence, procedure and other objects. In this post I will show you how to create Synonyms in Oracle Database using TOAD for Oracle. Connect to Oracle Database using TOAD. Click on Database menu and open Schema Browser

Calling stored procedure is same as calling stored function as mentioned in previous post. In this example we will perform INSERT, UPDATE, DELETE on Account table. Code snippets to show you how to call a Oracle stored procedure via JDBC   CallableStatement , and how to pass IN parameters from Java to stored procedure. 1) Stored Procedure A stored procedure in Oracle database. Later, calls it via JDBC. create or replace procedure spAccount(p_acode number,p_aname varchar, p_obalance number,p_descflag varchar) IS begin --INSERTING METHOD IF p_descflag = 'I' THEN insert into account values (p_acode,p_aname,p_obalance); END IF; --UPDATING METHOD IF p_descflag = 'U' THEN update account aa set aa.acct_name = p_aname, aa.obalance = p_obalance where aa.acct_code = p_acode; END IF; --UPDATING METHOD IF p_descflag = 'D' THEN delete from account aa where aa.acct_code = p_acode; END IF; commit; end spAccount; ...

Oracle PL/SQL JAVA Variable declaration T1 Number (8,2); T2 Number (5) := 5; T3 Varchar2(23); T4 Date; T5 Char; Variable declaration String T1; int T2; double T3; Date T4 = new Date(); char T5; if condition in PL/SQL IF (condition) THEN statement; statement; ELSE statement; statement; END IF; if condition in Java if (condition){ statement; statement; }else { statement; statement; } Exception handling BEGIN statement; statement; statement; EXCEPTION WHEN (exception) THEN statement; statement; END; Exception handling try{ statement; statement; } catch (Exception ex){ statement; } finally { statement; } For Loop FOR i IN [REVERS] low..upper statement; statement; END LOOP; For Loop for (int; condition; inc){ statement; statement; } While Loop WHILE (condition) LOOP statement; statement; END LOOP; While Loop while (condition){ statement; statement; }

Hi, I am Oracle Developer and working in Oracle Forms, Reports, SQL and PL/SQL. Now i am starting to learn JAVA Programming and Oracle ADF because Oracle announced that they will stop the support of Oracle Developer Suite in 2013 and Oracle ADF will replace Oracle Developer Suite in future. In the upcoming posts i will share my learning of Java Programming and Oracle ADF.

Following tools are used to insert Unicode Urdu Data into Oracle Database without any configuration or setting. 1) Oracle iSQL*Plus 2) Oracle SQL Developer Oracle iSQL*Plus iSQLPlus (iSQL*Plus) is a web-based utility similar to SQL*Plus. Using iSQL*Plus you can run SQL and PL/SQL commands and display their results. iSQLPlus commonly used by users, administrators, and programmers. How to access Oracle iSQL*Plus Use the following command to start the services of Oracle iSQL*Plus. Isqlplusctl start Open your web browser and navigate to the system (http://your.system.name:5560/isqlplus) . Oracle iSQL*Plus used the 5560 port. Oracle SQL Developer Oracle SQL Developer is an integrated development environment (IDE) for working with SQL in Oracle databases. Oracle Corporation provides this product free. You can use this IDE to insert Unicode Urdu data in Oracle Database without any NLS LANG setting.

EXISTS  is always perform faster in Oracle Database because “ EXISTS ” returns a boolean value (True or False), whereas “ IN ” returns a value.

The Oracle BFile type, is used to store unstructured binary data outside of the database in the operating system. BFiles are read-only and only support random-reads. CLOB type is stored in the database using two byte code character set. Unlike BFile, CLOB support sequential access and can be modified.

Whenever we issue an INSERT, DELETE, UPDATE or single row SELECT INTO statement, Oracle creates a implicit cursor to deal with this operation. An explicit cursor, is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR … IS  statement.

TRUNCATE is much faster than delete because it does not allow for rollbacks to occur, and automatically commits data when finishes.Also truncate does not fire any trigger, so if there is a trigger on your table, TRUNCATE would not fire it but DELETE will and you cannot use the WHERE clause on TRUNCATE.

The DUAL table is a table that Oracle creates with it is data dictionary consisting in exactly one row. DUAL belongs to the SYS user but all users can query it , and use it’s unique property of returning only one row, to select pseudo-columns .

An anonymous PL/SQL block is a block of code that is not stored in the database. When submitted to the database, the code will be executed immediately. An anonymous PL/SQL block always starts with DECLARE or BEGIN. Example: BEGIN    dbms_output.put_line (' Hi, this is anonymous PL/SQL '); END; /

The SET_APPLICATION_PROPERTY build-in in Oracle Forms allow to change the mouse pointer. Example: SET_APPLICATION_PROPERTY(CURSOR_STYLE, BUSY);

FORMS_DDL built-in or DBMS_SQL database package are used to execute dynamic SQL from Oracle Forms.  Example: FORMS_DDL('INSERT INTO X VALUES (' || col_list || ')'); Note that FORMS_DDL will force an implicit COMMIT and de-synchronize Oracle Forms.

The first thing that the user sees when using run form is the Oracle logon prompt asking them for their username, password, and database to connect to. You can bypass this screen or customize it by displaying your own logon screen. For example: ON-LOGIN Trigger at Form Level declare uname varchar2(10); pass varchar2(10); begin uname := 'username'; pass :='password'; logon(uname, pass||'@connect_database'); end;

DDL (Data Definition Language) commands like CREATE, DROP and ALTER are not directly supported in Oracle Forms because Oracle Form is not supposed to manipulate the database structure. A statement like CREATE TABLE X (A DATE); will result in error: Encountered the symbol "CREATE" which is an reserved word. However, you can use the FORMS_DDL built-in to execute DLL statements. Eg: FORMS_DDL('CREATE TABLE X (A DATE)');