TOP

Oracle DBMS_Job

http://psoug.org/reference/dbms_job.html

dbms_job.change(
job       IN BINARY_INTEGER,
what      IN VARCHAR2,
next_date IN DATE,
interval  IN VARCHAR2,
instance  IN BINARY_INTEGER DEFAULT NULL,
force     IN BOOLEAN        DEFAULT FALSE);
exec dbms_job.change(14144, NULL, NULL, ‘SYSDATE + 3′);
dbms_job.isubmit (
job       IN BINARY_INTEGER,
what      IN VARCHAR2,
next_date IN DATE,
interval  IN VARCHAR2 DEFAULT ‘NULL’,
no_parse  IN BOOLEAN DEFAULT FALSE);

Note: no_parse indicates whether to parse job PL/SQL at time of submission (FALSE) or execution (TRUE)

exec dbms_job.isubmit(4242, ‘MYPROC’, SYSDATE);
dbms_job.submit(
JOB       OUT BINARY_INTEGER,
WHAT      IN  VARCHAR2,
NEXT_DATE IN  DATE           DEFAULT SYSDATE,
INTERVAL  IN  VARCHAR2       DEFAULT ‘NULL’,
NO_PARSE  IN  BOOLEAN        DEFAULT FALSE,
INSTANCE  IN  BINARY_INTEGER DEFAULT 0,
FORCE     IN  BOOLEAN        DEFAULT FALSE);
– To run everynight at midnight starting tonight
exec dbms_job.submit(:v_JobNo, ‘proc1;’, TRUNC(SYSDATE)+1, ‘TRUNC(SYSDATE)+1′);

– To run every hour, on the hour, starting at the top of the hour
exec
dbms_job.submit(:v_JobNo, ‘proc2;’, TRUNC(SYSDATE+(1/24), ‘HH’),
‘TRUNC(SYSDATE+(1/24),”HH”)’);

– To run every hour, starting now
exec dbms_job.submit(:v_JobNo, ‘proc3;’, INTERVAL => ‘SYSDATE+(1/24)’);

– To run every ten minutes at 0,10,20,etc. minutes past the hour,
– starting at the top of the hour

exec dbms_job.submit(:v_JobNo, ‘proc4;’, TRUNC(SYSDATE+(1/24), ‘HH’),
‘TRUNC(SYSDATE+(10/24/60),”MI”)’);

– To run every 2 min., on the minute, starting at the top of the
– minute

exec dbms_job.submit(:v_JobNo, ‘proc5;’, TRUNC(SYSDATE+(1/24/60), ‘MI’),
‘TRUNC(SYSDATE+(2/24/60),”MI”)’);

– To run every two minutes, starting now
exec dbms_job.submit(:v_JobNo, ‘proc6;’, INTERVAL => ‘SYSDATE+(2/24/60)’);

– To run every half hour, starting at the top of the hour
exec dbms_job.submit(:v_JobNo, ‘proc7;’, TRUNC(SYSDATE+(1/24), ‘HH’),
‘TRUNC(SYSDATE+(30/24/60),”MI”)’);

Read More
TOP

SQL Statement select from one table where not in another table

select FileId
from Files
where FileId not in (select FileId
from FileComponentFile)

Read More
TOP

SQL where in

select * from FileAttribute

where  fID  in  (select fid from v_ers_hydropower)

Read More
TOP

Oracle PL/Sql cursor

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-plsql.html#cursorsCursors

 

A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. By fetching into the cursor each tuple of the relation, we can write a program to read and process the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position.

The example below illustrates a cursor loop. It uses our example relation T1(e,f) whose tuples are pairs of integers. The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1.

 1) DECLARE

        /* Output variables to hold the result of the query: */

 2)     a T1.e%TYPE;

 3)     b T1.f%TYPE;

        /* Cursor declaration: */

 4)     CURSOR T1Cursor IS

 5)         SELECT e, f

 6)         FROM T1

 7)         WHERE e < f

 8)         FOR UPDATE;

 9) BEGIN

10)     OPEN T1Cursor;

11)     LOOP

            /* Retrieve each row of the result of the above query

               into PL/SQL variables: */

12)         FETCH T1Cursor INTO a, b;

            /* If there are no more rows to fetch, exit the loop: */

13)         EXIT WHEN T1Cursor%NOTFOUND;

            /* Delete the current tuple: */

14)         DELETE FROM T1 WHERE CURRENT OF T1Cursor;

            /* Insert the reverse tuple: */

15)         INSERT INTO T1 VALUES(b, a);

16)     END LOOP;

        /* Free cursor used by the query. */

17)     CLOSE T1Cursor;

18) END;

19) .

20) run;

Here are explanations for the various lines of this program:

  • Line (1) introduces the declaration section.
  • Lines (2) and (3) declare variables a and b to have types equal to the types of attributes e and f of the relation T1. Although we know these types are INTEGER, we wisely make sure that whatever types they may have are copied to the PL/SQL variables (compare with the previous example, where we were less careful and declared the corresponding variables to be of type NUMBER).
  • Lines (4) through (8) define the cursor T1Cursor. It ranges over a relation defined by the SELECT-FROM-WHERE query. That query selects those tuples of T1 whose first component is less than the second component. Line (8) declares the cursor FOR UPDATE since we will modify T1 using this cursor later on Line (14). In general, FOR UPDATE is unnecessary if the cursor will not be used for modification.
  • Line (9) begins the executable section of the program.
  • Line (10) opens the cursor, an essential step.
  • Lines (11) through (16) are a PL/SQL loop. Notice that such a loop is bracketed by LOOP and END LOOP. Within the loop we find:
    • On Line (12), a fetch through the cursor into the local variables. In general, the FETCH statement must provide variables for each component of the tuple retrieved. Since the query of Lines (5) through (7) produces pairs, we have correctly provided two variables, and we know they are of the correct type.
    • On Line (13), a test for the loop-breaking condition. Its meaning should be clear: %NOTFOUND after the name of a cursor is true exactly when a fetch through that cursor has failed to find any more tuples.
    • On Line (14), a SQL DELETE statement that deletes the current tuple using the special WHERE condition CURRENT OF T1Cursor.
    • On Line (15), a SQL INSERT statement that inserts the reverse tuple into T1.
  • Line (17) closes the cursor.
  • Line (18) ends the PL/SQL program.
  • Lines (19) and (20) cause the program to execute.
Read More
TOP

Oracle SQL Developer

Oracle SQL Developer is a free and fully supported graphical tool for database application development. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own. SQL Developer can connect to any Oracle Database version 9.2.0.1 and later (as well as to third-party platforms) and runs on Windows, Linux, and Mac OS X.

http://oss.oracle.com/sqldeveloper.html

Read More
TOP

Hottest Questions – Stack Overflow

http://stackoverflow.com/

lots of IT questions & answers

Read More
TOP

CREATE table new as (SELECT * from old)

http://www.techonthenet.com/sql/tables/create_table2.php

 

You can also create a table from an existing table by copying the existing table’s columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

 

Syntax #1 – Copying all columns from another table

The basic syntax is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

 

For example:

CREATE TABLE suppliers
  AS (SELECT *
         FROM companies
         WHERE id > 1000);

This would create a new table called suppliers that included all columns from the companies table.

If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

 

Syntax #2 – Copying selected columns from another table

The basic syntax is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, … column_n FROM old_table);

 

For example:

CREATE TABLE suppliers
  AS (SELECT id, address, city, state, zip
          FROM companies
          WHERE id > 1000);

This would create a new table called suppliers, but the new table would only include the specified columns from the companies table.

Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

 

Syntax #3 – Copying selected columns from multiple tables

The basic syntax is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, … column_n
          FROM old_table_1, old_table_2, … old_table_n);

 

For example:

CREATE TABLE suppliers
  AS (SELECT companies.id, companies.address, categories.cat_type
          FROM companies, categories
          WHERE companies.id = categories.id
          AND companies.id > 1000);

This would create a new table called suppliers based on columns from both the companies and categories tables.

 

Acknowledgements: We’d like to thank Dave M. for contributing to this solution!

Read More
TOP

MetaData 101 a Beginner’s Guide

Metadata 101 a Beginner’s Guide

Read More
TOP

Oracle Tricks & Tips

http://ss64.com/ora/
An A-Z Index of Oracle SQL Commands
   ANALYZE
   AUDIT
c
   CALL
   Cluster - CREATE CLUSTER
           - ALTER  CLUSTER
           - DROP   CLUSTER
   COMMENT
   COMMIT
   CONNECT
   CONSTRAINT - Add / Enable
   Context - CREATE CONTEXT
           - DROP CONTEXT
   Controlfile - CREATE CONTROLFILE
d
   Database - CREATE DATABASE
            - ALTER  DATABASE
   DELETE
   DESCRIBE
   Dimension - CREATE DIMENSION
             - ALTER DIMENSION
             - DROP DIMENSION
   Directory - CREATE DIRECTORY
             - DROP DIRECTORY
e
   EXEC
   EXECUTE IMMEDIATE
   EXPLAIN PLAN
f
   Function - CREATE FUNCTION
         - ALTER  FUNCTION
         - DROP   FUNCTION
g
   GRANT
i
   Index - CREATE INDEX
         - ALTER  INDEX
         - DROP   INDEX
   Indextype - CREATE INDEXTYPE
             - DROP INDEXTYPE
   INSERT
   INTERSECT
j
   Java - CREATE JAVA
        - ALTER JAVA
        - DROP JAVA
l
   Library - CREATE LIBRARY
           - DROP LIBRARY
   Link - CREATE DATABASE LINK
        - DROP DATABASE LINK
   LOCK TABLE
m
   Mview - CREATE MATERIALIZED VIEW
         - ALTER MATERIALIZED VIEW
         - DROP MATERIALIZED VIEW
         - CREATE MATERIALIZED VIEW LOG
         - ALTER MATERIALIZED VIEW LOG
         - DROP MATERIALIZED VIEW LOG
   MINUS
n
   NOAUDIT
o
   Operator - CREATE OPERATOR
            - DROP OPERATOR
   Outline - CREATE OUTLINE
           - ALTER OUTLINE
           - DROP OUTLINE
p
   Package - CREATE PACKAGE/BODY
           - ALTER  PACKAGE
           - DROP   PACKAGE
   Pfile - CREATE PFILE
   Procedure - CREATE PROCEDURE
             - ALTER  PROCEDURE
             - DROP   PROCEDURE
   Profile - CREATE PROFILE
           - ALTER  PROFILE
           - DROP   PROFILE
r
   RECOVER
   RENAME
   Resource - ALTER RESOURCE COST
   REVOKE
   RMAN - Recovery Manager
   Role - CREATE ROLE
        - SET    ROLE
        - ALTER  ROLE
        - DROP   ROLE
   ROLLBACK
   Rollback - CREATE ROLLBACK SEGMENT
            - ALTER  ROLLBACK SEGMENT
            - DROP   ROLLBACK SEGMENT
s
   SAVEPOINT
   Schema - CREATE SCHEMA
   SELECT
   Sequence - CREATE SEQUENCE
            - ALTER  SEQUENCE
            - DROP   SEQUENCE
   Session - ALTER SESSION
   SHUTDOWN
   SNAPSHOT
   SPfile - CREATE SPFILE
   STARTUP
   Statistics - ASSOCIATE STATISTICS
              - DISASSOCIATE STATISTICS
   Synonym - CREATE SYNONYM
           - DROP   SYNONYM
   System - ALTER SYSTEM
t
   Table - CREATE TABLE
         - ALTER  TABLE
         - DROP   TABLE
   Tablespace - CREATE TABLESPACE
              - ALTER  TABLESPACE
              - DROP   TABLESPACE
              - CREATE TEMPORARY TABLESPACE
   Transaction - SET TRANSACTION
   Trigger - CREATE TRIGGER
           - ALTER  TRIGGER
           - DROP   TRIGGER
   TRUNCATE
   Type - CREATE TYPE
        - ALTER TYPE
        - DROP TYPE
        - CREATE TYPE BODY
        - DROP TYPE BODY
u
   UPDATE
   UNION
   User - CREATE USER
        - ALTER  USER
        - DROP   USER
v
   View - CREATE VIEW
        - ALTER  VIEW
        - DROP   VIEW

Syntax:

Words in CAPS are Oracle commands, words in lower-case are the options you can modify.
Sections enclosed in [ ] are optional, underlined options are the default
Sections enclosed in { } are alternatives. The pipe symbol `|` indicates OR (more)
The syntax on these pages is based on Oracle version 9.2, while this is almost all still supported in 10g and 11g there are a few notes added where things have changed. Pages covering the newer functionality (RAC, Data Guard etc) may appear in the future.

SS64 Oracle Discussion forum
Links to other Sites, books etc…

Read More
TOP

Command line reference: Database and Operating Systems.

http://ss64.com/

Read More

Switch to our mobile site