I receive the output from only one of the nested blocks:
bad
PL/SQL procedure successfully completed.
SQL>
I understand that I don't need nested blocks for the example above, but this was just a condensed version of what I'm trying to do. I think nesting blocks will be easier to read and maintain, instead of having a huge CASE statement.
How can I execute only the nested block for which the condition is true and ignore the nested blocks that follow?
Are nested blocks not the correct answer here? Should I be looking at invoking procedures/functions instead?
While increasing the tablespace i am getting below error. How to handle this
SQL> set lin 300 SQL> col TABLESPACE_NAME for a25 SQL> col FILE_NAME for a65 SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE,sum(BYTES/1024/1024) MB 2 from dba_data_files where TABLESPACE_NAME='SYSAUX' group by TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE order by sum(BYTES/1024/1024) DESC,file_name;
TABLESPACE_NAME FILE_ID FILE_NAME AUT MB ------------------------- ---------- ----------------------------------------------------------------- --- ---------- SYSAUX 3 /ora2/oradata/dbname/sysaux_01.dbf NO 300
SQL> Alter database datafile 3 RESIZE 60000M; Alter database datafile 3 RESIZE 60000M * ERROR at line 1: ORA-01144: File size (7680000 blocks) exceeds maximum of 4194303 block
We need to do load testing on our DB before going live. Mainly with 5 queries which are mainly used in our web interface to pull data from the database. For example. I need to send morethan 400-500 sessions to our database using this 5 queries (with different values) and to note down the time taken with every 50 number of users..
Oracle 10.2.0.4 I belive the following query is incorrect in my opinion. (there is an index on col but NULLS are ignored?
SELECT COUNT(*) from <TABLE>
where col in (null,'a','b','c') this works (no errors) and returns pretty quick. However I think the correct query would be SELECT COUNT(*) from <TABLE> where col IS NULL OR col in ('a','b','c') This one takes a long time. As I see it it does a table scan for NULL part and uses the index for the rest as the index cannot be used for NULL values. explanation on this, especially why Oracle accepts the first query "where col in (null,'a','b','c')" without any issue.
My task is to test a field in a certain database. We shall refer to the field as ship_to. There is an algorithm for which the field ship_to is populated and higher up in the algorithm, a variable we shall call X is created. The algorith states that I should assign the variable to ship_to unless X is = -1. If X is = -1, the algorithm continues with multiple joins and assignments. What would be the best way to go about coding this solution. There are 4 individual paths. I have only described the first path, but they are similiar in structure. I was thinking of using either Case's or decodes?The field ship_to is a number. I have already created a statement to test the sum of the Target, but now I need to test the entire algorithm to see if it too sums the Target.
I work as a Sys. Admin. for several RHEL 3.8 servers, most of them are clusters of 2 machines. All these servers are running Oracle 9.2.0.7 database. They are running fine on a separate filesystem. So everytime the system has to be formatted for some particular reason, there is no need to re-install the database.
I am trying to make some tests by running the same filesystem containing the oracle database in a fresh Red Hat Enterprise Linux 4.8 X86_64 Install. This task has become impossible, and I'm not quite sure why.
I installed all the compat- packages required for a fresh oracle 9 install in a RHEL4 machine, but at the time of stating the STARTUP sentence, it gives me the next error:
CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2.
After this error (repeated) it says something like: Cannot start an already running database.... But if i stat a shutdown sentence, it says that the instance has not been initialized....
I don't know whether i have to re-install all the oracle software in order to make a clean install in the new kernel version or not, i tried to apply a patch, and the oracle installer recognized the installation i had.
I think it might be because the original system is configured to work as a cluster, and i'm running it on only a virtual machine.
I am trying to add a not null constraint on a collection for testing. It not allowed null values as below
pointers@ORCL> declare 2 type test_type is table of number not null; 3 t_test test_type; 4 begin 5 t_test := test_type(2,4,null); --null is being added 6 dbms_output.put_line('Type variable is initialized and the first element value is '||t_test(1));
[code]....
But what I observed is, i can add, null elements by using 'EXTEND' routine though 'not null' constraint is used for that type.
The below is the example.
pointers@ORCL> ed Wrote file afiedt.buf 1 declare 2 type test_type is table of number not null; 3 t_test test_type; 4 begin 5 t_test := test_type(2,4);
I have problems in opening the database of the physical standby in read- write mode/ read only mode. I have a primary server which is running on 2 node RAC and the standby on a seperate single server being used as DR. I recently got this server and my aim was to isolate the standby server from primary server and perform few test. As it has never been tested even once.
Primary Database spec: (2 Node Rac on ASM) Oracle Version : 10.2.0.3.0 O/s : HP-UX B.11.23
alter database recover managed standby database cancel;
Database altered.
SQL> alter database open 2 ; alter database open * ERROR at line 1: ORA-16004: backup database requires recovery ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '+DATA/dprod/datafile/system01.dbf'
Steps tried so far: Changed log_archive_dest_2 = DEFER on both the primary nodes
Standby :
startup nomount alter database mount standby database; alter database recover managed standby database disconnect; alter database recover managed standby database cancel; alter database open/readonly (tried both) Same error.
On Primary: SQL> select max(sequence#) from v$log_history;
Additional Information : There is a delay of 20 minutes before the logs get applied. which has been intentional set by team. Dataguard broker is not configured as well.
I'm using Oracle Database 11g R2 for study purposes.Currently I'm learning about the DBCA clonic templates.I have an Oracle DB 11g R2 X86 running properly in Ms Windows XP Professional SP 3 X86, using DBCA I created the seed template file .dbc and the .CTL and .DBF files, later I copied those files to a server running Oracle DB 11g R2 X86 in Ms Windows 7 Ultimate X86. Again, using DBCA I successfully created the source database through the seed template file, everything was ok.
Now, I formatted my testing server and I installed Ms Windows 7 Ultimate X64 and Oracle Database 11g R2 X64. I copied the seed template file .dbc and the .CTL and .DBF files to "assistantsdbca emplates" directory. Well, I started DBCA to try create the source database and when the DBCA is creating and starting the Oracle instance it shows the errors:
i am testing a proc after tuning it but the problem is, it is taking a very very less time which it shouldn't. I know that it is because of the buffer cache and the shared pool. that why i need to clean the cache to retest it.
I cannot bounce the database as other schemas are part of it. so is there any way to clean the cache for that particular schema i.e bouncing any particular schema(i know that the term is not appropriate).
I have to implement a new logic such that , Old logic which pulls data will stay in place.now New logic pull data form SAP . So i have implemented the new logic in to the existing cursor as below.
i.e.
Cursor new_sap old_query Union New_query;
I have 3 new cursor like dis. How to process these old and new cursors in FOR loop.. Ex:
For c1 in C2(order_number) if order_source is sap then ( dis filters old data which is not in SAP) new query in cursor should process else old query in cursor should process end if;
i have three tables: ot_req, ot_po_breakup, and ot_po. when a row is inserted into the ot_po table reffering to ot_req,there is a trigger which creates a seperate reocrd in ot_po_breakup with the details of ot_req ot_req.ri_sys_id as pb_ri_sys_id and ot_req.ri_qty as pb_ri_qty ,pi_sys_id as pb_pi_sys_id .upto this part is okand when i insert also the logic is okay.
i have created a trigger to update the rows in the ot_po_breakup table after insert on ot_poin order of pb_ri_sys_id and pb_pi_sys_id and try to update the values in the columns pb_ves1q, pb_ves2q, and pb_ves3q order. i am trying to take the quantities in ot_po.pi_qty and insert them into the pb_ves1q, pb_ves2q, and pb_ves3q columns of ot_po_breakup where those columns are empty (0 or null) such that the sum of those three columns for that row does not exceed the pb_ri_qty in that row.
My problem is i need to mofify my trigger to do the update and delete , that is whenever the user is updating the column ofot_po.pi_qtythe qty should in ot_po_breakup should get updated accordingly and sum of pb_ves1q,pb_Ves2q and pb_ves3q should be equal to pb_ri_qty and do the same for delete as well
CREATE TABLE Emp_addrs ( EMP_ID NUMBER(15) NOT NULL, ADDRESS_ID NUMBER(15) NOT NULL, SITE_USE_ID NUMBER(15) NOT NULL, SITE_USE_STATUS VARCHAR2(1 BYTE) NOT NULL, SITE_USE_CODE VARCHAR2(30 BYTE) NOT NULL)
Insert Script code :
insert into Emp_addrs values ( '1207' , '1846', '2342','A'); insert into Emp_addrs values ( '1207' , '1846', '2343','I'); insert into Emp_addrs values ( '61618' , '165200', '261449','A');
[Code]...
A combination of emp_id & address_id can have multiple site_use_id's. I want to select the max(site_use_id) where site_use_status ='A'. Now Site_use_status can have either = 'I' or 'A' value.
For a combination of emp_id and address_id , there could be cases when there is no record with site_use_status ='A'. In such cases I need to select the max(site_use_id) (and obviously site_use_status ='I').
Just to clear my requirements, out of the above records I want the following records:
i am using one query but not getting correct minutes.
here is my query:
v_Interval:= to_timestamp(v_temphrs,'HH24:MI:SS')-to_timestamp(v_outpunch1,'HH24:MI:SS'); v_TotalHrsMin1 := extract(hour from v_interval) * 60 + extract(minute from v_interval);
here v_interval datatype is "interval day to second" and v_temphrs datatype is varchar2 and value is : 12:00:00 and v_outpunch1 datatype is varchar2 and value is: 06:10:00 and v_totalHrsMin1 datatype is number.
here i should get value 370. but i am getting value 350.
CREATE OR REPLACE VIEW V_CATALOGUER_REPORT (COUNT, EVENT_USER, TO_DATE, SHORT_NAME) AS SELECT COUNT (DISTINCT A.PART_REF),A.EVENT_USER, TO_DATE(A.EVENT_DATE, 'DD-MON-YY'),B.SHORT_NAME FROM J_SUPPLY_CHAIN_HIST A, J_SOURCE B
[code]...
i created one view,i link two tables ,i can see the correct data in this view,but the result from front end application is not correct.i cant see the dates are correct.
select job,case when deptno=10 then ename ELSE 'null' end e10 ,case when deptno=20 then ename ELSE 'null' end e20 ,case when deptno=30 then ename ELSE 'null' end e30 from emp order by 1;
Basically what I want is that I need to get desired result in such a way that, whenever Transaction type is Sales Order Issue, I want last TRANSACTION_COSTED_DATE of 'Intransit Shipment'
INVENTORY_ITEM_ID TRANSACTION_COSTED_DATE TRANSACTION_TYPE R 123 28-06-2012 21:36 Intransit Shipment 123 23-07-2012 01:25 Sales order issue 28-06-2012 21:36 123 30-07-2012 05:20 Sales order issue 28-06-2012 21:36
[Code]...
Lag with offset 1 doesn’t work as it will only go to previous row, What I want is that it should go to row above where transaction type is Intransit Shipment
I need to run a query for students that took ALL the following courses: 6710, 6711, 1032, 1035 and ANY of the following courses: 3061,3065
here is my query: =SELECT distinct student_number, last_name, first_name, gender, ethnicity, students.grade_level, lunchstatus, course_name, course_number ,termid from students inner join storedgrades on students.id=storedgrades.studentid where students.schoolid='0976111' and (students.grade_level >'9' and students.grade_level <= '12') and (course_number = all('6710', '6711', '1035')and course_number=any('1031','1032','3061','3065','3062', '3401', '3082')) order by last_name, first_name, grade_level, course_number
The problem is that I'm not getting any results but I know for a fact that some students took the required courses. On a side note, if I change the ALL to ANY - I do get results - but it is not what I want because it would be hard to keep track of the students that met the requirements.
I have a BEGIN Block of the code where a loop is running .I also have a CONTINUE statement in the EXCEPTION Block of the code where I return the control to the BEGIN Block in case of any exception.Now as per ORACLE Company, CONTINUE has a old issue which is already registered by Oracle Company Bug 7306422.
The issue is where CONTINUE statement does not run properly when your code Optimization level is 2. However it will work properly if we have the Optimization Level as 0 and 1.are facing the same issue here and have a workaround.
Is there any code which I can permanently use instead of CONTINUE but have the same logic incorporated.i.e returning to the next iteration whenever I say to pass the control.
i am trying to upgrade my existing system (6i) to 11g. I have installed Weblogic 10.3.5 with forms and reports 11.1.2.1.0 on windows 7 32 bit. I can connect to my database (10g) from Form builder.
But when I try to open a form/pll of 10g or 6i, it says PDE-PLI018 could not find library and it shows the path of my earlier folder, which now does not exist.
I have made changes in registry FORMS_PATH, FORMS_BUILDER_PATH, UI_ICON And in default.env file FORMS_PATH and CLASSPATH
I have added entry to tnsnames.ora, through which I can connect to DB. And have followed below site to configure weblogic and FM forms and reports;
[URL]
1) Do we have to connect (create a bridge) to DB from weblogic except tnsnames.ora.
2) For library error I think I am missing some settings to be done.
3) If I am able to rectify above error then can I directly use [URL] and access the system from another pc, or still there are some modifications needed.
create table t (a varchar2(20),b number(8)); insert into t values ('aa',4); insert into t values ('ba',6); insert into t values ('ca',7); insert into t values ('da',8);
in place of 8 there can be any number between 1 to 100
if in place of 8 number is <10 insert into t values('ea',10); ---- this i need dynamic insert if in place of the number between 11-19 then insert into t values('ea',20); ---- this i need dynamic insert and so on
i tried as below
select case when max(b) <10 then 10 when max(b) between 10 and 20 then 20 end from t;
but i cant write case for again and again upto nth
I have oracle 10g R2 on windows.I have two tables and say table A ant Table B. Both have column Total_amount. There are primary key's on Both tables.
Sample query SELECT A.EMPNO,A.EMP_CODE,nvl(A.TOTAL_AMOUNT,0) "A_TOTAL_AMOUNT", nvl(B.TOTAL_AMOUNT,0) "B_TOTAL_AMOUNT",(nvl(A.TOTAL_AMOUNT,0) - nvl(B.TOTAL_AMOUNT,0)) "DIFFERENCE" FROM A, B WHERE A.EMPNO =B.EMPNO (+) AND A.EMP_CODE = B.EMP_CODE (+) AND round((nvl(A.TOTAL_AMOUNT,0) - nvl(B.TOTAL_AMOUNT,0)),3) <> 0Above query retrives only Non-matching rows.
create table test (id number, last_name varchar2(15), first_name varchar2(15), empno varchar2(15))
select * from test;
insert into test values (143,'frank','kadel,watson','j2098,k09876'); insert into test values (143,'steve','kadel,watson','j0987i,kuy765'); [code]....
The requirement is as follows: I need to split the rows by first_name and assign the respective empno in the results child rows if there is any.
For example:- Where id = 143, the resultset should be like this.
ID LAST_NAME FIRST_NAME EMPNO ---------- --------------- --------------- --------------- 143 frank kadel j2098 143 frank watson k09876 143 steve kadel j0987i 143 steve watson kuy765
sofar, i am able to come with the query to split the records by last name but unable to find the way to extract the respective empno and assign to the splited records correctly.
