Oracle Forms: Oracle Server features

Oracle Forms: Oracle Server features

• 1.
  Using Oracle ServerFeatures In Forms http://blog.ebiztechnics.com
• 2.
  Objectives • Use Oracleserver functionalities in forms • Deal with server-side PL/SQL • Recognize which PL/SQL8 features are supported in forms • Handle Oracle server errors • Perform DDL commands by using the FORMS_DDL built in subprograms http://blog.ebiztechnics.com
• 3.
  Oracle Server Features UsefulOracle server features: • Declarative constraints • Stored program units • Database triggers • DDL http://blog.ebiztechnics.com
• 4.
  Dealing with PL/SQLCode • Where to store PL/SQL code: – On the Oracle server – In the attached PL/SQL libraries – In the form itself • Where to call PL/SQL code: – From database triggers – From forms triggers • Application partitioning http://blog.ebiztechnics.com
• 5.
  PL/SQL8 Support inForms • Client-side program units cannot support Oracle8i object-related functionality. • Stored program units can use PL/SQL8 features. http://blog.ebiztechnics.com
• 6.
  PL/SQL8 Scalar Types •NCHAR and NVARCHAR2 • SIGNTYPE • FLOAT • NATURALN • POSITIVEN • PLS_INTEGER http://blog.ebiztechnics.com
• 7.
  Unsupported Client-side PL/SQLFeatures • Untrusted external procedures • Object types • Collection types • LOB types • Methods • Objects as stored procedure parameters http://blog.ebiztechnics.com
• 8.
  Causes for OracleServer Errors Base Table Block Implicit DML Trigger/PU Explicit DML Stored PU call Form Oracle Server Declarative Constraints Database Triggers Program Units http://blog.ebiztechnics.com
• 9.
  Trapping Server Errors Basetable block On-Error: DBMS_ERROR_CODE DBMS_ERROR_TEXT Explicit DML/PU call When Others: SQLCODE SQLERRM Constraint DB trigger Stored PU Form Oracle Server Predefined message RAISE_APPLICATION_ ERROR RAISE_APPLICATION_ ERROR http://blog.ebiztechnics.com
• 10.
  FORMS_DDL Built-in • Syntax:FORMS_DDL (statement); • Parameter must be<=32K • Can pass PL/SQL block, DML or DDL statement • Characteristics: – Unrestricted; valid in Enter Query mode – Statement must not contain bind- variable references http://blog.ebiztechnics.com
• 11.
  FORMS_DDL Built-in • Characteristics: –Statement cannot return results directly – Use FORM_SUCCESS to check success of statement • Hints: – Create SQL Query record group instead of FORMS_DDL for better performance. – Call stored program unit instead of FORMS_DDL http://blog.ebiztechnics.com
• 12.
  FORMS_DDL Example Example 1:Create a table FORMS_DDL(‘CREATE TABLE t (x number(5))’); IF not FORM_SUCCESS THEN MESSAGE(‘Table creation Failed…’); END IF; Example: 2: Execute Procedure with given name: FORMS_DDL (‘BEGIN ‘ || p_name ||‘; END; ‘); IF not FORM_SUCCESS THEN message(DBMS_ERROR_CODE||’ ‘|| DBMS_ERROR_TEXT); END IF; http://blog.ebiztechnics.com
• 13.
  Summary • Identify Oracleserver features useful for Form Builder • Identify PL/SQL8 supported features • Write PL/SQL code to trap errors raised by the Oracle server – Implicit DML – Explicit DML or stored program units • Perform DDL with FORMS_DDL http://blog.ebiztechnics.com