Learning Oracle Integration Cloud Services SOA | BPM

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.

The Oracle PL/SQL language allows you to write functions and procedure to centralize the business logic and store the code in the database. This business logic may be call in many different ways including from a Glassfish Server's JDBC Pool Connection Resource. There are five steps involved in calling a PL/SQL Function from within a application: Create and prepare a JDBC CallableStatement object that contains a call to your PL/SQL function. Register the output parameter for your PL/SQL function. Provide all of the required parameter values to your PL/SQL function. Call the execute() method for your CallableStatement object, which then performs the call to your PL/SQL procedure. Read the returned value from your PL/SQL function.

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

Restriction for the use of LONG columns in Oracle Databsae 1) Only one column of type LONG is allowed.

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.

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)');

In Oracle SQL/PL-SQL the  TRANSLATE function performs a character wise replacement of a string. The syntax is: TRANSLATE ( input_string , string1 , string2 ) - String1 is the string that will be searched for in input_string - All characters in the String1 will be replaced with the corresponding character in the String2.

In Oracle SUBSTR function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position. The syntax for the SUBSTR function is: SUBSTR ( string, start_position, [ length ] ) Examples SELECT SUBSTR(‘PASSWORD’,1,4) FROM DUAL; RESULT: PASS

In Oracle, REPLACE function is used to replace a sequence of characters in a string with another set of characters. The syntax for the REPLACE function is: REPLACE( string1, string_to_replace, [ replacement_string ] ) Examples: SELECT REPLACE (' PASSWORD ',' PASS ') FROM DUAL; Result: WORD

In Oracle RTRIM  function removes all the rightmost characters matching string from  Characters. The syntax for the  RTRIM function is: RTRIM (character,Removing String) For Example: SELECT RTRIM (' FOREST ',' EST ') FROM DUAL; Result : FOR

In Oracle LTRIM  function removes all the characters matching string from the left side of Characters. The syntax for the  LTRIM function is: LTRIM (character,Removing String) For Example: SELECT LTRIM (' FOREST ',' FOR ') FROM DUAL; Result : EST

In Oracle, RPAD function pads the right-side of a string with a specific set of characters.The syntax for the RPAD function is: RPAD( string1, padded_length, [ pad_string ] ). For Example SELECT RPAD (' SHAHEER ',12,*****) FROM DUAL; RESULT: SHAHEER*****

In Oracle, LPAD function pads the left side of a string with a specific set of characters. The syntax for the LPAD function is: LPAD ( string1, padded_length, [ pad_string ] ). For example SELECT LPAD (' SHAHEER ',12,'*****') FROM DUAL; RESULT: *****SHAHEER

In Oracle,  UPPER function converts all letters in the specified string to uppercase. The syntax for the  UPPER function is:  UPPER (string). For Example SELECT UPPER (' shaheer ') FROM DUAL; RESULT : SHAHEER