Oracle Database

78K

Solutions

25K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Share tech news, updates, or what's on your mind.

Sign up to Post

I want to delete a record from parent table, I am unable to do so, as I am getting error
how can i delete parent records with out affecting child table

can you plse any one help me
0
MS Dynamics Made Instantly Simpler
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Hi Team,

I need to grant execute privilege on dbms_debug package to hr user . Iam using Oracle 12c database.

Any help is really appreciated
0
Hi Team.

I need the difference between DESCRIBE_COLUMNS3 ,DESCRIBE_COLUMNS2,DESCRIBE_COLUMNS procedures. I know it is used to get the table column names , but not able to figure out the difference and why oracle is providing 3 procedures which does the same task.
Any help is really appreciated.
0
Hi Team,

Iam written the below code , In one block , i written the code to put data into buffer , in another block iam reading the buffer , iam not getting the expected output.

begin
dbms_output.enable(20000);
dbms_output.put('Hello');
dbms_output.put(' Welcome to DBMS_OUTPUT package');
dbms_output.new_line;
dbms_output.put_line('Its used to communicate between program units in the server');
dbms_output.new_line;
end;
/


declare
v_line varchar2(100);
v_lines dbms_output.chararr;
v_status integer;
begin

dbms_output.get_line(v_line,v_status);
end;
/
Iam not getting the output, Can any one help what is wrong with the code.
0
i have a table where i need to look up .... and then update a single field..
table A
c1 c2 c3 c4 c5
table B
c1 c2 c6 c7 c8 c9

in these two we have c1 and c2 common ....
will populate c9 based on c4 if (c1,c2 are same and c4 not null)
else update c9 to value Base for c7 equal B
 or update c9 to Dollar for c7 equal D
or update c9 to Sed for c7 equal S

my query does this
if c9 not null then
check in table A for C4 not null (c1 and c2 found)
else set based on c7 ...it works

but when an update happens to table and c7 gets set then it does not reflect ....
how to solve this ?
0
Hi,

I created an oracle external table, but got error when select from the table.
Please see the attached file.

Any idea would be appreciated.

Thanks a lot,

Luchuan
Ext_table.docx
0
I have to determine which spec_int have a tests where all have any result.  The other group would be spec_int which have tests where one or more does not have a result_id

Have tried to put together inner queries to get counts of tests with results, then bring it out to an outer query that would then list the group where all tests have result, and/or the groups of spec_int in which one or more tests have no result_id.

Table spec_int contains spec_int_id and hold.
table test3 contains spec_int_id and  test_ids,
table result4 contains test_ids and result_id.
Table 5 contains results.

I need to determine which spec_int's are not on hold and have tests that all have result_id's.   Does not a matter what the result is, just whether there is a result for every test contained in a spec_int.

Data
Spec_int
Spec_int_id         hold
       1                    N
       2                    N
       3                     N
       4                    Y



Spec_int_id     Test   result
       1                69
       1                55       5
       2                87       8
       2                44       6
       3                45
 

Desired results:           
Groups  status
   1           open
   2          closed
  3           open
  4           on hold

Open in new window


CREATE TABLE SPEC_INT
(
  SPEC_INT_ID  NUMBER(10),
  HOLD         CHAR(1 BYTE)
);

CREATE TABLE TEST3
(
  SPEC_INT_IID  NUMBER(10),
  TEST_ID       NUMBER(10)
);

Open in new window

0
I have set up my connection to an Oracle Database in Excel and the Command Text in the definition tab of the Properties window says 'SELECT * FROM "PS_OWNER"."PS_JOB"' which returns too much data for an excel spreadsheet. So my question is, can I use VBA to return the data the same way I return it in SQL Developer?
0
I had this question after viewing Create Function  which returns records from multiple tables..

I see this query from the above question:

select * from table(DepartmentEmployees('07/09/2017','07/20/2017'));

how to change the above to get the parameters from other table.

TableA  ==> This table will only have one record as below, and I need to get those into above query.
=========
start_date|end_date
=================
07/19/2017|07/22/2017

I tried something like below but I'm getting error ORA--00936 Missing Expression.


select (select start_Date from tableA where rownum = 1) as start_date,
       (select end_Date from tableA where rownum = 1) as end_date,
       *
  from table(DepartmentEmployees(start_date - 30, end_date) );
0
If we have "-" in between postal code, like "N6L-1A1"
We need to replace the "-" by an empty space, making it:
"N6L 1A1"

* But we have some exceptions, like this: "NOB 1-E0"
If we apply the code on this, it would make = "NOB 1 E0"
and we have to make it  "NOB 1E0".
0
Online Training Solution
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Hi ,

 

I have a question regarding inserting values to the table..i have like 2 table T1 and T2..

T1 has 80 columns and T2 82..

both have same 79 columns ....
i need to insert data into T2 from T1 ..

since so many columns are their ..

i used this code..

 

 

declare

type col is table of varchar2(200)

index by BINARY_integer;

col_name col;

v_record T1%rowtype;

begin

select column_name bulk collect into col_name from cols where table_name like 'T2' where column_name NOT LIKE 'NAME'; --excluding the dissimilar one

select * into v_record from T1;

insert into  T2 col_name values v_record;

end;

/

 

but it throws error as insufficient value ....how to resolve this?
0
I'm trying to set up a trigger that will execute a stored procedure when the row being inserted contains certain verbiage.

The procedure is only sending an email notification.
I'd like to pass a value from the row to the procedure so that it can be included in the email.   It's a date value.

I keep getting the following error when I try to create the trigger:

SP2-0552: Bind variable "NEW" not declared.

Here is the trigger code:

CREATE OR REPLACE SCHEMA_NAME.TRG_INTERFACE_LOG_ERROR
AFTER INSERT ON SCHEMA_NAME.INTERFACE_LOG
REFERENCING NEW AS NEW
FOR EACH ROW WHEN (new.USER_MESSAGE='java.sql.SQLRecoverableException: Closed Connection')
 
BEGIN
 
 SCHEMA_NAME.EMAIL_EXCHANGE_INCIDENT( :NEW.LOG_DATE );

END;
/

Here is the table:
SQL> desc schema_name.interface_log
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------

 LOG_ID                                    NOT NULL NUMBER(10)
 LOG_DATE                                           DATE
 USER_MESSAGE                                       VARCHAR2(4000)

I could sure use an expert eye.
0
I'm new to Oracle functions. Need help for the following  task.

I need to write a function which accepts 2 parameters and it would return records from 2 different tables by joining them.

For Example:

Employee - EMP table

empno||empname||deptno||startdate
========================================
1                 Sam               1                 July 18th 2017
2                 Andrew         2                July 10th 2017

Department - Dept Table

deptno||deptname
================
1                Arts
2                Science

I need a function - DepartmentEmployees( Start_Date, End_Date ) this function returns all the employees whose join date is in between the Start_Date and End_Date. with the selective no. of columns like empno, empname, deptname, stardate

something like this query

select * from emp e join dept d on e.deptno=d.deptno where Start_date >= e.startdate and End_Date <=e.startdate.

so if I do this query select * from DepartmentEmployees('07/09/2017','07/20/2017'); then i should get the below output:

empno||empname||deptname||startdate
========================================
1                 Sam               Arts                 July 18th 2017
2                 Andrew         Science           July 10th 2017
0
I have mistakely delete Oracle tables and views like 'DMRS_%´ and 'DM' that automaticly generated.After that,when I am trying to connect that user ,It show error something like this.

  ERROR:
ORA-12541: TNS:no listener
0
i have 2 tables like

table 1                                       table2
--------                                         ---------      

id       s_name                id        s_name  
1         null                        1        tirpur  
2         null                        2        jaipur
3         null                        3         nagpur
4          null                       4         kocchi


so, iwant to updaet table 1 at at a time
if 1 then tirpur
if to 2 jaipur like

can any one help me plsee
0
Hi Team ,

I need to create a table partition which can store the employees who have joined in a given month and year
Can I achieve it using range partition
Iam trying the below command.
Create table emp_part1
(
  employee_id number,
  hire_date date
)
PARTITION BY RANGE(hire_date)

(  
   PARTITION pos_data_p2 VALUES LESS THAN (TO_date(HIRE_DATE', 'MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE(HIRE_DATE, 'MM-YYYY'))
);

Actually i want to create partitions like below

MONTH-YEAR
JAN-2007
FEB-2007

create partitions like above and store the records , but  iam getting an error
0
The data file is like this
  description
---------------------
,,,,,,,,,,,,,,yes,,,,,,,,,,,
,,,,,,,,,,no,,,,,,,,,
,,,,,,,,,,,,,,,,,,,yes,,,,,,,,
,,,,,,,,,,,,,,yes,,,,,,,,,,,
,,,,,,,,,,no,,,,,,,,,
,,,,,,,,,,,,,,,,,,,yes,,,,,,,,

i need to be load the data by using sqldr like

desc
------
y
n
y
y
n
y

can any one help me
0
(Disclaimer: I'm not an Oracle expert, I'm a sysadmin assisting our DBAs with this issue)

So this is a real head scratcher.  We're rebuilding an Oracle analysis cluster after a major storage hardware failure.  There's a shared staging iSCSI mount that's set up to be shared between all the compute nodes and set up through multipath/ASM as an OCFS2 partition.  When we attempt to load the previous backups from the staging partition, we get the following error:

Import: Release 11.2.0.4.0 - Production on Thu Jul 13 09:53:46 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "/u01/app/oracle/data/data_pump_dir/TEST.DMP"
ORA-27072: File I/O error
Linux-x86_64 Error: 22: Invalid argument
Additional information: 4
Additional information: 1
Additional information: -1

Open in new window


The head scratcher is that:

1. We attempted this with one of the backups and it failed, so we ended up making our own test dump file to further test to make sure it wasn't the backup file that was bad.

2. This error ONLY when loading files from the OCFS2 partition.  I set up a second iSCSI share that was just formatted as ext4 and impdp loads the dump file just fine.

3. We've deleted and recreated the OCFS2 partition with several different cluster sizes and whatnot and we always get the same error back.

4. I can read and write to the filesystem on the staging partition just fine.

File permissions are as follows:

-rw-r----- 1 oracle oinstall 184320 Jul 13 10:17 TEST.DMP

Open in new window


System is Oracle Linux 6.8 (clone of RHEL/Centos)

Any ideas...?
0
Hi Team ,


What is the difference between Joins and Subqueries . Can any when help as to when to use joins and subqueries.
0
Free learning courses: Active Directory Deep Dive
LVL 1
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Hi Team,

I need an clarification , I have the below output , i want to know why oracle does not a number when i pass round(1.46) to 2 .

  1* select round(1.46) , round(1.56) ,round(1.5), round(1.6) from dual
SQL> /

ROUND(1.46) ROUND(1.56) ROUND(1.5) ROUND(1.6)
----------- ----------- ---------- ----------
          1           2          2          2

In the above output , oracle round the number the  number to 2 for all the round options except for round(1.46). Any reason why oracle iis not rounding it to 2?
0
Hi, I'm not very familiar with oracle oraclient11g.

I just seem to have a very basic issue of needing to either uninstall it or figure out how to manually remove move it. And may need to locate the correct oraclient11g that can be easily installed and uninstalled if and when needed.

All of our machines have this installed on them as part of an image and when we have an issue with it, I have been told the best solution is to reimage the pc rather than a simple uninstall/reinstall.

When I go to the  Oracle Universal Installer - OUI from the start menu to uninstall as has been suggested by a few posts on the web, a CMD window opens, it checks swap space & screen resolution then just closes while "preparing to launch OUI from
users\username\local\temp\orainstall~ directory.

When I go to that path, I see it has created one for ever time I have ran it.

My basic issue is how to completely uninstall and confirm as well as how to locate the oraclient11g package so I maybe able to easily uninstall/ reinstall if and when needed.


Thank you very much
0
I have the following code which I am using to create a view. When I run the code below, I get the error shown at the bottom of this post. Note that I am able to run this select statement (SELECT DB_LINK FROM DBA_DB_LINKS) directly from within sql developer, but when I added it to the view and try to create the view, then I get the below error message.g

Also I know I have sufficient priviledges to create a view, as I have done it before. However for some reason the query against DBA_DB_LINKS within the view seems to be the issue here?

CREATE OR REPLACE VIEW MY_VIEW
(
  SERVER
)
AS
SELECT DB_LINK FROM DBA_DB_LINKS;
/



Error starting at line : 1 in command -
CREATE OR REPLACE VIEW MY_VIEW
(
  SERVER
)
AS
SELECT DB_LINK FROM DBA_DB_LINKS;
/

Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause:  An attempt was made to perform a database operation without the necessary priviledges.
*Action: Ask your database administrator or designated security administrator to grant you the necessary priviledges.
0
Hi Experts,

I have a table dept
there are 3 address columns like address1,address2,address3
i have to get these 3 address columns  as single value along with line separator.

address:
sathya street
AP
India

i need to show it in 3 lines as single value called address.

can some one suggest me how to do using orcle.

Thanks
0
Hello,

I want to update Oracle DB table from SQL server SSMS using Linked Server. Can you please tell me how to resolve below error

Error: "Invalid object [GAIAPRE_LS].CLIent"

DB link name: GAIAPRE_LS
Oracle Table name: CLIent

update T1
set T1.REF_CLIENT_EXT=T2.ojo_accountnumber
from [GAIAPRE_LS].CLIent T1 inner join CRMDB.dbo.etel_billingaccountBase T2
on T1.NCLI collate SQL_Latin1_General_CP1_CI_AS= T2.etel_externalid collate SQL_Latin1_General_CP1_CI_AS
where T2.ojo_sourceId=2 and T2.statecode=1 --and  T2.ModifiedOn  >= cast(dateadd(day, -2, getdate()) as date)

Open in new window


I am able to execute below query successfully from SQL Server  without any error.
SELECT * FROM OPENQUERY([GAIAPRE_LS] , 'select * from client') ;

I am not able to execute below query due to below error.
select *
from [GAIAPRE_LS].client

Please advise how to use OPENQUERY in above update statement.
0
Hi
I have a set of data as per the attachment (select company, date, amt, ref from mytable)... and want another column added
which is called Ref2  - see attachment for clarification.
NO PLSQL please.

RegardsEXP_EXCH.xlsx
0

Oracle Database

78K

Solutions

25K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.