Show: Today's Messages :: Unanswered Messages :: Polls :: Message Navigator
16 Search Results Found
1 Forum: SQL & PL/SQL «» Posted on: Tue, 31 July 2018 12:36 «» By: Solomon Yakobson
Re: Create JSON from SQL 12C R1
It is, 12.1 allows to store valid JSON as CLOB or query JSON: SQL> select banner 2 from v$version 3 / BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition ...
2 Forum: SQL & PL/SQL «» Posted on: Fri, 14 July 2017 19:19 «» By: Barbara Boehmer
Re: Insert statement with bind variable and Create statement
1. Are you doing this from SQL*Plus or from something else like SQL*Developer or Toad? If from SQL*Plus, then it may be do to some setting. Please post the result of SHOW ALL. 2. You cannot split any identifier or value between lines. If you do so,...
3 Forum: SQL & PL/SQL «» Posted on: Wed, 17 May 2017 08:49 «» By: whdyck
Re: Use ":NEW" in Dynamic SQL in Trigger?
@cookiemonster: Thanks for your helpful post. (The first such in this discussion, I might add.) Actually, I've been referring to a check constraint violation (ORA-02290). Yes, SQL users will understand what a check constraint violation is in a gene...
4 Forum: SQL & PL/SQL «» Posted on: Sun, 19 March 2017 07:53 «» By: Solomon Yakobson
Re: need help in SQL Check constraint on column referencing other tables
You can use Barbara's solutions or (if you are on older version) you can denormalize and use constraints: SQL> CREATE TABLE player 2 ( 3 playerid NUMBER, 4 name VARCHAR2(7), 5 age NUMBER, ...
5 Forum: SQL & PL/SQL «» Posted on: Fri, 17 March 2017 23:24 «» By: Barbara Boehmer
Re: need help in SQL Check constraint on column referencing other tables
Here is another method that uses a function, a virtual column, and a check constraint. It requires that the function be deterministic, which means that your player table must be static or you may get incorrect results based on previous results. -...
6 Forum: Forms «» Posted on: Fri, 08 January 2016 03:30 «» By: cookiemonster
Re: VALIDATING QTY
Your best bet is probably a view with a check constraint on it: SQL> CREATE TABLE stock (item VARCHAR2(10), seq NUMBER, amt NUMBER); Table created SQL> ALTER TABLE stock ADD CONSTRAINT stock_pk PRIMARY KEY (item, seq); Table altered SQL&...
7 Forum: SQL & PL/SQL «» Posted on: Thu, 26 June 2014 07:21 «» By: Solomon Yakobson
Re: How to enforce key uniqueness in a temporal table?
Overlaps are not allowed, but are gaps allowed? Anyway: SQL> CREATE MATERIALIZED VIEW CATCH_OVERLAPPING_VALUES 2 NOLOGGING 3 CACHE 4 BUILD IMMEDIATE 5 REFRESH COMPLETE 6 ON COMMIT 7 AS 8 SELECT END_PERIOD...
8 Forum: SQL & PL/SQL «» Posted on: Mon, 21 October 2013 11:50 «» By: Solomon Yakobson
Re: Materialized views to rescue "mutating tables"
Oops, You are correct, it will not apply fast refresh when committing theo_life changes. And you can find it by using dbms_mview.explain_mview: SCOTT@orcl > exec dbms_mview.explain_mview('mv_check_theo'); PL/SQL procedure successfully complet...
9 Forum: SQL & PL/SQL «» Posted on: Mon, 21 October 2013 07:30 «» By: Solomon Yakobson
Re: Materialized views to rescue "mutating tables"
Yes, it is possible to create fast refresh on commit MV in this case: create materialized view log on theo_life with primary key,rowid,sequence(nb_theo) including new values / create materialized view log on real_life with rowid,sequence(id) ...
10 Forum: SQL & PL/SQL «» Posted on: Fri, 27 September 2013 15:27 «» By: Michel Cadot
Re: complex constraint
Not true: SQL> create table abcd( 2 trans_type VARCHAR2(10) not null, 3 original_tran_no NUMBER, 4 original_trans_dt DATE); Table created. SQL> alter table abcd add constraint t1_chk1 2 check ( 3 ( (trans_type != 'R') o...
11 Forum: SQL & PL/SQL «» Posted on: Fri, 27 September 2013 15:02 «» By: Solomon Yakobson
Re: complex constraint
The above allows for trans_type = 'R' and any/both original_tran_no and original_trans_dt being null, which violates when trans_type = 'R' then (original_tran_no and original_trans_dt ) should be not null You need something: SCOTT@orcl > cre...
12 Forum: SQL & PL/SQL «» Posted on: Mon, 27 May 2013 07:51 «» By: Michel Cadot
Re: regex validation
SQL> CREATE TABLE OM_AIC_SALES_CODES ( 2 SALE_CODE VARCHAR2(6) 3 check (regexp_like(sale_code,'^\d\d\.\d\d$')) 4 ) 5 / Table created. SQL> insert into OM_AIC_SALES_CODES values ('01.01'); 1 row created. SQL> insert ...
13 Forum: SQL & PL/SQL «» Posted on: Fri, 08 February 2013 11:59 «» By: Solomon Yakobson
Re: assertions
Michel, I am not negating need for assertions. There are many cases where it can't be or can't be easily achieved with standard Oracle features. And in many cases it obviously simplifies implementation. Now that second example can be also implemented us...
14 Forum: SQL & PL/SQL «» Posted on: Wed, 08 August 2012 01:28 «» By: Littlefoot
Re: special character not allowed in column value
Check constraint. For example: SQL> create table stud_mst 2 (stud_name varchar2(20) constraint ch_ename 3 check (regexp_like(stud_name, '^[[:alnum:]]+$')) 4 ); Table created. SQL> insert into stud_mst ...
15 Forum: SQL & PL/SQL «» Posted on: Fri, 11 May 2012 16:33 «» By: Barbara Boehmer
Re: sql & syntax (regex maybe) (4 threads merged by bb)
I would remove each + from the regular expression. Once the table is cleaned up, I would add a check constraint that uses the regular expression, to prevent future bad input. I have demonstrated both below. SCOTT@orcl_11gR2> set null null SCOTT@...
16 Forum: SQL & PL/SQL «» Posted on: Mon, 05 March 2012 10:28 «» By: Solomon Yakobson
Re: Constraints on objects?
Inventing a square wheel? SQL> CREATE TABLE object_tab_ext of object_ext 2 / Table created. SQL> ALTER TABLE object_tab_ext 2 ADD CONSTRAINT object_tab_ext_chk1 3 CHECK( 4 NVL(course_name,'X') IN ('Scien...

Current Time: Tue Jun 18 18:45:59 CDT 2019