A SELECT INTO statement will throw an error if it returns anything other than 1 row. If it returns 0 rows, you’ll get a no_data_found exception. If it returns more than 1 row, you’ll get a too_many_rows exception. Unless you know that there will always be exactly 1 employee with a salary greater than 3000, … Read more
The user and password are DEFINITELY incorrect. Oracle 11g credentials are case sensitive. Try ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; and alter password. http://oracle-base.com/articles/11g/case-sensitive-passwords-11gr1.php
The solution, finally: SELECT OrderNumber, InstallDate, CompleteDate, (TRUNC(CompleteDate) – TRUNC(InstallDate) ) +1 – ((((TRUNC(CompleteDate,’D’))-(TRUNC(InstallDate,’D’)))/7)*2) – (CASE WHEN TO_CHAR(InstallDate,’DY’,’nls_date_language=english’)=’SUN’ THEN 1 ELSE 0 END) – (CASE WHEN TO_CHAR(CompleteDate,’DY’,’nls_date_language=english’)=’SAT’ THEN 1 ELSE 0 END) as BusinessDays FROM Orders ORDER BY OrderNumber; Thanks for all your responses !
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
If your 200+ packages work as intended with “old fashioned” syntax, let it be. SQL will not start to perform better after migration to ANSI syntax – it’s just different syntax. All that being said, ANSI syntax is cleaner – you are not going to normal join if you forget (+) in some multi-column outer … Read more
What is the effect of placing the commit like this in program? The Oracle Documentation describes COMMIT as: Purpose Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement … Read more
TableA LEFT OUTER JOIN TableB is equivalent to TableB RIGHT OUTER JOIN Table A. In Oracle, (+) denotes the “optional” table in the JOIN. So in your first query, it’s a P LEFT OUTER JOIN S. In your second query, it’s S RIGHT OUTER JOIN P. They’re functionally equivalent. In the terminology, RIGHT or LEFT … Read more
Thanks to those who replied. After perusing your answers and the answers in the link supplied, I arrived at this solution: SQL> select REGEXP_SUBSTR(‘1,,3,4,5’, ‘(.*?)(,|$)’, 1, 2, NULL, 1) data 2 from dual; Data —- Which can be described as “look at the 2nd occurrence of an optional set of zero or more characters that … Read more
Answer is: It depends. In total your database has three time zones Your session time zone: SESSIONTIMEZONE This you can change by ALTER SESSION SET TIME_ZONE=… at any time. It is relevant for result of CURRENT_DATE LOCALTIMESTAMP CURRENT_TIMESTAMP It is also the target time zone when you do CAST({TIMESTAMP/DATE without any timezone} AS TIMESTAMP WITH … Read more
I had this issue and the fix was to make sure in tnsnames.ora the SERVICE_NAME is a valid service name in your database. To find out valid service names, you can use the following query in oracle: select value from v$parameter where name=”service_names” Once I updated tnsnames.ora to: TEST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS … Read more