ORA-12704: character set mismatch
you should do select COALESCE (EMAIL, n’NO EMAIL’) from crmuser.accounts to convert the literal to NVARCHAR. eg http://sqlfiddle.com/#!4/73929/1 vs http://sqlfiddle.com/#!4/73929/2
oracle11g
Insert into from CTE
This is the syntax to insert into a table from a CTE: — CREATE TABLE tmp ( tmp_id NUMBER(10) ); INSERT INTO tmp( tmp_id ) WITH cte AS ( SELECT 1 AS tmp_id FROM dual ) SELECT tmp_id FROM cte;
Using bind variables with dynamic SELECT INTO clause in PL/SQL
In my opinion, a dynamic PL/SQL block is somewhat obscure. While is very flexible, is also hard to tune, hard to debug and hard to figure out what’s up. My vote goes to your first option, EXECUTE IMMEDIATE v_query_str INTO v_num_of_employees USING p_job; Both uses bind variables, but first, for me, is more redeable and … Read more
return resultset from function
If you need a result set and a ref cursor won’t do with a datatype called sys.anydataset. i.e what you seem to want is a pipelined function, but of course with a regular pipelined function you need to define the output structure, which in your case isn’t static. Enter anydataset. this type allows us to … Read more
No more data to read from socket
Generic advice for troubleshooting “No more data to read from socket” errors. These errors are usually caused by another serious error, such as an ORA-600 error. A problem so serious that the server process crashed and could not even send a proper error message to the client. (Another common reason for these errors is a … Read more
Why cannot I create triggers on objects owned by SYS?
You should not be creating any objects in the SYS schema. That user is part of the Oracle database management system, and changing its schema is likely to break your database. Certainly it could invalidate your Oracle Support contract (if you have one). From the documentation: “The administrative account SYS is automatically created when a … Read more
Oracle RAC and sequences
Exactly what do you mean by “ordered” in this context? By default, each node in the cluster has a separate cache of sequence numbers. So node 1 may be handing out values 1-100 while node 2 is handing out values 101-200. The values returned from a single node are sequential, but session A on node … Read more
How to call Oracle MD5 hash function?
In Oracle 12c you can use the function STANDARD_HASH. It does not require any additional privileges. select standard_hash(‘foo’, ‘MD5’) from dual; The dbms_obfuscation_toolkit is deprecated (see Note here). You can use DBMS_CRYPTO directly: select rawtohex( DBMS_CRYPTO.Hash ( UTL_I18N.STRING_TO_RAW (‘foo’, ‘AL32UTF8’), 2) ) from dual; Output: ACBD18DB4CC2F85CEDEF654FCCC4A4D8 Add a lower function call if needed. More on … Read more
Subtracting Dates in Oracle – Number or Interval Datatype?
Ok, I don’t normally answer my own questions but after a bit of tinkering, I have figured out definitively how Oracle stores the result of a DATE subtraction. When you subtract 2 dates, the value is not a NUMBER datatype (as the Oracle 11.2 SQL Reference manual would have you believe). The internal datatype number … Read more
Curious issue with Oracle UNION and ORDER BY
This doesn’t really answer the question, but it seems to be a parser bug (or ‘feature’) rather than a language requirement. According to My Oracle Support, this seems to have been raised as bug 14196463 but closed with no resolution. It’s also mentioned in community thread 3561546. You need a MOS account, or at least … Read more