Questions tagged [oracle-11g]
Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.
715
questions
0
votes
1
answer
1k
views
How to convert the JSON date with to_date function
How to convert the specific string Aug 6, 2018 3:17:11 PM with the function to_date?
That string is a result of a Java parsing mechanism for JSON. I would like to be able to recover it directly from ...
- 133
2
votes
0
answers
2k
views
Changing a JSON object parsed with APEX_JSON
I would like to be able to change a JSON object parsed with the APEX_JSON.parse() function, but I found nothing in the official documentation, no tips or examples about that.
In the code below I ...
- 133
0
votes
2
answers
11k
views
Update row with multiple row in another table Error : ORA-01427: single-row subquery returns more than one row? [closed]
I have Table A with 1 row and wanted to update it from another table (which has 3 rows for it and I can take any one of them).
Table A
-----------
TrnID | trn_Status
2345 | null
9876 | null
9878 | ...
- 31
0
votes
2
answers
1k
views
How to create a trigger in oracle to prevent overlapping date ranges
I'm learning Oracle and I had a problem.
Chat table:
CREATE TABLE training
(
id_training NUMBER,
id_user NUMBER,
start_training DATE,
end_training DATE
);
I want to ...
- 1
3
votes
0
answers
6k
views
How to apply a condition on "count over partition"?
I'm using Oracle 11g.
Assuming multiple consecutive entries (sorted by datetime) with the same place_id represent a single visit.
With this table:
datetime_id | user_id | place_id
and I want to ...
- 159
0
votes
1
answer
1k
views
How to handle -904 Invalid identifier error in oracle store procedure
My proc is like this
CREATE OR REPLACE PROCEDURE TEST_INSERT
IS
TotalUpd pls_integer;
err_code number;
err_msg varchar(500);
invalid_excep EXCEPTION;
PRAGMA ...
- 103
2
votes
1
answer
312
views
Inserting year with zero
Is it possible to enter a date with a zero year?
What would the insert clause look like?
I ask, as an application is doing this, see the dump of the date:
dt_data: 05/01/00
dump(dt_data): Typ=12 Len=...
- 123
1
vote
2
answers
714
views
How to switch between Oracle 11g and 12c on same linux
I have oracle11g and 12c installed on same machine and few test cases that need to be run on both versions.
Can power up both database simultaneously and run tests parallely.
Do you know any document ...
- 167
1
vote
2
answers
1k
views
Does oracle database have a mechanism to replace specific character when storing data
I'm using oracle 11g database, does oracle database have a mechanism to replace specific character with other characters over all tables when storing data. For example i want to go to specific ...
- 111
1
vote
1
answer
766
views
Should DB_RECOVERY_FILE_DEST_SIZE be smaller than the respective partition size?
Is it OK to set the DB_RECOVERY_FILE_DEST_SIZE to be equal to a drive partition that is dedicated for this purpose?
That is, if I use a partition of 10GB, is it ok to set the ...
- 125
1
vote
0
answers
2k
views
Oracle Number (10, 0) decimal in (C#) code
The following query is executed to add a new column to an existing table:
ALTER TABLE TABLE1 ADD COLUMN1 NUMBER(10, 0) DEFAULT -1 NOT NULL.
Using DBForge to check the database, result in NUMBER (10, ...
- 143
0
votes
1
answer
5k
views
Can't install Oracle 11g [closed]
Error while installing Oracle 11g:
Please find the StackTrace of this error.
ID: oracle.install.driver.oui.OUISetupDriver:13
oracle.cluster.verification.PreReqNotSupportedException: Reference data ...
- 13
0
votes
1
answer
2k
views
Rename column in a partitioned table
I want to rename a column in a partitioned table.
Will oracle do this for all partitions?
Is there anything special for this change?
- 103
0
votes
0
answers
264
views
Grant in advance privileges on Schema A for dynamically created tables on Schema B in oracle
I have the following scenario in my Oracle Database (11g) and I am trying to work around grants and access privileges.
Note that the following is just a Minimum Viable Example and it does not cover ...
- 186
1
vote
0
answers
176
views
How to detect if insert transactions in oracle db are slow?
At work, I have an Oracle DB (11g) in which I want to detect if there's slow performance while inserting data.
Here's the situation:
Some production devices send data results from tests to Server A, ...
- 11
1
vote
0
answers
97
views
How to configure partial replication of Oracle DB
Suppose we have 2 identical Oracle 11g DB schemas: PRODUCTION and ARCHIVE.
The goal is to keep in PRODUCTION the minimal amount of data required for online operations,
and use ARCHIVE to store all the ...
- 131
0
votes
1
answer
1k
views
Oracle Logon Trigger ReCompile
Typically when an oracle object goes invalid due to a dependent object being modified, the next execution of the invalid object will compile it and everything will be execute as expected (assuming no ...
- 631
0
votes
1
answer
325
views
ORACLE 11g Know Insert Record Details which Failed to insert
I have started auditing insert records by user on failure to any table in my oracle 11g Database. I have used following command to do the same.
AUDIT INSERT ANY TABLE BY SHENA BY ACCESS WHENEVER NOT ...
1
vote
1
answer
166
views
PL/SQL Loop Returning One Result
I'm working with the following script:
create or replace PROCEDURE a_proc
is vi_get_emails varchar2(500);
cursor get_emails is
SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl....
- 111
0
votes
1
answer
76
views
Add column to a table for every shema in an oracle db 11g
Our oracle db has various users with same schema. I would like to add a column to one table in those schemas. The same column to same table in every schema. I know that I can do that manually, like ...
- 25
0
votes
3
answers
201
views
Restrict sysdba or system administrator from reading package in oracle 11g
I have a requirement wherein the oracle database contains packages which have important stored procedures and functions which should not be exposed to the client / customer. The client / customer will ...
- 101
0
votes
1
answer
45
views
Group by checks constraints are possible?
I have a table with the following columns:
TEAM_ID integer,
NAME varchar(64),
IS_LEADER char(1) not null default 'Y'
I want to apply the constraint that, in the same team, only a leader can be ...
- 103
0
votes
2
answers
4k
views
Oracle data transfer rate determined by rows instead of data size
Is there a setting in Oracle 11g that can throttle the rows/second being pulled from the db?
Our data loads from Oracle are really slow. It does not seem to matter whether its OPENQUERY, SSIS, or SQL ...
- 103
0
votes
1
answer
272
views
XML parsing failed in the sqlplus
I get following error when executing the query
Invalid token in '[count(../[name(../)=name(.)])=count(../) and count(../*)>1'
However when installing the Itstar_xml_util package, it asks for two ...
- 103
0
votes
2
answers
672
views
How to fetch last two year annual data in oracle
There is table named sales_data which I used for my project.
The table structure is below:
+------+------+------+--------+
| ID | Name | Year | Sales |
+------+------+------+--------+
| 1000 | ...
- 101
0
votes
1
answer
40
views
How can I find state name which has the most vehicle accidents in the database?
I have a table "ACC_DATA" that has the following columns: ACCIDENT_ID, STATE_CODE, ACC/DATE and a second table "US_STATES" which has the following columns: STATE_CODE and STATE_NAME
Each accident ...
- 117
0
votes
2
answers
175
views
Unable to remove a user in Oracle 11g
I have a streams replication related user in Oracle 11g. I see the user in dba_users table and was anle to login using the user but when I try to drop the user i get the error.
SQL> drop user &...
0
votes
1
answer
2k
views
Keep default value as 0.0 for 0 or NULL for numeric column
I have a numeric column Number(28,3) in which I have to insert value as 0.0 ,
whenever I encounter 0 or NULL/blank at the time of data insertion.
I have tried below :
NVL(col_name,0.0)
but it's ...
- 103
2
votes
1
answer
8k
views
How to Count Rows in a Separate Table Using a Foreign Key
I am in school to become a software engineer, and I am taking my first class on SQL. We usually use MsSQL, but the schools VMWare instance is down, so I have an older version of Oracle on my computer (...
- 21
0
votes
1
answer
120
views
If a select statement returns no result, change the where condition
I have two queries. I want to run the first select statement and only if it does not return any record then I want to run the second statement. How can I achieve this. These are my two statements:
...
- 239
1
vote
1
answer
61
views
Oracle query performance when filtering results
I am currently working on a query for a front end application, which is experiencing weird performance issues when filters are applied on large sets of data. Here is a link to an SQLFiddle of the ...
- 11
0
votes
2
answers
168
views
How can I recover oracle database without new data?
I have oracledb 11g. I setup archivelog mode on my server. There's my backup\recovery scenario:
rman TARGET SYS/pass@XE
RMAN> backup database plus archivelog;
After that I make changes to the ...
- 103
0
votes
1
answer
201
views
How to create a database user for oracle 11g on windows 8?
I am new to oracle database and therefore is trying to follow the office guide. (link to the guide) However, it seems that the guide is for Windows 7, so in step 2 - creating database user, it says:
...
- 101
1
vote
1
answer
3k
views
Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results in another table oracle
I am very much new to the DB world, so wanted to review whether I am following right approach or not.
I have two tables,
table A --> is a table with 40 columns
table B --> is a table with the 3 ...
- 111
3
votes
2
answers
4k
views
oracle performance on date range query
I am trying to count the number of orders passed by a certain organization over a certain time range. But I found that the below query (time range of 2 days) is much much slower that doing two ...
- 131
-3
votes
1
answer
63
views
Please help me to solve this complex query [closed]
An interviewer asked me this scenario in a job interview.
We have an Employee table:
GENDER NAME
------ -----
M A
F B
F C
M D
How can we write a query to output this:
...
0
votes
1
answer
152
views
FK to table with non-unique values via a on commit refreshing materialized view?
I would like to create a FK to a table without unique values. So I cannot create a simple FK. [to clarify, the parent table has duplicates - dont ask why, not in my control ]
I tried creating a ...
- 1,005
1
vote
2
answers
314
views
Using SUBSTR and CONCAT to get the required Result
I Am trying to get the results for the 'CENTRE' whereby if the 'DEP2' does not exist it has to use 'DEP'
Have done this by using SUBSTR and CONCAT in my query
But only get back 3 from 'DEP' when ...
- 15
1
vote
1
answer
1k
views
Oracle Database 11G Installation "Error al llamar al destino 'install' del archivo make"
I get this error installing Oracle Database 11g in Linux..
INFO: /usr/lib64/libstdc++.so.5: referencia a `memcpy@GLIBC_2.14' sin definir
INFO: collect2: error: ld devolvió el estado de salida 1
...
- 121
1
vote
1
answer
4k
views
Oracle Query execution finishes first time but hangs up from second run
We have a very complex view that finishes execution only on first time, but hungs up when run from the same session second time.
complex view - view with nested views, lots of inner and outer joins, ...
- 299
0
votes
1
answer
23
views
Getting n slip number based on the number of times in a month a slip has been created
Hi Guys so here is my table, I need a slip number for the slipno collom that is based on the number of times the empno has a slipno in a month.
To try and better explain here is my table
------------...
- 15
0
votes
3
answers
202
views
Command Line Creation of a new 12g Oracle Database
I am new to creating an Oracle database using the command line. I am logged in as superuser; now I would like to create a new blank database, "mynewdatabase".
$ . oraenv
ORACLE_SID = [oracle] ? ...
1
vote
0
answers
46
views
how to show row values in column?
i have a database sample that looks like this:-
as you can see my student master id is repeated. i want student master id to appear only once (in one row) can corresponding roll numbers to appear in ...
- 111
0
votes
1
answer
8k
views
Oracle: Sum function "not a single-group group function error"
I have an ORDER_LINE table in which I am trying to multiply the NUM_ORDERED of parts by the QUOTED_PRICE for each ORDER_NUM. I am adding SUM() function to group by ORDER_NUM, because if I didn't, ...
- 207
0
votes
1
answer
414
views
Export a very big amount of files from a table
I'm using Oracle Database 11g Release 11.1.0.6.0 and have a table recording a very big amount of files (GIF) with IDs (ID).
Name Type
------------------------...
- 103
1
vote
3
answers
659
views
Does increased Data Dictionary size affects performance
As I understand, Data Dictionary
Is a collection of tables, part of SYS schema (if not the whole SYS
schema)
It is stored as 'regular' tables, so whatever is applied to
database tables, also ...
- 186
3
votes
4
answers
29k
views
How to calculate or track monthly database growth?
Using oracle how can i track database growth monthly? The following is a query apart of a query i used to stored information using sql server. Is there a way to replicate the same using oracle?
...
- 141
0
votes
3
answers
508
views
Connect to oracle 11 on a corporate network via oracle developer 17
Sorry for this absolute oracle noob question:
I am in an environment that uses integrated / active directory authentication, for developer to access the oracle databases via an old version 9 PL/SQL ...
- 1,005
0
votes
1
answer
703
views
Does Oracle do a native conversion to numbers for varchar2 that are aggregated?
I have a table in an Oracle 11g database that's similar to the following:
CREATE TABLE BUDGET_DATA (
BUDGET_MONTH varchar2(30)
, BUDGET_CATEGORY varchar2(30)
, BUDGET_AMOUNT varchar2(30)
)
All ...
- 231
1
vote
1
answer
2k
views
Display child records in single column by comma separted with parent records
Have parent & child table like below for Oracle
T_OPT_PARENT
OPT | OPT_DESC| STATUS
XY | XY_DESC | A
YZ | YZ_DESC | A
ZZ | ZZ_DESC | A
T_OPT_CHILD
OPT | CODE
XY | ONE_CODE
XY | ...
- 113