|Dropping Temporary Tables in SQL Developer|
Well this is how global temporary tables work in Oracle.
They are always present (e.g. in the system catalogs) and you only
need to create them once (not every time you use them).
I'm having to manually drop each table, which is really
You don't have to drop them - that's the whole point of a global
Each session has its own copy of the data in that table.
|How to combine in one condition HAVING and AND in Oracle|
Having begins a clause similar to where (except applied after
aggregation (e.g. sums) instead of before), so you can only have it
once in the query like this:
having ((sum(col)<=30 and type=4) or (sum(col)>=30 and type=5))
Also you need a group by clause specifying the columns that the query
group by type, ...
|microsoft odbc driver manager the specified dsn contains an architecture miss match between driver and application|
I bet you are using the 64 bit ODBC driver. Remove the DSN from 64 bit
and add it in the 32-bit (odbcad32.exe).
|Repository for Oracle Coherence Maven plugin|
Typically a full install of Coherence will install the desired
plug-ins into your maven repository. However if you'd like to get
them directly using maven, you can now do so by using the Oracle Maven
Repository at: maven.oracle.com
You'll have to register for this repository (free of course), but once
you've done that you can get the latest releases from there.
Hope this helps.
|Cannot install Oracle 12c on Windows 8.1 - Error in Process ... perl.exe|
Think this may be it. The new Windows user for the account was
oracle-user. When I tried this with a username OracleUser the
I also switched off the AntiVirus - not sure which fixed the problem
A colleague pointed me to this blog which gave a little more info.
Hope this helps someone
|How to select records between two timestamps in oracle|
SELECT * FROM myTable
WHERE recorded BETWEEN to_date('19700101', 'YYYYMMDD') +
and to_date('19700101', 'YYYYMMDD') +
1414794711050/24/60/60/1000 converts milliseconds to a number of days
(24 hours in a day, 60 munites in a hour, 60 seconds in a minute)
to_date('19700101', 'YYYYMMDD') + [N days] = new date (N days later
|Error on Partition over row number|
It's not the ROW_NUMBER, it's the *, Add an alias to the subquery:
select T.*, -- here
row_number() over (partition by DIS_COL order by COL_2) as
row_number --ORDER BY FIELD DETERMINES WHICH ROW IS THE FIRST ROW AND
THUS WHICH ONE IS SELECTED.
)T as rows -- and here
where row_number = 1
AND CRITERIA_COL = 'CRIT_1'
OR CRITERIA_COL_2 = 'CRIT_2';
|How to auto increment the id in oracle?|
you can model it easily with a sequence and a trigger:
Create sequence sequence_name start with value increment by value
minvalue value maxvalue value;
First, let’s create an emp table with primary key constraint on
SQL> create table emp ( emp_id number(10),
constraint pk_emp_id PRIMARY KEY(emp_id)
Now let’s create a sequence.
|select top 3 highest number in each group in a table|
If you're using Oracle (as you marked your question), this will work:
select Lesson, Year, Name, Count,
ROW_NUMBER() OVER (PARTITION BY Lesson, Year, Name ORDER BY
Count DESC) as RN
WHERE RN <= 3;
It won't work in MySQL (as you created it in sqlfiddle), but I'm sure
you get the idea to do the same there.
|calling a function from a stored procedure in oracle|
voucher_no := Func_Voucher_No_Gen (vc_comp_code,vou_dt,'JN') ;
should do the job.
select Func_Voucher_No_Gen (vc_comp_code,vou_dt,'JN') into voucher_no
|Oracle TIMESTAMP Comparison with TO_TIMESTAMP function|
Because CURRENT_TIMESTAMP is the name of an Oracle function the
database prefers to use the function instead of your column - and
thus, since CURRENT_TIMESTAMP (the function) is always greater than a
time in the past the predicate returns TRUE for every row in the
table, and thus every row gets counted. If you put a table alias in
your query and qualify the column name with the alias you should ge
|how to enclose the oracle csv output header in double quotes?|
You can not go with double quoted alias name as per my best knowledge
in sql. But you can definitely go with single quoted alias name. If
you will try below
when userid is not null then '"A"'
when userid is null then '""'
as " ""H"""
You will have ORA-03001: unimplemented feature error in sql.
But you can get single quoted alias with below query
|Date query giving two results in sqlplus & oracle sql developer|
This is because you're comparing two different datatypes in your where
clause. In your query, the field date is not DATE I presume. It is
either DATETIME or TIMESTAMP.
To overcome the problem, use to_date on both sides in the where clause
select id,date from tableName where
TO_DATE(date,'yyyy-MM-dd')>=TO_DATE('2015-09-25' , 'yyyy-MM-dd');
Hope this helps !!
|oracle replace view during query|
Since SQL cursor is open Oracle fetches all data from rollback
segment. So even you drop table in another session your first session
will continue to fetch rows from dropped table. Same for view.
|How to format strings to numbers with apostrophe as group separator in Oracle|
select to_char(1234.56, '999G999D99',
'NLS_NUMERIC_CHARACTERS=''.''''') from dual;
With a quote operator:
select to_char(1234.56, '999G999D99',
q'[NLS_NUMERIC_CHARACTERS='.'']') from dual;
|DBMS Job scheduler for materialized view|
Could you please let me know where I am going wrong?
Try to write your statement like that instead:
View list is specified using a string -- so using single quotes. I
don't know if spaces are relevant in the view list. In doubt, I've
removed them too.
|Oracle query fine tuning|
Make sure you have an index on: "PC0".CAMPAIGNID and on:
Make sure your SGA is set high enough. Without knowing a lot
information about the server and database it's hard to provide
guidance other than make sure the SGA is large enough.
|Hibernate Sequence Generator is not Consistent|
The sequence generator is consistent. It's only task is to generate
unique integer values, nothing else. And why would you be bothered by
As mentioned this behaviour is caused by oracle caching,
pre-allocating, the sequences numbers (20 by default). The ID column
is an surrogate/artificial primary key and only used to uniquely
identify the row, no information whatsoever should be
|error "ORA-00932: inconsistent datatypes: expected - got CLOB" select join multiple tables|
WITH t AS
,MAX(y.seq) AS seq2
,MAX(y.extern_order_status) AS extern
FROM t_order_demand x
JOIN t_order_log y
ON x.order_id = y.order_id
JOIN p_catalog_status z
ON z.status_code_sc = y.extern_order_status
|pl/sql query which takes deptno from emp as input and return ename, sal all the columns from dept table|
The error is because of oracle can't fetch cursor in both rec type and
normal type together.
I changed your query and it's working NOW!
CREATE OR REPLACE PROCEDURE PROC01(DNO IN emp.deptno%TYPE,
/*REC OUT DEPT%ROWTYPE,
NAME OUT EMP.ENAME%TYPE,
SAL OUT EMP.SAL%TYP
|How to know whether the created view is valid or not|
Maybe this helps:
WHERE object_type = 'VIEW';
or (for views in all schemas)
WHERE object_type = 'VIEW';
|Set select list value via PLSQL|
So it sounds like you have a select list generating correctly and you
you want to change the value that is selected when the page loads. For
that, go to the Source section of the select list item. You can drive
that based on another page item, like P44_PARENT_PK_ID, a query, or a
number of other options.
Make sure that the list of values for your select list will have the
source value in it.
|Inserting values with a CASE statement|
You can try this:
if INSERTING then
l_op := 'INSERT';
elsif UPDATING then
l_op := 'UPDATE';
if l_op in ('INSERT', 'DELETE')
or l_op = 'UPDATE'
and (nvl(:old.fied1, 0) != nvl(:new.fied1, 0)
or nvl(:old.fied2, 0) != nvl(:new.fied2, 0)
|Oracle PL/SQL Cursor in Trigger|
Ok, i found the error, in my cursor declaration in the WEHRE clause, i
had to remove
emp = :NEW.emp AND name = :NEW.name
because there's no such column in the table that im currently
|SQL command not properly ended help oracle|
select * into dumpfile from dual 'C:WWWsite.comfile.php' from
Syntax is definitely wrong here. You should be doing something like:
WHERE field = 'C:WWWsite.comfile.php';
|ORACLE PL/SQL - GRANT/REVOKE Privilege|
You cannot grant or revoke privileges in a trigger. It seems like you
just want to grant users the ability to INSERT and UPDATE(col4).
CREATE TABLE tableName(
GRANT INSERT ON tableName TO public;
GRANT UPDATE(col4) ON tableName TO public;
Of course, this only affects the privileges that other users have on
|ORA-02256: referencing foreign key|
You need to specify all the columns in both parent and child tables
create table EVENT_REGISTER
CONSTRAINT pk1Edate PRIMARY KEY (edate,Bookid),
CONSTRAINT fk2Bookid FOREIGN KEY (Bookid) references BOOK(Bookid),
CONSTRAINT fk3edate FOREIGN KEY (edate,Bookid) references Event
|Installing Oracle XE 11.2 on Centos 7|
a) Verify whether the host name, localhost.localdomain-efw is correct
and is resolving to VM's IP. If not replace with correct VM host name
or VM IP.
b) Verify whether the VM hostname and IP entry are in /etc/hosts file.
If not add entries.
c)edit ./bash_profile of oracle user, as follows:
(replace the content within tags <> for your environment)
# Oracle Settings
TMP=/tmp; export TMP
|Oracle: Trying to loop thru insert statement using dynamic list of table names|
This problem requires dynamic SQL. Bind variables can be used for
values but not for objects.
type array_t is varray(12) of varchar2(25);
array array_t := array_t('BT_ABANDONED_HIST', 'BT_BROWSED_HIST',
'BT_PURCHASED_HIST', 'CM_ABANDONED_HIST', 'CM_BROWSED_HIST',
'CM_PURCHASED_HIST', 'CM_PAGE_VIEWS_HIST', 'MB_ABANDONED_HIST',
'MB_BROWSED_HIST', 'MB_CARTED_HIST', 'MB_PAGE_VIEWS_H
|how to turn off sequence generation in oracle 11g|
Sequence properties are quite simple.
New sequence number is generated every time when you call nextval
function. If you inserting a row in a table, new number is generated
once per row. For example, you have new sequence:
insert into my_table (num_field) values (my_sequence.nextval);
You will get 1 in num_field.
insert into my_table (num_field1, num_field2)
|What's the difference between oracle server and client database|
It is your environment, your requirement, your decision, whether to
install Oracle as :
Choose from the options accordingly.
I think, you must also read about client-server model for a better
understanding. I don't have to look at the URL posted by you, all I
care is what you describe here in Stack Overflow. Anyway, my
suggestions will be seen as comments and not a
|identifying duplicate row sets|
, DENSE_RANK() OVER (ORDER BY inv_num) "MyRecords"
, COUNT(line_num) OVER (PARTITION BY po_num, inv_num ORDER BY
WHERE cnt > 1;
| INV_NUM | PO_NUM | LINE_NUM | MYRECORDS |
| 19782594 |
|insert into derived column with in same table|
Since you have a computed column you have to specify the columns you
insert into db (col, col2) values (10, 20);
a select * from db after the insert above would give you:
| COL | COL2 | CAL3 |
| 10 | 20 | 30 |
|Creating Oracle sequence that starts with alphanumeric|
Oracle only provides numeric sequences, but you can construct your
identifiers by converting to a string, e.g. 'INV' ||
Mind you, you might need more than 2 digits, depending on how many
records you're expecting.
|Oracle database error in symfony2 (doctrine). Is parameters.yml setup correctly?|
Checkout getEasyConnectionString method (it was called differently in
previous versions od doctrine as far as I know.). According to this
you have two options:
Configure connection using separate parameters.
Your parameters.yml file is missing database_servicename parameter you
So complete parameters.yml file:
|Why are panelGridLayouts overlapping?|
What would you expect it to do in such a case where there is basically
not enough space to show the fields in one of the sides?
ADF won't automatically shrink or eliminate fields in such a case.
|Need to get error/warning when building a PL/SQL package with Ant|
After some more searches and experimentation I found the solution.
Basically you have to use `WHENEVER SQLERROR EXIT WARNING SQL.CODE'
and capture the error by querying the USER_ERROR table. Based on
<exec executable="sqlplus" failonerror="true"
|Is this a PL/SQL bug?|
It looks like an Oracle bug. I am seeing the same thing on our
instance of Oracle Enterprise 220.127.116.11.0 (64bit).
After playing with it a little, it's pretty clear that the bug is in
the if condition: guid_ is null is evaluating to true even though it
contains a value.
My earlier comment regarding else no longer appears to be true. Either
I inadvertently changed the test script in some way or t
|EDT and EST timestamp sqlldr data load in oracle|
EDT ist not a valid timezone region, this is a time zone abbreviation.
Valid regions are these ones:
WHERE TZNAME = TZABBREV;
The reason for that you see by this query. EDT may represent several