My Blog was migrated to http://sungur.wordpress.com
If redirection is not working, click here



Blogum Wordpress tasindi : http://sungur.wordpress.com
Yönlendirme çalışmaz ise : Buradan Ulasabilirsiniz.

Oracle Apex 3.1 Sunumu

18 Ekimde, OracleTURK grubuna, Oracle Akademi sponsorluğunda, Kadıköy Bireysel Eğitim Merkezinde gerçekleştirmiş olduğum, Oracle Application Express v3.1 sunumu ve workshop esnasında gösterdiğim bazı özelliklere ait kodlar için tıklayın.

For english:
I present the Oracle Application Express v3.1 to OracleTurk Group, at Kadıköy Bireysel Eğitim Merkezi, sponsored by Oracle Turkiye Akademi. To access this presentation click here.

Asynchronous Ajax in Apex

We use Ajax for rapid query processing without submitting page. In apex, you can build ajax within your pages. For this purposes, we use "Application Process" under the Shared Components.

Sometimes, Application Process can not response rapidly. For example, if you query a count from very large table, it takes for minutes or hours. If you put a text field for table name and if user can query number of records of input big table, also if you perform this operation with ajax in apex as synchronously, the BROWSER will freeze and consumes %100 cpu time. Everything will go bad.

In this situation, you have to use "Asynchronous Ajax" opposite of Synchoronous, so you can more than one job on same time. To perform this, give an eye to this example:

Firstly, you can build your ajax javascript :


function getNumberOfRecords(){
var ajaxRequest = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=GetCount',0);
ajaxRequest.add('P6_TABLE_NAME',html_GetElement('P6_TABLE_NAME').value);
ajaxRequest.GetAsync(f_AsyncReturn);
ajaxRequest = null;
}


This code is used to connect your apex page with your application process. ( By the way, Application Processes are need to created as On Demand ). In normally, synchronous method, you call get() method of ajaxRequest object. Pay attention to this, in asynchronous method, you need to call GetAsync() method by passing an Object. What is the f_AsyncReturn ? :


function f_AsyncReturn(){
if(p.readyState == 1){
$x('P6_TEXTAREA').value = '';
}else if(p.readyState == 2){
}else if(p.readyState == 3){
}else if(p.readyState == 4){
$x('P6_TEXT_AREA').value = p.responseText;
// you can add other things add this area
}else{return false;}
}


If you coded ajax in other languages such as php,jsp; probably you coded as above. There are 4 states to deal with ajax. One of them (state 1) is "Loading", one of them (state 4) is "Success". Result of ajax is written in 4. states. Before ajax Request is completed, you can write "Loading" message in first state.

The other operations are the same with Synchronous method. You need to create an Application Process, it's running time must be "On Demand" as well.

So, by performing this Asynchronous method, your browser will not be freezed. And, not consuming all of CPU time. But disadvantage of this ( may be not seemed apparently ), your job is take much time rather than synchronous method. But, it is worth!

Running DDL Statements on Remote Database

You cannot run any DDL statement on a remote databases via using our custom remote sqls.

CREATE TABLE EMP@DB1 ...

This causes an error. Instead of this you can use the Dynamic Sql to produce ddl statement to execute it when it is called.

For example, There are two DB. A_DB and B_DB. We write this function in A_DB:

CREATE OR REPLACE PROCEDURE dyn_sql( v_Sql_in VARCHAR2 )
IS
   curName INTEGER;
   iRet INTEGER;
BEGIN
   curName := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(curName, v_sql_in, DBMS_SQL.NATIVE);
   iret := DBMS_SQL.EXECUTE(curName);
   DBMS_SQL.CLOSE_CURSOR(curName);
END;

When you call this procedure as "exec dyn_sql('CREATE TABLE EMP (EMPNO INTEGER)');" in B_DB database, an EMP table will be created in A_DB database.

About Returning Into Clause on Remote DB - ORA-22816

We use PL/SQL RETURNING INTO clause for returning value(s) after dml commands(INSERT,UPDATE,DELETE). Assume that you would insert row a employee table with employee_id value. Likely, you type this sql statement:

INSERT INTO TEMPLOYEES (EMP_ID,EMP_NAME) VALUES( SEQ_TEMPLOYEES.NEXTVAL,'FUAT SUNGUR');

After this statement you need this employee_id to add another table. You can use RETURNING INTO clause to get employee_id value after an insert statement as follows:

INSERT INTO TEMPLOYEES (EMP_ID,EMP_NAME) VALUES( SEQ_TEMPLOYEES.NEXTVAL,'FUAT SUNGUR') RETURNING EMP_ID INTO iEmpId;

After these, iEmpId variable has employee_id value.
There is no problem if you perform these on one database.

If you want to perform this between databases, likely you would get :

ORA-22816: unsupported feature with RETURNING clause


This error may be pothering and discouraging you to what do you want. We can use other tactic to perform our willings.

Assume that you have two databases A_DB, B_DB and in B_DB databases you want to add a row in Employee Table that table in A_DB. In this situation you take advantage of functions. Write a function that returns the sequence number of just added row.

FUNCTION INSERT_EMPLOYEE_AND_GET_SEQ(vEmpName_in IN VARCHAR2)
RETURN INTEGER
IS
    iEmpId INTEGER;
BEGIN

    INSERT INTO TEMPLOYEES (EMP_ID,EMP_NAME) VALUES (SEQ_TEMPLOYEES.NEXTVAL,vEmpName_in) RETURNING EMP_ID INTO iEmpId;
    RETURN iEmpId;
EXCEPTION
    WHEN OTHERS THEN NULL;
END;

Also you have add function parameter to whether the returning into is used or not. May be this sequence number is need only once and after you would use that sequence to insert employee. So, there are lots of need why you do it.

Google Syntax Highlighter for PL/SQL

Google Syntax Highlighter App. provides us to show code in our blogs or other html pages. This application shows code of C,Java,Jscript,C#,SQL,XML,Ruby,Phyton. But as you predicted, Oracle PL/SQL is missing. So, i decided to develop a code to highlight our Oracle/PLSQL code. Things you have to do them are as follows :

• Download the Google Syntaxlighter to your pc: http://code.google.com/p/syntaxhighlighter/
  
• Then upload the folder that is extracted from zip file, to your server.
• For blogger, go to your template page and edit.
• After first head tag and before the closed head tag you should write some javascript code to template page.
• The template page should look alike this page :
  
  <link type="text/css" rel="stylesheet" href="http://xxx.googlepages.com/SyntaxHighlighter.css"></link>
  
  <script language="javascript" src="http://xxx.googlepages.com/shCore.js"></script>
  
  <script language="javascript" src="http://xxx.googlepages.com/shPlsql.js"></script>
  
  ....
  
  ....
  
  ....
  
  // dont modify if you dont want to change template except to show coding
  
  ....
  
  <script language="javascript">
  
  window.onload = function () {
  
  dp.SyntaxHighlighter.ClipboardSwf = 'http://afsungur.googlepages.com/clipboard.swf';
  
  
  
  dp.SyntaxHighlighter.BloggerMode();
  
  
  
  dp.SyntaxHighlighter.HighlightAll('code');
  
  }
  
  </script>
  
  
• 
  
• Then writing a post, you should write your pl sql code as follows:
  <pre name="code" class="plsql">
  
  
  
  ... your pl sql code ...
  
  
  
  </pre>
• 
  

Download shBrushPlSql.js

(This is compatible with reserver words and keywords of Oracle Latest Version )

After all of these step, now you can test your plsql code. Like this :)

CREATE OR REPLACE PACKAGE BODY PKG_RULE_CONTROL IS



-- CREATED BY A. FUAT SUNGUR

-- afsungur@gmail.com

--

--

-- Kural ile ilgili islemleri gerceklestiren pakettir



------------------------------------------------------------------------------------------

-- DBMS_SCHEDULER tarafindan cagrilacak pl sql blogunun dinamik olarak uretilmesini saglar

-- RUN_JOB prosedurune gonderilecek parametreler dinamik olarak ayarlanir.

FUNCTION PRODUCE_JOB_SQL

(

     vExpr_1  VARCHAR2,

     vExpr_2  VARCHAR2,

     nCond_Id NUMBER,

     iRuleId  NUMBER

) RETURN VARCHAR2 IS



     vJobSql    VARCHAR2(2000);

     iSqlCode   INTEGER;

     vSqlErrMsg VARCHAR2(250);



BEGIN

     -- CHR(39) = ' ( tek tirnak )

     IF (vExpr_2 IS NOT NULL)

     THEN

          vJobSql := 'BEGIN PKG_JOB_CONTROL.RUN_JOB(' || CHR(39) ||

                     vExpr_1 || CHR(39) || ',' || CHR(39) || VExpr_2 ||

                     CHR(39) || ',' || nCond_Id || ',' || iRuleId ||

                     '); END;';

     ELSE

          vJobSql := 'BEGIN PKG_JOB_CONTROL.RUN_JOB(' || CHR(39) ||

                     vExpr_1 || CHR(39) || ',NULL,' || nCond_Id || ',' ||

                     iRuleId || '); END;';

     END IF;

     RETURN vJobSql;

EXCEPTION

     WHEN OTHERS THEN

          ROLLBACK;

          INSERT INTO TERRORS

          VALUES

               (SEQ_TERRORS.NEXTVAL,

                iSqlCode,

                vSqlErrMsg,

                CURRENT_TIMESTAMP,

                'FUNCTION PKG_RULE_CONTROL.PRODUCE_JOB_SQL()');

          COMMIT;

          RETURN NULL;

END;

END;

Creating your own authentication method in Apex

With this operation, you can do your own login page instead of default apex login page.

Firstly, you need to create a package that deal with user login operations. PKG_APP_SECURITY package name is appropriate for this need.

CREATE OR REPLACE PACKAGE BODY PKG_APP_SECURITY
AS

PROCEDURE login

(p_uname IN VARCHAR2,
p_password IN VARCHAR2,
p_session_id IN VARCHAR2,
p_flow_page IN VARCHAR2)
IS
BEGIN
-- THIS PROVIDES AUTHENTICATION
wwv_flow_custom_auth_std.login (
p_uname => p_uname,
p_password => p_password,
p_session_id => p_session_id,
p_flow_page => p_flow_page || ':' || 1);

EXCEPTION
WHEN OTHERS
THEN RAISE;
END login;

PROCEDURE add_user (p_username IN VARCHAR2, p_password IN VARCHAR2)
AS
BEGIN
INSERT INTO P_USERS (username, PASSWORD)
VALUES (UPPER (p_username),
get_hash (TRIM (p_username), p_password));

COMMIT;
EXCEPTION
WHEN OTHERS
THEN ROLLBACK; RAISE;
END add_user;

FUNCTION get_hash (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN DBMS_OBFUSCATION_TOOLKIT.md5 (
input_string => UPPER (p_username) || '/' || UPPER (p_password));
END get_hash;

PROCEDURE valid_user2 (p_username IN VARCHAR2, p_password IN VARCHAR2)
AS
v_dummy VARCHAR2 (1);

BEGIN

SELECT '1' INTO v_dummy
FROM P_USERS
WHERE UPPER (username) = UPPER (p_username)
AND PASSWORD = get_hash (p_username, p_password);

EXCEPTION
WHEN NO_DATA_FOUND
THEN raise_application_error (-20000, 'Invalid username / password.');
END valid_user2;

FUNCTION valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS
BEGIN
valid_user2 (UPPER (p_username), p_password);
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN RETURN FALSE;
END valid_user;

END PKG_APP_SECURITY;

The P_USERS table has two columns, types of both columns are varchar2 and first of them is username and second of them is password.

Secondly, you should use an authentication schema. To perform this, in Shared Components, click Authentication Schemas. Then Create Button.

1-> Click "Create Schema" Button.
2-> Write your own authentication schema name, such as "MY AUTH METHOD".
3-> Then "Create Scheme" button.
4-> After scheme is created, click your authentication schema icon in authentication schemas page.
5-> Then Login Processing tab, in "Authentication Function" textarea, type this:
"RETURN PKG_APP_SECURITY.valid_user"
6-> If this method return false in application the authentication is not successfullied.
7-> Don't forget, you should set your own authentication schema to as current used.

Last thing we need to do, edit your login page (genrally page 101). Then, in processes tab ( in page processing tab ), click login process. And, type this to process textarea in source tab:
"pkg_app_security.login (P_UNAME => :P101_USERNAME, P_PASSWORD => :P101_PASSWORD, P_SESSION_ID => v('APP_SESSION'), P_FLOW_PAGE => :APP_ID );".

After performed these 3 steps, you can use your own login page with your own user tables in Apex Applications.