Search This Blog

Showing posts with label Oracle/PLSQL. Show all posts
Showing posts with label Oracle/PLSQL. Show all posts

Monday, May 19, 2014

Determining NLS_DATE_FORMAT Oracle

OracleNLS_DATE_FORMAT

NLS_DATE_FORMAT specifies the default date format to use with the TO_CHAR and TO_DATE functions

Example:
Get NLS_DATE_FORMAT 

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'

Result:





Tuesday, April 29, 2014

Find last modified tables Oracle

Oracle > Scripts

USER_TAB_MODIFICATIONS describes modifications to tables owned by the current user that have been modified since the last time statistics.

Example: Find last modified tables:

select * from USER_TAB_MODIFICATIONS order by timestamp desc




Friday, March 21, 2014

Oracle Sequence Example

OracleSequence

Sequences are database objects from which multiple users can generate unique integers. The sequence generator generates sequential numbers, which can help to generate unique primary keys automatically, and to coordinate keys across multiple rows or tables.


Example:

CREATE SEQUENCE order_sequence
      INCREMENT BY 1
      START WITH 1
      NOMAXVALUE
      NOCYCLE
      CACHE 10;

INSERT INTO orders (order_no)
VALUES (order_sequence.NEXTVAL);





Wednesday, February 26, 2014

Read XMLTYPE Oracle C#

C# > Oracle.DataAccess > Read XMLTYPE 

An OracleXmlType object represents an Oracle XMLType instance.

Example:


OracleConnection con = new OracleConnection() ;
con.ConnectionString = "Data Source=sid; User ID=xx; Password=xx; enlist=false; ";
con.Open();
OracleCommand xmlCmd = new OracleCommand();
xmlCmd.CommandText = "select xml from table ";
xmlCmd.Connection = con;
OracleDataReader poReader = xmlCmd.ExecuteReader();
OracleXmlType poXml;
while (poReader.Read())
{
     poXml = poReader.GetOracleXmlType(0);    
}

con.Close();




Thursday, February 20, 2014

Oracle DBMS_SCHEDULER

Oracle >  SYS.DBMS_SCHEDULER 

The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures.







Wednesday, February 19, 2014

Oracle create job example

Oracle SYS.DBMS_SCHEDULER > CREATE_JOB

The CREATE_JOB procedure creates a single job. You can create multiple jobs using the CREATE_JOBS procedure.


Example:

Oracle create job example


BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"db"."job"',
            job_type => 'PLSQL_BLOCK',
            job_action => 'delete from item',
            number_of_arguments => 0,
            start_date => TO_TIMESTAMP('19-FEB-14 04.51.50.316000000 PM', 'DD-MON-RR HH.MI.SS.FF AM'),
            repeat_interval => 'FREQ=DAILY;BYHOUR=3',
            end_date => NULL,
            job_class => 'DEFAULT_JOB_CLASS',
            enabled => false,
            auto_drop => true,
            comments => NULL);


END;






Tuesday, February 18, 2014

Timestamp to date Oracle

Oracle > Timestamp > To Date

select * from your_table
where
TO_DATE (TO_CHAR (timestamp_date, 'YYYY-MON-DD'),'YYYY-MON-DD') = '18-FEB-2014'
 











Wednesday, February 12, 2014

ORA_ROWSCN Oracle example

Oracle

ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This is useful for determining approximately when a row was last updated.

Example

Get the system change number of the last operation on the items table

SELECT ORA_ROWSCN, item_name FROM items WHERE item_id = 10;




Wednesday, November 20, 2013

What is Oracle PL/SQL?

Oracle PL/SQL

PL/SQL is an language that was designed for processing of SQL commands. 
PL/SQL stands for Procedural Language extension of SQL.
Server-side PL/SQL is stored and compiled in Oracle Database and runs within the Oracle executable.

PL/SQL is a block structured language, which means program is in logical blocks of code.

Structure


DECLARE   
      <declarations section>
BEGIN   
      <executable command(s)>
EXCEPTION   
      <exception handling>
END;

Example

DECLARE
   msg varchar2(50):= 'PL/SQL Example';
BEGIN
   dbms_output.put_line(msg);
END ;
/

Links






Tuesday, November 19, 2013

Get list of all tables in Oracle

Oracle >List of all tables

See all the tables that your account has access


SELECT owner, table_name FROM all_tables

See table that you own


SELECT table_name FROM user_tables





Oracle Connection strings

Oracle > Connection strings


With tnsnames.ora

Data Source=OracleDB;User Id=Username;Password=Password;
Integrated Security
=no;

Without tnsnames.ora

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yourHost)(PORT=yourPort))(CONNECT_DATA=(SERVICE_NAME=yourOracleSID)));
User Id=Username;Password=Password;





Monday, November 18, 2013

BLOB (Binary Large Object) Oracle Data Type

Oracle > Data Type > BLOB

A BLOB (Binary Large Object) can hold up to 4 GB of data. BLOB's are designed for storing digitized information like images, audio, video.

Example:

CREATE TABLE blob_table (id NUMBER, doc BLOB);
INSERT INTO blob_table VALUES (1, EMPTY_BLOB());

select * from blob_table




Friday, November 8, 2013

Oracle FOR LOOP

Oracle > FOR LOOP IN

The syntax for the FOR Loop In Oracle is:

FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
{.statements.}
END LOOP;

Example:

begin
  for i in 1..10 loop
    DBMS_OUTPUT.PUT_LINE (i);
   end loop ;
end ;
/




Thursday, November 7, 2013

Get Oracle Version

Oracle > Version

V$VERSION displays version numbers of core library components in the Oracle Database.

Example:

select * from v$version

Result:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production





Monday, January 14, 2013

Duplicate rows in Oracle

Oracle > Scripts

SQL Query to find duplicate rows in Oracle

SELECT
  *
FROM
  table_name A
WHERE
  A.rowid >
  ANY (SELECT B.rowid FROM  table_name B WHERE
             A.col1 = B.col1
            AND
             A.col2 = B.col2
             )




Friday, February 18, 2011

Extract hour, minute from date column Oracle

Oracle > Extract hour, minute from date column

SELECT
  to_char(column_date,'HH24') hh
  ,to_char(column,'mi') min
from
  table;




Wednesday, September 1, 2010

Null data in Oracle using the ORDER BY clause

From:
Control null data in Oracle using the ORDER BY clause
Date: June 19th, 2007
Author: Bob Watkins

NULL data, which is sometimes called “absent” data, can be difficult to work with in relational databases. When a query contains the ORDER BY clause to sort the output, NULL data will sort to the bottom if the sort is in ascending order (ASC) and to the top if the sort is in descending order (DESC). In effect, NULL is treated as a very large value by Oracle.

This can create reports that are difficult to read. Consider a simple query in Oracle’s HR sample schema. Let’s say you want to list the names and commission percents of all employees in descending order, including those who get no commission (commission_pct is NULL). The following simple query does this:

SELECT employee_id, last_name, first_name, commission_pct
FROM employees
ORDER BY commission_pct DESC;

The problem is that all employees with no commission come out on top. You have to read through them all to find those who actually have a commission.

Starting with Oracle 8i, there is a little known syntax in the ORDER BY clause that fixes this. All you have to do is change the last line above to the following:

ORDER BY commission_pct DESC NULLS LAST;

The null rows will sort to the bottom after all the rows that contain commission data. You can also use NULLS FIRST when you’re sorting in ascending order, and you want the NULL rows to appear at the top of the report.

If you’re still supporting Oracle 8.0 or 7.3 databases, you can achieve the same effect using the Null Values function (NVL). Use something like the following in your ORDER BY:

ORDER BY NVL(commission_pct, -1);

This forces the NULL rows to be sorted as if they had the value (-1) in them, and they will appear at the bottom of the output. You won’t see the (-1) values because the query only sorts by the NVL function — it doesn’t display it in the SELECT lis

Monday, June 7, 2010

Oracle/PLSQL: Instr Function

Oracle/PLSQL > Functions > Instr

Instr function returns the location of a substring in a string.

The syntax for the instr Oracle function is:

instr( string1, string2 [, start_position [, nth_appearance ] ] )

Example

select INSTR('test', 'e') from dual

result:
2






Monday, May 17, 2010

Oracle/PLSQL: Oracle System Tables

Oracle/PLSQL > Oracle System Tables

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

Below is an alphabetical listing of the Oracle system tables that are commonly used.

System Table Description

ALL_ARGUMENTS Arguments in object accessible to the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS Comments on columns of accessible tables and views
ALL_CONSTRAINTS Constraint definitions on accessible tables
ALL_CONS_COLUMNS Information about accessible columns in constraint definitions
ALL_DB_LINKS Database links accessible to the user
ALL_ERRORS Current errors on stored objects that user is allowed to create
ALL_INDEXES Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS Description of LOBs contained in tables accessible to the user
ALL_OBJECTS Objects accessible to the user
ALL_OBJECT_TABLES Description of all object tables accessible to the user
ALL_SEQUENCES Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS Snapshots the user can access
ALL_SOURCE Current source on stored objects that user is allowed to create
ALL_SYNONYMS All synonyms accessible to the user
ALL_TABLES Description of relational tables accessible to the user
ALL_TAB_COLUMNS Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters
ALL_TAB_COMMENTS Comments on tables and views accessible to the user
ALL_TRIGGERS Triggers accessible to the current user
ALL_TRIGGER_COLS Column usage in user's triggers or in triggers on user's tables
ALL_TYPES Description of types accessible to the user
ALL_UPDATABLE_COLUMNS Description of all updatable columns
ALL_USERS Information about all users of the database
ALL_VIEWS Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL Database compatible parameter set via init.ora
DBA_DB_LINKS All database links in the database
DBA_ERRORS Current errors on all stored objects in the database
DBA_OBJECTS All objects in the database
DBA_ROLES All Roles which exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SOURCE Source of all stored objects in the database
DBA_TABLESPACES Description of all tablespaces
DBA_TAB_PRIVS All grants on objects in the database
DBA_TRIGGERS All triggers in the database
DBA_TS_QUOTAS Tablespace quotas for all users
DBA_USERS Information about all users of the database
DBA_VIEWS Description of all views in the database
DICTIONARY Description of data dictionary tables and views
DICT_COLUMNS Description of columns in data dictionary tables and views
GLOBAL_NAME global database name
NLS_DATABASE_PARAMETERS Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS NLS parameters of the instance
NLS_SESSION_PARAMETERS NLS parameters of the user session
PRODUCT_COMPONENT_VERSION version and status information for component products
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS Privileges which the user currently has set
SESSION_ROLES Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names





Saturday, May 15, 2010

Oracle FOR LOOP

The syntax for the FOR Loop in Oracle is:

FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
{.statements.}
END LOOP;