Hot answers tagged

51

The query is syntactically correct SQL even if table_b does not have a name column. The reason is scope resolution. When the query is parsed, it is first checked whether table_b has a name column. Since it doesn't, then table_a is checked. It would throw an error only if neither of the tables had a name column. Finally the query is executed as: select a....


10

I am not proud of publicly writing something like this. Sample + data: CREATE TABLE donaldduck ( SURNAME VARCHAR(100), TOWN VARCHAR(100) ); CREATE TABLE daisyduck ( SURNAME VARCHAR(100), TOWN VARCHAR(100) ); CREATE TABLE goofy ( something number(1), SURNAME VARCHAR(100), TOWN VARCHAR(100) ); INSERT INTO DONALDDUCK ( SURNAME, TOWN ) VALUES ( '...


10

Anonymous PL/SQL blocks don't start with IF. The above code should be in between a begin and end; at least. EXISTS is an SQL function, it can't be used in PL/SQL like that. Try something like this: set serveroutput on declare c number; begin select count(*) into c from my_table where rownum = 1; if c != 0 then dbms_output.put_line('has rows'); ...


9

I'm a DBA on Oracle and SQL Server. The confusion is due to a mismatch of semantic and an extra level in SQL Server's hierarchy but there's more than that! Note that we will talk only about single instance database in both instances (pun intended). Instances In Oracle, one instance will have an amount of memory allocated to it (SGA/PGA) and processes ...


8

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have not finished your SQL statement, and it starts counting the lines. 2 is actually a line number, it is the 2nd line of your SQL statement. If you type nothing here ...


8

I think people were having trouble understanding your question due to the table structure which is so bad it seems designed to give you a headache. As Balazs Papp indicates very little can be implemented that will scale or not look like something hacked together. However there are solutions that can be done in PL/SQL. A pipelined table function will end ...


7

Have you seen this? https://community.oracle.com/thread/889338?start=0&tstart=0 It states you can only exchange partitions from a partitioned table to a non-partitioned table, or vice-versa. You'd need to temporarily exchange the partition into an interim, non-partitioned, table, then into the target partitioned table. There are some interesting ...


7

As mentioned in the comments, Oracle doesn't like the square brackets around identifiers, and it also doesn't support TOP n clauses, however, you can work around that with a couple of simple changes: SELECT T1.*, (SELECT T3.SMN_DATEC-1 FROM (SELECT T2.DET_NUMBERA , T2.SMN_DATEC , row_number() over (ORDER BY ...


7

Because Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357 It means in order to determine whether subquery is correlated Oracle must try to resolve names in ...


6

Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias? The column item_cnt may not exist in the base tables (we believe you, we didn't go and search the linked documents.) But this part of the code: FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i ...


6

You could use Flashback to return the table data back to its original state. Is Flashback enabled? Database needs to be in archive mode: select log_mode from v$database; Should return "ARCHIVEMODE" Check if flashback is enabled: select flashback_on from v$database; Should return "YES" If "NO", you can enable flashback by runing: alter database ...


6

Often the RULE-hint helps when querying dictionary views. select /*+ RULE */ constraint_name,table_name from all_constraints where r_constraint_name in (select constraint_name from all_constraints where table_name='SUPPLIER'); But ...


6

First, join TABLE_2 and TABLE_3 using an inner join and additionally filtering on Value_A = 'a': SELECT t2.ID_1, t3.ID_A, t3.Value_A FROM TABLE_2 t2 INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A WHERE t3.Value_A = 'a' This will give you the following result set: ID_1 ID_A Value_A ---- ---- ------- 1 b a 4 d a Now use the ...


6

Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle? No. When you create a primary key or unique constraint in SQL Server, a unique index to support that constraint is created automatically, with the same keys. Which seems to imply that there is a way of specifying what index should be used for a Primary Key. No. The ...


6

An ORDER BY clause will always add a sort operation if there is no usable index to retrieve the data in order. For larger sets of data the data being sorted may be written to disk adding additional processing. For smaller result sets where indexes are being used, there may be no requirement for a sort step. If sorting is required, the database will have ...


6

Is Cluster index exist in Oracle database since I read in some blogs Yes there is. It is called "index organized table" (IOT) - which in my opinion is the better name as it makes it absolutely clear that the index and the table are the same physical thing (which is the reason why can only have one clustered index in SQL Server) If yes, please let me ...


6

For single node Oracle RDBMS installations, an SCN is never duplicated. With older versions of Oracle RAC, Oracle 9i for example, it was possible that two transactions, running on separate RAC nodes, could end up with the same SCN. Duplicates happened only in rare situations where high transaction rates on the RAC nodes was coupled with a large value for ...


6

Wrong If syntax. And exists not allowed here. Try this way: declare cnt number; begin select count(*) into cnt from my_table; if cnt != 0 then dbms_output.put_line('has rows'); else dbms_output.put_line('no rows'); end if; end;


5

Please find the below 4 queries and run these queries from system database. //For seeing Full user details SELECT profile FROM dba_users WHERE username = 'SYSTEM'; //This query is used to change the password life time to unlimited ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; //This query is used to chagne the default password. alter user ...


5

This will delete anything three years older than January 1st of this year. DELETE table_name WHERE change_date < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36);


5

As always: it depends. However in most cases the default values are adequate. The business requirement of handling 50 concurrent inserts (transactions) alone does not imply the need for setting INITRANS 50. Are they really that concurrent? Will the duration of the transactions overlap each other so they really happen at the same time? All the 50? INITRANS ...


5

It gives an error: ORA-01790: expression must have same datatype as corresponding expression From the documentation for UNION: The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character). Tested at SQLFiddle


5

You have to use Service Names to connect to Pluggable Databases in 12c. It's just one of those things that can trip you up, I guess! SQL Plus connecting OK probably threw you.


5

define my_schema_name = 'SCHEMA_1'; CREATE OR REPLACE PROCEDURE "&my_schema_name".updateDBUSER( p_userid IN DBUSER.USER_ID%TYPE, p_username IN DBUSER.USERNAME%TYPE) IS BEGIN UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid; COMMIT; END;


5

This is a feature that's controllable by a parameter. From the documentation: Multi-byte Character Sets Ratio Suppression This feature optionally suppresses the ratio expansion from SQL Server database to Oracle database involving multi-byte character set. By default, Oracle gateways assume the worst ratio to prevent data being truncated or ...


5

The part "within any time period of 6 months" makes things quite tricky. It is very easy to check this for a specific period: select cid, sum(amount) from transfer where trans_date >= date '2014-11-01' and trans_date < date '2015-06-01' group by cid having sum(amount) >= 1000; But this is not what you want. You want a "rolling running ...


5

It is really easy to log in as another user, just use the proxy user feature that is available since 8i. Unfortunately it does not work with SYS, but works with any other user. Basically you can grant the privilege to users to connect as another user. SQL> alter user system identified by Oracle123; User altered. SQL> alter user hr identified by ...


5

This is a Gap & Island problem. Plenty of sites talk about it if you Google it. Here is just the first link I clicked among many others: Solving Gaps and Islands with Enhanced Window Functions Here is how the query works: YYYYMM is a varchar and does not give consecutive numbers. Therefore it first changes them to proper date format. then ROW_NUMBER() ...


5

I have found the following about Clustered Index: Data is stored in the order of the clustered index. Only one clustered index per table. When a primary key is created a cluster index is automatically created as well. You dug a couple of hours. You should have noticed that all the above facts are for SQL Server and not Oracle. Tables (...


5

Your question's not brilliantly clear, but if you want the 2nd, 3rd and 4th characters in the column city, in the table zip, then use SUBSTR: SELECT SUBSTR(city,2,3) FROM student.zip; REGEXP_INSTR returns the location of a match, not a string, so you don't want to use that. REGEXP_MATCH would be the one to use, but SUBSTR is simpler.



Only top voted, non community-wiki answers of a minimum length are eligible