SQL & PL/SQL :: Set Of Data After Every X Number Of Records
Feb 24, 2012
I have the following requirement.
Data from table 1 is printed first and after every X number of rows another set of data either from the same table or a different table needs to be printed.
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS del_records NUMBER:=0; BEGIN DELETE FROM candidate c WHERE empid in (select c.empid from employee e, candidate c where e.empid = c.empid and e.emp_stat = 'TERMINATED' ); [code]....
I have two sql queries. They run the one after another.
Query 1: select * from capital where member_status = 'MEMBER' AND rownum <= 25 order by price desc
Query 2: select * from capital where member_status = 'MEMBER' AND rownum > 26 order by price desc
Question is, in the query 2 I want records greater than row number 25. In query 2, I don't want the records that were fetched in Query 1. Is there any way to do this without using rownum?
I know how to use cursors to read all the records in a table.
But how can records that should be processed together be read in a loop, using cursors?
If there were, for instance persons from different nationalities, French, German, English, ..., I woluld like to read the first unknown number of persons from France and process that data, and then continue with the unknown number of germans and process that, and so on.
However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.
We have a package that processes(Insert and Update) set of records and runs in loop.
Package is working fine when we set number of records to 300 for each loop and commit. when we increase the number of records to 1000, package is getting stuck(Not ending and not coming out of loop).
Is there any DB parameter to be changed in my environment to process more number of records at at time?
In form i call a record from database table through execute_query. i have lov on ITEM_NO
i want to display on that form, no. of records of ITEM_NO which i select from the list. like if i have 10 records with item_no which i select from list is must show 1/10.
if i press the down arrow it should show next record of the same item_no.
If I run the following query I got 997 records by using GROUP BY.
SELECT c.ins_no, b.pd_date,a.project_id, a.tech_no FROM mis.tranche_balance a, FMSRPT.fund_reporting_period b, ods.proj_info_lookup c, ods.institution d WHERE a.su_date = b.pd_date AND a.project_id = c.project_id AND c.ins_no = d.ins_no AND d.sif_code LIKE 'P%' AND d.sif_code <> 'P-DA' AND a.date_stamp >='01-JAN-2011' AND pd_date='31-MAR-2011' GROUP BY c.ins_no, b.pd_date, a.project_id, a.tech_no;
I want to show the extra columns a.date_stamp and a.su_date in the out put so that I have used PARTITION BY in the second query but I got 1079 records.
MY problem is in my reports i wish to have filter number of records on my requirement , when i call reports from form then ask me that how many number of records you want..
I need to verify huge number of records in two different databases. Basically i wanted to check if same record exist in other database's table or not? but as the number of records are more than billions who would i verify?
checking record one by one would be so hectic and time consuming. any other option is there?
Updated to Add: In a last ditch search, I found my answer with ROWNUM <= 1 in the Where clause. It works and I can go from there with what I want to do.
I have a website that pulls similar information from multiple queries using a Union-based query. I want to only pull 1 record from one section, two from another, and 5 from the third. I've so far found LIMIT but haven't been able to get it to work in that way. Is it possible to limit each query in the union as I am looking to do?
The query is:
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath FROM (so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID WHERE p.storyType = 'alert' AND c.display = 'yes' AND c.startDate <= sysDate AND c.endDate >= sysDate UNION All SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function
Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)
CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB; CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB ( P_LIST CLOB, P_DEL VARCHAR2 := ',' ) RETURN SPLIT_TBL_CLOB PIPELINED
[code]....
But here I am facing 2 problems.
1. The function is not accepting a large string & I am getting the error
We are building a custom form extension with our EBS R12 where against certain parameters we are inserting and updating records in a table through a PL/SQL procedure.This PL/SQL doesn't use a commit exclusively, instead the user must click the Save button after verifying the data (new records are inserted, existing ones are updated and after the insert and update routines, execute_query is fired to fetch the latest data)
Everything works fine, however, when the user clicks on the Save button form status bar says "FRM-40401: No changes to save"
We would like to alter this message with our own custom message stating how many records were inserted and hows many were updated (We have counters counting this information)
Under which particular trigger we should write the code to enhance this requirement?
Actually m working on oracle 10g forms. I have a list item in which no. of records displayed is 10. I am populating this list item through a record group. I want when user select a value from first record then he should not be able to select the same value in further record of that list item.
I need to export large number of records from select into the text file. It's about 2milion records.I can do it by PLSQL (see below) where executing of process takes time too much. How to export to text file faster?
1. My detail form has part number as one of the fields and I want to make sure the user can enter a part number only once. How can I make sure user cannot enter it more than once?
2. I want to limit number of detail records for every parent record to 8. How can I accomplish this?
3. I have approval flags at both master and detail. If the user tries to approve the master with some/all detail records unapproved, system should display error message and make the user approve the detail before approving the master.
I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.
How to set any triggers for the items to auto populate and i am suppose to finish this work today.
how to display the data which is shown below without duplicate records in compid and compname and all policy_id's should be there while excuting this query iam getting this data.
select distinct comp_id as compid, comp_disp_name as company, plcy_id as policyid,
19734 Save the Children 9013 GPA 19734 Save the Children 9012 GMC 20097 JMT 9486 GTL 10890 Steelco Gujarat Ltd. 9727 CAR 17330 Golden Jubilee Hotels Limited 8915 CGL 23117 NBHC 9093 GMC 17542 Heinz India 10693 Fire 19821 KSK Fabricators 10341 D&O 3769 Jones Lang Lasalle India 9199 WC 19821 KSK Fabricators 10340 WC
We have table with number as datatype which starts with number 10 like 1056723. Here we need to update the first 2 digits which is 10 to 04.Digit 10 will be always constant across all the data.
select * from temp; id ---------- 1056723
After update, it should be
id ---------- 0456723
What function in pl/sql should be used for achieve the desired results.
I'm trying to figure out how to import data from another schema for an oracle SQL project in an MIS Course.Each student/schema created the following tables STUDENT, FACULTY, COURSE, IS_QUALIFIED, SECTION, IS_REGISTERED. Each student was supposed to make up names and ID numbers for 20 different students, then use real university faculty, courses, and sections to populate the respective tables.
I need to import data from all of another student's tables, without duplicating any courses, faculty, etc I already have in my tables.
The other student already granted acess to her tables, so now I'm just having trouble with the insert statement. This is what I have so far for the first table.
INSERT INTO myschema.STUDENT SELECT * FROM otherschema.STUDENT
I know i need to add a "WHERE" statement to successfully copy the other persons data into mine, without duplicating any results. This is the last part of this project and I'm having trouble finding the answer anywhere.
I have table which contains huge data. around 12 lakhs records. when I use sum function on accountname and docdate it gives wrong value. once I restart the server it gives the correct value. one or two days it gives correct value after that again I get the same problem. If I restart again it gives correct value.
I use Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 64 bit server on Linux.