SQL & PL/SQL :: Combining New Logic Using Old And New Cursor
Aug 22, 2011
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'm having some trouble combining some data. I've done a lot of research into joins and merges, nothing really seemed to do what I needed. I could be over thinking it.
Basically, I want the query to look at the "info" table from a specified time-span (1 to 7). I want it to look at the source of the data and if it is "one_better" always use that data for that specific time. Otherwise, return the data from "one". The results don't care about the source of the data.
The "info" table I'm working with looks like this:
TIMEDATASOURCE 1DATA1one 1DATA1betterone_better 2DATA2one 3DATA3betterone_better 4DATA4one 4DATA4betterone_better 5DATA5 one 7DATA7one
A basic select and group by query I am optimising for my Database course has returned results that indicate it will perform better on a clustered index when returning a smaller number of rows (5% of the largest table) and on a hash clustered index when returning higher volumes (50% and 80%). I understand that it is possible to use more than one index type on a table to improve performance, but I am struggling to understand how I might establish a hash cluster and a cluster on the same table? and then use hints to drive the query down one access path or the other.
1. I Wnat to analyze the buffer cache hit ratio. This is what i did.
DECLARE bufcac NUMBER(10, 2); BEGIN
[Code]....
2. I would like to analyze the PGA and determine what percentage out of the maximum allocated PGA is being used. I tried the code below but can't find the percentage. Sad
Is there any way to combine an identifier and select statement in PL/SQL when using the insert into command?
e.g.
DECLARE name := 'BOB'; BEGIN insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP) name, (select min(time), max(time) from lLAP_TIMES); END;
In the above statement I am trying to insert the identifier "name" (BOB) into MYTABLE.NAME, along with the result of the select query from the table LAP_TIMES ... min(time) into MYTABLE.SLOWEST_LAP, and, .... max(time) into MYTABLE.FASTEST_LAP.
If the above is possible in one statement how would I also combine an identifier, with two select statements into an "insert into" statement?
e.g.
name := 'BOB'; insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP, EVENT) name, (select min(time), max(time) from LAP_TIMES), (select race_event from MEET);
In the above example I am also trying to insert the result from MEET.RACE_EVENT into the column MYTABLE.EVENT
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
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.
SQL> CREATE OR REPLACE TRIGGER TRI_COMPL_FEATURES 2 AFTER INSERT OR UPDATE ON COMPLEMENTS FOR EACH ROW 3 DECLARE 4 v_fno NUMBER; 5 v_tab VARCHAR2(30); 6 v_unique_id VARCHAR2(40); [code]....
Trigger created.When I am trying to insert into complements table it is throwing error as follows:
SQL> insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL); insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL) * ERROR at line 1: ORA-20010: ORA-04091: table TEEMNGWS.COMPLEMENTS is mutating, trigger/function may not see it ORA-06512: at "TEEMNGWS.TRI_COMPL_FEATURES", line 19 ORA-04088: error during execution of trigger 'TEEMNGWS.TRI_COMPL_FEATURES'
I can understand that I am trying to perform DML operation(i.e select) on table which trigger is fired.But how can I implement the same logic when AFTER INSERT OR UPDATE trigger is written.
The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.
I installed web logic server and form & report on my linux system and gave configuration option later . so now web logic admin server is running on different port and wls_reports server is running to different port in cluster. How its running on cluster and when i run
localhost:9002/reports/rwservlet/getserverinfo?server="report_server_name" i am getting rep-51002 error.
I'm having a problem with null values. I want to display old student in a display item if subjects text item has records. Otherwise, display new student if it has null values. This is the code that i tried so far..
IF :block2.subjects IS NULL THEN :block3.type := 'NEW STUDENT'; ELSE :block3.type := 'OLD STUDENT'; END IF;
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?
I have written the following PL/SQL block logic tofetch the records from the emp table and compare the records with emp_10 table to perform insert if the records are newelse to perform update the existed records in the emp_10 table.
DECLARE CURSOR tranche_balance_cur IS SELECT empno, ename, sal,
[code]...
Execution scenario 1:
I have commented insert and update statements in that case I got the following out put.
Inserted Records4 Updated Records10
As per the logic it's giving the correct output because the cursor is fetching 14 records in that already 10 records are existed in emp_10 tableand 4 are new records.so that it's showing the count for inserted records as 10 and updated records as 4.
Execution scenario 2: I have uncommented insert and update statements in that case I got the following out put.
Inserted Records13 Updated Records1
As per the logic it's not giving the correct output.
I tried with using TRIM function in the comparision logic to avoid spaces.
TRIM(emp_10.empno) = TRIM(tranche_balance_rec.empno) AND TRIM(emp_10.ename) = TRIM(tranche_balance_rec.ename) AND TRIM(emp_10.sal) = TRIM(tranche_balance_rec.sal) AND TRIM(emp_10.deptno) = TRIM(tranche_balance_rec.deptno)
I have to load data file into a table. And the requirement is as below:
Input Data:
1234|20130815|20130822|This is a test, this is the the part 3456|20130823|20130809|This is a test 3456|20130823|20130809|This is a test 3456|20130823|20130809|This is a test 3456|20130823|20130809|Siva 1234
The data should be inserted only in two rows as below:
When Value in first 3 fields is same, 4th field should be appended to the existing value in table.
1234|20130815|20130822|This is a test, this is the the part 3456|20130823|20130809|This is a testThis is a testThis is a testSiva 1234
I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS TYPE emp_cur_type IS REF CURSOR; PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER); END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and sid = 7 and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 4 opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 11 <----- 7+ opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors) opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 53 <----- 17+ opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
