w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
  Home » ORACLE » Page 1
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 time-consuming. You don't have to drop them - that's the whole point of a global temporary table. Each session has its own copy of the data in that table.

Categories : Oracle

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 groups by: group by type, ...

Categories : Oracle

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

Categories : Oracle

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

Categories : Oracle

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

Categories : Oracle

How to select records between two timestamps in oracle
SELECT * FROM myTable WHERE recorded BETWEEN to_date('19700101', 'YYYYMMDD') + 1414794711050/24/60/60/1000 and to_date('19700101', 'YYYYMMDD') + 1415399511053/24/60/60/1000; 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

Categories : Oracle

Error on Partition over row number
It's not the ROW_NUMBER, it's the *, Add an alias to the subquery: select * from ( 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. from MY_TABLE )T as rows -- and here where row_number = 1 AND CRITERIA_COL = 'CRIT_1' OR CRITERIA_COL_2 = 'CRIT_2';

Categories : Oracle

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 emp_id column. SQL> create table emp ( emp_id number(10), fname varchar2(25), lname varchar2(25), constraint pk_emp_id PRIMARY KEY(emp_id) ); Now let’s create a sequence.

Categories : Oracle

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 * FROM ( select Lesson, Year, Name, Count, ROW_NUMBER() OVER (PARTITION BY Lesson, Year, Name ORDER BY Count DESC) as RN from A )t 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.

Categories : Oracle

calling a function from a stored procedure in oracle
Direct assignment, voucher_no := Func_Voucher_No_Gen (vc_comp_code,vou_dt,'JN') ; should do the job. Or, select Func_Voucher_No_Gen (vc_comp_code,vou_dt,'JN') into voucher_no from dual;

Categories : Oracle

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

Categories : Oracle

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 select case when userid is not null then '"A"' when userid is null then '""' end as " ""H""" from tableName; You will have ORA-03001: unimplemented feature error in sql. But you can get single quoted alias with below query

Categories : Oracle

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 as below. select id,date from tableName where TO_DATE(date,'yyyy-MM-dd')>=TO_DATE('2015-09-25' , 'yyyy-MM-dd'); Hope this helps !!

Categories : Oracle

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.

Categories : Oracle

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;

Categories : Oracle

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: BEGIN DBMS_MVIEW.REFRESH(''m_view1,m_view2,m_view3'',''C'') Or BEGIN DBMS_MVIEW.REFRESH(''m_view1,m_view2,m_view3'',''CCC'') 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. Please note

Categories : Oracle

Oracle query fine tuning
Make sure you have an index on: "PC0".CAMPAIGNID and on: "Campaign".PYID 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.

Categories : Oracle

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 this behaviour? 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

Categories : Oracle

error "ORA-00932: inconsistent datatypes: expected - got CLOB" select join multiple tables
WITH t AS (SELECT x.order_id,x.customer_name,y.ncli,y.ndem2,y.ndem1,y.nd2,z.status_resume,y.nd1,yz.param ,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 JOIN t_order_demand_eai

Categories : Oracle

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! Procedure : CREATE OR REPLACE PROCEDURE PROC01(DNO IN emp.deptno%TYPE, /*REC OUT DEPT%ROWTYPE, NAME OUT EMP.ENAME%TYPE, SAL OUT EMP.SAL%TYP

Categories : Oracle

How to know whether the created view is valid or not
Maybe this helps: SELECT object_name, status FROM user_objects WHERE object_type = 'VIEW'; or (for views in all schemas) SELECT owner, object_name, status FROM all_objects WHERE object_type = 'VIEW';

Categories : Oracle

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.

Categories : Oracle

Inserting values with a CASE statement
You can try this: ... l_op aud.op%type; begin ... if INSERTING then l_op := 'INSERT'; ... elsif UPDATING then l_op := 'UPDATE'; ... end if; ... 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) ... ) then INSERT I

Categories : Oracle

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 inserting. Thank !

Categories : Oracle

SQL command not properly ended help oracle
select * into dumpfile from dual 'C:WWWsite.comfile.php' from filetables ; Syntax is definitely wrong here. You should be doing something like: SELECT * INTO dumpfile FROM filetables WHERE field = 'C:WWWsite.comfile.php';

Categories : Oracle

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( col1 INTEGER, col2 INTEGER, col3 INTEGER, col4 INTEGER ); 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 the table.

Categories : Oracle

ORA-02256: referencing foreign key
You need to specify all the columns in both parent and child tables while referring. create table EVENT_REGISTER ( CID number(6), Bookid number(5), edate date, CONSTRAINT pk1Edate PRIMARY KEY (edate,Bookid), CONSTRAINT fk2Bookid FOREIGN KEY (Bookid) references BOOK(Bookid), CONSTRAINT fk3edate FOREIGN KEY (edate,Bookid) references Event (edate,Bookid) );

Categories : Oracle

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 TMPDIR=

Categories : Oracle

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

Categories : Oracle

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. For query insert into my_table (num_field1, num_field2) values (my_sequenc

Categories : Oracle

What's the difference between oracle server and client database
It is your environment, your requirement, your decision, whether to install Oracle as : Server class Desktop class 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

Categories : Oracle

identifying duplicate row sets
Updated: SELECT inv_num , po_num , line_num , DENSE_RANK() OVER (ORDER BY inv_num) "MyRecords" FROM ( SELECT po_num , inv_num , line_num , COUNT(line_num) OVER (PARTITION BY po_num, inv_num ORDER BY NULL) cnt FROM testing ) WHERE cnt > 1; returns | INV_NUM | PO_NUM | LINE_NUM | MYRECORDS | |----------|----------|----------|-----------| | 19782594 |

Categories : Oracle

insert into derived column with in same table
Since you have a computed column you have to specify the columns you insert into: 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 |

Categories : Oracle

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' || TO_CHAR(invoice_num.NEXTVAL,'fm00') Mind you, you might need more than 2 digits, depending on how many records you're expecting.

Categories : Oracle

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 got. So complete parameters.yml file: parameters: database_driver: oci8 database_host: <ip> datab

Categories : Oracle

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.

Categories : Oracle

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 http://grokbase.com/t/ant/user/048v9ah8eq/compiling-pl-sql <target name="RunExec"> <exec executable="sqlplus" failonerror="true" errorproperty="exit.status" logError="true">

Categories : Oracle

Is this a PL/SQL bug?
It looks like an Oracle bug. I am seeing the same thing on our instance of Oracle Enterprise 11.2.0.3.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

Categories : Oracle

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: SELECT * FROM V$TIMEZONE_NAMES WHERE TZNAME = TZABBREV; TZNAME TZABBREV ====================== CET CET CST CST EET EET EST EST GMT GMT HST HST MET MET MST MST PST PST WET WET The reason for that you see by this query. EDT may represent several differen

Categories : Oracle




© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.