Loading...

https://www.eugdpr.org

One of the major change of privacy law effective next month is the right to get forgotten.

I welcome this change and don’t keep information about my readers behind their comments.

I also recommend my blogger family to switch to https and accept deletion requests

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I wrote a few odbc articles using ODBCCONF in my blog, so I edit them because ODBCCONF will be removed; read https://docs.microsoft.com/en-us/sql/odbc/odbcconf-exe

Using Powershell Add-OdbcDsn is much easier

PS> remove-OdbcDsn -name DB01 -dsntype User
PS> Add-OdbcDsn -name DB01 -DriverName 
  "Oracle in client12201" -DsnType "User" 
  -SetPropertyValue @("Server=DB01")
PS> Get-OdbcDsn

Name       : DB01
DsnType    : User
Platform   : 64-bit
DriverName : Oracle in client12201
Attribute  : {Password, StatementCache, ...}

PS> remove-OdbcDsn -name DB01 -dsntype User
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Itching to start playing with 18c?

Now you can on @oraclelivesql !

Here's a script to get you started: a dynamic CSV-to-columns converter using polymorphic table functionshttps://t.co/UfddLQ2tn5 pic.twitter.com/BxnXeIKoCx

— Chris Saxon (@chrisrsaxon) February 17, 2018


The ingenious solution of Anton Scheffer using Data Cartridge is now beaten in 18c using polymorphic table function

Anthologic post of Anton : forums.oracle.com
Chris magic with Oracle 18c :
livesql.oracle.com

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

SQL> create table t1(x number primary key);
Table created.
SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 X                       NOT NULL NUMBER

SQL> create table t2 as select * from t1;
Table created.
SQL> desc t2
 Name                    Null?    Type
 ----------------------- -------- ----------------
 X                                NUMBER

The table T2 has the column X, but not the constraint (primary key / not null).

If you want to do a create table as select but want to keep index / constraints etc, then you rather use datapump

SQL> set autop on
SQL> var job_state varchar2(30)
SQL> declare
  n number;
begin
  n := DBMS_DATAPUMP.open('IMPORT','TABLE','DB01');
  DBMS_DATAPUMP.metadata_filter(n,'NAME_LIST','''T1''');
  DBMS_DATAPUMP.metadata_remap(n,'REMAP_TABLE','T1','T3');
  DBMS_DATAPUMP.start_job(n);
  DBMS_DATAPUMP.WAIT_FOR_JOB(n, :job_state);
end;
/
PL/SQL procedure successfully completed.
JOB_STATE
--------------------------------------------------
COMPLETED
SQL> desc t3
 Name                    Null?    Type
 ----------------------- -------- ----------------
 X                       NOT NULL NUMBER

The Table T3 is a copy of T1. DB01 is my implicit loopback database link (database name).

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Let me today tell you, I now hate FBI for real.

Let’s start with an easy working example

SQL> CREATE TABLE t(x NUMBER PRIMARY KEY)
Table created.
SQL> insert into t(x) values (1)
1 row created.
SQL> insert into t(x) values (2)
1 row created.
SQL> insert into t(x) values (3)
1 row created.
SQL> commit
Commit complete.
SQL> CREATE FUNCTION f (x NUMBER)
  RETURN NUMBER DETERMINISTIC IS
  BEGIN
    RETURN x * x;
  END;
Function created.
SQL> CREATE INDEX i
   ON t (f (x))
Index created.
SQL> select * from t where f(x)=4

         X
----------
         2
Execution Plan
------------------------------------------------
 0  SELECT STATEMENT 
 1 0  TABLE ACCESS BY INDEX ROWID BATCHED T
 2 1    INDEX RANGE SCAN I

Okay, this is a nice-working example. I can use where f(x)=4.

A non-fbi code would be something like

SQL> create or replace type tn as table of number;
Type created.
SQL> create or replace function f2(y number) 
  return tn deterministic is 
  begin
  if (y<0) then return null; end if;
  return tn (sqrt(y), -sqrt(y));
  end;
Function created.
SQL> select * from t where x  member of f2(4)

         X
----------
         2

Execution Plan
------------------------------------
 0   SELECT STATEMENT
 1  0  INDEX FULL SCAN SYS_C0026437

The reverse function is somehow more challenging to code, but the benefit is enormous, I have no more fbi.

What’s wrong with fbi?

First example : I recreate my function:

SQL> DROP FUNCTION f
Function dropped.
SQL> CREATE FUNCTION f (x NUMBER)
  RETURN NUMBER DETERMINISTIC IS
  BEGIN
    RETURN power(x,2);
  END;
Function created.
SQL> select * from t where f(x)=4
*
Error at line 0
ORA-30554: function-based index I is disabled
SQL> SELECT object_type, object_name, status
  FROM user_objects
 WHERE object_name IN ('F','I')

OBJECT_TYPE             OBJECT_NAME  STATUS 
----------------------- ------------ -------
INDEX                   I            VALID  
FUNCTION                F            VALID  
SQL> SELECT index_name,
       table_name,
       index_type,
       status,
       funcidx_status
  FROM user_indexes
 WHERE index_name = 'I'

INDEX TABLE INDEX_TYPE     STATUS FUNCIDX_STATUS
----- ----- -------------- ------ --------------
I         T FUNCTION-BASED VALID  DISABLED      

Remember this error. ORA-30554. And this not-so-well-known column, USER_INDEXES.FUNCIDX_STATUS. The behavior is pretty agressive, every object is valid, but you can no longer select from the table.

A small parenthese. We all know about unusable indexes. Index often get unusable due to partition maintenance and the like.

SQL> create table t2(x number)
Table created.
SQL> insert into t2 values (1)
1 row created.
SQL> create index i2 on t2(x) unusable
Index created.
SQL> SELECT index_name,
       table_name,
       status
  FROM user_indexes
 WHERE index_name = 'I2'

INDEX TABLE STATUS
----- ----- ---------
I2    T2    UNUSABLE 
SQL> insert into t2 values (2)
1 row created.
SQL> select * from t2 where x=2

         X
----------
         2

Execution Plan
---------------------------------
   0       SELECT STATEMENT
   1    0    TABLE ACCESS FULL T2

The index is not unused, but it prevents neither INSERT nor SELECT.

Let’s add a constraint

SQL> alter index i2 rebuild
Index altered.
SQL> alter table t2 add primary key (x)
Table altered.
SQL> alter index i2 unusable
Index altered.
SQL> insert into t2 values (2)
ORA-01502: index 'I2' or partition of such index is in unusable state
SQL> select * from t2 where x=2

         X
----------
         2

If the index is used by a constraint or is unique, then insert is prevented. But no select is prevented ever.

Okay, frequent readers may wonder why I did DROP FUNCTION and CREATE FUNCTION instead of CREATE OR REPLACE FUNCTION.

Fine, let’s try.

SQL> CREATE or replace FUNCTION f (x NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
BEGIN
   RETURN power(x,2);
END;
Function created.
SQL> alter index i rebuild
Index altered.
SQL> alter index i enable
Index altered.
SQL> select x, f(x) from t where f(x)=4

         X       F(X)
---------- ----------
         2          4
SQL> create or replace function f(x number) 
  return number deterministic is  
begin
  return 1;
end;
Function created.
SQL> select x, f(x), f(2) from t where f(x)=4

         X       F(X)       F(2)
---------- ---------- ----------
         2          4          1

Oh my goodness, select returns completly wrong result, but the index is valid and enabled.

There is more than way to solve this

  1. rebuild your index after create function. You could find the candidates by looking at the last ddl time and dependencies
  2. SQL> select name from user_dependencies d 
      where referenced_type = 'FUNCTION' 
      and type = 'INDEX' and 
      (
        select last_ddl_time 
        from user_objects i 
        where i.object_name=d.name
      ) < (
        select last_ddl_time 
        from user_objects f 
        where f.object_name=d.referenced_name
      )
    NAME
    -----
    I                                                                               
    SQL> alter index i rebuild
    Index altered.
    SQL> select x, f(x), f(2) from t where f(x)=4
    no rows selected.
    SQL> select x, f(x), f(2) from t where f(x)=1
    
             X       F(X)       F(2)
    ---------- ---------- ----------
             1          1          1
             2          1          1
             3          1          1
    
    SQL> select name from user_dependencies d 
      where referenced_type = 'FUNCTION' 
      and type = 'INDEX' and 
      (
        select last_ddl_time 
        from user_objects i 
        where i.object_name=d.name
      ) < (
        select last_ddl_time 
        from user_objects f 
        where f.object_name=d.referenced_name
      )
    no rows selected.
    
  3. file an SR and encourage Oracle to test features before making them available
  4. stop using FBI immediately
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

To get the status of a directory, I wrote my own function, which uses DBMS_LOB.FILEEXISTS.


CREATE FUNCTION
status (DIRECTORY_NAME VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
IF (DBMS_LOB.FILEEXISTS(
BFILENAME (DIRECTORY_NAME, '.')) = 1)
THEN
RETURN 'VALID';
ELSE
RETURN 'INVALID';
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN SQLERRM;
END;
/


SELECT
directory_name NAME,
directory_path PATH,
status (directory_name) STATUS
FROM dba_directories;

NAME PATH STATUS
---- ---- ---------
FOO /foo INVALID
TMP /tmp VALID
BAK /u99 VALID

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

followup of check invalid database link
If you need to check db link in another schema, you need to create code that run with that schema.

base on the example from yesterday, here is an extended version for the dba


CREATE FUNCTION dba_status
(owner VARCHAR2, db_link VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
status VARCHAR2 (4000);
BEGIN
EXECUTE IMMEDIATE
'create or replace function "'
|| owner
|| '".status(db_link varchar2) return varchar2 is '
|| 'x number;'
|| 'begin execute immediate ''select 1 from dual@"''
||DB_LINK||''"'' into x;'
|| 'return ''OK'';'
|| 'exception when others then return sqlerrm;'
|| 'end;';
EXECUTE IMMEDIATE
'begin :1 := "' || owner
||'".status(''' || db_link || '''); end;'
USING OUT status;
EXECUTE IMMEDIATE 'drop function "' || owner || '".status';
COMMIT;
RETURN status;
END;
/


SELECT
owner, db_link, dba_status (owner, db_link)
FROM dba_db_links;
OWNER DB_LINK DBA_STATUS(OWNER,DB_LINK)
----- ------- --------------------------------
SCOTT L3.EXAM OK
SCOTT L2.EXAM ORA-12154: TNS:could not resolve
SCOTT L1.EXAM ORA-01017: invalid username/pass

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview