I’ve been looking into this problem further, and you simply need to grab all the appropriate DLL’s from the same downloaded version of ODP.Net and put them in the same folder as your Exe file, because ODP.Net is fussy about not mixing version numbers. I’ve explained how to do this here: http://splinter.com.au/using-the-new-odpnet-to-access-oracle-from-c Here’s the gist … Read more
31-DEC-95 isn’t a string, nor is 20-JUN-94. They’re numbers with some extra stuff added on the end. This should be ’31-DEC-95′ or ’20-JUN-94′ – note the single quote, ‘. This will enable you to do a string comparison. However, you’re not doing a string comparison; you’re doing a date comparison. You should transform your string … Read more
Run this SQL: select * from v$version; And you’ll get a result like: BANNER —————————————————————- Oracle Database 10g Release 10.2.0.3.0 – 64bit Production PL/SQL Release 10.2.0.3.0 – Production CORE 10.2.0.3.0 Production TNS for Solaris: Version 10.2.0.3.0 – Production NLSRTL Version 10.2.0.3.0 – Production
Instead of node_name=”Geometric Vectors \& Matrices” use node_name=”Geometric Vectors ” || chr(38) || ‘ Matrices’ 38 is the ascii code for ampersand, and in this form it will be interpreted as a string, nothing else. I tried it and it worked. Another way could be using LIKE and an underline instead the ‘&’ character: node_name … Read more
To see all tables in another schema, you need to have one or more of the following system privileges: SELECT ANY DICTIONARY (SELECT | INSERT | UPDATE | DELETE) ANY TABLE or the big-hammer, the DBA role. With any of those, you can select: SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = ‘TABLE’ AND … Read more
You must put all columns of the SELECT in the GROUP BY or use functions on them which compress the results to a single value (like MIN, MAX or SUM). A simple example to understand why this happens: Imagine you have a database like this: FOO BAR 0 A 0 B and you run SELECT … Read more
BEGIN FOR cur_rec IN (SELECT object_name, object_type FROM user_objects WHERE object_type IN (‘TABLE’, ‘VIEW’, ‘MATERIALIZED VIEW’, ‘PACKAGE’, ‘PROCEDURE’, ‘FUNCTION’, ‘SEQUENCE’, ‘SYNONYM’, ‘PACKAGE BODY’ )) LOOP BEGIN IF cur_rec.object_type=”TABLE” THEN EXECUTE IMMEDIATE ‘DROP ‘ || cur_rec.object_type || ‘ “‘ || cur_rec.object_name || ‘” CASCADE CONSTRAINTS’; ELSE EXECUTE IMMEDIATE ‘DROP ‘ || cur_rec.object_type || ‘ “‘ || … Read more
Let us assume the database character set is UTF-8, which is the recommended setting in recent versions of Oracle. In this case, some characters take more than 1 byte to store in the database. If you define the field as VARCHAR2(11 BYTE), Oracle can use up to 11 bytes for storage, but you may not … Read more
Use the V$SESSION view. V$SESSION displays session information for each current session.
You use the sql%rowcount variable. You need to call it straight after the statement which you need to find the affected row count for. For example: set serveroutput ON; DECLARE i NUMBER; BEGIN UPDATE employees SET status=”fired” WHERE name LIKE ‘%Bloggs’; i := SQL%rowcount; –note that assignment has to precede COMMIT COMMIT; dbms_output.Put_line(i); END;