Oracle Database

78K

Solutions

26K

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

SELECT UPPER(COLUMN1)
FROM SCHEMA1.TABLE1

SELECT UPPER(COLUMN2)
FROM SCHEMA2.TABLE2

i have 100 records in column 1 and column 2 with roughly half capitol words(HONDA etc.) and rest of half small words(nissan etc.)

when i do UPPER character function

i expected to see 50 records with values like NISSAN


when i do LOWER character function
i expected to see 50 records with values like honda

but i got only 2 records.

can you please advise how to fix this
0
Ultimate Tool Kit for Technology Solution Provider
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I'm curious if you guys could advise me with some methods of improving gathering statistics in oracle base on your experience.
I would like to get better quality of stats, performance not so much. Maybe they should be done more often or maybe 'stale percent' could improve something.
I have basic gathering and 'autostats target' set to 'oracle' to use my own... to improve performance and have power over execution plans.
Please advise...
0
HI Experts,

QUERY:

select vw.vw_reqxml
FROM SCHEDULE sch, views vw
WHERE sch.VIEWID = vw.VW_VIEWID
AND (trunc(NEXT_RUN)) =trunc(sysdate)
and (TO_CHAR(SUBSTR(TIME, 1, 2))) >=(SELECT (TO_CHAR(SYSDATE, 'HH24')) from dual);

NEXT_RUN column having only date -->Example   22-JUN-18
TIME -->08:00:00

(SELECT (TO_CHAR(SYSDATE, 'HH24')) from dual);
I HAVE TO REDUCE OR SUBTRACT 2HRS FROM THE ABOVE LINE.

THE ABOVE QUERY WILL REPEATEDLY EXECUTE EVERY 2HRS DAILY-->2,4,6,8....
AND I HAVE TO FETCH LATST 2HRS DATA EVERY TIME.
0
i am facing same problem

SQL>  recover database using BACKUP CONTROLFILE until cancel;
ORA-00279: change 7918209709 generated at 06/20/2018 14:57:16 needed for threa
1
ORA-00289: suggestion : I:\ORACLE\AE1\ORAARCH\AE1ARCH1_256980_926244410.DBF
ORA-00280: change 7918209709 for thread 1 is in sequence #256980


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256980_926244410.DBF
ORA-00279: change 7918242496 generated at 06/20/2018 15:00:28 needed for threa
1
ORA-00289: suggestion : I:\ORACLE\AE1\ORAARCH\AE1ARCH1_256981_926244410.DBF
ORA-00280: change 7918242496 for thread 1 is in sequence #256981
ORA-00278: log file 'I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256980_926244410.DBF'
no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256981_926244410.DBF
ORA-00279: change 7918292819 generated at 06/20/2018 15:02:46 needed for threa
1
ORA-00289: suggestion : I:\ORACLE\AE1\ORAARCH\AE1ARCH1_256982_926244410.DBF
ORA-00280: change 7918292819 for thread 1 is in sequence #256982
ORA-00278: log file 'I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256981_926244410.DBF'
no longer needed for this recovery
0
I am looking for the following Oracle query
   I have a table TB_Docs . The DocNo column has sample values ( Length is not fixed . Also the "_ " can appear anywhere )  
     123_456-23_0
      12-32_1-24567_0
       A1-23_c2_1
      G2/334/1_A

  The "_ " before the last character should be replaced by  "/"  ( For example 123_456-23_0  should be updated as 123_456-23/0 . But it should not update any other  "_ " other than the one before the last character)
0
I had this question after viewing Syntax to import multiple files with Oracle IMP.

I invoke imp by passing parfile=Import_Parfile.txt. Is it still possible in below way? please help

file=file1.dmp,file2.dmp,file3.dmp,file4.dmp
log=file_alll.log
ignore=Y
rows=Y
grants=none
fromuser=SCOTT

Open in new window

0
I have an Oracle database with Table TB_Documents   I need to get all the Docuemnts ending as _0
While I try  DocNo like   '%_0'  , I am getting more rows than the ones I need . The _ seems to be ignored
How can I run a query to get the right result ?
0
Hi Experts,

HashSet<TestData> hashset = new HashSet<>();

using select query getting data from Test1 table and adding data in TestData Object, then finally adding it in hashset.
now hashset is haviing 1000 records.

i want to insert this data in Test2 table(different database).

in Test2 table might have the same record if the record is available need to do update the values
if no data available in Test2 table need to do insetion.

Can some suggest me best way to do insert or update in Test2 table using jdbc.
0
Hi Expert,

I have following data as source!

ID          NAME
----         ---------
10              A
NULL      B
20              C
NULL      D
NULL      E
30              F

Looking for Output as ::

ID          NAME
----         ---------
10          AB
20          CDE
30          F


Thanks In Advance ...
0
Dear Experts,

I have a field in table type VARCHAR2(255), this field contain a number

I cannot convert this field to number because some values are a negative number with minus (-) sign after the number like: 124-

how do I convert this field to number ?

to_number( field) returns error: invalid number

Thanks
0
Upgrade your Question Security!
LVL 12
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Hi,

can you please anyone let me know how to find the business days between two dates in oracle sql ?

Example :

Start Date               End Date            Total Days      Business Days( Weekends Excluded)
01/01/2018            01/31/2018            31                                 23
0
Hi EE,

Just finished setting up connectors to Oracle from SQL Data Engine standpoint now I need to get it working on SSIS.

Troubleshooting so far:

  • Installed the 32 bit and 64 bit Oracle client drivers on the windows server
  • Created a linked server on the SQL Server database engine instance works fine when I apply the whole EZCONNECT string. Doesn't work if just reference the label of the string in TNSNAMES.ORG file.
  • Installed the latest Microsoft Connectors V4.0 on the SQL server 2016 box.
  • Set ORACLE_HOME as a system environment variable.
  • TNS_ADMIN entry is now an environment variable.

Attached is the error I am getting

Any assistance is welcome.

Thank you.
SSIS-Connecton-Error.PNG
0
Hi ,
   I have an oracle 11g r2 table created with union of two tables .    
       TB_User Columns
        =========
         UserID
          Name
           .....

  There are some duplicate UserID ( not caused by union. But I can't change the original source tables  )  .  I need to delete all except one row for a given userID .      I am not able to delete duplicates based on  RowID  as there are duplicate  ROWIDs  after Union
0
Dear Experts,

 I have a table1 containing Sales data for all times
Month | Customer | Product | Quantity | Revenue

I have another staging table contain data for specific interval of months:
Month | Customer | Product | Quantity | Revenue

I want to update the Revenue & Quantity to be 0 in table1 for all the rows which are in the same months interval of staging table & not exist in staging table

each row is identified by Month, Customer & product



I tried the following solution bu unfortunatly ot takes more than half an hour how do I can enhance it ? or provide a better solution


                  update CPDATA.table1 V 
                      SET V.KF_QUANITY = 0 , V.KF_REVENUE = 0 Where ROW_ID in  
                       (
                              Select  ROW_ID FROM CPDATA.STAGINING T Where MONTH >=  StartInterval AND MONTH <= EndInterval  AND
                                   NOT EXISTS   
                                          ( SELECT * FROM CPDATA.STAGINING S  WHERE T.MONTH =S.MONTH  AND T.CUSTOMER = S.CUSTOMER
                                                 AND nvl(T.PRODUCT,'p')  = nvl(S.PRODUCT,'p')  )                                                 
                        );

Open in new window

0
i want to Write clob data into a text file without using utl_file???
0
can any one to provide me a document  to create datagurad  to two nodes  rac  environmet on linux 6.7  ?
0
I had this question after viewing REP-62204: Internal error writing the image BandCombine: a row of the matrix does not have the correct number of entries.

the image files that were created by an adobe application and converted to gif later is not abled to extract by oracle reports 11g
what to do?
0
one data block named Employees  ( which is a Table )

one canvas on which you are showing its data

in menu there are options like:

  Employees

  Employees History

 

when user select Employees then first data block appear and show the data

when user select Employees History then this will show data from table Employees History ( both tables have same number of columns and column names )


create table EMPLOYEES  
(  
  employee_id    NUMBER(6) not null,  
  first_name     VARCHAR2(20),  
  last_name      VARCHAR2(25) not null,  
  email          VARCHAR2(25) not null,  
  phone_number   VARCHAR2(20),  
  hire_date      DATE not null,  
  job_id         VARCHAR2(10) not null,  
  salary         NUMBER(8,2),  
  commission_pct NUMBER(2,2),  
  manager_id     NUMBER(6),  
  department_id  NUMBER(4)  
);  
  
  
  
  
  
  
create table EMPLOYEE_HISTORY  
(  
  employee_id    NUMBER(6) not null,  
  first_name     VARCHAR2(20),  
  last_name      VARCHAR2(25) not null,  
  email          VARCHAR2(25) not null,  
  phone_number   VARCHAR2(20),  
  hire_date      DATE not null,  
  job_id         VARCHAR2(10) not null,  
  salary         NUMBER(8,2),  
  commission_pct NUMBER(2,2),  
  manager_id     NUMBER(6),  
  department_id  NUMBER(4)  
); 

Open in new window

0
hi how to delete file in oracle webutil after download

i try this

PROCEDURE openreportoutput (pfilename varchar2) IS
	V_KDIC_PATH  varchar2(255) :='C:\;
	v_clientfile varchar2(255);
	vfilename varchar2(255);
	vs boolean;
	v_output  VARCHAR2(4000);
	
BEGIN
message('newfrm');

  tool_env.getvar('OUTPUT', v__output);
  vfilename := substr(pfilename, instr(pfilename,'/',-1)+1);
 
  v_clientfile :=webutil_clientinfo.get_system_property('user.home')||'\'||vfilename;

  
   vs := webutil_file_transfer.as_to_client_with_progress(v_clientfile,pfilename,'Downloading ...','OK');
   if vs then
   	   client_host ('explorer '||v_clientfile);
   	   message('Opening report in excel ....');
   	   message(' ');
   else
   	   message('File transfer Error !!!!');
   	   message(' ');
   end if;
   message('del12 ');
   host ('del '||v_ncs_output||'/'||vfilename);
   message('del2 ');
   
end;  

Open in new window

0
Network Scalability - Handle Complex Environments
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Hello!

Does anyone know if below features Deferred Segment Creation, EM Perfmormance Page, ASO native encryption and checksumming and VPD are extra license cost
option? Do I need an EE license or in some cases I can have an SE license?

Regards!
0
hello everyone
 My name is Allam living in  Sudan
i don't know english language perfectly
I'm developer at company and i upgrade the system created by old version of oracle to 10g
but i have problem when i want to run reports that i created shortcuts on Desktop it not run with me
so Please i want help quickly



thanks in advance
0
Hello Experts

I have Table1 contain Month ,  Customer , Region , Sales Office , Quantity, Revenue
Also I have Another similar table contain the same columns ( Table2) except of Revenue not exist in Table2
in each table Month & Customer are unique row,

How do ( in same procedure)
1. I update in Table 1 fields Region, Sales Office , Quantity from Table2 if exist by same Month & Customer ( Revenue in Table1 should not be updated )
2. Set Quantity = 0 & Revenue = 0 in Table 1 for all records which not exist in Table2 by Month & Quantity
3. Insert to Table1 all records which exist in table2 & not exist in Table by same (Month & Customer)

Thanks
0
I had this question after viewing Passing table name as parameter to a PL/SQL Stored procedure?.

How can we drop a table if table name start with UD_% but not any table passed as a parameter
0
Hi,

How you guys connect to Oracle application server, BI answer , BI server, and Oracle OLAP server?

What is the tools to connect and check all detail ,what user right can see all detail ?
0
How to store more than 4000 char and show in oracle?
0

Oracle Database

78K

Solutions

26K

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.