Lever et activer les contraintes d'un schéma Oracle

Désactivation

BEGIN FOR c IN (SELECT c.owner, c.table_name, c.constraint_name FROM user_constraints c, user_tables t WHERE c.table_name = t.table_name AND c.status = 'ENABLED' ORDER BY c.constraint_type DESC) LOOP dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name); END LOOP; END; 

Activation

BEGIN FOR c IN (SELECT c.owner, c.table_name, c.constraint_name FROM user_constraints c, user_tables t WHERE c.table_name = t.table_name AND c.status = 'DISABLED' ORDER BY c.constraint_type) LOOP dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name); END LOOP; END;

Source : http://www.silverlake.fr/index.php?post/2010/12/11/Lever-et-activer-les-contraintes-Oracle

Equivalent type TEXT MySQL in Oracle

Oracle has BLOB, CLOB and NCLOB for storing binary, character and unicode character data types. You can also specify the LOB storage area which allows a DBA to fine tune the storage if necessary (i.e. putting the LOB data on separate disks)

Check this out : http://docs.oracle.com/cd/B10501_01/server.920/a96524/c13datyp.htm#13754

Oracle Keywords

The following words also have a special meaning to Oracle but are not reserved words and so can be redefined. However, some might eventually become reserved words.

ADMIN	CURSOR	FOUND	MOUNT
AFTER	CYCLE	FUNCTION	NEXT
ALLOCATE	DATABASE	GO	NEW
ANALYZE	DATAFILE	GOTO	NOARCHIVELOG
ARCHIVE	DBA	GROUPS	NOCACHE
ARCHIVELOG	DEC	INCLUDING	NOCYCLE
AUTHORIZATION	DECLARE	INDICATOR	NOMAXVALUE
AVG	DISABLE	INITRANS	NOMINVALUE
BACKUP	DISMOUNT	INSTANCE	NONE
BEGIN	DOUBLE	INT	NOORDER
BECOME	DUMP	KEY	NORESETLOGS
BEFORE	EACH	LANGUAGE	NORMAL
BLOCK	ENABLE	LAYER	NOSORT
BODY	END	LINK	NUMERIC
CACHE	ESCAPE	LISTS	OFF
CANCEL	EVENTS	LOGFILE	OLD
CASCADE	EXCEPT	MANAGE	ONLY
CHANGE	EXCEPTIONS	MANUAL	OPEN
CHARACTER	EXEC	MAX	OPTIMAL
CHECKPOINT	EXPLAIN	MAXDATAFILES	OWN
CLOSE	EXECUTE	MAXINSTANCES	PACKAGE
COBOL	EXTENT	MAXLOGFILES	PARALLEL
COMMIT	EXTERNALLY	MAXLOGHISTORY	PCTINCREASE
COMPILE	FETCH	MAXLOGMEMBERS	PCTUSED
CONSTRAINT	FLUSH	MAXTRANS	PLAN
CONSTRAINTS	FREELIST	MAXVALUE	PLI
CONTENTS	FREELISTS	MIN	PRECISION
CONTINUE	FORCE	MINEXTENTS	PRIMARY
CONTROLFILE	FOREIGN	MINVALUE	PRIVATE
COUNT	FORTRAN	MODULE	PROCEDURE

PROFILE	SAVEPOINT	SQLSTATE	TRACING
QUOTA	SCHEMA	STATEMENT_ID	TRANSACTION
READ	SCN	STATISTICS	TRIGGERS
REAL	SECTION	STOP	TRUNCATE
RECOVER	SEGMENT	STORAGE	UNDER
REFERENCES	SEQUENCE	SUM	UNLIMITED
REFERENCING	SHARED	SWITCH	UNTIL
RESETLOGS	SNAPSHOT	SYSTEM	USE
RESTRICTED	SOME	TABLES	USING
REUSE	SORT	TABLESPACE	WHEN
ROLE	SQL	TEMPORARY	WRITE
ROLES	SQLCODE	THREAD	WORK
ROLLBACK	SQLERROR	TIME

Source : https://docs.oracle.com/cd/B10501_01/appdev.920/a42525/apb.htm

Oracle Reserved Words

The following words are reserved by Oracle. That is, they have a special meaning to Oracle and so cannot be redefined. For this reason, you cannot use them to name database objects such as columns, tables, or indexes.

ACCESS	ELSE	MODIFY	START
ADD	EXCLUSIVE	NOAUDIT	SELECT
ALL	EXISTS	NOCOMPRESS	SESSION
ALTER	FILE	NOT	SET
AND	FLOAT	NOTFOUND	SHARE
ANY	FOR	NOWAIT	SIZE
ARRAYLEN	FROM	NULL	SMALLINT
AS	GRANT	NUMBER	SQLBUF
ASC	GROUP	OF	SUCCESSFUL
AUDIT	HAVING	OFFLINE	SYNONYM
BETWEEN	IDENTIFIED	ON	SYSDATE
BY	IMMEDIATE	ONLINE	TABLE
CHAR	IN	OPTION	THEN
CHECK	INCREMENT	OR	TO
CLUSTER	INDEX	ORDER	TRIGGER
COLUMN	INITIAL	PCTFREE	UID
COMMENT	INSERT	PRIOR	UNION
COMPRESS	INTEGER	PRIVILEGES	UNIQUE
CONNECT	INTERSECT	PUBLIC	UPDATE
CREATE	INTO	RAW	USER
CURRENT	IS	RENAME	VALIDATE
DATE	LEVEL	RESOURCE	VALUES
DECIMAL	LIKE	REVOKE	VARCHAR
DEFAULT	LOCK	ROW	VARCHAR2
DELETE	LONG	ROWID	VIEW
DESC	MAXEXTENTS	ROWLABEL	WHENEVER
DISTINCT	MINUS	ROWNUM	WHERE
DROP	MODE	ROWS	WITH

Source : https://docs.oracle.com/cd/B10501_01/appdev.920/a42525/apb.htm

Installing PHP and the Oracle Instant Client for Linux and Windows

Installing PHP and the Oracle Instant Client for Linux and Windows

• http://www.oracle.com/technetwork/articles/dsl/php-oic-install-linux-2275399.html
• http://www.oracle.com/technetwork/articles/technote-php-instant-084410.html
• https://blogs.oracle.com/opal/entry/using_php_oci8_with_32-bit_php

Configuration de PHP avec OCI8

• http://php.net/manual/fr/oci8.installation.php

High performance PHP applications with Oracle Database

• http://www.oracle.com/technetwork/database/database-technologies/scripting-languages/php/index.html

Instant Client Downloads for Microsoft Windows (32-bit)

• http://www.oracle.com/technetwork/topics/winsoft-085727.html

Call to undefined function oci-connect php oci8

• http://stackoverflow.com/questions/29257894/call-to-undefined-function-oci-connect-php-oci8-12c-dll-windows-8-1-php5-6-6

Avant installation du client oracle oci8

php --ri oci8

PHP Warning:  PHP Startup: Unable to load dynamic library 'C:\php-5.2.8\ext\php_

oci8.dll' - %1 is not a valid Win32 application.

 in Unknown on line 0

PHP Warning:  PHP Startup: Unable to load dynamic library 'C:\php-5.2.8\ext\php_

pdo_oci8.dll' - %1 is not a valid Win32 application.

 in Unknown on line 0

Extension 'oci8' not present.

Aprés installation du client oracle oci8

php --ri oci8

PHP Warning:  PHP Startup: Invalid library (maybe not a PHP library) 'php_pdo_oc

i8.dll'  in Unknown on line 0

oci8

OCI8 Support => enabled

Version => 1.2.5

Revision => $Revision: 1.269.2.16.2.43 $

Active Persistent Connections => 0

Active Connections => 0

Temporary Lob support => enabled

Collections support => enabled

Directive => Local Value => Master Value

oci8.max_persistent => -1 => -1

oci8.persistent_timeout => -1 => -1

oci8.ping_interval => 60 => 60

oci8.privileged_connect => Off => Off

oci8.statement_cache_size => 20 => 20

oci8.default_prefetch => 10 => 10

oci8.old_oci_close_semantics => no value => no value

How to check character set in Oracle

One of the requirements for samePage is that the character set should be UTF-8. 

You can either check with your DBA or run the following SQL to determine whether your database character set is UTF-8. 

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

It should return the value AL32UTF8. 

Another option is to run the following SQL :

SELECT * FROM NLS_DATABASE_PARAMETERS

The value against NLS_CHARACTERSET should be UTF8.

Source : http://support.etouch.net/cm/wiki/?id=43321

[ORACLE/PLSQL] Using the single ampersand substitution variable : &

The single ampersand (&) substition variable causes Oracle to stop and prompt the user for a value before executing the command. This si a very good tool when the developer wants to use the same query to retrieve  different set of data

Example :

SELECT &colname FROM &tabname WHERE &colname IS NOT NULL;

Result: