Formatting email id strings using regexp
with testdata as (select ',[email protected], [email protected], [email protected],' as e from dual union all select '[email protected],, [email protected], [email protected],[email protected],' from dual) select TRIM(',' FROM REPLACE(REPLACE(e,' '),',,',',')) from testdata; [email protected],[email protected],[email protected] [email protected],[email protected],[email protected],[email protected] ...
sapinst giving database connection error
database,installation,oracle10g,database-connection,sap
Just in case anyone faces the same issue, the problem lies with profile files, in profile files- Check for parameter named "SAPDBHOST" in DEFAULT.PFL. Entry should be as SAPDBHOST = if it doesn't exit add hostname of the database instance. Also, do check DEFAULT.PFL if the file name is "DEFAULT"...
oracle sql loader maximum size of a expression
move the select statement to a function and use select myfunction(params) from dual instead
Using Group by with a table
sql,sql-server,oracle,oracle10g
SELECT cities, COUNT(*), MAX(lastupdate) lastupdate FROM yourTable GROUP BY cities ORDER BY lastupdate GROUP BY gets the distinct cities. COUNT(*) will count the rows in that group, and MAX(lastupdate) will get the latest lastupdate for each city....
Why I can't compare dates?
First, your date comparison is too complicated. If h.time is an internal date format (which it should be), then just do: WHERE h.time BETWEEN DATE '2015-05-07' AND DATE '2015-06-07' Another very important issue with your query is that the WHERE clause is turning the LEFT JOIN into an INNER JOIN....
How to_date function with sysdate
Your code select TO_CHAR(sysdate, 'YEAR')-365 FROM DUAL; doesn't work because you try to make an mathematical operation on string value TO_CHAR(sysdate, 'YEAR') which is not properly. This will work select EXTRACT(YEAR from sysdate)-365 FROM DUAL; result 1650 but it doesn't make sense, so maybe you need this select EXTRACT(YEAR from...
Raw array lenght
The following set serveroutput ON DECLARE datos RAW(100); BEGIN datos := utl_raw.Cast_to_raw('test'); dbms_output.Put_line('The raw variable has a lenght of ' ||utl_raw.Length(datos)); END; / will return 4 As you did not assign any value to it, the length is still null. Declaring a variable, identify the maximum limit to it. However,...
OracleBulkCopy from CSV - Can not insert null column has value
c#,oracle,csv,oracle10g,bulkinsert
You can try oracle transaction commit. Maybe this helps to you. using (OracleConnection connectiontodb = new OracleConnection(databaseconnectionstring)) { connectiontodb.Open(); using (OracleBulkCopy copytothetable = new OracleBulkCopy(connectiontodb)) { OracleTransaction tran = connectiontodb.BeginTransaction(IsolationLevel.ReadCommitted); try { copytothetable.ColumnMappings.Add("TBL_COL1", "TBL_COL1"); copytothetable.ColumnMappings.Add("TBL_COL2", "TBL_COL2"); copytothetable.ColumnMappings.Add("TBL_COL3", "TBL_COL3");...
XML to String works propertly on one database, but not on second
java,xml,database,oracle,oracle10g
Problem solved. Problem lies in java classes. What I was supposed to do was export java classes, java sources and java resources via datapump from other database (not even from SYS scheme) and - in this incorrectly working database - delete all this stuff (for particular scheme) and load dmp...
Why do I get PLS-00302: component must be declared when it exists?
You can get that error if you have an object with the same name as the schema. For example: create sequence s2; begin s2.a; end; / ORA-06550: line 2, column 6: PLS-00302: component 'A' must be declared ORA-06550: line 2, column 3: PL/SQL: Statement ignored When you refer to S2.MY_FUNC2...
select rowid Oracle
This seems to be a Toad setting to hide the pseudocolumn: In your data grid, mouse-right-click - SELECT COLUMNS Enable 'ROWID' You also have - Toad - View - Options - Data Grids - Data - Display - 'Show ROWID in editable grids' It's displayed in SQL*Plus, SQL Developer, and...
Pl/Sql open cursor problems
database,plsql,oracle10g,cursor
Will these open cursors cause any memory leak issue ? No if you don't leave them opened after using (for explicit cursors), when opening a cursor, oracle creates a context area, PL/SQL controls the context area through a cursor which holds the result returned by a SQL statement. and...
Reading XML element occurrences using PL SQL
oracle,plsql,oracle10g,xmltable,oracle-xml-db
I didnt run it but as far as i see below code should work ; DECLARE vs_Xml VARCHAR2(32000):= '<INPUT> <A> <B>1</B> </A> <A> <B>2</B> </A> </INPUT>'; vx_ParameterList XMLTYPE; vx_Parameter XMLTYPE; vn_ParameterIndex NUMBER; vs_Key VARCHAR2(64); vs_XPath VARCHAR2(255); vs_Value VARCHAR2(10000); BEGIN vx_ParameterList := xmltype(vs_Xml); vn_ParameterIndex := 1; vs_XPath := '/INPUT/A'; WHILE vx_ParameterList.existsNode(vs_XPath...
Sub Sorting in Oracle
To get a RANK per state you need PARTITION BY: RANK() OVER (PARTITION BY CONTINENT, CNTRY, STATE ORDER BY poulation DESC) If there's a city twice with a state you might switch to ROW_NUMBER instead. And because this is a small table you might consider calculating the rank dynamically within...
Get separate count for each condition within group
sql,oracle,group-by,oracle10g,aggregate-functions
You can use sum with the case expression to get conditional count: select processed_by , sum(case when priority = 1 then 1 else 0 end) as P1 , sum(case when priority = 2 then 1 else 0 end) as P2 from agreement_activity group by processed_by P.S. If you don't care...
Mandatory relationship m:n
database,oracle,database-design,oracle10g,powerdesigner
You would need to understand the reasons for producing a conceptual, logical and physical model. A Conceptual Model is a representation of the area of interest in a form that is understood by users. It will consist of classes of entities with attributes and the business rules regarding these. As...
oracle external table get selected columns from the file
If you have delimited fields in your file that you want to ignore, just specify them in the field list clause with a dummy field name, and don't include them in the table column list. This will ignore the first two fields in the file: create table tmpdc_ticket( SERVICE_ID CHAR(144),...
How to get difference amount with in the same table?
U can try using case statement as follow Select serNo, ( SUM(CASE accorder_ when 1 then balance else 0 end) - SUM(case.accorder_ when 2 then balance else 0 end)) as finalAmount from table name group by serNo ...
How do I list all tables in a schema having specific column in oracle sql?
You could use USER_TAB_COLS view. For example, SQL> select table_name, column_name, data_type from user_tab_cols where column_name ='DEPTNO'; TABLE_NAME COLUMN_NAM DATA_TYPE ---------- ---------- ---------- DEPT DEPTNO NUMBER EMP DEPTNO NUMBER SQL> You could try, select * from user_tab_cols where column_name in ('STARTTIME_DATE', 'STARTTIME'); ...
Why the Time is missing?
sql,oracle,plsql,oracle11g,oracle10g
The time is not "gone". That format is just the default. You need to set the NLS_DATE_FORMAT for the default value: ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH:MI:SS'; or whatever format your want. Or, change your query to format the date field using the to_char function select to_char(sysdate, 'MM/DD/YYYY HH:MM:SS') from dual;...
VBA/Excel: Not accepting SQL subclauses
excel,vba,excel-vba,oracle10g,common-table-expression
You have to write nested select statement a bit differently inside VBA. Try select * from ( select start_value, country from MYTABLE where country = '840' ) ORDERED ...
Joining on a varchar to a char does not work in JPA (oracle)
java,oracle,jpa,oracle10g,varchar2
What I finally did was to use @Subselect. @XmlRootElement @XmlAccessorType(XmlAccessType.FIELD) @javax.persistence.Entity @Table( schema="SECURITYDBO", name = "XREFERENCE") @Subselect("SELECT " + " ,cast( trim(aCharColumn) as varchar(100) ) AS aCharColumn" + // NEED TO TRIM SO THAT JPA INNER JOINED BETWEEN 2 tables WHICH IS A VARCHAR AND THIS ONE THERE WILL NOT...
FRM-40403: A calling form has unapplied change. Save not allowed
You have a form A (where you made some changes) and then called form B. If you want to commit the changes in form B you get the error above. You need to do a post instead of a commit in form B. Then the changes will be validate and...
Combining rows to create two columns of data
Here is one of the options. Since you are on 10g you can make use of partition outer join(partition by() clause) to fill the gaps: with DCodes(code) as( select 'A' from dual union all select 'B' from dual union all select 'C' from dual ), DGlobal(code, value1) as( select code...
Left Outer Join - ORA-00933: SQL command not properly ended
LEFT OUTER should be 2 words, not LEFT_OUTER.
How to select actual year in Oracle Date?
sql,oracle,oracle10g,oracle-sqldeveloper
You can pull it from sysdate SELECT employee_name FROM employees WHERE EXTRACT(year FROM datemp) = EXTRACT(year FROM sysdate) ...
exporting db objects for version control
sql,oracle,oracle10g,ddl,database-metadata
but how to pull that information object wise. Pass the parameters properly. You could then customize your output. DBMS_METADATA.GET_DDL (object_type, object_name, object_owner) For example, to get the METADATA for all the tables in the user SCOTT. SQL> select dbms_metadata.get_ddl('TABLE',t.table_name, 'SCOTT') from USER_TABLES t; DBMS_METADATA.GET_DDL('TABLE',T.TABLE_NAME,'SCOTT') -------------------------------------------------------------------------------- CREATE TABLE "SCOTT"."DEPT" (...
How to REPLACE any form of the word null using decode in Oracle?
sql,oracle,oracle11g,oracle10g
SQL Fiddle Oracle 11g R2 Schema Setup: CREATE TABLE SENTENCES ( sentence ) AS SELECT 'Null' FROM DUAL UNION ALL SELECT 'null' FROM DUAL UNION ALL SELECT 'NULL' FROM DUAL UNION ALL SELECT ' Null' || CHR(9) FROM DUAL UNION ALL SELECT NULL FROM DUAL UNION ALL SELECT '{NULL}' FROM...
How to check if there exist only one record for a certain Id
I have solved this by using ans by @davegreen100 in comment SELECT comp.companyid, count(distinct emp.employee_id), FROM Tbl_Company comp , Tbl_Employee emp WHERE emp.company_id = comp.company_id AND emp.company_id = 1234 GROUP BY comp.companyid This will give me the count of employees per company...
How can I Delete row from table with self reference foreign key
Drop the constraint, delete the rows you would like to delete and at the end re-create the constraint. However if you delete a row which was referenced by another rows, you should delete the child rows as well, otherwise you won't be allowed to recreate the constraint at the end...
ORA-1691: unable to extend lobsegment
database,oracle,oracle11g,oracle10g,ddl
You can extend a tablespace by adding an additional datafile to it or by extending an existing one. Since you currently seem to have a convention of uniformly sized files, I'd just add another one: ALTER TABLESPACE "ABC" ADD DATAFILE '/ora/db/user/abc5.db' SIZE 4194304000; This can be done with the database...
substr function for substract the column value basis of another column in oracle
oracle,oracle11g,oracle10g,oracle-sqldeveloper
Here's a couple of solutions that may or may not help you, based on the sketchy information you have provided. If they don't, then you will need to edit your question to provide more a more detailed explanation of what you're after! with sd as (select '01-CEDAPR' a, 'AB_52MM_01-CEDAPR' b...
Why data from the servlet is not inserted into sql database?
You should learn to interpret stack traces. The first line of your stack trace means that the Java runtime was not able to find the class oracle.jdbc.driver.OracleDriver. This is the Oracle JDBC driver class. So get the Oracle driver JAR and put it in your web application's WEB-INF/lib folder. By...
Can't convert timestamp to date
To truncate the time part of a DATE or TIMESTAMP value you should use the TRUNC function. By default it truncates the time part, returning a date with a time of midnight. Depending on your database version you can directly truncate the timestamp, or you may have to cast it...
“ORA-00932 inconsistent datatypes expected - got CLOB” on varchar2 field
c#,linq,visual-studio-2010,asp.net-mvc-4,oracle10g
After a long journey, what resolved my problem was updating Oracle Developer Tools for VS to its newest version (ODTwithODAC121021). Hope this will help make the world a better place. :)...
How to know if DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY was set to false for a Trigger?
Justin's answer seems to work for Oracle 11, however those columns are not available in Oracle 10, which is what I am using. In Oracle 10 the DBMS_DDL.IS_TRIGGER_FIRE_ONCE function returns the value of the flag for the trigger. DECLARE isFiring BOOLEAN := false; BEGIN isFiring := dbms_ddl.IS_TRIGGER_FIRE_ONCE('S1', 'MY_TRIGGER'); IF isFiring...
Comma seperated column without XML path
sql,oracle,oracle10g,string-aggregation
Since you are on Oracle 10g version, you cannot use LISTAGG. It was introduced in 11g. And please DON'T use WM_CONCAT as it is an undocumented feature, and has been removed from the latest release. See Why does the wm_concat not work here? For 10g, you have following string aggregation...
How to fetch the list of errors for invalid objects in Oracle 10g
You could query [DBA/ALL/USER]_ERRORS. It describes current errors on all stored objects (views, procedures, functions, packages, and package bodies) owned by the current user. Chose which view to query, depending on the privileges you have: DBA_ : All objects in the database ALL_ : All objects owned by the user...
while opening the page getting error there is no row at position 0
Try this Error Says that Your are accessing a row which is Not present so, Always Check For whether Row exists in DataSet/DataTable using RowCount protected void Page_Load(object sender, EventArgs e) { if(!Page.IsPostBack) { Label1_pl.Text = Session["UserID"].ToString(); string sqlstr = "select zzfname from sap_empmst where pernr = '" + Label1_pl.Text...
Passing in a list of ids to several queries
In SQL Plus, SQL Developer etc.: SQL> define list = 1,2,3 SQL> select * from x where id in (&list.); ID ---------- 1 2 3 ...
Why does regexp_replace replaces nothing with something?
This is the problem: \s|($) That represents an alternating match that will end up replacing the end of string anchor with the new text. I think you want this instead: (\s|$) ...
What datatype in Oracle would map to a Java long?
The Java long primitive ranges from -263 to 263 - 1. In Oracle NUMBER(19) covers the same range with the same precision....
How to find a table name which's column name consist cl_
ALL_TAB_COLS would give you the required details. For example, I add a new table T to SCOTT schema, with column name as EMP_ID, I expect only 1 row in output for column name like 'EMP_%' Let's see - Edit Forgot to ESCAPE underscore. SQL> CREATE TABLE t(EMP_ID NUMBER); Table created....
ORA-04052: error occurred when looking up remote object [email protected]_LINK Oracle 11g to 10g
oracle11g,oracle10g,linked-server
You need to apply patchset 10.2.0.3 in the 10g database.Kindly refer this link for the similar issue Kindly download the patch set from metalink Patch 5337014 from metalink...
Improving my query efficiency - reducing full table scans?
Oh, the infamous count() in a subquery. You want to use exists, not count: SELECT c.* FROM Car c WHERE EXISTS (select 1 FROM CarEvent ce where ce.car_id = c.id AND ce.carEventType = 'CRASHED') AND NOT EXISTS (select 1 FROM CarEvent ce where ce.car_id = c.id AND ce.carEventType = 'FIXED');...
how to use like clause for a value saperated with comma in oracle
I have used regexp_like instead of only like and it worked for me.
Delete row with confirmation from Oracle table
java,sql,oracle,oracle11g,oracle10g
executeUpdate() method of PreparedStatement gives You the number of rows deleted.If no rows have been deleted by the query You get 0.I think that's the easiest solution. If You need to know which rows have been deleted You can user "Returning" clause, that will give You rows deleted. Regards...
Oracle Sql Query : How Pivot Function Sum() in Packages?
You need to unpivot, not pivot, but 10g doesn't support either natively. There are ways to do it, as shown here with decode, but in this case since you already have CTEs you could just union three queries against those instead: /* Result */ SELECT 'EXCLUDE_VAT' AS CALCULATE_VAT, SUM(TOTAL_AMOUNT) AS...
Select distinct lines by a field
You can just use aggregation: select name, max(surname) as surname from table t group by name; You can also do something similar with analytic functions: select t.name, t.surname from (select t.*, row_number() over (partition by name order by name) as seqnum from table t ) t where seqnum = 1;...
ORA 12154 - unable to connect from toad/SQLplus
ORA-12154: TNS could not resolve service name my_db= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=local.db) (PORT=1521) ) (CONNECT_DATA= (SID=SCMPROD) ) ) The error is about incorrect service name. I see your tnsnames.ora doesn't have the service_name, rather you have mentioned SID. Probably this is the cause of the error. You could edit...
Oracle 10g users are expired
database,oracle10g,sqlplus,ora-12560
Login to database server as the Oracle sofrware owner (or, in case of Windows, as user who is member of ORA_DBA group), set ORACLE_HOME and ORACLE_SID environment variables, then login as: sqlplus / as sysdba You should get logged in without having to provide a password. Hope that helps....
How to spool cells to different files?
The pseudo code does not make it clear what you want, but here goes .... This method works by generating a script from the data to re-query the data with embedded spool commands for each distinct NAME. set lines 200 set trimspool on set head off pages 0 -- may...
Query to get number of % sign = length of string in the next row in Oracle 10g
This would normally be considered an issue for presentation logic, not database logic. However, one option would be to use union all, and then length and rpad to get the correct number of % signs. You'd also need to establish a row number to keep the order together. Here's one...
Insert a Sequence for every query execution in Oracle
oracle,plsql,oracle11g,oracle10g
Sure, you can do this several ways. First, you could create a sequence and select the next number from that before running your batch. The next would be to simply: SELECT Max(Job_Run_ID) + 1 INTO New_Job_ID FROM employee_master; Then do your job loop running your inserts: INSERT INTO employee_master (New_Job_ID,...
Oracle: do Truncates maintain Atomicity within a transaction?
database,oracle,oracle11g,transactions,oracle10g
Once a table has been truncated in a transaction, you cannot do anything else with that table in the same transaction; you have to commit (or rollback) the transaction before you can use that table again. Or, it may be that truncating a table effectively terminates the current transaction. Either...
How to identify previous scheduled date and next scheduled date among many dates in scheduled table via procedure
oracle,oracle11g,oracle10g,oracle-sqldeveloper
For the previous date, get maximum date that is less than today. And for the next date, find the minimum date that is greater than today. Previous: SELECT max(scheduled_date) from schedule_table where scheduled_date < sysdate; Next: SELECT min(scheduled_date) from schedule_table where scheduled_date > sysdate; Extract the day or date from...
group by alias Oracle
The UNION should remove any duplicates. If it doesn't then you should check the data -- maybe you have extra spaces in the text columns. Try something like this. select state1 as state,TRIM(desc1) as desc from table where id=X Union select state2 as state,TRIM(desc2) as desc from table where id=X...
Oracle date between two dates
sql,oracle,oracle10g,between,date-arithmetic
Sysdate - 4 must be the first where h.Date_Created between (Sysdate - 4) and (Sysdate - interval '15' minute) ; ...
How to arrange columns based on a particular order of values present in the rows
sql,oracle,oracle11g,oracle10g,sql-order-by
A more generic solution which would work with more than three columns is to unpivot the columns into separate rows, work out what order the values should be in, and pivot them back into columns. From 11g you can unpivot with select * from your_table unpivot (sal for month in...
Use 'group by' on one column but get data of multiple columns
@Alex Poole got it right: I was not only missing the equivalent of PIVOT code in 10g, but also ROW_NUMBER(). So the answer to my problem becomes as following: select tab1.group_name, MAX(CASE WHEN tab1.rank_number = 1 THEN tab1.album_name ELSE NULL END) AS ALBUM_1, MAX(CASE WHEN tab1.rank_number = 2 THEN tab1.album_name...
SQL datable query to insert multiple column values
Use two insert statements instead insert into emp values (id, name,phone) values (1,'lee','23455'); insert into emp values (id, name,phone) values (1,'lee','67543'); or If you want to store both the values in single row insert into emp values (id, name,phone) values (1,'lee','23455,67543'); Here table is not normalised. You either need to...
OCI8 doesn't enable on windows 8 + php 5.2.5
No way without instantclient. Just copied files from instantclient 10.2 and paste them into, C:/XAMP/APACHE/BIN C:/XAMP/PHP/EXT Restarted Apache and it is awesome....
How can i get data on 1 hour basis
sql,oracle,datetime,oracle10g,date-arithmetic
You might: select ... from ... where to_char(date_time,'HH24') = '17' Place an index on the expression to_char(date_time,'HH24') and you may get improved performance, but it depends on the distribution of values within the data. If 1/24th of the rows meet the condition then an index might help, but if it's...
Find all child nodes for any parent node in Oracle 10g
oracle,plsql,oracle10g,plsqldeveloper
You need to restrict it to the same company ID with another PRIOR clause, and move the specific company ID you're looking for into the START WITH clause: SELECT id FROM TABLE_NAME START WITH ID = 3 AND COMPANY_ID = 12 CONNECT BY PARENT_ID = PRIOR ID AND COMPANY_ID =...
I am trying to retrieive data from two tables SQL Oracle 10g
if your date_id is without time part, you can drop the trunc. the trunc drops the timepart if it would be there. you select just one month, grouping and selecting the dateparts year and month seams useless since you already know them. select extract(day from t.date_id) as tday , ta...
How to remove null value form multiple column in sql select statement result set
You could work along WITH T1 AS ( SELECT val, ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY id) rn FROM Table1 ), T2 AS ( SELECT val, ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY id) rn FROM Table2 ), T3 AS ( SELECT val, ROW_NUMBER() OVER (PARTITION BY NULL ORDER...
Check: Oracle Data Access Components for Windows 7
oracle,oracle11g,oracle10g,oracle-sqldeveloper,oracle9i
Oracle Universal Installer is a Java application that can handle complex requirements. ODAC release for 9.2.0.7.0 will work fine with Windows7 32 bit release. If you face any problem during installation change the compatibility setting to "WindowsXP ServicePack 2". Hope it helps.
Counting multiple columns with the same criteria
Remove the where clause and use Conditional Aggregate instead, which will help you to count the rows only when data is T. select t1.name, count(case when t1.alpha ='T' then 1 end), count(case when t1.bravo='T' then 1 end), count(case when t1.charlie='T' then 1 end) from t1 group by name order by...
Oracle : Count entity created last 12 month
I assume you want to include a zero count for months where there is no row in the table. For this you first need to create a list of possible months and then use an outer join. with month_numbers as ( select level as month from dual connect by level...
importing a csv file into oracle database using java
java,eclipse,csv,oracle10g,indexoutofboundsexception
For starters you have pstmt.setString(1,nextLine[0]); pstmt.setString(1,nextLine[1]); pstmt.setDouble(2,Double.parseDouble(nextLine[2])) Note you have repeated parameter one twice and if exception is coming from database then this is very likely to be the cause. more over IndexOutOfBoundsException in Java is a runtime exception .As stated in the doc Thrown to indicate that an index...
is possible to build a warehouse application using Oracle Application Express oracle 11g?
you can access pl sql from apex applications and apex itself generate a web frontend. yes you can also create a login page and the separate pages cann be secured by user specific access rights
Oracle exp function numeric overflow error
sql,oracle,plsql,oracle11g,oracle10g
As Boneist said, Oracle NUMBER datatype has its scale from -84 to 127. The EXP function is defined to take in a NUMBER and return a NUMBER. So EXP function can't be directly used. There are some workarounds though. Binary_double can store 1.79E308. So for numbers upto E308, you can...
Large numbers in Oracle
sql,oracle,oracle10g,largenumber
As the comments are hinting at, you are hitting the limits of Oracle's number precision. In other words, Oracle has a limit of significant digits it can handle (even in memory) before being forced to perform some rounding and start losing precision, which is exactly what you observed. Although your...
How to fetch a part of string upto a chracter?
Please try below query: select SUBSTR(name,(INSTR(name,':')+1)) || ' ' || SUBSTR(name,1,(INSTR(name,':'))-1) from employees; hope above query will resolve your issue....
Using a subquery within PLSQL conditional logic; error PLS-00405
You cannot use a SELECT statement within a PL/SQL IF condition in that way. However you can do this: IF ('HOLD' member of p_queue_type) THEN ... ...
Dynamic Select query In Oracle Stored Procedure
From the documentation: If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then execute_immediate_statement never executes. You don't have an into clause as part of your execute immediate. However, fromthe OUT parameter, it looks like what you really want is to open the ref cursor for...
oracle join depth while updateing table
sql,oracle,oracle11g,oracle10g
If I were you, I would work out the results that I wanted in a select statement, and then use a MERGE statement to do the necessary update.
SQL Loader : Not enough values
sql,oracle,oracle10g,sql-loader
Change your control file entries for the timestamp columns to look like: RESPOND_BY_DATE EXPRESSION "CAST(SYSDATE AS TIMESTAMP)" A DATE and a TIMESTAMP are not the same datatypes. You just need to convert SYSDATE to a TIMESTAMP. ORA-00947 is being raised when the process tries to put a SYSDATE into the...
LEFT OUTER JOIN with CONNECT_BY_ROOT
sql,oracle10g,connect-by,hierarchical-query
I still don't know what happened (still not working) but this solution seems to work fine in both cases: SELECT IDBRANCH, ENDNODEIDCONTENT, IDCATEGORY, IDPARENTCATEGORY, TAXYJ.IDCONTENT, TCT.CONTENTNAME AS ENDNODECONTENTNAME, CATEGORYNAME, LVL FROM ( SELECT CONNECT_BY_ROOT TAXY.IDCATEGORY AS IDBRANCH , CONNECT_BY_ROOT TAXY.IDCONTENT AS ENDNODEIDCONTENT , TAXY.IDCATEGORY , TAXY.IDPARENTCATEGORY , TAXY.IDCONTENT , TAXY.CATEGORYNAME...
Sql - get previous order which uses the item
This should do: select max(order_id) from order_item where order_id <299 and item_id =3 ...
Concurrency issue on a database select statement
java,jdbc,oracle10g,transaction-isolation,weblogic8.x
You can use SKIP LOCKED for your purpose.With this when Session 1 locks the row, Session 2 can skip it and process the next. I believe it was there in 10g also, but never documented.
How to select between two dates without using year to get birthday
if the type of dob field of your table is date,the following query will help you: select * from tbl_contact e where e.dob between to_date('26-APR','DD-MON') and to_date('03-MAY','DD-MON') i hope to help you...
Researching the newest enties
the below query will give you information about tablespaces select * from user_tablespaces or this one mentioned by devagree100 select * from dba_data_files and try this I got it from this site hope it might help select round((sum(bytes)/1048576/1024),2) from V$datafile; select round((sum(bytes)/1048576/1024),2) from V$tempfile; Take the sum of this two...
Oracle SQL 10g query for creating comma separated records
sql,oracle,oracle10g,string-aggregation
You can just include the manager_name in the innermost query and the middle query, and then repeat the aggregation mechanism for that column too. I've change the column and table aliases to be a bit more consistent (and I've also simplified your case expression): SELECT essay_id, LTRIM ( MAX (SYS_CONNECT_BY_PATH...
Oracle 10G : How to convert multiple column values into rows
You must query the table thrice and glue the results together: select sname, sub1 as sub, mar1 as marks from mytable union all select sname, sub2 as sub, mar2 as marks from mytable union all select sname, sub3 as sub, mar3 as marks from mytable; ...
ORA-00911: invalid character
The problem is with the columns reading MANUFACTURER_NUM VARCHAR2(30) FOREIGN KEY, You forgot to specify what table these columns refer to. You to that with a REFERENCES clause: create table cd_title ( cd_num number(4) primary key, title varchar2(30), manufacturer_num REFERENCES MANUFACTURER, cd_type REFERENCES CD_TYPE, acquired_date date, original char(1) ); Alternatively,...
Finding second highest salary in all the companies
sql,oracle10g,oracle-sqldeveloper
Use an analytical function like dense_rank to generate a numbering. In this case dense_rank or row_number are more suitable than rank, because the latter can have gaps in the numbering in which case you won't get any result at all. This happens when there are two persons with the same...
Oracle Add Date Column to Existing Table (default to sysdate new rows only)
You need to first add the column without a default: alter table mytable add date_created date default null; and then add define the default value: alter table mytable modify date_created default sysdate; ...
Django Oracle Database - Varchar2 Vs NVarchar2
django,django-models,oracle10g,django-orm,django-oracle
NVARCHAR2 will store the data with 16-bit characters, and VARCHAR2 will store the data with 8-bit characters. The difference that NVARCHAR2 will store unicode characters like Arabic characters, but it'll consume double size more than VARCHAR2.
How to select ranges in a range of record in oracle
sql,oracle,oracle10g,gaps-and-islands
This is a nice way, fancy name "Tabibitosan method" given by Aketi Jyuuzou. SQL> WITH data AS 2 (SELECT num - DENSE_RANK() OVER(PARTITION BY status ORDER BY num) grp, 3 status, 4 num 5 FROM t 6 ) 7 SELECT MIN(num) 8 ||' - ' 9 || MAX(num) range, 10...
DB Index not being called
oracle,performance,oracle11g,oracle10g,database-performance
You are comparing a varchar column with a numeric literal (245643). This forces Oracle to convert one side of equality, and off hand, it seems as though it's choosing the "wrong" side. Instead of having to guess how Oracle will handle this conversion, use a character literal: SELECT * FROM...