Oracle Cast Function Use

The Oracle CAST function converts one data type to another.  The CAST function can convert built-in and collection-typed values into other built-in or collection typed values.

 CAST can convert a date or other unnamed operand (or a nested table or other named collection) into a type-compatible datatype or named collection.  For this use of CAST, type_name and/or operand must be of (or evaulate to) a built-in datatype or collection type .

CAST and ANYDATA Type

When using CAST to convert an operand, the expr can also be either a built-in datatype or a collection type; however, it can be an instance of an ANYDATA type as well. When the expr is of an ANYDATA type, CAST attempts an extraction of the value of the ANYDATA expr and returns it if it matches the CAST target type.  If the there is no match to the CAST target type, NULL is returned.

CAST with LOB Datatypes

 LOB datatypes are not directly supported by the CAST function.  Using CAST to convert CLOB values into a character datatypes or BLOB values into the RAW datatype results in the database converting the LOB value implicity to character or raw data.  Once this implicit conversion is done, the resulting value is CAST into the target datatype.  This process will throw an error if the resulting value is larger than the target type.

The Oracle docs note the syntax for Oracle CAST as follows:

CAST({ expr | MULTISET (subquery) } AS type_name)

With the Oracle CAST function a block could be re-written as:

DECLARE
  v NUMBER;
BEGIN
  a(CAST (v AS INTEGER));
END;

On the other hand, the function could be re-written with Oracle CAST as:

DECLARE
  v NUMBER;
BEGIN
  a(v::INTEGER);
END;

Each of these uses of the CAST function will produce the same result.

Make Calendar in Oracle Apps

Followed these Steps which is of no use in Oracle apps.

1. Create a new MODULE in Forms Builder
2. Then open the stndrd20.olb file (this .olb file is located
in the ORACLE_HOME/TOOLS/DEVDEMXX/Forms FOLDER)
3. You will see 'STANDARDS' appear under 'Object Libraries' in the Object
Navigator
4. Double click on the book-like icon next to 'STANDARDS'
5. Expand the object library window that appears
6. Click on the 'COMPONENTS' tab
7. Look for the 'CALENDAR' object library
8. Drag-n-drop the 'CALENDAR' object into your new form
9. Notice all the new items are now in your form marked with a red arrow*
*The red arrow means that the items are subclassed.
10. Attach the PL/SQL library CALENDAR.PLL
(this .pll file is also located in the ORACLE_HOME/TOOLS/DEVDEMXX/Forms
FOLDER)
11. In Forms, Create a new block on a new canvas
12. Create a Key-Listval trigger on the date item for which you
would like to use the Date LOV Window. Add the below code to display the
calendar using the Date_LOV package.

date_lov.get_date(sysdate, -- initial date
'emp.hiredate', -- return block.item
240, -- window x position
60, -- window y position
'Start Date', -- window title
'OK', -- ok button label
'Cancel', -- cancel button label

13. If you want the end user to be able to close the Date List of Values
window by clicking the window close button (the X) in the title bar, create a
form-level When-Window-Closed trigger with the following code:

When-Window-Closed trigger:
GO_BLOCK('EMP');

VIEW-SEQUENCE-INDEX

 VIEW=  THE SUBSET OF THE TABLE. USE FOR COMPLICATED COMPLEX QUERY.

SQL> SELECT ENAME,JOB,SAL FROM EMP
  2  WHERE DEPTNO IN (10,20)
  3  OR JOB = 'SALESMAN' OR
  4  SAL <= 2500;

ENAME      JOB              SAL
---------- --------- ----------
AHMAD      CLERK          40000
MARIA      OFFICER         7000
ALI        CLERK           5000
SAHIR      CLERK           2000
ARIF       CLERK           2500
ASIF       DBA            77788
KIRAN      MANAGER         2000
SANA       MANAGER         2200
KASHIF     OFFICER         1400
IRFAN      CLERK           1200
EMRAN      CLERK           1000

ENAME      JOB              SAL
---------- --------- ----------
SIDRA      ASSISTANT        900
SMITH      CLERK            800
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
JONES      MANAGER         2975
MARTIN     SALESMAN        1250
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
TURNER     SALESMAN        1500
ADAMS      CLERK           1100

ENAME      JOB              SAL
---------- --------- ----------
JAMES      CLERK            950
FORD       ANALYST         3000
MILLER     CLERK           1300

25 rows selected.

SQL> CREATE VIEW ABCVIEW
  2  AS
  3   SELECT ENAME,JOB,SAL FROM EMP
  4   WHERE DEPTNO IN (10,20)
  5   OR JOB = 'SALESMAN' OR
  6   SAL <= 2500;

View created.

SQL> SELECT * FROM ABCVIEW;

ENAME      JOB              SAL
---------- --------- ----------
AHMAD      CLERK          40000
MARIA      OFFICER         7000
ALI        CLERK           5000
SAHIR      CLERK           2000
ARIF       CLERK           2500
ASIF       DBA            77788
KIRAN      MANAGER         2000
SANA       MANAGER         2200
KASHIF     OFFICER         1400
IRFAN      CLERK           1200
EMRAN      CLERK           1000

ENAME      JOB              SAL
---------- --------- ----------
SIDRA      ASSISTANT        900
SMITH      CLERK            800
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
JONES      MANAGER         2975
MARTIN     SALESMAN        1250
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
TURNER     SALESMAN        1500
ADAMS      CLERK           1100

ENAME      JOB              SAL
---------- --------- ----------
JAMES      CLERK            950
FORD       ANALYST         3000
MILLER     CLERK           1300

25 rows selected.

SQL> DROP VIEW ABCVIEW;

 SEQUENCE=  TO GENERATE AUTO NO

SQL> CREATE SEQUENCE ABCSQ
  2  START WITH 10
  3  INCREMENT BY 5;

Sequence created.

SQL> CREATE TABLE ABCDEF
  2  (RNO NUMBER(10),
  3  NAME VARCHAR2(20));

Table created.

SQL> INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4  (ABCSQ.NEXTVAL,'USAMA');

1 row created.

SQL> ED
Wrote file afiedt.buf

  1  INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4* (ABCSQ.NEXTVAL,'SAMI')
SQL> /

1 row created.

SQL> ED
Wrote file afiedt.buf

  1  INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4* (ABCSQ.NEXTVAL,'EJ')
SQL> /

1 row created.

SQL> ED
Wrote file afiedt.buf

  1  INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4* (ABCSQ.NEXTVAL,'HA')
SQL> /

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM ABCDEF;

       RNO NAME
---------- --------------------
        10 USAMA
        15 SAMI
        20 EJ
        25 HA

SQL> SELECT * FROM USER_SEQUENCES;

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
EMPSEQ                                  1 1.0000E+27           10 N N         20
       8336

DBOBJECTID_SEQUENCE                     1 1.0000E+24           50 N N         50
          1

ABCSQ                                   1 1.0000E+27            5 N N         20
        110


SQL> SET LINES 300 PAGES 300
SQL> /

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
EMPSEQ                                  1 1.0000E+27           10 N N         20        8336
DBOBJECTID_SEQUENCE                     1 1.0000E+24           50 N N         50           1
ABCSQ                                   1 1.0000E+27            5 N N         20         110

SQL> DROP SEQUENCE ABCSEQ;
DROP SEQUENCE ABCSEQ
              *
ERROR at line 1:
ORA-02289: sequence does not exist


SQL> ED
Wrote file afiedt.buf

  1* DROP SEQUENCE ABCSQ
SQL> /

Sequence dropped.

 INDEX= TO IMPROVE THE PERFORMANCE OF THE TABLE.WHEN UR DATA > 200 IN A TABLE THEN U APPLY INDEX.
     PK  UK IN COLUMNS AUTOMATIC CREATE INDEX.

SQL> CREATE INDEX ABCIDX
  2  ON EMP(JOB);
ON EMP(JOB)
       *
ERROR at line 2:
ORA-01408: such column list already indexed


SQL> ED
Wrote file afiedt.buf

  1  CREATE INDEX ABCIDX
  2* ON EMP(SAL)
SQL> /
ON EMP(SAL)
       *
ERROR at line 2:
ORA-01408: such column list already indexed


SQL> ED
Wrote file afiedt.buf

  1  CREATE INDEX ABCIDX
  2* ON ABCDEF(NAME)
SQL> /

Index created.

SQL> SELECT * FROM ABCDEF;

       RNO NAME
---------- --------------------
        10 USAMA
        15 SAMI
        20 EJ
        25 HA

SQL> DROP INDEX ABCIDX;

Index dropped.

OCP Database 10g Information

1Z0-042 Oracle Database 10g: Administration I exam. 
Important Exam Information and Objectives
The following objectives are included on this exam:

• Installing Oracle Database 10g Software
• Creating an Oracle Database
• Managing Schema Objects
• Managing Data
• Undo Management
• Monitoring and Resolving Lock Conflicts
• Database Interfaces
• Controlling the Database
• Oracle Database Security
• Oracle Net Services
• Backup and Recovery Concepts
• Storage Structures
• Administering Users
• Oracle Shared Servers
• Performance Monitoring
• Proactive Maintenance
• Database Backups
• Database Recovery

1Z0-043 Oracle Database 10g: Administration II exam
The following objectives are included on this exam:

• Using Globalization Support Objectives
• Securing the Oracle Listener
• Configuring Recovery Manager
• Recovering from User Errors
• Dealing with Database Corruption
• Automatic Database Management
• Using Recovery Manager
• Diagnostic Sources
• Recovering from Non-Critical Losses
• Monitoring and Managing Storage
• Automatic Storage Management
• Monitoring and Managing Memory
• Database Recovery
• Flashback Database
• Managing Resources
• Automating Tasks with the Scheduler