Feed aggregator

ORA-04080: trigger ‘PRICE_HISTORY_TRIGGERV1’ does not exist

Amardeep Sidhu - 10 hours 17 min ago

It is actually a dumb one. I was disabling triggers in a schema and ran this SQL to generate the disable statements. (Example from here)

HR@test> select 'alter trigger '||trigger_name|| ' disable;' from user_triggers where table_name='PRODUCT';

'ALTERTRIGGER'||TRIGGER_NAME||'DISABLE;'
--------------------------------------------------------------------------------
alter trigger PRICE_HISTORY_TRIGGERv1 disable;

HR@test> alter trigger PRICE_HISTORY_TRIGGERv1 disable;
alter trigger PRICE_HISTORY_TRIGGERv1 disable
*
ERROR at line 1:
ORA-04080: trigger 'PRICE_HISTORY_TRIGGERV1' does not exist


HR@test>

WTF ? It is there but the disable didn’t work. I was in hurry, tried to connect through SQL developer and disable and it worked ! Double WTF ! Then i spotted the problem. Someone created it with one letter in the name in small. So to make it work, we need to use double quotes.

HR@test> alter trigger "PRICE_HISTORY_TRIGGERv1" disable;

Trigger altered.

HR@test>

One of the reasons why you shouldn’t use case sensitive names in Oracle. That is stupid.

Categories: BI & Warehousing

Documentum – xPlore – How to Improve the Search results in DA or D2

Yann Neuhaus - 10 hours 40 min ago

When supporting Documentum, we often got complaining from users like “The search is not working” or “I cannot find my document”.
The first reflex is to verify if the xPlore is working properly. The second reflex is to perform a search test but all is working correctly.
Then we contact user to get more details about his search.
Sometimes the only problem is that user does not use the search properly. Indeed the Documentum search use its “own” language with wildcard and the user just don’t use the right syntax.
Here I propose to set a kind of Rosetta stone which make the link between Human and xPlore language.

Ready to “talk” with the Full Text ?
Let’s start !

Search comprehension:

A word: this is a set of alphanumeric characters in between space characters.

A wildcard character: this is a kind of placeholder represented by a single character, such as an asterisk [ * ], takes the place of any other single character or a string or zero charactere.

 

Search with one word and quotation marks [ ” ” ] When user type
in search field
Dsearch understand Will match Will
NOT
match
“word1″ “word1″: Here Fulltext will search for an exact match of terms contained between the quotation marks word1 word
word11
xword1

 

Search with one word When user type
in search field
Dsearch understands Will match Will
NOT
match
word1 “word1*”
Here FullText add an asterisk [ * ] at the end of the word which matches single character or a string or zero character.
And quotation marks are also automatically added at the beginning and the end of the word. word1
word1A
word14A
word132 word
wo
rword
1word
word4A

 

Search with one word and wildcard question marks [ ? ] When user type
in search field
Dsearch understands Will match Will
NOT
match
word1? “word1?”
Here Fulltext will replace the question mark [ ? ] with zero or one single character.
And quotation marks are also automatically added at the beginning and the end of the word. word1
word11
word12
word1a
word1x
word
word123
xword11

 

Search with one word and wildcard star [ * ] When user type
in search field
Dsearch understands Will match Will
NOT
match
word1* “word1*”
The asterisk [ * ] matches single character or a string or zero character.
And quotation marks are also automatically added at the beginning and the end of the word. word1
word12
word12a
word1x2b
word
word1

 

Search with several words When user type
in search field
Dsearch understands Will match Will
NOT
match
word1 word2 “word1*” OR “word2*”
The [ space ] between two words is translated as “OR“.
The rule “1) search with one word” is applied for each word word1 OR word2
word1A
word14A
word132
word2
word2A
word24A
word232
xword1 OR xword2

 

Search with several words and quotation marks When user type
in search field
Dsearch understands Will match Will
NOT
match
“word1 word2″ “word1 word2″
Here Fulltext will search for an exact match of terms contained within the quotation mark word1 word2 word1 OR word2
word1 AND xword2
xword1 AND word2

 

Search with several words and [+] character When user type
in search field
Dsearch understands Will match Will
NOT
match
word1 + word2 “word1*” AND “word2*”
Here combination of word [ space ][ + ]word is translated as “AND“.
The rule “1) search with one word” is applied for each word
Note: Search can be combined with several [+] word1 +word2 +word3 word1 AND word2
word1 AND word2x
word1x AND word2
word1x AND word2x word1
word2
xword1 AND word2
word1 AND xword2

 

These are the basic search “tips” mostly out of the box, be aware these search behaviors can be customized with some parameters modifications.

My advice is “Abuse quotation marks ¨” ;-)

I hope this has been helpfull !

Cet article Documentum – xPlore – How to Improve the Search results in DA or D2 est apparu en premier sur Blog dbi services.

Autotrace, statistics and the four majors products

Tom Kyte - 17 hours 17 min ago
Hello The Masters of Oracle, Last time I was using AUTOTRACE in order to collect statistics about a SELECT. I used SQLcl to change from SQL*Plus and... oh, it was a schock! Statistics were very very differents from SQL*Plus. So, I decided to...
Categories: DBA Blogs

dbms_backup_restore.searchfiles returns old opened files

Tom Kyte - 17 hours 17 min ago
Hello, I am using dbms_backup_restore.searchfiles & X$KRBMSFT to get the list of the files from a certain path but it returns me also some other files which were opened and closed with an editor in the past. More exactly, I have 2 files in the ...
Categories: DBA Blogs

Moving data file using a procedure

Tom Kyte - 17 hours 17 min ago
We need to move datafile from one location to another location using a procedure. CREATE OR REPLACE PROCEDURE FILE_MOVING_FROM_DISK AS DISK_FILE_NAME VARCHAR2(200); RES_TS VARCHAR2(100):='USERS'; sql_stmt VARCHAR2(500); ...
Categories: DBA Blogs

Export from 11g to 12c using data pump

Tom Kyte - 17 hours 17 min ago
Hi, We have an existing 11g instance on a lab/dev server. We have build a new server with 12c and exported and imported the data using data pump. That was all done by an external DBA, however, he's currently unavailable and I would like to h...
Categories: DBA Blogs

Database Sizing

Tom Kyte - 17 hours 17 min ago
Hi there, I need to come up with right sizing for our database based on the below information. 36 million records for a total of 1000 character per record. I have the following estimations but I need to add Index Size and other calculations as we...
Categories: DBA Blogs

Sortorder in Table Functions and Pipelined Table Function

Tom Kyte - 17 hours 17 min ago
Hey, suppose there is a function that return a numeric collection with 100.000 records. Will the rownum pseudocolumn always have the same value than the column_value pseudocolumn? Is there a difference between TF and PTF? What about Parallelism? ...
Categories: DBA Blogs

Testing out the new PFS (Pivotal Function Service) alpha release on minikube

Pas Apicella - Mon, 2019-01-21 19:25
I quickly installed PFS on minikube as per the instructions below so I could write my own function service. Below shows that function service and how I invoked using the PFS CLI and Postman

1. Install PFS using this url for minikube. Refer to these instructions to install PFS on minikube

https://docs.pivotal.io/pfs/install-on-minikube.html

2. Once installed verify PFS has been installed using some commands as follows

$ watch -n 1 kubectl get pod --all-namespaces

Output:



Various namespaces are created as shown below:

$ kubectl get namespaces
NAME                   STATUS    AGE
default                    Active       19h
istio-system           Active       18h
knative-build        Active       18h
knative-eventing  Active       18h
knative-serving    Active       18h
kube-public            Active       19h
kube-system          Active        19h

Ensure PFS is installed as shown below:

$ pfs version
Version
  pfs cli: 0.1.0 (e5de84d12d10a060aeb595310decbe7409467c99)

3. Now we are going to deploy this employee function which exists on GitHub as follows

https://github.com/papicella/emp-function-service


The Function code is as follows:

  
package com.example.empfunctionservice;

import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import java.util.function.Function;

@Slf4j
@SpringBootApplication
public class EmpFunctionServiceApplication {

private static EmployeeService employeeService;

public EmpFunctionServiceApplication(EmployeeService employeeService) {
this.employeeService = employeeService;
}

@Bean
public Function<String, String> findEmployee() {
return id -> {
String response = employeeService.getEmployee(id);

return response;
};
}

public static void main(String[] args) {
SpringApplication.run(EmpFunctionServiceApplication.class, args);
}

}

4. We are going to deploy a Spring Boot Function as per the REPO above. More information on Java Functions for PFS can be found here

https://docs.pivotal.io/pfs/using-java-functions.html


5. Let's create a function called "emp-function" as shown below

$ pfs function create emp-function --git-repo https://github.com/papicella/emp-function-service --image $REGISTRY/$REGISTRY_USER/emp-function -w -v

Output: (Just showing the last few lines here)

papicella@papicella:~/pivotal/software/minikube$ pfs function create emp-function --git-repo https://github.com/papicella/emp-function-service --image $REGISTRY/$REGISTRY_USER/emp-function -w -v
Waiting for LatestCreatedRevisionName
Waiting on function creation: checkService failed to obtain service status for observedGeneration 1
LatestCreatedRevisionName available: emp-function-00001

...

default/emp-function-00001-gpn7p[build-step-build]: [INFO] BUILD SUCCESS
default/emp-function-00001-gpn7p[build-step-build]: [INFO] ------------------------------------------------------------------------
default/emp-function-00001-gpn7p[build-step-build]: [INFO] Total time: 12.407 s
default/emp-function-00001-gpn7p[build-step-build]: [INFO] Finished at: 2019-01-22T00:12:39Z
default/emp-function-00001-gpn7p[build-step-build]: [INFO] ------------------------------------------------------------------------
default/emp-function-00001-gpn7p[build-step-build]:        Removing source code
default/emp-function-00001-gpn7p[build-step-build]:
default/emp-function-00001-gpn7p[build-step-build]: -----> riff Buildpack 0.1.0
default/emp-function-00001-gpn7p[build-step-build]: -----> riff Java Invoker 0.1.3: Contributing to launch
default/emp-function-00001-gpn7p[build-step-build]:        Reusing cached download from buildpack
default/emp-function-00001-gpn7p[build-step-build]:        Copying to /workspace/io.projectriff.riff/riff-invoker-java/java-function-invoker-0.1.3-exec.jar
default/emp-function-00001-gpn7p[build-step-build]: -----> Process types:
default/emp-function-00001-gpn7p[build-step-build]:        web:      java -jar /workspace/io.projectriff.riff/riff-invoker-java/java-function-invoker-0.1.3-exec.jar $JAVA_OPTS --function.uri='file:///workspace/app'
default/emp-function-00001-gpn7p[build-step-build]:        function: java -jar /workspace/io.projectriff.riff/riff-invoker-java/java-function-invoker-0.1.3-exec.jar $JAVA_OPTS --function.uri='file:///workspace/app'
default/emp-function-00001-gpn7p[build-step-build]:

...

default/emp-function-00001-deployment-66fbd6bf4-bbqpq[user-container]: Hibernate: insert into employee (id, name) values (null, ?)
default/emp-function-00001-deployment-66fbd6bf4-bbqpq[user-container]: 2019-01-22 00:13:53.617  INFO 1 --- [       Thread-4] c.e.empfunctionservice.LoadDatabase      : Preloading Employee(id=1, name=pas)
default/emp-function-00001-deployment-66fbd6bf4-bbqpq[user-container]: Hibernate: insert into employee (id, name) values (null, ?)
default/emp-function-00001-deployment-66fbd6bf4-bbqpq[user-container]: 2019-01-22 00:13:53.623  INFO 1 --- [       Thread-4] c.e.empfunctionservice.LoadDatabase      : Preloading Employee(id=2, name=lucia)
default/emp-function-00001-deployment-66fbd6bf4-bbqpq[user-container]: Hibernate: insert into employee (id, name) values (null, ?)
default/emp-function-00001-deployment-66fbd6bf4-bbqpq[user-container]: 2019-01-22 00:13:53.628  INFO 1 --- [       Thread-4] c.e.empfunctionservice.LoadDatabase      : Preloading Employee(id=3, name=lucas)
default/emp-function-00001-deployment-66fbd6bf4-bbqpq[user-container]: Hibernate: insert into employee (id, name) values (null, ?)
default/emp-function-00001-deployment-66fbd6bf4-bbqpq[user-container]: 2019-01-22 00:13:53.632  INFO 1 --- [       Thread-4] c.e.empfunctionservice.LoadDatabase      : Preloading Employee(id=4, name=siena)
default/emp-function-00001-deployment-66fbd6bf4-bbqpq[user-container]: 2019-01-22 00:13:53.704  INFO 1 --- [       Thread-2] o.s.c.f.d.FunctionCreatorConfiguration   : Located bean: findEmployee of type class com.example.empfunctionservice.EmpFunctionServiceApplication$$Lambda$791/373359604

pfs function create completed successfully

6. Let's invoke our function as shown below by returning each Employee record using it's ID.

$ pfs service invoke emp-function --text -- -w '\n' -d '1'
curl http://192.168.64.3:32380/ -H 'Host: emp-function.default.example.com' -H 'Content-Type: text/plain' -w '\n' -d 1
Employee(id=1, name=pas)

$ pfs service invoke emp-function --text -- -w '\n' -d '2'
curl http://192.168.64.3:32380/ -H 'Host: emp-function.default.example.com' -H 'Content-Type: text/plain' -w '\n' -d 2
Employee(id=2, name=lucia)

The "pfs service invoke" will show you what an external command will look like to invoke the function service. The IP address here is just the same IP address returned by "minikube ip" as shown below.

$ minikube ip
192.168.64.3

7. Let's view our services using "pfs" CLI

$ pfs service list
NAME            STATUS
emp-function  Running
hello                Running

pfs service list completed successfully

8. Invoking from Postman, ensuring we issue a POST request and pass the correct headers as shown below





More Information

https://docs.pivotal.io/pfs/index.html

Categories: Fusion Middleware

ODPI-C 3.1 is Available

Christopher Jones - Mon, 2019-01-21 16:31

Release 3.1 of Oracle Database Programming Interface for C (ODPI-C) is now available on GitHub

ODPI-C is an open source library of C code that simplifies access to Oracle Database for applications written in C or C++.

Top features: Multi-property Session Tags

The ODPI-C 3.1 release introduces some small enhancements and corrects a number of small issues that were discovered over the past few months. The main change is support for Oracle Call Interface 12.2's multi-property session tagging, allowing connections in a session pool to be assigned a semi-arbitrary string tag that you can use to represent the session state (e.g. ALTER SESSION values) of each connection. With multi-property tagging you can assign a PL/SQL procedure to 'fix-up' the session state, if necessary, before a connection is returned to the application from the pool. This is an efficient way to make sure connections have a required state.

See the release notes for all the changes.

ODPI-C References

Home page: https://oracle.github.io/odpi/

Code: https://github.com/oracle/odpi

Documentation: https://oracle.github.io/odpi/doc/index.html

Release Notes: https://oracle.github.io/odpi/doc/releasenotes.html

Installation Instructions: oracle.github.io/odpi/doc/installation.html

Report issues and discuss: https://github.com/oracle/odpi/issues

Upgrading of Oracle APEX 18.x and future

Dimitri Gielis - Mon, 2019-01-21 08:43
Late September 2018 Oracle Application Express (APEX) 18.2 was released.

Since 2018, Oracle adopts a new versioning for their software, and APEX is following that. The plan of the Oracle APEX development team is to do two releases a year which carry the year and the release number of that year e.g. 18.1, 18.2, 19.1.

I like this new way of providing us with new releases. You don't know the exact timing of the releases, but you know the development team will be close to release version 1 in March/April and version 2 in September/October timeframe. In between you can download one-of patches or patch set releases through Oracle Support.

However there's one important change compared to before you have to be aware of:
every new version of Oracle APEX will be a complete install, with its own schema.

The biggest benefit of this approach is that the downtime to upgrade an Oracle APEX instance can be very minimal and the install is fast. You can read more about it in Maximizing Uptime During an Application Express Upgrade. I believe the Oracle Cloud is a big driver for this, but it benefits us all.

Another benefit is that if you can download and install all APEX releases in Oracle XE more easily. Before if you had installed APEX 5.1.0 and wanted to upgrade to APEX 5.1.2, 5.1.3, 5.1.4 you had to download the software from Oracle Support. But you could only do that with a valid support contract. If you wanted a complete free system with Oracle XE, you could download the latest version of APEX and do a new install and migrate over the workspaces and apps. Since APEX 18 you can always install those versions and APEX itself is doing the migration for you.

Now the biggest disadvantage of this approach is that you can't easily export and import your apps and plug-ins to a different version. Before when you might have Oracle APEX 5.1.0 (released Dec. 2016) in one environment and Oracle APEX 5.1.4 (released Dec. 2017) in another (or anything in between) and you could easily export and import between those environments. Oracle APEX exports have always been compatible till the second dot, so you could export import from any 5.1.x to another 5.1.x. You can import an APEX 18.1 version in an APEX 18.2 environment, but not the other way.

We typically move fast to a new version of APEX as we have to support those versions for APEX Office Print. I also blogged how we upgrade major releases by running multiple PDBs. With APEX 5.0, released in April 2015 and APEX 18.1 released in May 2018, we covered over 3 years in 3 PDBs (1 PDB for every major release of APEX 5.0, 5.1, 18.1). With the new version numbers, with 3 PDBs we cover a maximum of 1.5 years.

If you are aware of this, you can plan your application releases with it. And these new release numbers will make customers probably want to upgrade faster, or at least once a year as numbers go up fast :) As developers it's not only nice to work with new technology, it's also more productive for everyone.

Oracle APEX is not the only software in our stack right? ORDS, SQL Developer, Oracle Database, they follow the new release numbers. ORDS released a few days ago 18.4. The ORDS/SQL Developer/SQLcl team releases every quarter and typically use the quarter of development in their release number.

Tim Hall wrote a nice article on his thoughts on the new release numbers for the Oracle Database.

I love getting new versions and play with the new features or not wait long on bug fixes. We release frequently with APEX Office Print (AOP) too, 10 days ago we released AOP 19.1, but that is for another blog post!

Happy upgrading :)
Categories: Development

How to profile with DBMS_HPROF into memory (RAM)?

Tom Kyte - Mon, 2019-01-21 06:26
Hi TOM, How to profile with DBMS_HPROF into memory (RAM)? For example, into BLOB, that will be stored in memory (RAM, PGA). The problem is that I need to save result only if a procedure running more than 1 minute. Most of the time there is no need...
Categories: DBA Blogs

How to export output of Stored Procedure returned in refcursor into csv file

Tom Kyte - Mon, 2019-01-21 06:26
Dear Team, I have created stored procedure which return output in refcursor. If I run procedure by "right click => Run", then I can see output in tab "Log => output variables". But, I can't export output from there. Then I tried below com...
Categories: DBA Blogs

queries are not running in Sql Developer

Tom Kyte - Mon, 2019-01-21 06:26
Hi Tom/Team, I have installed Oracle 11g Database on my Windows 10 64-bit machine.All installation has been completed successfully. I have checked connections by sqlplus and it's running fine. I am able to run queries as well there. So database is...
Categories: DBA Blogs

Difference join between (+) and (-) notation

Tom Kyte - Mon, 2019-01-21 06:26
Hi Tom. I have very old application. Then I saw some store procedure using (+) or (-) notation on the query. What i want to ask are : 1. what is the difference between (+) and (-) notation ? 2. what is the difference between (the position of t...
Categories: DBA Blogs

Plugging non-cdb into a cdb as a pdb

Tom Kyte - Mon, 2019-01-21 06:26
Hi, In test environment, i plugged a non-CDB into an existing multitenant container database (CDB) as a pluggable database (PDB). ---Non-CDB--- Version: Oracle Database 12c Releases 1 64-bit Character set:<b> WE8ISO8859P15</b> National chara...
Categories: DBA Blogs

Eliminating patching downtime

Tom Kyte - Mon, 2019-01-21 06:26
Hi Tom, With all the Oracle technologies including the logical, physical standby DBs, flashback, rolling patches, Active Data Guard, and Edition Based Redefinition, etc. available today, would you please explain by example why we still can?t comp...
Categories: DBA Blogs

Announcing Hosting for Oracle ADF Rich Client and Oracle ADF WorkBetter Demos

Andrejus Baranovski - Mon, 2019-01-21 03:03
If you are curious about how Oracle ADF works or want to explore a rich set of ADF Faces components - welcome to access Oracle ADF demo apps hosted on our cloud server.

We launched a dedicated website Oracle ADF Components. Hosted demos:

1. ADF Faces Rich Client
2. ADF Work Better


These demo apps can be downloaded from Oracle, you could run them on your own environment too. But sometimes it is useful to have apps online for quick access.

New ILM Planning Guide available

Anthony Shorten - Sun, 2019-01-20 15:37

All the whitepapers are going through a major overhaul and the first ones of these is the ILM Planning Guide. In past releases the ILM solution used a feature called ILM Assistant which was an addon to the database that generated some partitioning capabilities and had some additional planning capabilities. The ILM Assistant is largely been replaced with the base functionality in Oracle Enterprise Manager with superior functionality and capability in a comprehensive interface. Given that Oracle Enterprise Manager (with or without ANY packs) is becoming the defacto standard for DBA's to manage their on-premise and cloud databases, the ILM whitepaper has been updated to reflect this.

The new whitepaper covers the above as well as new advice and a simpler set of scenarios that illustrate the implementation of the Oracle Utilities ILM solution.

The whitepaper is available at ILM Planning Guide (Doc Id: 1682436.1) available from My Oracle Support.

 

Oracle Cloud Infrastructure (OCI) : New Region Added (Toronto,Canada)

Online Apps DBA - Sun, 2019-01-20 00:41

[New Updates] New Region Added (Toronto,Canada) for Gen 2 Cloud (OCI) Oracle has recently announced the availability of a new Cloud Infrastructure Region which went live on 17 January’19 Read More at https://k21academy.com/oci27 & know about the ✔ Oracle Cloud Infrastructure (OCI) Servers & Data hosted regions ✔ Domains Availabilities ✔ Region, AD, FD, Tenancy, […]

The post Oracle Cloud Infrastructure (OCI) : New Region Added (Toronto,Canada) appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Pages

Subscribe to Oracle FAQ aggregator