PL\SQL function return table

1 Comment

I tried today to return a table from a PL\SQL function and than use the result in Java, using JDBC.

First, create a temporary table:

CREATE GLOBAL TEMPORARY TABLE CLIENTS
(
    FILE_LINE_NUMBER INTEGER, 
    CIF INTEGER
) ON COMMIT DELETE ROWS ;

Than we have to define a type, using the created table columns and a table, for the defined type:

CREATE TYPE CLIENTS_TYPE AS OBJECT (FILE_LINE_NUMBER INTEGER, CIF INTEGER);
CREATE TYPE CLIENTS_TABLE AS TABLE OF CLIENTS_TYPE;

And of course, define the function:

CREATE OR REPLACE FUNCTION GET_CIFS RETURN CLIENTS_TABLE PIPELINED AS 
TYPE T_REF_CURSOR IS REF CURSOR;    
LC_CIFS        T_REF_CURSOR;
LR_OUT_REC             CLIENTS_TYPE := CLIENTS_TYPE(NULL, NULL);
    
BEGIN
    OPEN LC_CIFS FOR 
        SELECT 
            FILE_LINE_NUMBER,
            CIF 
        FROM CLIENTS;
        
        LOOP
            FETCH LC_CIFS 
            INTO 
                LR_OUT_REC.FILE_LINE_NUMBER,    
                LR_OUT_REC.CIF;
            EXIT WHEN LC_CIFS%NOTFOUND;
            PIPE ROW(LR_OUT_REC);
        END LOOP;
    CLOSE LC_CIFS;
    RETURN;
END;

For testing the function from PL\SQL we may use:

INSERT INTO CLIENT_FOR_PROMO VALUES(10, 10);
INSERT INTO CLIENT_FOR_PROMO VALUES(100, 100);
INSERT INTO CLIENT_FOR_PROMO VALUES(43, 32);
SELECT * FROM TABLE(GET_CIFS());

Now, in Java we just have to create a JDBC statement containing the SELECT * FROM TABLE(GET_CIFS()); code, and iterate through the ResultSet.

The web framework of your dreams

Leave a comment

For some time I’ve been wondering which are the characteristics that make a web framework great. Why to use one web framework and not another… Why to use a technology or another… Now I reached a conclusion. There are a few essential things a web framework must have, and also must respect some basic principles.

First of all, a web framework must be easy to use!

A web framework must be programmer centric, everything must be done with minimum effort.┬áThe framework’s designer must help the framework’s user to write functionalities with as few as possible lines of code.

The convention over configuration is necessary to be used here. Of course, this makes the framework harder to learn, but after the learning is done, the development effort is minimized. The learning curve should be adjusted using examples. Maybe a medium to complex application could be made available for study.

The framework must be based on the defaults principle. When declaring a component, it must have the most used proprieties. If the user wants to customize it, he could add some configuration. But in most cases the defaults should be enough.

The web framework must guide the developer to use the best practices and design patterns.

At this section we can put the documentation. The framework must be well documented… The easiness of development is for nothing if the people don’t know hot to do it!

Second, it must be fast!

Everyone of us waited for web sites to load, everyone knows how frustrating this waiting can be. If facebook would be slow, it wouldn’t have so many users.

The speed of a web application depends of many factors like the internet connection, the server the application is deployed, the technologies used for the application.

As framework designers we can influence a few of them:

  • we can write the framework with as few lines of code as possible and as optimized as possible. With fewer lines the framework becomes faster
  • we can choose and recommend technologies to the developers
  • we can recommend a web server

Third, it must be testable!

Anyone knows the benefits of automated testing… but few use them…

A web framework must be complete

It must offer a complete solution for developing web applications. There already are many frameworks specialized on different layers of a web application, but the developer will waste precious time to integrate these different technologies. Frameworks like RubyOnRails are so popular, because are complete solutions for the web application development problem.

It must be flexible

The developer must have the control of everything happening in the application. Typical technologies that takes the control from the developer are SQL code generators, or ORMs that generate SQL code. When complex things are needed, it is easier, faster and more flexible for the developer to write his own SQL code.

Finally I want to say that at some point, the principles stated below are contradictory and it’s up to the framework designer to find the balance between them. Like in life, the hardest thing thing to keep is the equilibrium.

Freemarker collection size comparation

3 Comments

Today I got frustrating errors related to finding how many elements exists in a Freemarker collection. And of course seeing if the collection is empty or not. The solution is simple:

<#if (project.tasks?size > 0) >
</#if>

So you have to use parenthesis and the “>” will be escaped!