A PL/SQL program to convert numbers into words

 

select
TO_CHAR(TO_DATE(substr(1234567.123,1,instr(1234567.123,’.’)-1),’J’),’JSP’)||’ and paise ‘||
replace(replace(replace(replace(TO_CHAR(TO_DATE(substr(1234567.123,instr(1234567.123,’.’)+1,length(1234567.123)),’J’),’JSP’),’MILLION’,”),’HUNDRED’,”),’THOUSAND’,”),’-‘,’ ‘) as number_char
from dual.

 

Thanks to bhupinderbs

http://www.orafaq.com/node/1448

Create View with Parameter

From the title of this post you guess that oracle give us capability to create view with parameter, but this is wrong, don’t think good of oracle to give you this capability as straight forward.

I have workaround to do this capability by the following techniques

1-virtual private database context
2-global package variable
3-Lookup Tables

#1 Virtual Private Database Context

I will use in where clause SYS_CONTEXT function as parameter to filter data of the query of the view.

First step is creating context that will handle session variables

 CREATE OR REPLACE CONTEXT MY_CTX USING CTX_PKG  
 ACCESSED GLOBALLY;  

Second I will create CTX_PKG package that context will use it to handle session parameters.

The package contains three procedures

a-SET_SESSION_ID to set unique id for every session I will use in my context.

b-CLEAR_SESSION to clear session from my context.

c-SET_CONTEXT to set variable value in my context.

Package Specification

 CREATE OR REPLACE PACKAGE CTX_PKG  
 IS  
   PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2);  
   PROCEDURE CLEAR_SESSION (IN_SESSION_ID VARCHAR2);  
   PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2);  
 END CTX_PKG;  

Package Body

 CREATE OR REPLACE PACKAGE BODY CTX_PKG  
 IS  
   GC$SESSION_ID  VARCHAR2 (100);  
   PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2)  
   IS  
   BEGIN  
    GC$SESSION_ID := IN_SESSION_ID; 
    DBMS_SESSION.SET_IDENTIFIER (IN_SESSION_ID); 
  END;  
   PROCEDURE CLEAR_SESSION (IN_SESSION_ID VARCHAR2)  
   IS  
   BEGIN  
    DBMS_SESSION.SET_IDENTIFIER (IN_SESSION_ID);  
    DBMS_SESSION.CLEAR_IDENTIFIER;  
   END;  
   PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2)  
   IS  
   BEGIN  
    DBMS_SESSION.SET_CONTEXT ('MY_CTX',  
                 IN_NAME,  
                 IN_VALUE,  
                 USER,  
                 GC$SESSION_ID);  
   END;  
 END CTX_PKG;  

Now let’s test context and my package

 BEGIN  
   CTX_PKG.SET_SESSION_ID (222);  
   CTX_PKG.SET_CONTEXT ('my_name', 'Mahmoud A. El-Sayed');  
   CTX_PKG.SET_CONTEXT ('my_age', '26 YO');  
 END;  

Now I set two context variable my_name, my_age

to query this variable I will use SYS_CONTEXT function

 SELECT SYS_CONTEXT ('MY_CTX', 'my_name'), SYS_CONTEXT ('MY_CTX', 'my_age')  
  FROM DUAL;  

The output will be

After insuring that my context and package working true let’s now create view on table EMP to get employee in department at SCOTT schema

 CREATE OR REPLACE VIEW EMP_IN_DEPRATMENT  
 AS  
   SELECT *  
    FROM EMP  
   WHERE DEPTNO = SYS_CONTEXT ('MY_CTX', 'deptno');  

to filter view by employees in department 20 only you should execute this PLSQL block first

 BEGIN  
   CTX_PKG.SET_SESSION_ID (222);  
   CTX_PKG.SET_CONTEXT ('deptno', '20');  
 END;  

Now lets create select statement against EMP_IN_DEPARTMENT view and see the result

 SELECT * FROM EMP_IN_DEPRATMENT;  

The output result is like below

#2 Global Package Variables

I will use in where clause global package variables as parameter to filter data of the query of the view.

I will create package that hold every global variables which I will use it as parameters in view.

Package Specification

CREATE OR REPLACE PACKAGE GLB_VARIABLES
IS
   GN$DEPTNO   EMP.DEPTNO%TYPE;

   PROCEDURE SET_DEPTNO (
      IN_DEPTNO EMP.DEPTNO%TYPE);

   FUNCTION GET_DEPTNO
      RETURN EMP.DEPTNO%TYPE;
END;

Package Body

 CREATE OR REPLACE PACKAGE BODY GLB_VARIABLES  
 IS  
   PROCEDURE SET_DEPTNO (  
    IN_DEPTNO EMP.DEPTNO%TYPE)  
   IS  
   BEGIN  
    GN$DEPTNO := IN_DEPTNO;  
   END;  
   FUNCTION GET_DEPTNO  
    RETURN EMP.DEPTNO%TYPE  
   IS  
   BEGIN  
    RETURN GN$DEPTNO;  
   END;  
 END;  

Let’s now create view filter its data by global variables in GLBL_VARIABLES package

 CREATE OR REPLACE VIEW EMP_IN_DEPRATMENT2  
 AS  
   SELECT *  
    FROM EMP  
   WHERE DEPTNO =GLB_VARIABLES.GET_DEPTNO;  

Now lets create select statement against EMP_IN_DEPARTMENT2 view and see the result

 EXEC GLB_VARIABLES.SET_DEPTNO(20);
 SELECT * FROM EMP_IN_DEPRATMENT2;  

The output result is like below

3-Lookup Tables

another solution is to create lockup table for storing view parameters on it and build view based on the data stored in lockup table.

This solution is straight forward solution so no need to make demo for this solution to make post shortly as possible

 

Thanks to Mahmoud A. El-Sayed

URL: http://mahmoudoracle.blogspot.in/2012/06/create-view-with-parameter.html#.UNKdlm8Xb54

How to create multiple tabular form in apex page

Basically, one page should have only one tabular form but using iframe we can show two or more on the same page.

Main page actually consists of two pages. The form at the bottom is defined on another page. By using an iframe, this 2nd page is displayed on the same page as the form at the top.

Start by creating a normal tabular form page. Nothing special there.

Create a second page as a new blank page.

Change the template to Printer friendly. This way nothing extra will be shown on the page.

Now you can add the tabular form region as you would normally do. Make sure that the submit and cancel buttons branch back to the page you are now creating. This is important. There should be no way to go to any other page from this page. Otherwise, you are going to be stuck on the wrong page in the iframe.

Go back to the Main page and create a HTML region on this page. Change the Region source to something like this:

 

Thanks to SQLIntegerator

Oracle Application Express 5.0–statement of direction

 

Oracle Application Express 5.0 will focus on enhancements to existing functionality and is planned to incorporate the following:

  • Modal Dialog – Provide the ability to declaratively define modal dialogs.
  • Drag and Drop Layout Editor – Reintroduce the drag and drop layout editor which is compatible with laying out items within a grid layout.
  • HTML5 Capabilities – Improve native capabilities for handling HTML5 constructs.
  • PDF Printing – Improve the printing capabilities utilizing the APEX Listener FOP Support
  • Web Services Support – In combination with the APEX Listener further extend the Web Services integration capabilities.
  • Tablet User Interface – Enhanced themes and templates to enable developers to declarative create applications and/or pages for tablet devices.
  • Packaged Applications – Improved framework and enhancements to the packaged applications.
  • New Multi-Row Edit Region Type – Define a new region type with a modern UI for updating multiple rows of data and allow multiple regions on one page.
  • Master / Detail / Detail – Provide a wizard interface to define declarative master/detail/detail regions.
  • Multiple Interactive Reports – Allow any number of Interactive Reports to be defined on a single page.
  • Application Builder Security – Allow different authentication schemes to be used to control developer access to the Application Builder.
  • Numerous functional improvements.

http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-sod-087560.html

View in PDF