Questions tagged [oracle11g]
5845 questions
1
votes
4
answer
671
Views
Dynamic Insert — ORA-00923: FROM keyword not found where expected
I'm trying to implement a simple procedure that calculates the proportion of distinct values of a given field and stores the result in an auxiliary table.
CREATE TABLE TEST_DATA_QTY_AND
(
TABLE_ID VARCHAR2(30),
FLD_ID VARCHAR2(30),
MEASURE NUMBER(1, 2),
DATA_T DATE DEFAULT SYSDATE,
NOTES VARCHAR2(25...
1
votes
1
answer
25
Views
oracle regexp - partition value at columns and rows
Oracle DB
I have value at one cell - example:
ID TEXT
1 textText text №1-2-3 texttext, text,text №1 - 1000
textText text №1-2-3 texttext, text,text №2 - 1030
2 textText text №1-2-3 texttext, text,text №3 - 1031
textText text №1-2-3 texttext, text,text №4 - 1032
textText...
1
votes
2
answer
51
Views
How to manage concurrent access in an sql procedure
I have create an sql procedure in my oracle database that I will call later on on my c# code,
the main goal of this procedure is to loop over entries and make some treatment in a condition that every entry should be treated only once.
The body of the procedure looks like that.
FOR item IN
( SELECT *...
0
votes
1
answer
6
Views
Multiple Regex not using level
I want to separate the strings one by one and become row.
I've query its only get the arithmetic operator not for the number
SELECT
REGEXP_SUBSTR('141*47+2/5','[^0-9]+| [0-9]+',1,LEVEL) AS FORMULA
FROM DUAL
CONNECT BY REGEXP_SUBSTR ('141*47+2/5','[^0-9]+| [0-9]+',1,LEVEL) IS NOT NULL
input
141*47+2/...
0
votes
0
answer
7
Views
Remove duplicate in CONNECT BY LEVEL query
I've tried find all days between 2 dates and I have a query here
with tmp as(
select 1 sno, to_date('20181227', 'YYYYMMDD') curr_date, to_date('20181231', 'YYYYMMDD') curr_date2 from dual
union all
select 2 sno, to_date('20181227', 'YYYYMMDD'), to_date('20181231', 'YYYYMMDD') from dual
)
SELECT sno,...
1
votes
3
answer
36
Views
Oracle Sql update multiple records at once?
Is it possible to Update multiple records in one sql query?
so update the status of all printers with id A and ip 1.1.1.1 to working (true), and all other as not working (false)
So basically combining these two query together into one query:
update printers set status = true ,row_update_date =sysda...
1
votes
3
answer
50
Views
Join period of time, grouping by id
I have rows with periods of time that intersect for the same user. For example:
-------------------------------------------------------------
| ID_USER | START_DATE | END_DATE |
-------------------------------------------------------------
| 1 | 01/01/2018 08:00...
1
votes
3
answer
81
Views
Trying to know which tables are executed in the oracle external application
I wonder if this could be possible or not.
I am using TOAD, connected to an oracle database (11g) and i have access to the oracle E-BUSINESS-SUITE application.
Basically, i want Toad to trace what sql are being executed by the oracle E-BUSINESS-SUITE application
I have this query:
SELECT nvl(ses.use...
1
votes
2
answer
58
Views
Why is this query taking a long time to finish?
I have a function. When I use this function in my query it is always taking a long time like 1 to 2hrs to finish. Here's my first query ...
SELECT GET_DAYSNS_SF(CONTRACT_ID)
FROM CONTRACT_TEMP;
and function...
CREATE OR REPLACE FUNCTION GET_DAYSNS_SF
(CONTRACT_ID IN NUMBER)
RETURN DATE
AS
CONTRAC...
1
votes
2
answer
31
Views
Pass employeeno as an argument to procedure and modify salary to that employee?
ORA-06550: line 4,column 3
PLS-00905: object HR.procempno is invalid
-- I have tried this code --
create or replace procedure procempno(p_no in number)
IS
v_salary number(10,2);
begin
v_salary:=(select salary from emp where employ...
1
votes
2
answer
32
Views
Q: How to retrieve date with time(hours,minutes,seconds) from a 19 digit number in Oracle
I want to get date and time from a 19 digit number in Oracle and could not find any solution for this big numbers. The time should be in the format HH24:MI:SS. I am able to retrieve the date but unable to get the time.
**Example Numbers to get the date and time:
1190205161950369000 , 119020609285914...
1
votes
2
answer
56
Views
How to copy data from TableA to TableB with new partitions?
I have TableA that has hundreds of thousands of rows and is still increasing in size. With no partitions, the speed has decreased very noticeably.
So I made a new table called TableB made columns exactly like (both name and type) TableA in Oracle SQL Developer. (TableA and TableB are in the same da...
1
votes
1
answer
40
Views
Understanding UPDATE with CASE statement in ORACLE evaluating all rows
I'm trying to understand the results of a query I wrote in Toad for Oracle 12.1.
My test table contains 3 columns as so:
EMPID LOGGEDIN CURRENT_MONITOR
00001 1 1
00002 1
00003 1
00004 2
The following SQL will update the table so that the value...
1
votes
2
answer
30
Views
How can I use rank function to solve this?
I have this data:
and I want to this result:
So I ve tried this:
SELECT
text_area,
ordered_area,
RANK () OVER (ORDER BY text_area) ranked_area
FROM
(
SELECT '1200 AA' text_area ,1 ordered_area
FROM
DUAL
UNION ALL
SELECT '1200 AA' ,2
FROM
DUAL
UNION ALL
SELECT '1200 BB' ,3
FROM
DUAL
UNI...
1
votes
1
answer
19
Views
How to insert data from multi tables to one table in Oracle?
I'd a trouble with inserting data from 3 table:
A (id, name), B (id, name), C (id, name). They have the same field like that.
How can I insert data from 3 tables above into table D (id, name)?
3
votes
4
answer
107
Views
When establishing access to data on a remote database, are views or synonyms preferred?
Consider a scenario where you have two databases: On database A you need to access data on database B.
You could create a view on database A that selects from the table on database B.
CREATE OR REPLACE VIEW Demographics.Employees(
employee_id, employee_dept
)
AS
SELECT employee_id, employee_dept
F...
0
votes
0
answer
7
Views
In term of performance is mormot rest connection is better than Oracle direct connection?
i work in a company on an a huge application made with delphi seattle(VCL) using direct connection to Oracle database server (2-tier), one of the experts proposed to migrate to the 3-tier architecture using mormot (or another library/components).
One of his arguments that the new architecture (3-tie...
1
votes
1
answer
73
Views
updatable join view
I am currently trying updatable_join_view using hr scheme(EMP, DEPT Table).
I tried to join to tables named as emp and dept. And I also made view as like this.
create or replace view v4
as
select e.empno, e.ename, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno;
So, I made view above lik...
1
votes
1
answer
64
Views
HQl query to retrieve data from Pipe row
I had tried to fetch data which is returning as Array in oracle which is written in a function of oracle as pipe row.
When i tried using native query like select ar_0th_element from table(fn_name(arg)).
the above code works fine in native sql query.
how to do the same thing using hql.
1
votes
1
answer
56
Views
How execute a member procedure on a table of objects record?
I'm currently working on Oracle11g and I've come to grips with Table of objects.
Classic example:
create or replace TYPE T_person AS OBJECT
(
name varchar2(20),
surname varchar2(20),
MEMBER PROCEDURE Set_Sir
);
/
create or replace TYPE BODY T_person AS
MEMBER PROCEDURE Set_Sir IS
BEGIN
name := '...
1
votes
0
answer
98
Views
Investigate if ActiveRecord Connection Pool in Ruby process leak Pool
I have configured database.yml as below
development:
adapter: oracle_enhanced
host: 10.80.70.58
port: 1521
database: TTSDB
username: TTSADMIN
password: xxxxx
encoding: utf8
pool: 20
I have spawned ruby processes which give me error for connection timeout
[ExceptionHandler] Exception ActiveRecord::Co...
1
votes
0
answer
560
Views
Spark job dataframe write to Oracle using jdbc failing
When writing spark dataframe to Oracle database (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit), the spark job is failing with the exception java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection. The scala code is
dataFrame.write.mode(...
1
votes
1
answer
250
Views
Firedac and Oracle : works at runtime but not at design time
Hello to all Stackoverflow readers.
I'm trying to establish a connexion from a Delphi application to an Oracle database on my network, using FireDac.
At runtime, a 'simple' :
procedure TForm1.Button1Click(Sender: TObject);
begin
with FDConnection1.Params do
begin
DriverID := 'Ora';
Database := '(DES...
1
votes
1
answer
393
Views
Error connecting to AWS RDS Oracle Service Instance
I have spun up an aws rds instance for Oracle 11g db. I have also configured VPC & Security Groups.
Now I'm trying to connect to the db using
sqlplus64 '[email protected](DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbinstancedns) (PORT=1521))(CONNECT_DATA=(SID=sidofdb)))'
I'm on Ubuntu 16.04 & have set up 'SQLPl...
1
votes
0
answer
161
Views
Oracle pivot for in () all values
I have a working query using the PIVOT function but I am wondering if I have to define all values I want to see as column headers. Here is what I have:
select * from
(
SELECT ID, product, notebal
from accounts
where (accounts.effdate = ( Select MAX(EFFDATE) FROM accounts))
)
PIVOT
(
SUM(notebal)...
1
votes
1
answer
250
Views
Connect Oracle cloud DB in JAVA using SSH with Local port forward
I want to connect to an Oracle db in JAVA which hosted in cloud. I have server private key and remote details to connect.
I can establish SSH connection to server with private key authentication and created SSH tunnel. Now using this tunnel, I can connect to remote server and forwarded session to lo...
1
votes
1
answer
695
Views
Error Importing a .dmp file in AWS RDS Oracle DB
I have followed the AWS Importing Data into RDS guide.
I have copied the .dmp file from Source Oracle Instance on another machine to the DATA_DUMP_DIR on RDS Instance.
Now I am trying to import it using the code below:
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job...
1
votes
1
answer
37
Views
SQL script, how can i execute the following script?
I have to execute this script . The problem in that in SQL Developer i cant run it,since some operators are not recognized .
P_jobgroup_id :
'GDWH2MYGDWH-GDWH2MYGDWH' CORE load finished
'GDWH2MYGDWH-GDWH2MYGDWH_IF' IF finished
'GDWH2MYGDWH-GDWH2MYGDWH_INIT' IF or CORE started
decl...
1
votes
0
answer
29
Views
Why insert into consume less time than select
I have a curiosity. I had to create a select to retrive data from a table which contains milions of data and to add some filters. I needed the data only for a specific period between start_date and current_date.
I needed this data to calculate some requirement.
First I tried to use the with clause...
1
votes
1
answer
242
Views
Update query using JPA Query Annotation to Oracle 11g Hangs
I am trying to update a simple row using Query Annotation with JPA to Oracle 11g
This is my code:
@Transactional()
@Modifying
@Query(value='UPDATE Irregularities SET IRREST_ID = 0 WHERE IRREGS_ID = 1006', nativeQuery = true)
int updateState();
This was working fine but for some reason is not working...
1
votes
0
answer
132
Views
Performance Impact of Using RLS
Generic_Schema has one common table, GENERIC_TABLE. The primary key of GENERIC_TABLE is COL1 and COMPANY_ID.
Company_id Col1 Col2 Col3
-------------- ---- ---- ----
ALL_COMPANIES A B C
Comp1 A B1 C1
Comp3 A B3 C
Specific Company Schema
Schema1 =>Comp1
Schem...
1
votes
2
answer
47
Views
printing messages “ZERO NOT DIVISBLE”, after exceptions
Working with Oracle 11g, in the DBMS exit from SQL Developer and in the output script I would like to see some results after creating the below function.
CREATE OR REPLACE
FUNCTION FN_DIVISION(NUMBER1 NUMBER, NUMBER2 NUMBER)
RETURN NUMBER
IS
RESULTS NUMBER;
V_RESULT NUMBER;
ERR_NUM NUMBER;
ERR_MSG V...
1
votes
1
answer
43
Views
Oracle users in many to many relationship
I am creating a database that would represent a simple videogame inventory where a user would have ownership of certain items. I am looking to form a many to many relationship between the dba_users view and my items table to form a table called userItems.
I've done some research and found that the...
1
votes
0
answer
35
Views
Oracle - hierarchical bottom up query with time costraint
I have a table that stores the relationships between organizational units.
These relations can change in the time, so it's perfectly normal to have an object thet refers to a parent yesterday, and to another parent today.
A costraint in DB ensures that an object can refers only to a parent in a give...
1
votes
2
answer
38
Views
How to split column names attributes and length separate using SQL
I want to split oracle table column names,attributes and length separately for to prepare Data sheet. Currently I am doing manual by copying into Note pad and doing split. This is good for small tables but for large columns it is taking more time. Is it any way do program or in SQL query itself.Plea...
1
votes
0
answer
22
Views
Efficient method to identify or skip overlapping records from a large table
I have a CTE based on a large table (over 3.5 million rows). The CTE contains start and end dates for attendance for a student attending a base (a base is a school or other establishment) during a term. For each student-base-term combination there should usually be only one row. If there is more tha...
1
votes
1
answer
43
Views
Returning multiple values using function causing multiple query runs
We have kiosks for customers to check their purchase volume for two different categories of items. They will input their mobile number, which will send an OTP to their mobile numbers and they will input it back to authenticate, the system has to check the data and display for them. As a developer, t...
1
votes
0
answer
39
Views
Oracle (ORA-02270) : no matching unique or primary key for this column-list error: when referencing a materialized view
I am getting the ORA-2270 error when I try to create some Oracle tables.
The error is quite simple from what I have read so far:
It happens when the columns I reference in the foreign key do not
match a primary key or unique constraint on the parent table.
The problem is that my tables have severa...
1
votes
0
answer
100
Views
JPA Named query having join with result of aggregate function not working
I have a query which works fine with SQL Developer but when I change it to Named query, it shows me exception. The join association path is not a valid expression. I have googled it a bit and found out that JPQL/JPA does not support subquery in from clause. I tried to modify my query also but all in...
1
votes
2
answer
33
Views
Oracle SQL sequence
I made a sequence in Oracle and when I delete a row (making active 0) in java desktop, deleted row became invisible in desktop. But it stays in database. That's why, when I try to insert a new row in desktop, the sequence considers deleted id too and id sequence id desktop becomes 1, 3, 7 etc. How c...