I am stuck somewhere in my project, situation is that we are trying to display data gathered from 3 different tables, we used cursors for this, here is the cursor declaration.
DECLARE procedure DocInfoCur(myWhere IN varchar2) IS
init_dept_id number; init_dept_name varchar2(40);
rcvg_dept_id number; rcvg_dept_name varchar2(40);
CURSORCUR_DOCINFO IS SELECT DISTINCT I.CTN, I.DIARYNUMBER, I.INITIATINGDATE, T.TYPENAME, I.INITIATINGDEPT, I.DOCUMENTSUBJECT, I.DESTINATIONDEPT FROM DOCUMENTINFO I, DOCUMENTTYPE T, DEPARTMENT D
[code]...
This displays all the records out there.
Now, I have a search form where I need to filter the same data with different parameters, I have compiled the entered data in one string, now i need to join that string at the end of where clause of my cursor, how can I do that, i have tried concatenating both but received compile error, is there a way out in the current scenario???
I want to display all records from table 1 (even the null values) that do not match records in Table 2. Below I am creating both tables and I am posting the result query I need.
------------------------------------ CREATE TABLE temp_table1 ( name VARCHAR2(12 BYTE), last_name VARCHAR2(12 BYTE), STATE VARCHAR2(2 BYTE), BIRTH_DATE DATE );
CREATE TABLE temp_table2 ( name VARCHAR2(12 BYTE), last_name VARCHAR2(12 BYTE), STATE VARCHAR2(2 BYTE), BIRTH_DATE DATE );
[code].....
The result query need to have 5 rows as shown below
NAME LAST_NAME STATE BIRTH_DATE ------------ ------------ ----- --------------------- john smith MA 12/1/1979 null null AZ null null null CT null null null MA null null null CT null
I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
Consider EMPLOYEES table and If an employee 'A' joined in Jan month then he should come under JAN, if employee B and C joined in MARCH month than both has to come under MAR and so on..
OUTPUT: JAN FEB MAR APR MAY JUNE JULY AUG SEP OCT NOV DEC A B D E C
I need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?
I created a data warehouse in oracle 10g n with three Dimension and one cube after that it crates 4 tables . How to use an insert sql statement to insert data in those tables n how to access them.
I have a query on displaying data as per my requirement. I have created a table called sales it has four columns
create table sales(country,state,district,sales); and am inserting some same data
insert into sales('india','TN','Chennai',100); insert into sales('india','TN','KPURAM',120); insert into sales('india','TN','Bangalore',35); insert into sales('india','ANDR','Guinder',100); insert into sales('india','ANDR','Nellai',76); insert into sales('london','city-a','xstreet',89); insert into sales('london','city-a','binroad',100);
select * from sales;
country state district sales india TN chennai 100 india TN KPURAM 120 india TN Bangalore 35 india ANDR Guinder 100 india ANDR Nellai 76 london city-a xstreet 89 london city-a binroad 100
the data is displayed in this format. How i am trying to display data.
Web page that retrives data from SQL database and display it to the user in well formatted manner. Data retrieved depend upon criterias selected by the user. But sometimes data retrieved is very large. I want to display records to the user page wise, i.e. 100 records on first page and next 100 records displayed when user clicks next button. This means only 100 records should be retrieved when user first select search criteria, next 100 records retrived when he clicks next button and so on, as to reduce data transferred from server to client. how to achieve this by using single sql query as soon as possible.
formatting the mail message sent using utl_mail, i have created one table and it has around 17 rows inside as sample and it may increase as well, my present email format is in single line..find below the code i have written along with test case, you may substitute your emailid for test, especially my main focus is on the column mrk , where all the mrk are comming in one line but i want to be arranged in a line not more thant 10 rows, if it exceeds it should go to next line.
CREATE TABLE FAB_LOG ( MRK VARCHAR2(30)) INSERT ALL INTO FAB_LOG VALUES ('1018017M-6001') INTO FAB_LOG VALUES ('1018017M-6002') INTO FAB_LOG VALUES ('1018017M-6003') INTO FAB_LOG VALUES ('1018017M-6004') INTO FAB_LOG VALUES ('1018017M-6005') INTO FAB_LOG VALUES ('1018017M-6006') [code]....
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
I have a table where multiple combination of records are store and i want to display data in range format as below, there is any way to group data as below.
This is my first post in this portal. I want display the details of emp table.. for that I am using this SQL statement.
select * from emp where mgr=nvl(:mgr,mgr);
when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it isdisplaying all the records except the mgr with null values.
1)I want to display all the records when I won't give any input including nulls 2)I want to display all the records who's mgr is null
Is there any way to incorporate to include all these in a single query..
I need a query to display the birth date of all emp in such a way that if i run a query today the dob which lies between today & today+15 days data must be displayed.
I have to write a proc to display data based on user input for t_grp, and it should exclude grp0
So below is the proc I have written, and I want to display the report for all t_grp when user gives user_input as All or gives null. How can I acheieve this is in procedure.
i have a problem to view tens of millions of data in oracle 11g i have table Book_Issue that have tens of millions of data but when i execute query to see all the data select * from Book_Issue
it only view 5.000 data
what query should i execute to view million data in oracle 11g
I have the following requirement, where I have to display the data based on the group and links when input is given as month. I have written the following code, which is good to display for group. But I want to display for all the groups. CREATE TABLE target_data ( T_LINK VARCHAR2(50) , t_mon varchar2(6) , t_grp varchar2(30) , t_views NUMBER
i like to display a Table data like the below format,
Output:
EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 00094 122 153 145 224 245 545 114 544 444 111 555 222 00095 233 123 145 224 205 545 114 444 444 111 555 222 00096 163 123 145 224 215 545 114 551 444 111 555 222 00097 163 0 145 224 215 545 114 551 444 111 555 222 conditions:
where condition: where year = 2007
Table Structure:
create table HR_PAYSLIP ( EMP_NO VARCHAR2(6) not null, YEAR NUMBER(4) not null, MONTH NUMBER(2) not null, BASIC_PAY NUMBER(9,2), )
Insert Command;
INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY) VALUES(00046, 2007, 1, 2314);
Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month
I would like to display data using the Hierarchical tree up to 3 levels.I need to create all the nodes programatically.
Means Create the parent (Parent1) level nodes , then create the child (Child1) nodes and add them under the parent1.Similarly create child nodes (Child2) and add them under the parent (Child1).
