A few days ago, somebody asked if it is possible to write a Calendar in a single SQL statement.

Here you go...

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",

I was working on a Application using oracle forms,where i need to call a sql script file with dynamic schema (dynamic owner), so i need to pass the owner from the oracle forms,
I came with the following procedure:

[code]----------------------------------------------
-- PROCEDURE PR_RUN_SCRIPT_PARAM
----------------------------------------------

PROCEDURE PR_RUN_SCRIPT_PARAM( P_USER VARCHAR2,
P_PASS VARCHAR2,
P_FILE VARCHAR2,
PAR1 VARCHAR2,
PAR2 VARCHAR2) IS
V_CONNECT_STRING VARCHAR2(30) := GET_APPLICATION_PROPERTY(CONNECT_STRING);
V_PROGRAM VARCHAR2(15) := 'SQLPLUS.EXE';
V_TAIL

Some times you need to disable user constraints before doing the import for some user,
for this reason i created a procedure that can be used to disable the constraints before the import,and than use it again to enable the constraints after doing the import,and also this procedure can be used to drop user constraints when needed.
I named it MANAGE_USER_FK_PK_UK,note the order in the name FK_PK_UK,because when you want to disable constraints you should disable the foreign keys after doing that for the primary keys,for the unique keys it can be done at first or not,i do it at last.
The proced

XML: XML is an acronym for ‘Extensible Markup Language’. It is used for defining data
elements on a webpage and business to business document. XML uses a similar tag
structure as HTML; however, whereas HTML defines how the elements are displayed,
XML defines what those elements contain.

In a previous article I talked about using Natural Oracle Features to get XML data out of an Oracle database, and put data into an Oracle database, without actually having to learn a lot about XML related technologies. Still, there are many who continue to resist using OBJECTS, COLLECTIONS, and INSTEAD-OF-TRIGGERS in their daily work with the Oracle RDBMS. Since the biggest hurdle with XML seems to be parsing it into its component data elements, here is a more programming oriented approach to parsing XML data inside Oracle that should make this crowd happy.

Manually database creation is one of the more frequently performed actions of a professional DBA. And, with Oracle 11g it only takes a few minutes to do :)

The procedure is same as previous which we are used for 9i and 10g.

In this database creation I used below features:

1. OMF (Oracle Managed File) for datafiles, redolog files & controlfiles
2. FRA (Flash Recovery Area) for Archivelog or backup files
3. ASM (Automatic Storage Mgmt) for Volume manager

1. Create Required Directories

E:\>mkdir e:\oracle\ORA11G

There are some question which asked frequently on forums from every oracle newbie.

1. Which Oracle Certification I choose ?

Here I will explain in details.

There types of Oracle Certification we can do.
1. Oracle DBA
2. Oracle Developer
3. Oracle Application DBA

1. Oracle DBA

The person which maintain Oracle Database called Oracle Database Administrator.
There are some frequent work for Oracle DBA
1. Database Backup Responsibity.
2. Database Performance Tunning
3. Database Basic Administration Like " user creation, database security"

There are five ways to recompile invalid objects in schema.

1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL
5. Manually Recompile

DBMS_DDL.ALTER_COMPILE

Definition

This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]

[code]
Syntax

Exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username

Name : Objects name

Example

Dear Friends,

In this article we focus on How to migrate existing database to ASM.

Step:1
Login to SYSDBA user and alter below three parameter for controlfile,datafile or FRA location with SPFILE option.

1. First need to set below parameter for controlfile,datafile or FRA.

NOTE: I have two disk group here I am using "DGRP2" disk group.
[code]
SQL> alter system set control_files='+DGRP2' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DGRP2' scope=spfile;
System altered.

Before commencing to read this article, I would suggest you to go through the Oracle Database Architecture ( Oracle Database Architecture ) Article to gain insight about the oracle various structures.

The following example describes the most basic level of operations that Oracle performs.