Questions tagged [oracle]

42428 questions
-2

votes
0

answer
22

Views

ORACLE SQL - Show number of working hours between two dates

I'm trying to produce a report to show the number of working hours between 2 dates in Oracle SQL and have come a bit stuck so after help / advice. In the report I have 4 columns: - Job Logged (job.job_entry_date) - Target on site (job.target_comp_date) - Actual on site (job.actual_start_date) - Jo...
Rob Morris
0

votes
1

answer
21

Views

I want to add a subquery with a date parameter to return a calculated value but my query is not working

I have a query that fetches data from oracle inventory and purchasing. Now I want to add a subquery from po_line_locations_all with a date parameter to return a calculated value as if the value lies in the subquery show it else display zero, But in my case the query shows nothing if subquery returns...
theTechAlpha
1

votes
1

answer
19

Views

Display all tables for different schema

I have executed this query to show all tables into my db SELECT TABLE_NAME, num_rows counter FROM all_tables; but the db has got different schema and I want to execute this query for each of these. I already tried to select one table for every schema and the only way is from the main schema with th...
Matteo
0

votes
0

answer
3

Views

How to get more metrics(Threads count, Handles Count) other than the compute metrics offered by oracle cloud for an instance?

I launched the windows instance. I found the config.yml file in the oracle cloud agent folder under program files. config.yml file looks like below telemetry: endpoint_format: 'https://telemetry-ingestion.{}.oraclecloud.com' endpoint_path: /20180401/metrics submission_headers: accept: application/js...
karthik_varma_k
1

votes
1

answer
33

Views

I'm having a problem in deleting rows from several tables

I want to delete all the rows which have productid = 'value' from several tables so I tried to join between them but gives me an error sql command not properly ended so I was asking if this could actually work and if not can anyone tell me how should I do such a thing? DELETE FROM products, product...
Maha
1

votes
3

answer
1.8k

Views

'Missing right parenthesis' SQL DEVELOPER

was doing a school project but then came across this error when I was trying to create a table. CREATE TABLE SSV_PASSENGERS ( PASS_ID# CHAR(6), PASS_FNAME VARCHAR(15), PASS_LNAME VARCHAR(15), PASS_ADDRESS VARCHAR(30), PASS_NATION VARCHAR(20), PASS_DOB DATE(yyyy-mm-dd), PASS SATRATE VARCHAR(2), PASS...
J. Lee
1

votes
1

answer
1.5k

Views

SQL error - ORA-00933: SQL command not properly ended

I'm aware that there are numerous questions similar to this that have been asked before, but I've looked through many of them and I haven't been able to find a solution to my following problem. This is my attempt at writing a query that displays the total number of reservations by room type in the m...
lefrost
1

votes
3

answer
498

Views

HikariCP with Oracle com.zaxxer.hikari.pool.HikariProxyCallableStatement cannot be cast to oracle.jdbc.OracleCallableStatement

I using Hikari Pool and OracleCallableStatement: My datasource: And I try make a request to Oracle: public List getProducts(int numbersMonths, Long initServiceId, List serviceIds) throws SQLException { Connection cnn = null; OracleCallableStatement stm = null; ResultSet rs = null; List res = new Ar...
All_Safe
1

votes
1

answer
85

Views

How do i connect to a ODBC Oracle database?

I have information about the connection like username, password, host, port, SID and i know that is an oracle database but i don't know how to connect. I am on lubuntu 18.04.
José Augusto
1

votes
1

answer
182

Views

How to do Oracle (+) left join query for multiple columns

We are using Oracle 11. When doing LEFT JOIN like below, the result of the query is 11,053 rows …LEFT JOIN LIQ ON LIQ.ID = SEC.ID AND LIQ.UPD_DT >= '08-JAN-19' WHERE … But, when doing LEFT JOIN using (+) like below, the result of the query is 10,136 rows ...FROM SEC, LIQ WHERE LIQ.ID (+)= SEC.I...
faujong
0

votes
1

answer
14

Views

How to subtract two dateTime field containing dateTime in ISO format and get the result in hours?

How to subtract two dateTime field containing dateTime in ISO format and get the result in hours? I have tried subtracting two date fields but it has just subtracted date and not taken time into consideration to_number( TRUNC(to_timestamp(T1.attribute_2,'YYYY-MM-DD'T'HH24:MI:SS.ff3'Z''))- TRUNC(to_t...
Gaurav Kanodia
0

votes
0

answer
14

Views

DBD-Oracle-1.76 installation issue

I am setting up a new server for POSTGRESQL, so we have to migrate from ORACLE to POSTGRES while installing DBD-Oracle-1.76 package. However, we are getting error even though we have exported ORACLE_BASE, ORACLE_HOME, LD_LIBRARY, LD_LIBRARY_PATH. Here is what I have tried: make rm -f blib/arch/auto...
Abhinav
0

votes
0

answer
5

Views

Powershell cannot use database view unless recompiled

Powershell program on Widnows server 2012 performs some database operations in Oracle database using regular Oracle client. Database is XE 11g. Program first retrieves data from a view and then loops through recordset and sends processed data to SQL server using its client. The problem is it sees so...
some1
1

votes
2

answer
28

Views

how to make af:inputText resizible in adf?

I have An inputText that i'm trying to Make Resizible and i've put it in AF:PanelStrechLayout but it didn't work and this my code im also have an inputText in popup that don't resize depend on the Text Length and the text is truncated and this is the code for it
osfar
1

votes
4

answer
54

Views

ORACLE LIKE with multiple values

I have the below query in ORACLE which takes a really long time to run because of the OR in between the 2 likes. select pat_mrn_id,surgery_date, proc_name from or_log_all_proc, or_proc, or_log, patient where upper(proc_name) like ('%CRANIOTOMY ANEURYSM%') OR upper(proc_name) like ('CLIPPING%ANE...
JHegde
1

votes
1

answer
43

Views

SQL to distinct part of the string - Oracle SQL

I have a table table1 with column line which is of type CLOB Here are the values: seq line ------------------------------ 1 ISA*00*TEST ISA*00*TEST1 GS*123GG*TEST*456:EHE ST*ERT*RFR*EDRR*EER GS*123GG*TEST*456:EHE ------------------------------- 2 ISA*01*TEST GS*124GG*TEST*456:EHE GS*1...
dang
1

votes
2

answer
33

Views

concatenate some of the column content and select in one query

Here is my film table: FILM (Catalog_Num, Format, Title, Year, Number_Discs, Rating, Timing, Genre) I want to concatenate the genre column if the year is before 1970 and it cannot be repeated, for example, if the genre is Romantic --> (Classic Romance) its ok. but is the genre is already Classic, i...
Shin Yu Wu
1

votes
1

answer
36

Views

Use of asterisk in oracle sql

Why is the use of asterisk perfectly valid in oracle sql when the asterisk is by itself in the SELECT clause, but it results in an error when there are other expressions in the SELECT? For example: select * from table1 -- is ok But: select field, * from table -- is not ok
paolov
1

votes
3

answer
83

Views

How to add a constraint that checks the sum in Oracle SQL?

I'm currently doing a school project in which we need to create a database for a real estate management company. We have an OWNER table, a BUILDING table, and an OWNERSHIP table. I want to make sure that when I enter a value for the ownership stake percentage, the sum of all ownership stakes from th...
Nic
1

votes
1

answer
38

Views

dbms_output in function from package does not showed

I need to know what employee_ids has been updated in a function from a package for the HR Oracle Schema. For this I tried to execute a dbms_output function inside another function in this package but the output only shows the return value fron the function, not the output. What is the right form to...
reymagnus
1

votes
1

answer
39

Views

Terminate a running sub procedure call

I have a procedure which will call another procedure, but sometime the sub-procedure takes more than 2 mins or more to complete. I trying to find a way to: terminate the called sub-procedure if it is still running after 20 seconds or force the sub-procedure RETURN to the caller procedure after 20...
Ray Wong
1

votes
1

answer
56

Views

How to implement APP_USER variable in oracle apex

I have a teacher table that stores a teacher username, password, id and other things. I have a course creation page and a teacher details page. Currently, when a teacher logs in with their username and password and clicks on the teacher details page, they can see every other teachers details includi...
john leonard
1

votes
4

answer
46

Views

how to join tables not according to equality between two fields

I have a problem with a query in SQL oracle and I will be glad to hear if someone here can help me. so, I have two tables. One contains start time and end time of something (mostly long times) , and the second contains also start time and end time of something else (mostly short times). I want to jo...
Amit Vais
1

votes
2

answer
59

Views

Trying to replicate a '=countifs' function from excel to SQL

I have an excel file with a table that looks like this: A B C Registry ID Parent Reg ID Focus Account (Y/N) 100000033 100000036778 Y 100000343 1000...
Catalin
1

votes
2

answer
50

Views

Oracle - How to repeat query for every year/month/week

I've made a query to get the rows from my chosen columns where the date is 2018 December: SELECT myColumn1, myColumn2 FROM MyTable WHERE to_char(myColumn_DATE, 'YYYY/MM') = '2018/12' This is quite simple and I can now use these rows to create some further logic. However what if I need to repeat th...
MattSom
1

votes
1

answer
68

Views

Oracle SQL Developer and code/line change indicator

Does Oracle SQL Developer 4.1 provide an indicator for what lines were changed in the code editor? For example, in SSMS a yellow vertical bar appears next to the line number, whenever new code is added/modified/deleted. This line changes to green after it is saved.
myidealab
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
3

answer
169

Views

How to calculate the actual number for each day using start and end date?

I need to calculate the number of distinct ID for each date. Is there a way to do it only with SQL, without using PL/SQL? create table test ( id number(10), start_date timestamp, end_date timestamp ); insert into test values(1, to_date('01.01.2019', 'dd.mm.yyyy'), to_date('03.01.2019', 'dd.mm.yyyy'...
Room'on
1

votes
1

answer
23

Views

SQLDeveloper is slow at exporting results to CSV

I run a 500k-Record query. It runs fast (15 sec) but it only shows the first 50 rows. When I right-click and export it, it takes around 2 hours and I see the counter advancing in chunks of 50 rows. It's definitely running the full query at that moment, but I was wondering if there was a faster metho...
1

votes
1

answer
24

Views

Oracle APEX: Unexpected error, unable to find item name at application or page level

Getting error: Unexpected error, unable to find item name at application or page level. Error gives the item ID like :P1_ITEM_ID_HERE The error does not tell the component that has the problem, other than the page. I'm unable to find any reference to the offending item when searching for it in APEX.
EdHayes3
1

votes
2

answer
29

Views

Syntax to escape a reserved word in Oracle while using abbreviated table names

I have an Oracle 11g database with table named 'time_recs'. I need to run a query in SQL Developer, including a column named comment. As comment is a reserved word, I need to escape it, that´s ok. The problem is I can´t find the correct syntax when you are using abbreviated table names. E.g.: If I...
Ruben Gullini
1

votes
1

answer
30

Views

How to fix PLS-00103 error in Zip Code function?

So I've created a function to determine the existence of a zip code in a table. I have a table called 'ZIPCODE' and a column called 'ZIP'. For this function, I want it to return a boolean value of true or false whether the zip code exists or not. However, I get this error: PLS-00103: Encountered the...
moneenja
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
28

Views

How to pass item value from one page to another without a Button?

In ORACLE APEX, I need to pass an item value from one page to another that is not directly related to so I cannot use neither a link column nor a button that redirect to that page. Is there other way to do it?
Paola Medina
1

votes
1

answer
42

Views

IMP in oracle 12c does not create tables with IDENTITY feature

I exported an schema which has approximately 150 tables using EXP. Almost all of the tables in the schema have an IDENTITY column. When I imported the .dmp file using IMP on different server it created all the table and imported the data but IDENTITY is removed. Any suggestion?
Rajesh Ranjan
1

votes
2

answer
53

Views

How to ignore Null values at the end of a string in Oracle?

I have a column nse_credit_rating with datatype VARCHAR2(18). It contains values of form 189.00-250.00. But some records have null values at the end of the string i.e even if the data relevant to me is of length 13 (189.00-250.00) but length(nse_credit_rating) returns 17 as output i.e there are 4 nu...
arjun gaur
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
1

votes
1

answer
37

Views

Group by -SQL ORACLE

My column contains these values. ABCD INC. ABCD INC REGISTERED ABCD ORD SHS ABCD ORD REGISTERED How can I use correct group by using substring function so my distinct values looks like this? ABCD
kkl
1

votes
1

answer
21

Views

How to replace empty slots with a custom message using REGEXP_SUBSTR in SQL in Oracle

I have an assignment where I have a table of 'suppliers' and each supplier comes with a description. Some descriptions provide an email address while the rest do not. I need to display JUST the email provided, and if there isn't one I need to display 'No Email Provided'. I was easily able to extract...
user147219
1

votes
2

answer
3.2k

Views

PHP connecting Oracle and mySQL

I' trying to get some data from Oracle via ODBC to mySQL database. And then Insert some lines in mySQL database. I manage to list the lines from ORACLE but fail to Insert into mySQL. $time_start = microtime(true); set_time_limit(10000); //ligação ODBC ORACLE $connect = odbc_connect('test', 'test',...
Carlos Martins

View additional questions