[Gathering the answers in the comments] The problem is that the Oracle service is running on a IP address, and the host is configured with another IP address. To see the IP address of the Oracle service, issue an lsnrctl status command and check the address reported (in this case is 127.0.0.1, the localhost): (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))) … Read more
Liquibase liquibase.org: it understands hibernate definitions. it generates better schema update sql than hibernate it logs which upgrades have been made to a database it handles two-step changes (i.e. delete a column “foo” and then rename a different column to “foo”) it handles the concept of conditional upgrades the developer actually listens to the community … Read more
DML triggers are either defined as table level or as row level. A table level trigger fires once for each operation on the table, so if you update 30 rows then that is one operation as far as a table trigger is concerned. Table triggers do not have insight into which rows are modified, but … Read more
If you’re already using analytics (ROW_NUMBER() OVER …) then adding another analytic function on the same partitioning will add a negligible cost to the query. On the other hand, there are many other ways to do pagination, one of them using rownum: SELECT * FROM (SELECT A.*, rownum rn FROM (SELECT * FROM your_table ORDER … Read more
Historical reasons. They used to be different before 10g: On 8i and 9i, PLS_INTEGER was noticeably faster than BINARY_INTEGER. When it comes to declaring and manipulating integers, Oracle offers lots of options, including: INTEGER – defined in the STANDARD package as a subtype of NUMBER, this datatype is implemented in a completely platform-independent fashion, which … Read more
Shouldn’t it be current_date? Hibernate will translate it to the proper dialect. I did not find a real “Hibernate will translate this to that” reference documentation, but the expression, in general, can be found in HQL Expressions for Hibernate 4.3. Then there is the Java Persistence API 2.0 (JPA) specification which defines expressions for the … Read more
Use the command: SET FEEDBACK OFF before running the procedure. And afterwards you can turn it back on again: SET FEEDBACK ON
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.
If you’re talking about PL/SQL, you should put it in an anonymous block. DECLARE v_text VARCHAR2(10); — declare BEGIN v_text := ‘Hello’; –assign dbms_output.Put_line(v_text); –display END;
The Oracle documentation has an excellent set of considerations for indexing choices: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004 Update for 19c: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/designing-and-developing-for-performance.html#GUID-99A7FD1B-CEFD-4E91-9486-2CBBFC2B7A1D Quoting: Consider indexing keys that are used frequently in WHERE clauses. Consider indexing keys that are used frequently to join tables in SQL statements. For more information on optimizing joins, see the section “Using Hash Clusters for Performance”. … Read more