Feeds:
Posts
Comments

 

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

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

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 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

Dear All,

 

We have introduced, new advertisement option on Fresa Web application and the same is published on fresa technologies website.

Dear Readers,

The below information is very useful while you are adding master detail in apex page.

How to focus the cursor to the first input field of a newly added row using the ADD button in a standard APEX Tabular Form. Actually, I would expect APEX to do this automatically. But it doesn’t. To implement this behavior is actually really simple.

For this example I create a simple tabular form based on the demo_customers table:

focus1

This is what the form looks like when you click the “Add Row” button. As you can see, none of the input field has focus at this time. To focus the cursor on the first field of the new row (Cust First Name), I somehow have to change the behavior of the “Add Row” button. Let’s have a look at it’s definition:

focus2

The click-action of the button is actually a JavaScript call: addRow();. Naturally I want to keep this action, but after this, I want to start an additional action that should set the focus to the input field. To be able to have multiple actions performed when the button is clicked, I will change the Action definition to “Defined by Dynamic Action”:

focus3

Now I’m going to add a Dynamic Action to the “Add Row” button. First action will be the original JavaScript call to add the row, second action will be setting the focus. This is how the definition will look like after I added the actions:

focus4

Creating the Dynamic Action and the first True Action I will do with the Create Dynamic Action Wizard for the button:

focus5

focus6

focus7

focus8

Now I have implemented the original behavior.

Next I want to focus the cursor on the first text-field of the added row, once I click the button. I know there is a build-in Dynamic Action to set the focus on an element. I just need to know which element. Let’s have a look at the HTML code of the generated APEX page, especially the the text-field element we wish to target (I used the Chrome Developer Tool here):

focus9

As you can see, the name of this element is f02 and the id f02_0008. Because the ID can change depending on the number of rows displayed, I choose to work with the name attribute, which stays the same. The problem is, all “Cust First Name” fields do have the same name. Luckily jQuery offers a way to select the last element in an array of element with the same name and luckily, APEX Dynamic Actions support jQuery selector syntax. So here is, how I define the focus action for my “Add Row” button:

focus10

focus11

As you can see, I select all elements having the name attribute equal to f02 and :last allows me to select the last element of these. Here is a screenshot from the result (after clicking the “Add Row” button) with focus on the first text element of the last, newly added row:

focus12

 

http://rokitta.blogspot.in/2012/08/apex-tabular-form-focus-cursor-on-first.html

 

Thanks to Christian Rokitta

Dear All,

The below url is very useful if you want to migrate/move the schema/object to another database.

http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52sqldev-1735911.html

 

Thanks to Oracle Magazine