Create a diagram for existing database schema or its subset as follows: Click File → Data Modeler → Import → Data Dictionary. Select a DB connection (add one if none). Click Next. Check one or more schema names. Click Next. Check one or more objects to import. Click Next. Click Finish. The ERD is displayed. … Read more
Echoing Tony’s comment, you’d be far better off storing dates in DATE columns rather than forcing a front-end query tool to find and handle these exceptions. If you’re stuck with an incorrect data model, however, the simplest option in earlier versions is to create a function that does the conversion and handles the error, CREATE … Read more
I suspect you’re only reporting the last error in a stack like this: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package body “schema.package” has been invalidated ORA-04065: not executed, altered or dropped package body “schema.package” ORA-06508: PL/SQL: could not find program unit being called: “schema.package” If so, that’s because your … Read more
You have to explicitly tell sqlplus to do that, in your script. Basically, there are two statements that you can use: WHENEVER SQLERROR EXIT SQL.SQLCODE WHENEVER OSERROR EXIT For example: WHENEVER SQLERROR EXIT SQL.SQLCODE begin SELECT COLUMN_DOES_NOT_EXIST FROM DUAL; END; / And for OS errors: WHENEVER OSERROR EXIT FAILURE START no_such_file For more information, see … Read more
The context method is described here: http://docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm e.g. (example adapted from the above link) CREATE CONTEXT dates_ctx USING set_dates_ctx_pkg; CREATE OR REPLACE PACKAGE set_dates_ctx_pkg IS PROCEDURE set(d1 in date, d2 in date); END; / CREATE OR REPLACE PACKAGE BODY set_dates_ctx_pkg IS PROCEDURE set(d1 in date, d2 in date) IS BEGIN DBMS_SESSION.SET_CONTEXT(‘dates_ctx’, ‘d1′, TO_CHAR(d1,’DD-MON-YYYY’)); DBMS_SESSION.SET_CONTEXT(‘dates_ctx’, ‘d2’, … Read more
You can create and use oracle sequences. The syntax and details are at http://www.techonthenet.com/oracle/sequences.php Also read the article http://rnyb2.blogspot.com/2006/02/potential-pitfall-with-oracle-sequence.html to understand the limitations with respect to AUTONUMBER in other RDBMS
One additional idea, mentioned here is to use a regular expression to check: SELECT foo FROM bar WHERE REGEXP_LIKE (foo,’^[[:digit:]]+$’); The nice part is you do not need a separate PL/SQL function. The potentially problematic part is that a regular expression may not be the most efficient method for a large number of rows.
From official doc – 4.3.1 The default isolation level for that method is SERIALIZABLE, which is quite aggressive: READ_COMMITTED would work just as well; READ_UNCOMMITTED would be fine if two processes are not likely to collide in this way. However, since a call to the create* method is quite short, it is unlikely that the … Read more
Here is how you could create such a table: SELECT LEVEL AS id, REGEXP_SUBSTR(‘A,B,C,D’, ‘[^,]+’, 1, LEVEL) AS data FROM dual CONNECT BY REGEXP_SUBSTR(‘A,B,C,D’, ‘[^,]+’, 1, LEVEL) IS NOT NULL; With a little bit of tweaking (i.e., replacing the , in [^,] with a variable) you could write such a function to return a table.
Yes. Declare TABLE TYPE variables in a PL/SQL declare block. Table variables are also known as index-by table or array. The table variable contains one column which must be a scalar or record datatype plus a primary key of type BINARY_INTEGER. Syntax: DECLARE TYPE type_name IS TABLE OF (column_type | variable%TYPE | table.column%TYPE [NOT NULL] … Read more