Questions tagged [oracle11g]

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...
M.C.
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...
SergeySmirnov
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 *...
monther zlatan
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/...
Utami Kusumadewi
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,...
hungtt
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...
user648026
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...
Jonathan Cobo
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...
programming_amazing
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...
AJ Tibayan
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...
Bruno
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...
Dhanesh Agrawal
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...
Jedo
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...
Probius
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...
Ersin Gülbahar
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)?
leebongee
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...
Jeromy French
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...
Soufiane Bra
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.
Muthukumar Raju
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 := '...
Fantazzini
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...
Vaibhav Dhoke
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(...
Abhishek Joshi
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...
Mathmathou
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...
Ajinkya Bapat
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)...
sleven
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...
Parthiban
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...
Ajinkya Bapat
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...
eda
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...
FlorentinaP
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...
Sebastian Moreno E
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...
Gaurav Soni
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...
Carlos Johnes
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...
Noah492
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...
Paolo Ursini
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...
userAnalyst
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...
Zajonc
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...
user3625561
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...
andcl
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...
Deepak Gangore
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...
Bolshevik

View additional questions