In this article I present how NLS_NUMERIC_CHARACTERS parameter works.
This parameter specifies the characters to use as group separator and decimal character. The group separator separates integer groups (that is, thousands, millions, billions, and so on). The decimal separates the integer portion of a number from the decimal portion.
NLS_NUMERIC_CHARACTERS = “decimal_character group_separator”
There is a limit of of values that can be specified for IN clause. The limit is 1000 expressions. In case you try to to write following query you can get ORA-01795
Select
shogen_no,
shogen_desc,
disp_turn
From
shogen
where
shogen_no in ('one', 'two', 'three', ..........'more than thousand')
solution is quite simple you need to write many IN caluse
where
shogen_no in ('one', 'two', 'three', ..., 'first thousand') or
shogen_no in ('four', 'five', 'six', ..., 'another thousand') or ...
Have a fun 🙂
Tomasz
This article presents how to use remote debugger in Sqldeveloper.
It’s recommended to read article about debugging code in sqldeveloper:
Remote debuger is very nice feature of sqldeveloper. It gives possibility to a Developer to debug a code started in different session by a Client.So somebody else (on completly different computer) is starting code and we just debug it. It’s like setting a bait(by Developer) and wait for careless animal 🙂 (Client).
This article presents how to use debugger in Sqldeveloper.
Oracle SqlDeveloper is very popular application between developers. I’d like to show you how easy you can debug your code.
First you need to create simple procedure and compile it for debug. You can do it in sqldeveloper if you want
CREATE OR REPLACE PROCEDURE my_code IS v_id NUMBER := 5; BEGIN FOR i IN 1..v_id LOOP DBMS_OUTPUT.put_line(i); END LOOP; END; / ALTER PROCEDURE my_code compile debug; |
Enqueues – are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction. Enqueue names are displayed in the LOCK_TYPE column of the DBA_LOCK and DBA_LOCK_INTERNAL data dictionary views.
In case your session is slow you can always check what it’s waiting for in v$session table.
This article presents how you can suggest join order for tables. It’s recommended to read following article:
This article presents hints which can be used to force Oracle to use specific join operation between two tables. This article is not trying to prove which joins is better but just try to show how to force Oracle database to “play game” as we want even if our tactic is wrong.
It’s recommended to read following article before it:
This article presents and tries to explain joins between two tables that can be encountered in Oracle: HASH, NESTED, MERGE
---------------------------------------------- | Id | Operation | Name ---------------------------------------------- | 0 | SELECT STATEMENT | | 1 | HASH JOIN | | 2 | TABLE ACCESS FULL| <outer table> <- hash table in memory | 3 | TABLE ACCESS FULL| <inner table> -----------------------------------------------
This kind of join is the most popular in warehouses and enables to join very effectively large tables, sets of rows. <inner table> is quite often called “driving table” – table which searches data in other table. <outer table> which is hashed in memory you can treat as MEMORY INDEX.
Oracle delivers very nice extensions for GROUP BY clause which are very useful for developers and DBAs:
There are 2 interesting PRAGMA which can be defined for procedures/functions and packages:
AUTHID CURRENT_USER – specify CURRENT_USER to indicate that the code executes with the privileges of CURRENT_USER. This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the code resides. All roles for CURRENT_USER are active for dynamic code (EXECUTE IMMEDIATE) and disabled for static compilation.
AUTHID DEFINER – specify DEFINER to indicate that the code executes with the privileges of the owner of the schema in which the package resides and that external names resolve in the schema where the code resides. This is the default and creates a definer’s rights package. All roles for DEFINER are disabled for dynamic code(EXECUTE IMMEDIATE) and static compilation.
In this article I want to show specific feature for AUTHID CURRENT_USER enabled roles for dynamic code.
