In the Oracle database you can use pipelined functions to improve performance or decrease complexity. With the table function a pipelined functions can be called:


select * from table(pf_demo(10));

This way it is not possible to wrap the parameterised pipelined function in a view. You can only create a view for a specific parameter value:


create view pf_demo_view  as select * from table(pf_demo(10));

This is not very useful. Luckily there is a way to pass parameters to a pipelined function within a view. This solution uses package variables to store the parameter values and setter functions to set the values.
In our example we create the pipelined function in a package pf_demo_pkg. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. This way you can query the
view from PL/SQL.

create view pf_demo_view  as select * from table(pf_demo());

begin
  pf_demo_pkg.set_dept(10);
  for r in ( select * from pf_demo_view ) loop
    ...
  end loop;
end;

Now there is also a solution to use such a view from SQL. This solution utilizes the fact that the where clause of a query is interpreted before the from clause. So when we call the setter(s) in the where-clause the view will pass these parameters to the piplined function. You can see this in action in the code below:

SQL 
SQL create  type emp_rowtype
  2   as object
  3   ( empno number
  4   , ename varchar2(10)
  5   , hiredate date
  6   , deptno number
  7  );
  8  /

Type created.

SQL 
SQL create  type emp_table_type as table of emp_rowtype;
  2  /

Type created.

SQL 
SQL 
SQL create or replace package pf_demo is
  2  
  3    function set_deptno ( p_deptno in number) return number;
  4  
  5    function emp_pf (p_deptno in number default null)return emp_table_type  pipelined;
  6  
  7  end;
  8  /

Package created.

SQL 
SQL  create or replace
  2  package body pf_demo is
  3  
  4    g_deptno    number := null;
  5  
  6    function set_deptno ( p_deptno in number) return number is
  7    begin
  8    g_deptno := p_deptno;
  9         return(p_deptno);
 10    end;
 11  
 12    function emp_pf (p_deptno in number default null)return emp_table_type  pipelined is
 13    cursor c_emp (cp_deptno number) is
 14    select empno
 15         , ename
 16         , hiredate
 17         , deptno
 18    from emp
 19    where deptno = cp_deptno
 20   ;
 21   r_emp     c_emp%rowtype;
 22   r_rec     emp_rowtype;
 23   l_empno     number := null;
 24   l_ename     varchar2(10) := null;
 25   l_hiredate  date := null;
 26   l_deptno    number := null;
 27  begin
 28   l_deptno := nvl(p_deptno,g_deptno);
 29   open c_emp(l_deptno);
 30   loop
 31     fetch c_emp into  l_empno
 32          ,  l_ename
 33          ,  l_hiredate
 34          ,  l_deptno
 35     ;
 36     exit when c_emp%notfound;
 37     r_rec := new emp_rowtype
 38           ( l_empno
 39           , l_ename
 40           , l_hiredate
 41           , l_deptno
 42           );
 43     pipe row (r_rec);
 44   end loop;
 45   close c_emp;
 46   return;
 47   end;
 48  
 49  end pf_demo;
 50  /

Package body created.

SQL 
SQL 
SQL select * from table(pf_demo.emp_pf(10));

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7782 CLARK      09-JUN-81         10                                      
      7839 KING       17-NOV-81         10                                      
      7934 MILLER     23-JAN-82         10                                      

SQL 
SQL select * from table(pf_demo.emp_pf()) where pf_demo.set_deptno(20) is not null;

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7369 SMITH      17-DEC-80         20                                      
      7566 JONES      02-APR-81         20                                      
      7788 SCOTT      09-DEC-82         20                                      
      7876 ADAMS      12-JAN-83         20                                      
      7902 FORD       03-DEC-81         20                                      

SQL 
SQL create view pf_demo_view as select * from table(pf_demo.emp_pf());

View created.

SQL 
SQL select * from pf_demo_view where pf_demo.set_deptno(30) is not null;

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7499 ALLEN      20-FEB-81         30                                      
      7521 WARD2      22-FEB-81         30                                      
      7654 MARTIN     28-SEP-81         30                                      
      7698 BLAKE      01-MAY-81         30                                      
      7844 TURNER     08-SEP-81         30                                      
      7900 JAMES      03-DEC-81         30                                      

6 rows selected.

SQL 
SQL spool off

Hope you can use this, happy coding

Dick

Source Article from http://dickdral.blogspot.com/2015/01/using-pipelined-function-with.html

This is a nasty error that can occur when a package is recompiled during an Oracle session. I work now in an environment where this occasionally happens and sometimes minutes of data entry are lost.

 
 LF      VARCHAR2(10) := CHR(10);

is changed to

 
  FUNCTION LF RETURNS VARCHAR2 IS
  BEGIN
    RETURN(CHR(10));
  END;

To make things even easier I have coded a conversion utility where you can input your constant declarations a retrieve the corresponding functions. You find it here.

Enjoy!

Source Article from http://dickdral.blogspot.com/2014/07/avoiding-ora-04068.html

Inspired by Dan McGhan, today I launched a new meetup: ORCLAPEX-NL

Everybody speaking enthousiastic about Apex and speaking Dutch is welcome.

Eventhough the home-location is Tilburg, I think most of our meetups will end up to be more in the center of the country.

ORCLAPEX-NL

Tilburg, NL
8 Apex fanatics

Deze groep is voor iedereen in Nederland (en België) die een passie heeft voor Oracle Application Express (APEX) en geïnteresseerd in bijeenkomsten met gelijkgestemden. Zo kun…

Next Meetup

Pizza night

Thursday, Sep 18, 2014, 7:30 PM
5 Attending

Check out this Meetup Group →

The other day I was asked to create a page where a large number of properties should be connected to an object. I created a report consisting of the rows with the property name and a check box. This resulted in a very long and narrow report. All of the data could not be seen without scrolling. So I decided the report should be split into a number of columns. And I wanted to avoid special constructs in Apex and SQL (I have done that before and it was very compex). So I decided to use JavaScript to move the rows. By using JavaScript the Apex transaction mechanism keeps functioning, Apex does not know or notice that anything in the layout of the page has been changed.

I want to achieve the right side situation starting with the left report ( that by the way continues a long way below where the picture ends)

With JavaScript a number of extra table elements are created on the same level as the table element containing the report. The thead element of the original table element will be copied to the other table nodes to provide the same heading and a tbody element will be created to house the rows. After that the rows can be distributed among the tables. With CSS the table elements are positioned next to each other. The only limit to the number of columns is the available horizontal space.
You should call the JavaScript function below at startup or in the refresh event when using partial page rendering.

function reportToColumns ( tabSelector, numCols )
{ var numRows = 0;
  // bereken aantal rijen per kolom
  var rowsPerCol = Math.ceil( ( $(tabSelector+' tr').length-1 )/numCols);
  var baseName = 'reportColumn'; 

  $(tabSelector).addClass('reportColumn');
  // maak kolommen aan
  for ( i = 2; i <= numCols; i++)
  { $(tabSelector).parent().append('

‘);
$(tabSelector+’ thead’).clone().appendTo( $(‘#’+baseName+i) );
$(‘#’+baseName+i).append(”);
}

// verdeel de rijen over de kolommen
var id = 2;
var dest = ”;
$(tabSelector+’ tr’).each( function(index)
{ if (index > rowsPerCol)
{ dest = ‘#’+baseName+id+’ tbody’;
$(dest).append( $(this) );
numRows = numRows + 1;
// wissel de kolom als rijen per kolom is bereikt
if ( numRows >= rowsPerCol)
{ id = id + 1;
numRows = 0;
}
}
} );
}

I used classes for the layout of a specific report. You might need to change that according to your needs.
To make sure the div’s are placed next to each other a little bit of CSS is needed:

.reportColumn {
    display: block;
    float: left;
    margin-right: 30px;
    position: relative;
}

You can see this in action right here

This method can be used on either Apex Reports or Apex Tabular Forms. In fact it is not a specific Apex solution and can be used on any HTML table.

Happy Apexing

Source Article from http://dickdral.blogspot.com/2014/02/splitting-apex-report-into-several.html

This post show how to style a link into an iconic button just using CSS. This way the HTML-code remains clean and readable.






Very clean, isn’t it?
The CSS to style it is a bit more complex.

.icon {
  display: block;
  border-radius: 3px;
  color: #FFFFFF;
  font-family: FontAweSome;
  font-size: 20px;
  height: 30px;
  width: 30px;
  line-height: 30px;
  text-align: center;
  margin: auto;
  margin-top:5px;
}
.facebook {
  background-color: #3B5998;
}
.facebook:before {
  content : "\f09a";
}

As you see the .icon class does most of the work for us. It takes care of the A-element to be considered as a block, sizes the button etcetera. The .facebook class only takes care of the background color and the character to be used, You can look up this character in the Font Awesome Cheatsheet.

The implement this in Apex a list is created in which the URL’s to be used are entered. The display text should be the name of the class of the button. The template for the list elements is very simple and clean:


As you see the display text is used to indicate the class of the button.

Happy Apexing

Source Article from http://dickdral.blogspot.com/2014/01/iconic-buttons-with-just-css.html

By saving public Apex Interactive Reports you can offer various custom reports to users without programming. The user opens the page and can choose the reports from the Reports drop down list.
New reports can be added by defining the new report and then exporting the application and importing it in the production environment. Changing existing reports can in some cases cause double entries in the Reports drop down list. This is because the changed report has received a new ID and therefor the original report is not overwritten.
The following procedure can be run for each relevant page before importing the application. It deletes the existing public reports for a specific application and page.

create or replace 
  procedure delete_public_ir_report
                ( p_app_id      in  number
                , p_page_id     in  number
                ) is
begin
  dbms_output.put_line('Deleting public IR reports for application '||p_app_id||' page '||p_page_id);
  for r in ( select * 
             from   apex_application_page_ir_rpt
             where  application_id = p_app_id
               and  page_id        = p_page_id
               and  report_type    = 'PUBLIC'
           ) 
  loop
    dbms_output.put_line('Deleting report '||nvl(r.report_name,'with id '||r.report_id));
    apex_util.ir_delete_report(r.report_id);
  end loop;
end;
/

This procedure works in my situation but each situation is different. Be careful to apply this procedure to your environment and test the results thoroughly before applying it to a production environment.

Happy apexing

Source Article from http://dickdral.blogspot.com/2013/10/removing-old-public-interactive-reports.html

Lately I have been developing a kind of SaaS website with Apex. Several companies were to be represented in this website and each company wanted to have its own logo and colors. Should be a piece of cake with CSS, shouldn’t it?

But the requirements said rounded corners for tabs and buttons, and support for IE7. That rules out HTML5 :-(.
Using plugins for rounded corners I was not able to get a stable result, so I turned to the good old sliding windows solution using background images. This way the website was stable and reliable. Below two examples of look-and-feel using the same HTML.

The only drawback of this was that a set of 12 images was needed for each company.
Creating these images manually using drawing software is a time consuming, tedious and error-prone process.
So I created a webpage containing SVG-images. The webmaster could enter the colors and generate the images. Then these images could be grabbed and cut on the surrounding dotted lines in GIMP. Sounds a lot like kindergarten, doesn’t it?
This was already better, but still a tedious and error-prone.

Then my 15 year old son –who is a computer programming addict like myself ;-)- came up with a solution. The open source program ImageMagick is able to convert images including SVG to PNG. The only challenge was that it runs on the command line and Apex is webbased!

The solution is to generate a Windows BAT-file that can be run on a client where ImageMagick is installed. In the BAT-file the SVG images are generated with ECHO-statements. After this ImageMagick is called to do the conversion to PNG. With this solution the neccesary images can be generated in a few minutes, just the time needed to enter the colors and to execute the BAT-file. Below is an example of the generation of the image for a button background.

echo <svg height="22px" version="1.1" width="400px" xmlns="http:www.w3.org2000svg">                  >button.svg
echo </svg>      >>button.svg

convert button.svg button.png
convert button.png -transparent white button.png

The echo statements generate the 2 lines SVG file. The first convert statement converts the SVG file to a PNG. The second convert command changes the white background to transparent in order to use the button on other than white backgrounds.

Happy Apexing! (but you can use this for any kind of website)

Source Article from http://dickdral.blogspot.com/2013/09/automatically-generating-images-for.html

 

When logging into the Oracle Application Feature Request application you will see the following announcement:

Announcing the creation of the Feature Advisory Board

A selection of APEX community experts have agreed to be members of the Feature Advisory Board. They will assist the APEX Development Team to prioritize feature requests. The board members will be reviewing features based on the total number of votes, so please enter your votes for all of the existing features.

apex_feature_request

 

themes4apex

Source Article from http://rokitta.blogspot.com/2013/05/apex-application-express-feature.html
Oracle Application Express Feature Advisory Board
http://rokitta.blogspot.com/2013/05/apex-application-express-feature.html
http://rokitta.blogspot.com/feeds/posts/default
Oracle & Apex Geekery
And another thing …

Follow-up on my article: “Who are you?” – client device Categorizr for APEX

About a year ago I wrote an article on how to determine by what kind of device your APEX application is accessed: desktop, tablet or smartphone. The solution is based on a package interpreting the HTTP_USER_AGENT environment string.

This package (categorizr) in combination with APEX offers a few functions regarding the client device type (isDesktop, isTablet, isMobile, …) that can be used in conditions or anywhere else in your PL/SQL code. Now I wrote a complimentary plug-in to determine some viewport properties: ViewportHeight, ViewportWidth and Orientation and put these values into page items in session state, updating the values (in session state) each time the browser resizes or changes orientation. Additionally, I added a an event to trigger an (advanced) dynamic action: Categorizer Resize Event.

In fact, both components, the package and the plug-in, can be installed and used separate. But when installed together, the categorizr package offers a two new functions:

  • FUNCTION isLandscape RETURN BOOLEAN;
  • FUNCTION isPortrait RETURN BOOLEAN;

and three global package variables:

  • g_viewportWidth
  • g_viewportHeight
  • g_viewportOrientation

Installation

Just install the plug-in by importing the file categorizr-plugin.sql.

Install the package by running the categorizr-package.sql in the database schema associated with your application.

Usage

This region plug-in is ideally used in page 0/global page but can be used in just one or more designated pages, as long as you do not combine both approaches.

Create a new region of type plug-in and choose “Categorizr”. Make sure you use “no template” for this region, or an empty region will be displayed on your page(s).

categorizr1

The plug-in has three attributes to specify the names of the (hidden) page items to create for holding the viewport width, height en orientation. Just keep the default, which prefixes the items with the number of the current page (using the placeholder #PAGE_ID#).

categorizr2

Once having this region on your page 0, you can access the page items (i.e. P0_VIEWPORTWIDTH, …) in your SQL and PL/SQL or use the JavaScript variables viewportWidth, viewportHeight, viewportOrientation.

You can download both, the package and the plug-in here:

Package-Download
Package
addons
Plug-in

 

themes4apex

Source Article from http://rokitta.blogspot.com/2013/05/how-are-you-categorizr-for-apex-part-2.html
How are you? – Categorizr for APEX part 2: the Plug-in
http://rokitta.blogspot.com/2013/05/how-are-you-categorizr-for-apex-part-2.html
http://rokitta.blogspot.com/feeds/posts/default
Oracle & Apex Geekery
And another thing …