Oracle Database

79K

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.

hi,

any step by step procedure to install oracle on linux VM if I have the RPM package ?

please give configure guide too.
0
I have a function. Please see below.
 function func_get_mdi_window_title ( p_div_no in varchar2 )
      return varchar2 is

      v_title    varchar2(100);
      v_db_name  varchar2(100);
      v_instance varchar2(100);
      v_title1 varchar2(100);
      
      begin

         select global_name into v_db_name
            from global_name;

         if v_db_name = 'DEV_DS.AAR.COM' then
            v_instance := 'DS Development'; 
         elsif v_db_name = 'TEST_DS.AAR.COM' then
            v_instance := 'DS Test'; 
         elsif v_db_name = 'DS.AAR.COM' then
            v_instance := 'DS Production';
         elsif v_db_name = 'MOD.AAR.COM' then
            v_instance := 'MOD Production';
	     elsif v_db_name = 'TEST_MOD.AAR.COM' then
            v_instance := 'MOD Test';
         elsif v_db_name = 'DEV_MOD.AAR.COM' then
            v_instance := 'MOD Development';
         elsif v_db_name = 'DEV_DSGOV.AAR.COM' then
            v_instance := 'GOV Development';
         elsif v_db_name = 'TEST_DSGOV.AAR.COM' then
            v_instance := 'GOV Test';
         elsif v_db_name = 'DSGOV.AAR.COM' then
            v_instance := 'GOV Production';
         end if;
         if nvl(v_title,'x')='x' then
            v_title := 'Div '||p_div_no||'-IMOPS '||v_instance||' Application System';
        
         end if;
         
        return(v_title);
      exception when others then
         v_title := 'Div '||p_div_no||'-IMOPS Application System';
         return(v_title);
  

Open in new window

0
Oracle database function-  window title- set title color based on instances

 marked title is derived like below


   set_window_property
    ( forms_mdi_window
     ,title
     ,pkg_application_standards.func_get_mdi_window_title(:global.g_div_no));

pkg_application_stadards.func_get_mdi_window_title(:global.g_div_no) gives that marked title

  SELECT name
  INTO   v_name
  FROM   v$database;

function func_get_mdi_window_title ( p_div_no in varchar2 )
      return varchar2 is

      v_title    varchar2(100);
      v_db_name  varchar2(100);
      v_instance varchar2(100);
      begin

         select global_name into v_db_name
            from global_name;

         if v_db_name = 'DEV_DS.AAR.COM' then
            v_instance := 'DS Development';  
         elsif v_db_name = 'TEST_DS.AAR.COM' then
            v_instance := 'DS Test'; 
         elsif v_db_name = 'DS.AAR.COM' then
            v_instance := 'DS Production';
         elsif v_db_name = 'MOD.AAR.COM' then
            v_instance := 'MOD Production';
	     elsif v_db_name = 'TEST_MOD.AAR.COM' then
            v_instance := 'MOD Test';
         elsif v_db_name = 'DEV_MOD.AAR.COM' then
            v_instance := 'MOD Development';
         elsif v_db_name = 'DEV_DSGOV.AAR.COM' then
            v_instance := 'GOV Development';
         elsif v_db_name = 'TEST_DSGOV.AAR.COM' then
            v_instance := 'GOV Test';
         elsif v_db_name = 'DSGOV.AAR.COM' then
            v_instance := 'GOV 

Open in new window

0
Hi Team ,

iam running the below program on oracle varray . I need to print the current element , previous element in the dbms_output statement. But i get the below error  when i run the program

Error starting at line 2 in command:
declare
type v_numvarr  is varray(10) of number;
l_numvarr v_numvarr;
cnt simple_integer:=1;
begin

l_numvarr :=v_numvarr();
l_numvarr.extend;
l_numvarr(1):=10;
l_numvarr.extend;
l_numvarr(2):=20;
l_numvarr.extend(8);
l_numvarr(3):=30;
l_numvarr(4):=40;
l_numvarr(5):=50;
l_numvarr(6):=60;
l_numvarr(7):=70;
l_numvarr(8):=80;
l_numvarr(9):=90;
l_numvarr(10):=100;
-- l_numvarr(11):=110 this will cause an error , because we are accessing invalid index  position , the maximum numbe is 10 , we are accessing 11
dbms_output.put_line ('The total size of the array is: '||l_numvarr.limit);

/*
for i in l_numvarr.first..l_numvarr.last
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i));
end loop;
*/

l_numvarr.trim;

for i in l_numvarr.first..l_numvarr.last
loop

dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i) ||l_numvarr(l_numvarr(l_numvarr.prior(i))) );

end loop;

/*
l_numvarr.trim(2);


for i in l_numvarr.first..l_numvarr.last
loop

dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i));

end loop;*/
end;
Error report:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
0
hi,

in Oracle we know that adding more RAM can improve the performance but is the buffer pool cache will automatically enlarge to take advantage of the newly added RAM ?

or we need to configure that manually ? if so, how ?
0
hi,

any offical, practical guide to migrate Oracle and MS SQL to Azure MS SQL ?

Azure do not support Oracle DB yet, right?
0
I have a records loaded from a tool into a table as follows. The audit columns start_dt and end_dt and valid_fl is not loaded if the multiple records exists for the same empid. But after initial the audit column value is inserted properly. I am trying to come up with a script to update the audit columns after intitial load as followsas follows

empid   salary_rt   eff_dt                  start_dt               end_dt                                     valid fl

100       abc          10/05/2014         10/05/2014       06/04/2015                             N
100       lmn          06/04/2015         06/04/2015       05/10/2019                             N
100       pqr           05/10/2019         05/10/2019       12/31/9999 (Future date)     Y
200       lkd           04/02/2018          04/02/2018      05/20/2019                             N
200       fjk            05/20/2019          05/20/2019      12/31/9999 (Future date)      Y

Is there a way to do it in plsql?

Thanks
0
Hello, I'm trying to up an Oracle Database for a customer, and i have a few issues with it.

When i'm on a DOS and type "lsnrctl status <service_name>
i got an answer "Instance <name>, status BLOCKED, comport 1 handler(s) for this service..."
Command Success. (Could be not 100% accurate as I trad myself from french)

In a other hand, when i'm trying to connect with SQLPlus on the DB,  i got the following :
"ORA-01033: ORACLE initialization or shutdown in progress"

All services linked to Oracle are running exept OracleRemExecServiceV2.

As my lsnrctl start, stop and status gives answers, I dont know if problem comes from my listener.ora/tnsnames.ora (that was the previous problem I got and think have pass through).

Also SQLDeveloper send back the ORA-01033 error too.

The oracle DB version is 12c 12.1.0.2.0.
OS is Windows Server 2016 64 bits

Blocked Message afterr lsnrctl status

ORA 01033 at login attempt
I've tried to shutdown then startup DB, reload services.
The only thing I got is tnsping answer well.

If you need anything more for precision feel free to ask.

Thank by advance if you have an answer, I digged in many sites but no one give a clear answer that applied well for me.
0
Hi Team,

Iam running Oracle 11g r2 database , Iam getting the below error when I try to login into the database. Iam new to Oracle DBA .

I tried logging in as below and run the below command

sqlplus / as sysdba

sql> alter database orcl open;

I get the below error

rror starting at line 1 in command:
alter database orcl open
Error report:
SQL Error: ORA-00355: change numbers out of order
ORA-00353: log corruption near block 9972 change 57969482 time 01/28/2020 17:08:22
ORA-00312: online log 1 thread 1: 'E:\APP\oracomp\ORADATA\ORCL\REDO01.LOG'
00355. 00000 -  "change numbers out of order"
*Cause:    A change number found in the redo log is lower than a previously
           encountered change number. The log is corrupted in some way. The
           corruption may be at the earlier change or at this one.
*Action:   Do recovery with a good version of the log or do time based
           recovery up to the indicated time.


Kindly let me know what is the solution for this error and cause of the error. Any help is really appreciated
0
I have the following table:

clientid      configid      configvalue
100            a                  10
100            b                  20
100            c                  30
200            a                  10
200            b                  50
300            a                  a
300            d                  2/1/2020
400            d                  3/1/2020

I want to retrieve all the client id's who do not have configid, or if they have configid, the configvalue is in the future. Any ideas?
0
Hello Experts,

After lot of efforts I did my SSIS pkg working in my local, which connects to Oracle DB using Oracle.ManagedDataAccess.Client dll(32-bit). After deploying it to 2016 server it says below and also I was using script task in the SSIS pkg 2017 and the target version is sql serer 2014.

Cannot load script for execution.



Thanks,
ASPDEV
0
I rebooted our HP UX rx2800 system and went to login to the oracle database it runs but it hadn't automatically started as it normally does, so I started sqlplus as user oracle and typed "connect / as sysdba" as I normally do and got "you are connected to an idle instance", so I type "startup" and got the following error "SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=rx2800)(PORT=1521))'
SQL>"

Other than rebooting there wasn't anything done to the system.
Any ideas?
0
I have a db (SOURCE) that is Oracle Enterprise Edition 12.1.0.2, and its national character set UTF8.

I have an Oracle Database Appliance on which I will create an Oracle EE 12.1.0.2 DB (TARGET) to duplicate (RMAN) my source DB to, and during the creation of it I am presented with the option to choose either UTF8 or AL16UTF16 as my national character set.

Do they have to match? Both UTF8 for the duplication process to work?
0
Hi Experts

we have ERP system SAP running on red hat Linux 7  using oracle 12C

from time to time our file system which reserved for ora logs  get full so fast out of the sudden

we are not able to know the root cause which  

process or transaction or user  generate such amount of logs

do you know anyway or tool to know such info


thanks
0
Hello Experts,
I want to know how to compile invalid SYS objects especially "SYS.DBMS_UTILITY" as I've many invalid objects in SYS schema and when I try to compile them I get the following error message:

ORA-20001: Cannot recompile SYS objects
ORA-06512: at "SYS.DBMS_UTILITY", line 387
ORA-06512: at line 1

Oracle version: 11.2 g

Thanks,
Daniel Mina.
0
Hi Team,

Need an help in writing a query as below in HR schema in oracle 12c .  I need to convert the rows into columns

Employees table

First_name
department_id
Job_id


Department Id ST_MAN ST_CLERK  SH_CLERK
50                         joseph      vignesh         ratnavel      
50                         christine      sed               govinda
50                         rajesh          rajiv              
50                                              gopal        






Iam trying the below quey , but not able to aggrgate all the names in a column




Select department_id,
max(case when job_id='ST_MAN' then
first_name
end) ST_MAN,
max(case when job_id='ST_CLERK' then
first_name
end) ST_CLERK,
max(case when job_id='SH_CLERK' then
first_name
end) SH_CLERK
from employees
where department_id=50
group by department_id

But iam not getting all the nams in the order as shown in the output. Any help is really appreciated
0
Good afternoon all. I have a dilemma that I was hoping to get some help with. I thought my task was simple, but I don't understand enough about JavaScript or I'm missing something obvious. I'm basically trying to create a drop down box, and load with values from a simple Oracle database query. Easy enough. However, what I want is some additional text type form fields being filled in based on the users selection from the drop down box, also pulling that data from the database. Ive seen some examples and put something together that I thought should work, but I cant seem to get the text form fields to fill in from the selection of a drop down box. My code is below. If anyone could shed some ight on my mistakes I'd greatly appreciate it.

<cfquery datasource="PDBDEV" name="allusers">
SELECT *
from MCA.COR_POC_LIST
</cfquery><head>

<script type="text/javascript">
var users = [
<cfset variables.fs = "" />
<cfoutput query="allusers">
  #variables.fs#{
   "userID": "#jsStringFormat(ID)#",
   "userFirstName": "#jsStringFormat(fund_name_f_1)#",
   "userLastName": "#jsStringFormat(fund_name_l_1)#",
   "userPhone": "#jsStringFormat(fund_phone_1)#"
  }
  <cfset variables.fs = "," />
</cfoutput>
];
</script>

<script type="text/javascript">
      function selectUser(list) {
            // assume first item is empty
            if (list.selectedIndex > 0) {
   var userID = list.options[list.selectedIndex].value;
   var userFirstName = …
0
Add up Duplicate rows in Oracle 10g
Iam trying to add up duplicate rows in oracle
I have a table with data
PRODUCTID                 LOCATORID                  ATTRIBUTEID            QTYONHAND
1                                              2                                        3                                    -12
1                                               2                                        3                                    -6

Iam trying to make this into a single row as

PRODUCTID                 LOCATORID                  ATTRIBUTEID            QTYONHAND
1                                              2                                        3                                    -18
1                                              2                                        3                                      0

Basically zero out the duplicate row and add up the qty in the other row.
Is this feasible,Please let me know the approach.
0
I keep receiving "27/1  PLS-00103: Encountered the symbol "CREATE" Errors: check compiler log" error when running this create function below and cannot find the reason why. The code is below. Seems Experts Exchange removed the function that encapsulates code within the ask Window or my browser is not showing it. Apologize for displaying this way below. I also attached it.

create or replace FUNCTION BA_WO_EXCUSABLE_DELAY_V2(p_woo IN NUMBER,p_before IN date)
RETURN NUMBER
IS V_RETURN NUMBER(11,2);
BEGIN
select round(sum(case when time_in_status.start_time>nvl(p_before,sysdate) then 0
	else case when time_in_status.stop_time<=nvl(p_before,sysdate) then time_in_status.stop_time-time_in_status.start_time
	else nvl(p_before,sysdate)-time_in_status.start_time end end))
INTO V_RETURN
from (
	select adt.stamptime start_time
	,nvl(
		(select min(stamptime) stop_time from audit_trail
		where source_table='WOO' and source_field='STATUS' and orig_value=statuses.status and source_ak=adt.source_ak and stamptime>adt.stamptime)
		,sysdate
		) stop_time
	from audit_trail adt
	,(
		select descriptiON status,severity,status_type,delay_flag from wo_status
		where status_type='Delay' and delay_flag='T') statuses
	where adt.source_table='WOO' and adt.source_field='STATUS' and adt.new_value=statuses.status and adt.source_ak=p_woo
) time_in_status;
RETURN(V_RETURN);
END;

--BA_WO_EXCUSABLE_DELAY_V2
------------------------
create or replace FUNCTION BA_WO_EXCUSABLE_DELAY_V2(p_woo IN 

Open in new window

0
We have an Oracle 11g database running on HP UX.  A temporary file suddenly went from 4GB to 9GB and filled up the disk.  How do I shrink the file down?
The output from BDF and the db directory listing (db02/DATA) are below:

# bdf
Filesystem        kbytes       used       avail      %used      Mounted on
/dev/vg00/lvol3   1376256     425760      943160      31%           /
/dev/vg00/lvol1   1835008     204800     1617552      11%           /stand
/dev/vg00/lvol8   8912896    1626104     7239672      18%           /var
/dev/vg00/lvol7   7798784    3366744     4397480      43%           /usr
/dev/vg00/lvol10  12582912   6210586     5974795      51%           /usr/oracle
/dev/vg00/lvol11    524288    110703      387854      22%           /usr/cognos
/dev/vg00/lvol9    5242880   2199235     2853451      44%           /users
/dev/vg00/lvol4     524288     27368      493368       5%           /tmp
/dev/vg00/lvol6   11468800   6081280     5345552      53%           /opt
/dev/vg00/lvol5    6291456   1404456     4848880      22%           /home
/dev/vg00/lvol19  10485760   3250489     6783072      32%           /db08
/dev/vg00/lvol18  10485760   4821905     5309870      48%           /db07
/dev/vg00/lvol17  10485760    745704     9131308       8%           /db06
/dev/vg00/lvol16  10485760   1266390     8643163      13%           /db05
/dev/vg00/lvol15  10485760   2970481     7045579      30%           /db04
/dev/vg00/lvol14  10485760    772136     9106528       8%…
0
I have a temporary membership database that has some duplication.  The problem is the on the effective and expiration dates.  They don’t match exactly but they fall inside the other records effective and expiration dates.  Here is an example:

Member_ID      Eff_Date      Exp_Date      Plan      Group
A1234567      1/1/2019      12/31/2019      110      AG569
A1234567      10/1/2019      10/31/2019      110      AG569

I there a way suing in sql to identify the duplicate inside the effective and expiration dates (2nd record) so that I could filter them out.  I don’t want to delete them, just be able to identify them.  Something like adding a field titled “Dups” that would have a “Y” in it if it is a duplicate.

The code for a simple query would be
SELECT Member_ID, Eff_Date, Exp_Date, Plan, Group
FROM Membership;

Thank,
Scott
0
I'm trying to create an Oracle SQL query that will tell me what files are missing.

I have a lookup table that desribes when each file is due (D=daily, W=weekly, M=monthly; expected day is Oracle where Sunday =1; all columns are varchar2):

tblRef=
FileName   Frequency    ExpectedDay                          ExpectedTime
File_D           D                     [NULL]                                17:00:00
File_W          W                    2 (Monday)                        17:00:00
File_M           M                     10 (10th of the month)    17:00:00

I have a transaction table that contains each file delivered (FileDate is an Oracle DATE):
tblFiles=
FileName     FileDate
File_D           20-JAN-2020 11:05:00        
File_D           21-JAN-2020 11:05:00
File_M          10-JAN-2020  08:00:00

In this example, the query would return "1", since only File_W is missing. All files are historical (cumulative), so I don't need to worry about past missing files. I only want to see what is missing at the current point in time. This query would run periodically throughout the day, maybe once every 30 minutes.

In this example:

tblFiles=
FileName     FileDate      
File_D           20-JAN-2020 11:05:00
File_D           21-JAN-2020 11:05:00
File_W          21-JAN-2020 12:00:00
File_M          10-JAN-2020  08:00:00

The query would return 0 (or NULL), since although File_W was received late (on day 3, Tuesday), at the moment this query was run …
0
hello expert
I want to update column time_1 in the experiment table.
I want to update time_1 column between 1-10 minutes.
I want to update the current time randomly 1 to 10 minutes ago
I have written the sample data below
create table deneme
(
time_1 date
)
insert into deneme values(to_date('16.01.2020 17:17:27','DD.MM.YYYY hh24:mi:ss'))

insert into deneme values(to_date('16.01.2020 12:12:27','DD.MM.YYYY hh24:mi:ss'))

insert into deneme values(to_date('16.01.2020 12:07:27','DD.MM.YYYY hh24:mi:ss'))

insert into deneme values(to_date('16.01.2020 12:13:44','DD.MM.YYYY hh24:mi:ss'))

insert into deneme values(to_date('16.01.2020 12:33:44','DD.MM.YYYY hh24:mi:ss'))

Open in new window


before update=16.01.2020 17:17:27  && ı want  after update 16.01.2020 17:10:27  --There's a 7-minute time difference between the two dates.
before update=16.01.2020 12:12:27  && ı want  after update  16.01.2020 12:09:27--There's a 3-minute time difference between the two dates.
before update=16.01.2020 12:07:27 && ı want  after update  16.01.2020 12:02:27 --There's a 5-minute time difference between the two dates.
before update=16.01.2020 12:13:44 && ı want  after update 16.01.2020 12:11:44 --There's a 2-minute time difference between the two dates.
before update=16.01.2020 12:33:44 && ı want  after update 16.01.2020 12:24:44 -- There's a 9-minute time difference between the two dates.
I've written an update like the following but it gives the error.
update deneme xx
set
 xx.time_1 = xx.time_1 - (interval (round(DBMS_RANDOM.VALUE (1, 10))) minute ) 

Open in new window

How can I fix where I'm making the error.
thanks.
0
I'm no sql scripting person & need an sql script to
print out (ie save to disk file) the list of dormant
users from our Oracle DB for last 30 days:

select * from ACTION_LOG where ACTION_DTTM >= trunc(sysdate-30)
  and ACTION_CD in ('SORAS') order by ACTION_DTTM DESC;

Will need someone to prefix it with
set spool ....   DormantAccts_YYYYMMDD.spl

to save output to a spool file which we'll email out
0
I'm doing testing on a data warehouse.  I have to keep resetting the sequences. I'm looking for a quicker way to do it

some like this......

DROP SEQUENCE SEQ_PAY_STG_DAY_CAL;

CREATE SEQUENCE SEQ_PAY_STG_DAY_CAL
  START WITH (SELECT  last_number
  FROM all_sequences
  WHERE sequence_name = 'SEQ_DIM_CALENDAR')'
  MAXVALUE 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;



I have about 20 to do.


Any ideas? Thanks
0

Oracle Database

79K

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.