Skip to main content

How to write and execute an Oracle function


Here is an example of a function on Oracle. Then given following is a procedure that called the function. I have used them to implement the paging function. Here is the Function.

create or replace FUNCTION  "RETURN_EMPLOYEE" (argEmailAddress IN EMPLOYEE.EmailAddress%TYPE) 
 return EMPLOYEE_NESTED_TABLE
 as
 V_RET  EMPLOYEE_NESTED_TABLE;

 CURSOR C1 IS
 SELECT  EMPLOYEE.EMPLOYEEId,
         EMPLOYEE.Name,
         EMPLOYEE.Stake,
         EMPLOYEE.CreatedDate,
         EMPLOYEE.StartDate,
         EMPLOYEE.EndDate,
         EMPLOYEE.EMPLOYEEDurationId,
         EMPLOYEEMember.EMPLOYEEMemberStatusId,
         EMPLOYEEMember.UPDATENOTIFIED
 FROM  EMPLOYEE,EMPLOYEEMember
 WHERE  EMPLOYEEMember.EmailAddress = argEmailAddress AND
        EMPLOYEE.EMPLOYEEId = EMPLOYEEMember.EMPLOYEEId AND
  EMPLOYEEMember.EMPLOYEEMemberStatusId IN(2,5,6)
 ORDER BY EMPLOYEEId;

 i NUMBER := 0;
 begin
  V_RET  := EMPLOYEE_NESTED_TABLE ();
      FOR C1_REC IN C1
      LOOP
          i := i + 1;
          V_RET.EXTEND;
          V_RET(V_RET.COUNT) := EMPLOYEE_COLUMNS( C1_REC.EMPLOYEEId,
                                                  C1_REC.Name,
                                                  C1_REC.Stake,
                                                  C1_REC.CreatedDate,
                                                  C1_REC.StartDate,
                                                  C1_REC.EndDate,
                                                  C1_REC.EMPLOYEEDurationId,
                                                  C1_REC.EMPLOYEEMemberStatusId,
                                                  C1_REC.UPDATENOTIFIED);
      END LOOP;
  RETURN V_RET;
 end RETURN_EMPLOYEE;

Here is the procedure that calls the function.

PROCEDURE GetJoinedEmplyeeListByEmail( argEmailAddress IN EMPLOYEE.EmailAddress%TYPE,
                                     argPageNo IN  NUMBER,
                                     argEmployeesPerPage IN  NUMBER, 
                                     argNoOfPages OUT  NUMBER,
                                     argNoOfEmployees OUT  NUMBER,
                                     argEmployee OUT refCursorType)
IS
BEGIN
 SELECT COUNT(Employee. EmployeeId) INTO argNoOfEmployees
  FROM Employee,EmployeeMember
  WHERE  EmployeeMember.EmailAddress = argEmailAddress AND 
  Employee.EmployeeId = EmployeeMember.EmployeeId AND 
  EmployeeMember.EmployeeMemberStatusId IN(9,90,7);
 
 OPEN argEmployee FOR 
  SELECT * FROM (SELECT  EmployeeId,
                               Name,
                               Stake,
                               CreatedDate,
                               StartDate,
                               EndDate,
                               EmployeerId,
                               EmployeeDurationId,
                               EmployeeMemberStatusId,
                               rownum rn
                        FROM   (SELECT * FROM TABLE(RETURN_EMPLOYEE(argEmailAddress)))
                        WHERE  rownum <= argNoOfEmployees-(argPageNo*argEmployeesPerPage-argEmployeesPerPage))
                WHERE rn > argNoOfEmployees-(argPageNo*argEmployeesPerPage); 

  SELECT  ROUND(((COUNT(Employee.EmployeeId))/argEmployeesPerPage)+0.49) INTO argNoOfPages  
  FROM  Employee,EmployeeMember
  WHERE  EmployeeMember.EmailAddress = argEmailAddress AND 
  Employee.EmployeeId = EmployeeMember.EmployeeId AND 
  EmployeeMember.EmployeeMemberStatusId IN (9,90,7);
END GetJoinedEmployeeListByEmail;

Here the function has used a type that is created. Here are the statements for creating types.

CREATE OR REPLACE TYPE  "EMPLOYEE_COLUMNS" as object (
     EMPLOYEEID NUMBER(10,0),
     NAME VARCHAR2(500),
     STAKE VARCHAR2(1000),
     CREATEDDATE DATE,
     STARTDATE DATE,
     ENDDATE DATE,
     EMPLOYEERID NUMBER(10,0),
     EMPLOYEEDURATIONID  NUMBER(10,0),
     EMPLOYEEMEMBERSTATUSID NUMBER(10,0),
     rn NUMBER(10) );

CREATE OR REPLACE TYPE "EMPLOYEE_NESTED_TABLE" as table of EMPLOYEE_COLUMNS;

This worked perfect for my paging and if there is a better implementation for paging by oracle with an "oreder by" please do share it with me. The problem here was to do such work is because order by is executed after all the other statements.

Comments

Popular posts from this blog

Google API v3 with PHP using Blogger service

It was really hard for me to understand how the Google APIs are working at the first point and took few days for me to figure out. But after a successful working prototype it seems very easy. And also when I am searching for a simple example I was unable to find a good one that I can understand.

So let me list down step by step what I have done with URLs and as simple as I can.

Create a Google applocation - https://code.google.com/apis/consoleSwitch on the "Blogger API v3"Get the latest APIs client library for PHPlocation - https://code.google.com/p/google-api-php-client/downloads/listUpload the files to your host location on on localhostExtract the files to folder  named "GoogleClientApi"Create your php file outside of the folder Copy paste following code into the file and do the changes as needed  By changing the scope and the service object you can access all the services that is given by Google APIs through the PHP API library set given.
If there is anything …

How to get indexed by Google & Yahoo

I have seen a lot of ads and services online that provide the facility to get higher page ranks on search engines. But when I asked about those services on Google their respond was not much positive because they said it can decrease you page rank (because those services cannot be trusted always). It is happening because the page rank is depending on page visitors (as Google say). So the better way is to let the Google to index your site by sending them the address of your website. http://www.google.com/webmasters/ : here you can improve the traffic with the webmaster tools and you can send your website contents also. It is a legal, simple and direct hit.
You can send your web site address (even the blog) to Yahoo: https://siteexplorer.search.yahoo.com also. It helps them to index your pages on their search engines.


It is better to come up with the content and services which are really useful for the visitors and the traffic will be increased regularly. I have gone through a lot of expe…

How to Install ColdFusion 8 on Linux?

It was real pain for me to install ColdFusion on my Linux box with Apache. Anyway we have installed it and I thought to share it because this may help you also to install ColdFusion on a Linux box.
First of all open a shell and then continue on this.

Grant Execute permission to the Coldfusion‐8‐lin.bin binary file
chmod +x Coldfusion‐8‐lin.bin

Execute Coldfusion‐8‐lin.bin
./Coldfusion‐8‐lin.bin

Enter the Locale
1 (English)

Hit Enter 28 times to continue the agreement

Enter "Y" to accept the agreement

Enter the version to install
3 (Developer Edition)

Enter the next task to perform
1 (Server Configuration)

Enter whether you have an existing server configuration
2 (No existing server configuration)

Enter the next task to perform
5 (continue installation)

Enter the installation path
Hit enter to accept the default path

Hit Enter 30 times to continue the agreement

Enter "Y" to accept the agreement

Enter the serial number
Hit Enter for no serial number

Enter whether you h…