I found this link useful. Here is the paragraph highlighting some of the pros/cons of each approach. The most commonly seen design is to imitate the many Boolean-like flags that Oracle’s data dictionary views use, selecting ‘Y’ for true and ‘N’ for false. However, to interact correctly with host environments, such as JDBC, OCCI, and … Read more
99.9% of the time the error ORA-65096: invalid common user or role name means you are logged into the CDB when you should be logged into a PDB. For example, if you used the default 19c installation settings, you should login to ORCLPDB (the PDB) instead of ORCL (the CDB). DANGER – If you insist … Read more
Using CSV is probably the simplest way, assuming you can be 100% certain that your elements won’t themselves contain strings. An alternative, and probably more robust, way of doing this is to create a custom type as a table of strings. Supposing your strings were never longer than 100 characters, then you could have: CREATE … Read more
Since Oracle 9i there are two ways or declaring a directory for use with UTL_FILE. The older way is to set the INIT.ORA parameter UTL_FILE_DIR. We have to restart the database for a change to take affect. The value can like any other PATH variable; it accepts wildcards. Using this approach means passing the directory … Read more
Create the table and the sequence SQL> create table staff ( 2 emp_id number primary key, 3 staff_name varchar2(100) 4 ); Table created. SQL> create sequence emp_id_seq; Sequence created. Now, you can create a trigger that uses the sequence to populate the primary key SQL> create trigger trg_emp_id 2 before insert on staff 3 for … Read more
By default, SQL Plus treats ‘&’ as a special character that begins a substitution string. This can cause problems when running scripts that happen to include ‘&’ for other reasons: SQL> insert into customers (customer_name) values (‘Marks & Spencers Ltd’); Enter value for spencers: old 1: insert into customers (customer_name) values (‘Marks & Spencers Ltd’) … Read more
here is another easier option select to_number(column_value) as IDs from xmltable(‘1,2,3,4,5’);
None whatsover. They are synonyms supplied to make your code more readable: FUNCTION f IS … CREATE VIEW v AS SELECT …
This question is pretty much the inverse of Convert Unixtime to Datetime SQL (Oracle) As Justin Cave says: There are no built-in functions. But it’s relatively easy to write one. Since a Unix timestamp is the number of seconds since January 1, 1970 As subtracting one date from another date results in the number of … Read more
This is because Oracle internally changes empty string to NULL values. Oracle simply won’t let insert an empty string. On the other hand, SQL Server would let you do what you are trying to achieve. There are 2 workarounds here: Use another column that states whether the ‘description’ field is valid or not Use some … Read more