SERPland Flugverkehr/Airtraffic Zürich – Auto Direktimport Dänemark – Disneyland Paris – Legoland Deutschland


PL/SQL Tutorial

The site  published a great SQL Tutorial, but unfortunately no PL/SQL Tutorial on exists.

Ok, I understand that Oracle’s PL/SQL is not directly a www language. It’s located at the backend database, mostly Oracle.

But maybe a PL/SQL Tutorial will be adding in the future? Could be in the category of “MORE…”


Also I thought myself creating such a learning video, but PL/SQL’s functionality scope is very wide – will be a huge work to do

Related Posts:


TO_CHAR Oracle Function

With the function TO_CHAR, Oracle converts a number or date to a string.

The to_char Oracle syntax is:

to_char( value, [ format_mask ], [ nls_language ] )

  • value can either be a number or date that will be converted to a string.
  • format_mask is optional. This is the format that will be used to convert value to a string.
  • nls_language is optional. This is the nls language used to convert value to a string.

So, to convert a date variable to a text (string), you would use the to_char Oracle function.

TO_CHAR Oracle within SQL

select to_char(sysdate, 'yyyy/mm/dd') as to_char_oracle from dual; /* to_char Oracle SQL returns: '2011/07/17' */

select to_char(sysdate, 'Month DD, YYYY') as to_char_oracle from dual; /* to_char Oracle SQL returns: 'October 17, 2011' */

select to_char(sysdate, 'FMMonth DD, YYYY') as to_char_oracle from dual; /* to_char Oracle SQL returns: 'October 17, 2011' */

select to_char(sysdate, 'FMMON DDth, YYYY') as to_char_oracle from dual; /* to_char Oracle SQL returns: 'OCT 17TH, 2011' */

select to_char(sysdate, 'FMMon ddth, YYYY') as to_char_oracle from dual; /* to_char Oracle SQL returns: 'Oct 17th, 2011' */

TO_CHAR Oracle within PL/SQL

(Please turn serveroutput on)

SET serveroutput ON

Here, within PL/SQL loop with the function to_char oracle returns the number i converted to a text string:



DBMS_OUTPUT.put_line(TO_CHAR(i) ||' - converted by to_char Oracle function');




3 - converted by to_char Oracle function

2 - converted by to_char Oracle function

1 - converted by to_char Oracle function


Related Posts:


Oracle Service Bus Tutorial: basing dbAdapter on Nested Object Type (Master-Detail)

Part of my Oracle Service Bus Tutorial: Building a dbAdapter on a Master-Detail table or view. JDeveloper offers two ways of implementation:

  • Build a dbAdapter based on Views with JOIN within the dbAdapter.
  • Build a dbAdapter based on a direct Query
  • Build a dbAdapter based on a stored procedure / function or oracle package – the function returns a nested object type (master-detail).

Joining views or tables within the dbAdapter is not very elegant and therefore not part of my Oracle Service Bus tutorial. I’d like to describe the way of using object types for master-detail relationships.


Oracle Service Bus Tutorial: “departments--<employees” master-detail

Most of you know Oracle’s sample tables, I’m using departments and employees. To a department, many employees may exists. An employees belongs to one department. Easy. A simple master-detail relationship.


So, in Oracle Service Bus we like to use this master-detail data. But as mentioned above, we’d like to do it very elegant with clean and simple pl/sql code. No Joins within the dbAdapter. I’d like to have the complete database logic in the database itself. Not on the middleware.

So I use a nested object type. This oracle service bus tutorial now shows the specific implementation.

Oracle Service Bus Tutorial: PL/SQL code and Object Type definitions

Creation of the object types and the pl/sql program code.

----------------------------------------------------------------------- -- Oracle Service Bus Tutorial -- Drops ----------------------------------------------------------------------- DROP TYPE OSBTutorial_department_tab_t force / DROP TYPE OSBTutorial_department_obj_t force / DROP TYPE OSBTutorial_employee_tab_t force / DROP TYPE OSBTutorial_employee_obj_t force / ----------------------------------------------------------------------- -- Oracle Service Bus Tutorial -- Business Objects ----------------------------------------------------------------------- create or replace type OSBTutorial_employee_obj_t as object ( positionnr number(12) ,betrag number(12,2) ,wahrungcode varchar2(3) ,wahrung varchar2(60) ,positiontext varchar2(2000) ) / create or replace type OSBTutorial_employee_tab_t as table of OSBTutorial_employee_obj_t / create or replace type OSBTutorial_department_obj_t as object ( department_id number(12) ,departmentnr number(12) ,departmentdatum date ,departmenttext varchar2(2000) ,departmentsumme number(12,2) ,falligam date ,bezahltam date ,mahnstufe number(2) ,positionen OSBTutorial_employee_tab_t ) / create or replace type OSBTutorial_department_tab_t as table of OSBTutorial_department_obj_t / ----------------------------------------------------------------------- -- Oracle Service Bus Tutorial -- Package, will be called by dbAdapter ----------------------------------------------------------------------- CREATE OR REPLACE package OSBTutorial_department_pa is function getdepartmentenObjecte( pdepartment_id in number, pStatus in varchar2 default 'OFFEN') return OSBTutorial_department_tab_t; end OSBTutorial_department_pa; / CREATE OR REPLACE package body OSBTutorial_department_pa is ------------------------------------------------------------------- -- User Defined Exceptions ------------------------------------------------------------------- e_not_exists_dept exception; pragma exception_init (e_not_exists_dept, -20999); ------------------------------------------------------------------- -- Funciton getdepartmentenObjecte ------------------------------------------------------------------- function getdepartmentenObjecte( pdepartment_id in number, pStatus in varchar2 default 'OFFEN') return OSBTutorial_department_tab_t is cursor curdepartment(indepartment_id in number) is select department_id ,departmentnr ,departmentdatum ,departmenttext ,falligam ,bezahltam ,mahnstufe ,positionnr ,betrag ,wahrungcode ,wahrung ,positiontext ,sum(betrag) over (partition by department_id, departmentnr order by department_id, departmentnr) as departmentsumme ,lead(departmentnr) over (order by department_id, departmentnr) as next_departmentnr from OSBTutorial_departmenten_vie where 1=1 and department_id = indepartment_id order by department_id, departmentnr, positionnr ; rdepartment curdepartment%rowtype; vdepartmentTabListe OSBTutorial_department_tab_t; vdepartmentObjRecord OSBTutorial_department_obj_t; vemployeeTabListe OSBTutorial_employee_tab_t; vemployeeObjRecord OSBTutorial_employee_obj_t; vdepartmentExists boolean; cProg constant varchar2(130) := 'OSBTutorial_department_pa.getdepartmentenObjecte'; vStep varchar2(160) := 'init'; begin -- check if departmenten exists open curdepartment(pdepartment_id); fetch curdepartment into rdepartment; vdepartmentExists := curdepartment%found; close curdepartment; -- Object Types construct if vdepartmentExists then -- New departmentslist create vdepartmentTabListe := OSBTutorial_department_tab_t(); for rdepartment in curdepartment(pdepartment_id) loop -- jede Position der aktuellen department wird in die Liste erstellt. -- solange bis wieder die Position 1 erscheint, dann gibts eine -- neue Liste if (rdepartment.positionnr = 1) then -- new employees list create vemployeeTabListe := OSBTutorial_employee_tab_t(); end if; -- instance, add to list vemployeeObjRecord := OSBTutorial_employee_obj_t( rdepartment.positionnr , rdepartment.betrag , rdepartment.wahrungcode , rdepartment.wahrung , rdepartment.positiontext ); vemployeeTabListe.extend; vemployeeTabListe(vemployeeTabListe.last) := vemployeeObjRecord; -- on last employee, the department object will be created if ( rdepartment.next_departmentnr != rdepartment.departmentnr or rdepartment.next_departmentnr is null) then -- Neue department instanzieren und zur Liste add vdepartmentObjRecord := OSBTutorial_department_obj_t( rdepartment.department_id ,rdepartment.departmentnr ,rdepartment.departmentdatum ,rdepartment.departmenttext ,rdepartment.departmentsumme ,rdepartment.falligam ,rdepartment.bezahltam ,rdepartment.mahnstufe ,vemployeeTabListe -- all belonging employees to the department ); vdepartmentTabListe.extend; vdepartmentTabListe(vdepartmentTabListe.last) := vdepartmentObjRecord; end if; end loop; else --raise e_not_exists_dept; null; end if; -- exists (cursordaten gefunden) return vdepartmentTabListe; end getdepartmentenObjecte; -- end OSBTutorial_department_pa; /

can be easily tested with TOAD:

----------------------------------------------------------------------- -- Oracle Service Bus Tutorial -- Sample within SQL itself (using TOAD, SQL*PLUS, ...) ----------------------------------------------------------------------- select OSBTutorial_department_pa.getdepartmentenObjecte(37797) as result from dual ;

Related Posts: