Hot answers tagged

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 ...


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 ...


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() ...


4

If you issue an update statement on your table that effectively updates the data to the same value that is already there, then the database does update the row, so the SQL%ROWCOUNT is accurate even though the new value is the same as the old value. You can use a flashback versions query to see that database has indeed updated the record: select t.* , ...


3

The problem is in the NLS_NCHAR_CHARACTERSET = UTF8. I simulated the same table in a different database with NLS_NCHAR_CHARACTERSET = AL16UTF16. The describe statement shows the correct length as defined while creating table. UTF8 can be 1, 2, 3 or 6 bytes / character, any AL32UTF8 character that is 4 bytes will be stored as 2 time 3 bytes in UTF8. If you ...


3

You can simply do it in PL/SQL in a loop: begin for c in (select distinct profile from dba_profiles) loop execute immediate 'alter profile ' || c.profile || ' limit password_life_time unlimited'; end loop; end; /


3

The presentation of the data should not be the determining factor in how the data is stored - especially in the case of a primary key. That would be like saying that all dates need to be stored in the database as strings so that a particular date format could be displayed. To make the select max(pk) + 1 work in a system with more than one user is to ...


3

As andba77 suggested, it sounds like a problem with remote password file. First you need to determine if you have a password file. It should be on the database server at $ORACLE_HOME/dbs/orapw. Second is the setting of the init parm 'remote_login_passwordfile'. If it is set to 'none', no remote logins as sysdba will be allowed. You can read more about ...


3

You won't find 'remote_login_passwordfile' file under $ORACLE_HOME/dbs. It is not a file, it is an initialization parameter. That's why I posted a link to the documentation. What's under $ORCLE_HOME/dbs is the passwordfile itself, not the initialization parm that specifies how the password file is used. Now that I see the screen shots, I see you have no ...


3

Ok, that error (ORA-00600 [6856]) is related to corruption. Normally a DBA would go to My Oracle Support, follow the notes (for example ORA-600 [6006] ORA-600 [6856] During Startup Instance, Followed by Termination by SMON (Doc ID 549000.1)), identify the affected objects then try to repair them, recover corruption, or restore a valid backup. If you ...


3

The second expression is syntactically invalid (at least on its own, it could be a valid part of some other expression). sal := sal + 300; assigns a value to the local variable sal that is 300 more than the current value of that variable. sal = sal + 300 does not change the value of sal. It compares sal to sal + 300 and evaluates to a boolean. But it ...


3

The problem is with the below bind variable substitution: AND ( sv.project_id = DECODE(:' 1368','0', project_id, :'1368') AND TO_CHAR (date_in, 'dd-MM-yyyy') = DECODE(:' ', '0', date_in, :' ') AND vehicle_no = DECODE(:' GJ-06-0Z-9202', '0', vehicle_no, :' GJ-06-0Z-9202') )) Your application should pass the value ...


3

Expressions in SELECT evaluated after filter[s] in WHERE applied. So if phones for records with RATING <1 can be converted to number, you don't have errors. Simplified example : SELECT CASE WHEN n1=1 THEN 'a' ELSE 'b' END AS test_str FROM ( SELECT '1a' AS n1 , 0 AS n2 FROM dual UNION ALL SELECT '1' AS n1 , 1 AS n2 FROM dual ) x WHERE n2 =0 -- gives an ...


3

According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug. Oracle suggest to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the checking can be disabled by: SQL> alter system set "_fix_control"='17376322:OFF'; Alternatively an interim patch for this fix (patch ...


2

I'm quite sure that this is a problem of SQL Developer... Retry to desc table after issuing: alter session set NLS_LENGTH_SEMANTICS='CHAR'; Is correct now? If not try to desc the table using sqlplus on the database server bypassing the Oracle Listener: >export NLS_LANG=AMERICAN_SWITZERLAND.AL32UTF8 >export ORACLE_SID=<YOURINSTANCESID> ...


2

This line: if job_id<>jobidnew THEN is not part of the UPDATE which precedes it, so the identifier job_id does not refer to the column by that name but to a separate (undeclared) variable. The way to test whether the UPDATE did anything or not is to test the implicit cursor attribute SQL%notfound, as in if SQL%notfound THEN RAISE e; The ...


2

You can use TRUNC (date) function which returns date with the time portion of the day truncated to the unit specified by the format model. Syntax: TRUNC(date, fmt) or TRUNC(date). Where fmt can be 'DAY', 'YEAR','HH','MI'. For more format model please refer: ROUND and TRUNC Date Functions Example: SELECT 1 FROM sample_table WHERE ...


2

One option with SQL Server is Heterogeneous Database Replication. This works with SQL Server as a source and destination being either of: The following non-SQL Server databases are supported as Subscribers to snapshot and transactional publications: Oracle for all platforms that Oracle supports. IBM DB2 for AS400, MVS, Unix, Linux, and ...


2

if you do not have problems with your disks and the controlfile is corrupted you can do the following: start the database in nomount (startup nomount), then check the output of the command show parameter control (run the command in sqlplus). If the command shows more than one control file, you can overwrite D:\APP\FROZEN\ORADATA\ORCL\CONTROL01.CTL with ...


2

You could just use %_99_% instead of %99%. % means 0 ore more characters _ means exactly 1 character So %_99_% means at least 1 character before and after 99, and anything before and after. SQL> select * from t1; C1 -------------------- 1991 1199 9911 SQL> select * from t1 where c1 like '%_99_%'; C1 -------------------- 1991


2

According to the statistics provided user I/O wait time is 6172 out of db time 8383. When you see "user I/O" as a major wait event, SQL tuning is the best answer, specially adding missing indexes. Since the plan shows it is using full table scan you can add indexes in the column used in where clause.


2

Try creating the following index: CREATE INDEX s_contact_idx_001 ON s_contact_lupd_lastupdby_name ( last_upd, last_upd_by, last_name ); You should change the name to correspond to your naming convention and make sure that it is unique.


2

the query does not return 43 rows but 1 row that contains the number 43 the execution time of a query does not depend on the number of rows it return but on the number of rows it inspects. from your query plan you can see your query makes a full table scan. This means it reads the table from begin to the end and reads all 16 millions of rows of the table ...


2

This is relatively easy to do, you just need to add some analytic values to your original data set to get it: with grps as ( select t1.* , trunc((row_number() over (partition by Center_Code, PaperCode order by Roll_No)-1)/4) grp_id , roll_no - row_number() over (partition by Center_Code, PaperCode order by Roll_No) rng_id from your_data t1 ), ...


2

I'm a big fan of MERGE. You can try the following; MERGE INTO table1 main USING ( SELECT TKey, a, b FROM table2 ) subsel ON (main.TKey = subsel.TKey) WHEN MATCHED THEN UPDATE SET main.ColA = subsel.a, main.ColB = subsel.b Here's a quick example; SQL> SELECT * FROM maintab; TKEY COLA COLB ...


1

One syntax that can be used is the following. Instead of the syntax that is not working: update table1 set cola = a, colb = b where tkey in (select u.tkey, a, b from ... u ... -- some tables here, one of them is "u" ) ; This will do what is wanted (note that the where should be there, otherwise all the rows of table1 will be ...


1

Since this is easier to elaborate on in an answer than in a comment..... We tended to install our instances to /inst/<instance-name>. I leave the instance naming scheme up to you. If you make it too similar than technically you have introduced a security risk as the instance names are eaiser to guess. Then you could so something like the following: ...


1

Follow the below steps to get it done: Open the registry with regedit. Always back up the registry before making changes. Navigate to the key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\oracle_home_name. There will be a key called: ORA_SID_AUTOSTART. SID is your database SID. This key should be set to TRUE. If not the server starts but does not start the ...


1

I don't really understand why you're trying to pivot anything, so maybe I'm missing your point. But try this: select id, name , regexp_replace(metadata, '.*"a":"([^"]+)".*', '\1') m_a , regexp_replace(metadata, '.*"b":"([^"]+)".*', '\1') m_b from foo; Doesn't rely on the order of the JSON attributes to always be the same. It's not completely safe - ...



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