SQL & PL/SQL :: How To Use Cursors / Loops And Proper Syntax
May 31, 2011
homework assignment using pl/sql based on 2 tables I have created below? I am not sure of how to use cursors, loops and proper syntax.
ASSIGNMENT:
1. Create a PL/SQL Procedure (cursor to loop through the records, check the LastName, then update the grade table
where id=id on grade table)
Rule:
A
‐ LastName ends with a character between A‐F
B
‐ LastName ends with a character between G‐K
C
‐ LastName ends with a character between L‐P
D
‐ LastName ends with a character between Q‐T
E
‐ LastName ends with a character between U‐Z
I have a table where each record has a numerical x-coordinate value as one of its fields. I want to loop through a group of the records that have another field in common in a given order. In a nested loop, I would like to subtract the coordinate of the outer loop from the coordinate of the inner loop for all records in the inner loop that appear later in the sequence. The result is a list of the distances between all coordinates.
Example: x-coordinate 3 4 6 7
It would look like this: 4-3, 6-3, 7-3, 6-4, 7-4, 7-6
I can do this using two nested cursors that select the same thing basically. But the table being selected from is pretty large and it takes forever to keep selecting from the huge table when the inner cursor could just copy the results of the other cursor and repeatedly iterate through them.
Is it possible to copy a cursor's results into another cursor or reset the cursor index back to the beginning so that it doesn't have to do the select statement every time?
I'm attempting to write a plsql for finding missing archived logs for streams.
requirement is to run a select statement and print 1. 'NOT FOUND' if name column is null 2. '<name of the files>' if rows are returned 3. 'NOT FOUND' if no rows are selected. (here is were i'm having trouble)
code i developed so for: for cr in (select decode(name, NULL, 'NOT FOUND', name) from v$archived_log where deleted='YES' and status!='A') loop if (cr.name = 'NOT FOUND')
Predicate Information (identified by operation id): ---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
Nested loop by defintion means,for every row returned by the outer query,the inner query is executed that many times.
In the above example,oracle does a full table scan and returned 14 rows.Now for dept table,it does a index unique scan and applies the predicate a.deptno=b.deptno and returns 1 row.
My question is why it is returning only 1 row? That measn for every 14 rows,this one row is fetched 14 times.
i am trying to use loops to retrieve the ename of emp table of scott schema , then under they are retrieved , all "sal" values appear under them , i do not want employee's salary under each employee , i need retrieve the 14 record of enames , then under them the 14 record of sal column appears , so i used this code :
declare cursor emp_cur is select ename from emp; emp_rec emp_cur%rowtype ; [code]....
Main Aim : To find all those id's who have taken all the tests within a rolling window of 45 days.
I have a table "MBS_FIRST_DATE" with the following data :This table has the patients who have the test along with the first date..This table is derived such that it has only one record with the first date of the test irrespective of the test.
create table MBS_FIRST_DATE ( medical_record_number VARCHAR2(600), requested_test_name VARCHAR2(39), result_date DATE
[code]..
Process :will be explaining with a patient id : 1) Consider the patient 1001274 from mbs_first_date table. 2) This patient has an date of July 08th 2008 & test SBP from first table. (keep this test an an anchor). 3) For the patient above loop through the all_recs table with test & result date ordered for the patient. (excluding SBP) 4) The first record we have is CHL with 08/05/2009 (May 8th 2009).. 5) Since this record is not within 45 days from SBP date for the patient..we go to the next record of SBP for the patient. 6) The next record for SBP is 11/05/2009 (May 11th 2009) . 7) Consider the CHL date again which is with 08/05/2009 (May 8th 2009).. Since both are within 45 days ..store both the values keeping SBP date as an anchor date as it's the test that's having minimum date from table 1. Even though there is one more CHL date which is within 45 days from SBP we don't care about it. 9) Go to the next test for the same patient which is DBP.. 10) The DBP first date is July 08th 2008.. 11) Since it's not within 45 days from previously stored SBP date (11/05/2009) ignore the record. 12) GO to the next record which is 10/05/2009..as this is within 45 days from SBP & already CHL (stored date) is within 45 days..Grab all the 3 dates as all are within 45 days from anchor date (SBP date).
SO the o/p will be 1001274 SBP 11/05/2009 1001274 CHL 08/05/2009 1001274 DBP 10/05/2009
Code which I wrote :I know some where I am missing the loop
I want to make sure I am describing correctly what happens in a query where there is distributed database access and it is participating in a NESTED LOOPS JOIN. Below is an example query, the query plan output, and the remote SQL information for such a case. Of particular note are line#4 (NESTED LOOPS) and line#11 (REMOTE TABLE_0002).
What I want to know is more detail on how this NESTED LOOPS JOIN handles the remote operation. For example, for each row that comes out of line#5 and is thus going into the NESTED LOOPS JOIN operation @line#4, does the database jump across the network to do the remote loopkup? Thus if there are 1 million rows, does that mean 1 million network hops? Does batchsize play a role? For example, if the database batches in groups of 100 then does that mean 10 thousand network hops?
I think each row that comes out of line#5 means a network hop to the remote database. But I do not know for a fact.I have done some abbreviating in the plan in an attempt to make it fit on the page (line#7 TA = TABLE ACCESS).
i m using oracle d2k i want to open a pdf file given proper path of C:Program FilesAdobeReader 9.0ReaderAcroRd32.exe but i want to use only AcroRD32. exe without using proper path how i use it
I have a table whose size is 2.3 GB and there are two indexes on it. One index is based on a Date column whose size is 900 MB, and the Other index consists of 5 columns including the date column, and the size is almost 2GB. But when i query the table using the Date column, it is doing a range scan on the second index which is almost the same size as the table. why is it not using the first index? What steps should i take so that it uses the First index without passing hints.
getting proper value from the file in external table.
How can I get the whole status in STATUS column like completed , Inprogress, incompleted. Right now, if I gave position like (38:9) full status doesn't show. if I give (38:11) then '|1' is adding in status from the flat file.
BATCH_NO FILE_DATEEMP_ID COMPANY_ID TRANSACTIN_ID FILE_NAME STATUS DOC_NO 10000104252012100001***4252012**1:35:57***D100001***04252012***10:35:57***Diverified
I am trying to build a report.My query is working fine when i take out this report for a single area_code.But it is not showing proper result when report is take for all are_code's available in table.I have used two tables transactions and balance
create table transactions ( glcode varchar2(10), area_code varchar2(10), debit number,credit number ); insert into transactions values(2000,'ap',200,200); insert into transactions values(3000,'ap',222,222); insert into transactions values(4000,'ap',123,123); insert into transactions values(2000,'dp',200,200); insert into transactions values(3000,'dp',222,222); insert into transactions values(4000,'dp',123,123); insert into transactions values(2000,'pp',200,200); insert into transactions values(3000,'pp',222,222); insert into transactions values(4000,'pp',123,123); [code]....
I have a report and require the output in xls/xlsx (Excel) format. Currently following properties under System Parameters in Report Builder 6i have been made
Desname = The path for e.g: \C:sz-serverc$ihelp_workingxls pt_sample_report.xls Desformat = Delimited Destype = File
The rest parameters has default settings I have not changed any of the rest User parameter.The report comes up in excel format but totally in distorted manner. i.e; All the column names of report are displayed first in vertical format followed by actual rows for the report.
The requirement is like this
[Select all] [Show/ hide] Student ID Student Name Fathers Name Guardian Name 1 sample1 sample2 sample3 2 sample4 sample5 sample6
And it is displaying like this
Student ID Student Name Fathers Name Guardian Name 1 sample1 sample2 sample3 Student ID Student Name Fathers Name Guardian Name 2 sample4 sample5 sample6
i load data to this table from a file using sqlldr.
what is the proper data type should i use in control-file for both the fields.? i dont mention any datatye in ctl file which is working fine with given dataset.
I have two methods using pro*c to execute a query SQL and read the results. The query is a "select from" a view, this view is a union between two tables. The first method creates a cursor and fetches the results line by line. A second method creates a cursor and fetches the results in parts.
Method 1) CODEEXEC SQL FETCH Cursor1 INTO ...
Method 2) CODEEXEC SQL FOR :numLines FETCH Cursor1 USING DESCRIPTOR areaDescSQL;
I had executed this methods in my development enviroment, using Oracle 11g, Suse linux. As I expected, the method 2 spent much less time than the method 1. But, in the "customer enviroment", using another database, Oracle 11g and HP-UX, these methods spent almost the same time.
Is there some Oracle's configuration or parameter's settings that maybe explain that? What configuration can be differente between the two databases? What can I do to improve the time spent?
I'm currently looking for a way to declare a cursor in the 'declare' block using a previously defined variable that got its value from a query. For instance:
declare my_company_id INTEGER := 'select c.company_id from company_table c where company_name='Wal-Mart';
cursor employees is select e.employee_id from employees e where e.company_id = my_company_id;
an alternative to Oracle Cursors.My table has huge amount of data and is taking time in processing. I had thought of using BULK COLLECT but it can only be used to insert, delete or update data and will not allow me to select data.
declare type ref_cur is ref cursor; r ref_cur; enam emp%rowtype; dno dept.deptno%type; begin dbms_output.put_line('The Employee details are'); open r for select deptno from dept; loop fetch r into dno;
[code]....
Error at line 1 ORA-06550: line 12, column 28: PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
. ( % ;
if i need to use ref cursor to send parameters, is it possible? if yes how to use it?
i have created a stored procedure with a cursor in order to perform a function where the annual_sal from the employee_annual_sal table is refered and checked. The empno for all the records which satisfies the condition mentioned inside the loop should be displayed in an variable. My code is below
create or replace PROCEDURE sp_test_cursor(out_empno OUT number) IS v_get_data number; v_get_empno number; cursor c1 is select annual_salary from employee_annual_sal;
[Code]...
What should i do to return mulitple values in a single variable??
declare v1str varchar2(100):='select empno,ename from emp'; v2str varchar2(100):='select empno,ename,sal from emp'; type t_array is varray(2) of varchar2(100);
[Code]....
So my problem is while executing the different sql statements by passing it to the procedure,how can the procedure would behave dynamically.It must be able to process all the sql statements.
Ex: Cursor C_employees(C_empid number, C_cityname varchar2) is select emp_name, office_name from employee where employees where empid = c_empid and city = c_city_name;
I know we can pass one parameter to the cursor but I do not know how to pass multiple parameters.
DB version: Oracle DB 10g Enterprise Edition 10.2.0.4.0
I have the following four tables:
tab_main- which lists main projects tab_sub_main - which lists sub projects tab_budget - amounts per projects/subprojects tab_total - I want to load the data here
The table script with data is attached.
I want to load data into tab_total fields for prj_type= 'J' as follows:
1. accn_no from tab_main table.
2. fy from tab_budget table
3. fy_total_amt which is the sum(amt) from tab_budget table by accn_no and fy
4. all_FY_amt which is the sum(amt) from tab_budget table by accn_no
5. all the audit fields- date/user inserted/updated will come tab_budget table
how to create this procedure with cursors.
CREATE OR REPLACE PROCEDURE LOAD_DATA_INTO_TAB_TOTAL_PROC IS CURSOR C IS select distinct m.accn_no, a.control_no,m.prj_type, b.fy, b.amt, b.user_created, b.date_created, b.user_last_mod, b.date_last_mod from tab_main m, tab_sub_main a,
[code]....
CREATE TABLE tab_main ( ACCN_NO NUMBER(7) NOT NULL, PRJ_TYPE VARCHAR2(1 BYTE) NOT NULL ) / Insert into TAB_MAIN (ACCN_NO, PRJ_TYPE)
I am trying to write which will return cursors to front end program.The logic which I got stuck is while exiting the procedure.I am having some cursors in teh procedures which are read by the front end script.I have one condition which when satisfied I have to exit from the procedure.But the constraint I am facing is if I exit at that logic, oracle closes all the cursor and front end script is not able to read teh cursor and hence throws an error.Is there any way to exit the procedure without closing the REF cursors.
