Questions tagged [oracle]

29866 questions
1

votes
1

answer
10.7k

Views

Oracle 11g can't log with sys user

Today i meet a werid thing:I can't log on remote server Oracle database with sys user! The username/password is sys/123,and my colleage could!this situation maintained still now. But i can log on with an normal user,not a system dba.This is no privilege limit.Why? It's so werid. This error info: OR...
Dolphin
1

votes
2

answer
107

Views

Saving Objects from an Iterated List

I'm currently workin' on a sales module using java+hibernate+oracle... I'm done with my order form in my jsp like this: I'm getting my parameters doing this: ArrayList idMercaderias = new ArrayList(); ArrayList cantidades = new ArrayList(); ArrayList precios = new ArrayList(); for (int k = 0; k < 10...
Felipe Rodriguez
1

votes
1

answer
1.6k

Views

How to highlight a jsTree node based on a specific value?

I am using jsTree to build my tree within Oracle APEX v4.2, which all works fine. What I am after and unsure how to do is, whenever a node in the tree starts with the letter 'S', within the whole tree, I would like to highlight these nodes only with a background colour of say yellow. Example of res...
tonyf
0

votes
0

answer
4

Views

Export BLOB columns with other VARCHAR type columns from a table in a delimited file

I have below 3 column table Create table test ( UserName Varchar2(4000), Comments Varchar2(4000), File BLOB ); the file column will contain PDF files. Using PL/SQL i am trying to export the data in this table into any delimited flat file. I can export the UserName and Comments column values but...
Vishesh
1

votes
1

answer
796

Views

Migrating Spring application from Postgres to Oracle: defining table's schema

FINAL EDIT The reason why I didn't see any logs is that I was using an old version of log4j. Hibernate hbm2ddl uses slf4j for logging and so all the logs were ignored by log4j 1. Now I upgraded to log4j2 that has a slf4j bridge and I see all the errors in the log. Godd new is that from here I can pi...
malavock
1

votes
3

answer
2.6k

Views

Using DISTINCT for specific columns

select distinct employee_id, first_name, commission_pct, department_id from employees; When I use the above query it results in distinct combination of all the attributes mentioned. As employee_id (being the primary key for employees) is unique, the query results in producing all the rows in the t...
ethan
1

votes
2

answer
134

Views

How to get Nth big item [duplicate]

This question already has an answer here: Finding Nth Minimum of a Varchar value in Oracle 2 answers I am getting 11th older person from users table with the following SQL statement select MAX(age) from ( select * from (select * from users order by age asc) where rownum
Ahmet Karakaya
1

votes
1

answer
2.7k

Views

Get IP Address range from xxx.xxx.xx.0/16

Good day All, Is there a way to get the range of an IP addresses from the format 66.54.119.192/29 in Oracle pl/sql to something like 66.54.119.192 - 66.54.119.198 Thanks for any assistance offered.
jasmaar
1

votes
4

answer
2k

Views

How do I do a simple “select x where it's value of y is the max in z”?

How do I accomplish the following in the most efficient manner in SQL? SELECT FIELD1, FIELD2, FIELDN ... FROM TABLE1 WHERE FIELD1 = (the row with the max value from an aggregate query) For example SELECT * FROM PS_DEPT_TBL WHERE DEPTID = ... SELECT DEPTID, COUNT(*) FROM PS_JOB GROUP BY DEPTID ORDER...
GaryFurash
1

votes
2

answer
733

Views

NestedTable in SYS schema Oracle 11g

desc sys.dbms_debug_vc2coll sys.dbms_debug_vc2coll TABLE OF VARCHAR2(1000) I found DBMS_DEBUG_VC2COL1 for table of varchar2.I was searching on the SYS schema if there is defined type for table of numbers?. Any help would be greatly appreciated. Only reason for using the types in SYS schema is to a...
user1118468
0

votes
1

answer
21

Views

Scrambling the data for dev environment after refresh to hide/mask the sensitive data

I am working on some scripts to scramble the data from specific columns. I am not very good in scripting but still some how managed to write the code with the help from stackflow Q&A this table has xml column so I want to replace data from xml column by Field Position so sometimes it should be repla...
vancouver_dba
2

votes
1

answer
40

Views

Oracle Large Objects storage in blocks

In Oracle data storage, if the size of each block is from 2 KB to 32 KB, how do Oracle store the CLOB/BLOB data types as the size of the CLOB/BLOBS might be in several MB's? Thanks in advance.
oramas
1

votes
0

answer
12

Views

SQLPLUS : Stop Terminal output while spool to a file

How to stop terminal output in Unix while SQLPLUS and spooling the output in a file? I tried serveroutput, echo, termout, feedback in OFF mode but no luck. Also it is showing logon details as well . sqlplus -s $LOGON
sharan jain
1

votes
1

answer
3.1k

Views

WL5G3N0 notation in Proxy Server after exporting in OSB

I need to virtualize one web service in OSB, but the final wsdl is the same as the Business service (it's a asxm and everything is only in one file), this is OK, but after exporting the wsdl has a differente notation, please see the examples below: Expected and original The one I get: I'm getting th...
Alex
1

votes
1

answer
1.6k

Views

Oracle: Nested joins/sub-queries: Odd column names and one unexplained error

The following query works fine. It is a series of nested joins to give me a kind of master table: SELECT * FROM proj_trainer k JOIN ( SELECT * FROM proj_breeder i JOIN ( SELECT * FROM proj_jockey g JOIN ( SELECT * FROM proj_horses e JOIN ( SELECT * FROM proj_results c JOIN ( SELECT * FROM proj_r...
COOLBEANS
1

votes
2

answer
87

Views

Using SQL to compare tables

My manager has asked me to compile a report to compare the column names of the following two tables: UK Staff USA Staff So he's not looking for any data inside the tables, just a piece of SQL that we can run to check the column names and show the columns whose name is not in both databases. A piece...
user3481395
1

votes
3

answer
6.2k

Views

ORA-01427: single-row subquery returns more than one row error

I have 2 tables (USERS and ACCOUNTS) with the following data in them: USERS UserID Name Account_Number 10 John Smith 13 20 Alex Brown 14 30 Mary Wade 34 ACCOUNTS Account number Amount 13 40 34 30 14...
user3607758
1

votes
1

answer
252

Views

Oracle: Limit results of a select statement to XXX number of rows for each record

I have a scenario where I want to pull up to the first 500 rows for each agent_ID on a table. Example: TABLE NAME: AGENT Schema: AGENT_ID, CUST_ID, CUST_F_NAME, CUST_L_NAME PK: CUST_ID Scenario: AGENT_ID 1234 has 800 results AGENT_ID 4567 has 1000 results AGENT_ID 1212 has 300 results I would lik...
Matt K
1

votes
1

answer
108

Views

Oracle Missing Sequence Number

I'm using sequence to generate autoincrement id in Oracle. And I have a trigger to pull number from the sequence every time before insert. CREATE SEQUENCE "my_SEQ" MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 CACHE 5000 NOORDER NOCYCLE; And I found that there're 5000 (same as cache)...
bananaLeaf
1

votes
2

answer
596

Views

String Concat Vs Substring in Queries - Which has better performance?

I have 2 tables, say table1 and table2 with sample data as below: Table1 (User_id) -------------------- X1011 X1175 X1234 Table2 (User_id) ----------------- 1011 1175 1234 I need to write a query with a where condition, where I would compare these two values. Which of the following in general, w...
1

votes
2

answer
14

Views

Eliminating duplicate rows with null values using with clause

How do we eliminate duplicates by only selecting those with values in a certain field using with clause statement? Query is something like this: with x as (--queries with multiple join tables, etc.) select distinct * from x Output below: Com_no Company Loc Rewards 1 Mccin Ind...
BFF
0

votes
0

answer
10

Views

Adding local jar (oracle driver) to Maven and Bamboo

I need to use the oracle database driver (oracle.jdbc.driver.OracleDriver) for a project but Oracle does not have a Maven repository for it, so the only way that I have found is to download it onto my machine and then "install" the .jar to Maven as seen in this article. However, now it is time to ad...
Dylan Kidd
0

votes
0

answer
3

Views

During oracle 10 G installation, the error “you do not have sufficient privileges to write to the disk” comes

During oracle 10 G installation in windows 10, the error "you do not have sufficient privileges to write to the disk" comes
Guhanathan
1

votes
3

answer
3.2k

Views

PL/SQL Developer displays does not display Hebrew characters properly

Hi all, I've uploaded a photo of the way I see dates in PL/SQL Developer, I see question marks instead of the full date (need to look like "02-Aug-2013 13:50:21.663" for instance, as seen on other computers)... If someone have any idea what on my preferences makes it look like this - I'd love to kn...
Bramat
1

votes
1

answer
33

Views

Error in Inserting value in a table using trigger

I am trying to insert a value into a table using trigger concept but i am getting error below is my code 1 create or replace trigger trig_sub 2 before insert on subscription 3 for each row 4 declare 5 m purchases.msisdn%type; 6 c purchases.circleid%type; 7 con purchases.contentproviderid%type...
Raghav Bhardwaj
1

votes
2

answer
3.6k

Views

How to run 2 different “execute sql” steps in one session in Pentaho Kettle (PDI)

My question is, how can I run 2 different "Execute SQL script" steps, so that they both run in one same session, not in 2 different? In my transformation, I need to run one sql script at the very beginning of transformation process, to permit modifications on table. Then, I extract data from one d...
Nur4I
1

votes
2

answer
470

Views

Oracle REGEXP_SUBSTR column names for PIVOT

I am working with PL/SQL Developer v10 on Oracle 11g database. In our system we have a question and answer table that I need to 'flatten' for each customer per question answered. One set of questions had specific code (PIFQ) in description of question that made the following script possible. Simply...
Matas Vaitkevicius
1

votes
4

answer
9k

Views

ORACLE display count to zero, when row is null in column

I have a table with many of columns, I count the specific names on column and if that name not on list, I want to display it zero, but not get in to list. SELECT Names, COUNT (*) FROM NAMESTABLE WHERE names IN ('Jenny', 'Penny', 'George', 'James', 'Jessica', 'Monica', 'Erica') AND adeddate BETWEEN...
Arno
1

votes
1

answer
1k

Views

Lotus Notes to Oracle database migration [closed]

I have a NSF file of a lotus database. The objective is to give up the legacy lotus notes database and migrate it to relational database oracle. Do any one have expertise in this area to give a step wise process to carry out migration from lotus notes to oracle db.
Rohan K
1

votes
1

answer
8.7k

Views

Pivot Table Error 56901 non-constant expression

I need to create a pivot table of average salary in Division_ID as Row and Job_ID as Column using data in employees2 table. So far I have: SELECT * FROM ( SELECT Division_ID, Salary, Job_ID FROM Employees2 ) PIVOT ( AVG(Salary) FOR Job_ID IN (ENG, TEC, MGR) ) ORDER BY Job_ID I get the error, "non-c...
user3729062
1

votes
2

answer
1.1k

Views

PLSQL - Iinsert in trigger causing recursive loop

I have a trigger which runs when on an insert in to my keyword table. The key word is taken in as a string and separated by a comma. I have separated each value and then try insert them in each value into the table. But when I run the insert I get an error. My trigger create or replace TRIGGER trg_I...
Alan Mulligan
1

votes
0

answer
5

Views

Download Large PDF Files (Bigger Than 150 MB) With Crystal Report

I have .net mvc project. This web project has a screen which can download report file with specified credentials. This screen use Crystal Report. User specifies inputs and then click the download button after that stored procedure is executed to fetch data. User can download pdf files which's size...
John
0

votes
1

answer
9

Views

How to best re-use the same Excel doc in all envs

Web ADI (App Desktop Interator) We use an Excel xlsm (macro) spreadsheet that adds an "Oracle" button to the Ribbon tab. I'm new it but it looks like it is used to upload data on a "JE" (Journal Entries) tab to a production website Oracle URL, "https://ebscgitprod1.oracleoutsourcing.com:443/OA_HTML/...
ChadD
0

votes
0

answer
7

Views

ORA-00907: missing right parenthesis (All parenthesis are there!)

I am trying to create a table in oracle and keep getting the error. Here is my code. CREATE TABLE employee ( employeenumber INT(2) NOT NULL, firstname CHAR(30) NOT NULL, lastname CHAR(100) NOT NULL, department CHAR(35) NOT NULL, position CHAR(35) NOT NULL, supervisor...
Blake Gorman
1

votes
2

answer
1.1k

Views

Get the Query for Line Numbers from the Sales Order Form in Oracle EBS

From the Front-end Application, we can see the line number like 1.1 , 2.1, 2.1.1 like this. I only have the value of the line_ids. I would like to get the query for the line numbers in Sales order Form with the use of their line_id values.
snowbell
1

votes
1

answer
2k

Views

oracle connection into c# ssis script task

I have a connection manager that points to an oracle database.I then need to use that said connection into a ssis script task.I don't know how to proceed.I tried something and I got an error message could you help me.Here is my code: I also tried with those connection string: // SqlConnection conn =...
Simix
0

votes
0

answer
3

Views

how to access a node container through another oracle-jet container?

I want my UI to get data through my node server, where each is an independent container. How to connect them using docker-compose file? i have three container that run mongodb, node server and oracle jet for ui, i wanna access the node APIs through the oraclejet UI so and the mongo db through the n...
Ameur Baccoucha
1

votes
2

answer
4k

Views

Oracle, inner join with group by clause

I have two oracle tables : USERS : ID, USERNAME, PASSWORD, ROLE ARCHIVE_POSESSION : ID, USERID, ARCHIVEID What I'm trying to do is obtain a query that returns me the following result set : Username, Role, Number Of Archives So far so good, but I can't wrap my head around the query, I'm still a beg...
Sorin Lascu
1

votes
2

answer
19.2k

Views

How to update after insert in the same table in an Oracle trigger?

I have a table with columns X, Y and Z. I want to set Z when I insert or update X and Y, so Z=X*Y, using a trigger. create or replace trigger tr_tab after insert on tab for each row begin update tab set z=x*y; end; / But after insert I get an error.
newbie
1

votes
4

answer
311

Views

How to unfold the results of an Oracle query based on the value of a column

I have a table on a Oracle DB with two columns. I would like to see every row repeated as many times as the number stored in the second column. The table looks like this: col1 col2 a 2 b 3 c 1 I want to write a query that returns this: col1 col2 a 2 a 2 b 3 b 3...
Juanma

View additional questions