i want to display all the records of my database in a table with scrollingexample if i have 3records in my DB so a table must have 3records too !!!this is my code :
DECLARE
Cursor cur IS
SELECT numb_ph AS phone,
name_emp AS name
[code]...
but the problem 's all records of my DB are display in one record (to view another record i use the scrolling ..
how to display all the records in a table ,i am passing the table name as in param to the procedure/function suppose if i pass emp table name it will display 14 rec, if i pass dept it will display 4 records.
View 3 Replies View Related I have created two tabs tab1 & tab2. after changed(not while changing/before changing)) into tab2 all records should be display.
I have written execute_query in when-tab-page-changed.but that is before/while changing.i want after entered in tab2 it should be display.
want to display records of two tables in in one form.I have 2 tables. tab1 and tab2
columns in tab1 are code, desc, part, onhand_qty AND
columns in tab2 are code, room_no, row_no, rack, shelf, qty, remarks.
Common column in both the tables is code.
i want to display all the columns of both the tables where tab1.code=tab2.code ,and tab1.onhand_qty is not equal to SUM of tab2.qty
I have created one tab canvased form
On first tab I am taking input (search criteria)
on that input i am trying to display all records one by one as user click on next record button....
for that i used when-tab-page-change trigger for first record display and it display correctly.but for further record what should i need to write on next_record command button so all record i can see one by one.
how to display check box selected records in one column of form when push button is pressed.
View 1 Replies View RelatedHow to display table columns on oracle form?
View 7 Replies View RelatedI used Scott schema.I take 2 blocks ,one block having Deptno,Job and also 1st block is a non database block and Another Block Empno,ename,job,sal,deptno, is a database block,my question is when i enter Deptno,Job then Display Employees Details and also Department name
View 1 Replies View RelatedI need to write a query to sort the records in a particular order,
Say if I group the records by Dept number
Dept no Name
10A
10G
10f
20B
20K
30I
30M
30R
30Y
I need to write a query that will make this records listed like
Dept No Name
10A
20B
30I
10G
20K
30M
10F
20null
30R
10null
20null
30Y
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
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,
[Code]....
output
-----------------
compid compname policy_id policy_name
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 am using Oracle 11g
The procedure is compiled without any errors.
The select statement retrieves records well when executed at SQL promt.
But the same records are not displayed in this procedure by fetch from cursor.
The last message in the procedure 'completed' is also displayed.
create or replace procedure disp_rec(vid IN varchar2, vfeat IN number)
is
cursor c1 is select gid, listagg(vindex, ',') within group (order by tid) idx from ridrecords where idarpt=vid and feattype=vfeat group by gid;
type vg is table of number;
type vi is table of varchar2(3500);
[Code]....
i hav the following records i need to display the records of financial year wise (i.e april year-1 to mar year) here there are no records for months 9,10(september and october)
what i want is 0 to be displayed over there in the below table
s.no key id yyyymm ce1 ce2 ce3 t ihu pf mi pwd F
920110600000156431372010043424541500065457709539XXXXXXXXXXU
10201106000001565313720100534245415000009539XXXXXXXXXXU
11201106000001566313720100634245415000009539XXXXXXXXXXU
12201106000001567313720100738555415000009539XXXXXXXXXXU
13201106000001568313720100836445415000009539XXXXXXXXXXU
1420110600000157131372010110000009539XXXXXXXXXXU
15201106000001572313720101276881082200001888009539XXXXXXXXXXU
1620110600000157331372011010000009539XXXXXXXXXXU
My query formation is like below..
*Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi*
SELECT T1.COL1,T1.COL2,
T1.COL3,T2.COL2,
T3.COL1,T3.COL2,
T4.COL1,T4.COL3,
<CASE statements and calculations results - Some Amount1>,
<CASE statements and calculations results - Some Amount2>
[code]..........
First I need to display all the records from TAB_ONE which contains more than 10million records. If you see there are columns like AMOUNT_ONE,AMOUNT_TWO which has got some complex calculations and that is based on some other calculations and which in turn and goes on.... Like this I have some ten amount columns. Finally these records has to be inserted into a new table.
To get this I have written nested inline queries to calculate these AMOUNT columns but since considering the huge amount of records, it takes more than 8 hours for inserting this into a new table.
I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.
Name id dept_no
Ram 101 10
20
30
Ani 201 10
20
i have a master-detail form.
detail block is tabular.
when-button-pressed trigger iam inserting records in another table.i write
insert into ONHAND_QTY_LOCATION(sno,matid,matcode,description,partno,onhand_qty,location)
values (:ship_dtl.slno,
:ship_dtl.mat_id,
:ship_dtl.mat_code,
:ship_dtl.description,
:ship_dtl.part_no,
:ship_dtl.rec_qty,
:ship_mstr.place_from)
it is inserting only one record in onhand_qty_location table that too the last record.
i want all the records which iam entering in detail block should get entered in onhand_qty_location table.
i want to display records from a table in tabular form with one condition.
i made a tabular form.and in WHEN-NEW-FORM-INSTANCE i write
DECLARE
CURSOR hascr IS
SELECT
BILL_ID,
BILL_DATE,
MAT_CODE,
PLACE_FROM,
[code]....
when i run the form its not showing any errors but displaying only one record.that is the first record whose bill_type is send to other store.
am i using the right trigger or my select statement has problem?
i want to retrieve records of empno from SCOTT.EMP talbe in form but without data block wizard using ..how .. and what logic will be in coding
DECLARE
TYPE EROC IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
E1 EROC;
NO_EMPNOVARCHAR2(2000);
[code]....
just caling here EMPNO and :v_empno is fields on form...
I need to populate records from a child table when the user clicks on a record in the header table..The difficulty is that the user needs to be able to edit a field from the child table and there could be 1 to many records. When the user makes a change , I don't want the 'do you want to save changes' error to appear..
the user will only be able to update field 'amt'.When the user clicks on 'declaration_no' from the header table,other records will be displayed. URL.....
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....
a sample query to display second MAX value from the oracle database
View 1 Replies View RelatedWe deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
Oracle 11gI have a large table of 125 million records - t3_universe. This table never gets updated or altered once loaded, but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads - that will be updated with regard to when the lead is mailed and for other relevant information. select records from this 125 million record table to insert into the smaller table.
I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below. Then use a second query that inserts into T3_Leads from this View V_Market. This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause? My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.
CREATE VIEW V_Market asWITH got_pairs AS ( SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */ l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no , ROW_NUMBER () OVER ( PARTITION BY l.address_key ORDER BY l.hh_verification_date DESC ) AS r_num FROM t3_universe e JOIN t3_universe l ON l.address_key = e.address_key AND l.zip_code = e.zip_code AND l.p1_gender != e.p1_gender
[code]....
is there a way to keep the display of the form when we call another new form. my scenario will be as following:
am in form1 , i call form 2 from a menu, after this call form2 is open in place of form1.
i have no control in form1 and i can't change anything on it, i don't know hw the menu option is calling form2.the only control i have will be within form2 if there is any change i can make it.i read something about MDI, and OPEN_FORM() procedure, but i think this will required a change in form1 which i can't do.
is there any change i can make in form2 to display it and keep the display of form1 at the same time behind it ?
I would like to display PDFs in Oracle Forms. The PDFs are stored in an Oracle 10g database.
View 2 Replies View RelatedI have a following table,
create table test ( col1 date,col2 number);
Data:
col1 Col2
----------
NULL NULL
select * from test where col2=123 will give me null because It doesnt have any values, But can we display some harcoded value when I do not have anything ?
I am bit confused to display all the rows in a column using stored procedure like
In sqlserver:
create procedure test
as
begin
select * from table;
end
in sqlserver it is working perfectly.
In oracle:
create or replace procedure test
is
begin
select * from table;
end;
But in oracle i am getting an error like select * into something like that.I understand the error. i.e. i need to move the column values into variables.if it a single row then there will be no problem.i want to display all the rows in the table at one time.i can not use cursors.
following is the requirement
External Table
WKSHT_FILE_EXT
wksht_line
Export Table
Wksht_export
global_idvarchar2(10)
wksht_linevarchar2(250)
[code]....
Step 1.Insert all records from the external table into the export table. Truncate the export table first
Step 2.Read in a record from the export map table
Step 3.Search through export table records looking for the key words BRANCH =. Compare the branch code with the branch code form the map table
Step 4.If a match is found mark all records in the export table for the worksheet with the global ID from the export map table as follows..The first line of a worksheet is marked by the words WKSHTS..The last line of the work sheet is marked by the words COMPANY CONFIDENTIAL..We will need to capture the line break so also mark the next line after the COMPANY CONFIDENTIAL line
Step 5.Continue with Steps 2 - 4 until all records have been processed from the export map table.
first I have to create a procedure ti insert data from external table to export table.Global id will be blank.it will be updated by the mapping table's Global Id when The EB COLUMN's data(i.e 8p,2Betc ) will match with the BRANC=NA,2Betc of the datasheet loaded from the external table.. FOLLOWING IS THE SAMPLE DATASHEET
WKSHTS AAAAA BBBBBBBBBBB ELECTRONICS INC. TIME REPORT-DATE PAGE
SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 1
BRANCH = 2B
EMPLOYEE NAME SALVAAG, GREGG Days in the Month 28
[code]....
THERE ARE 2 pages..I have to split this LONG REPORT STORED IN WKSHT_LINE COLUMN OF EXPORT TABLE to 2 records..like wise 500 pages are there means 500 records.. AND THEN FIND BRANCH= after that which two words will come i.e NA,2B etc if it will MATCH WITH MAPPING TABLE"S EB COLUMN"S DATA,THEN MAPPING TABLE's GLOBAL ID WILL BE UPDATED TO EXPORT TABLE's GLOBAL ID WHICH IS BLANK
As far as SQL is concerned, I am using oracle 9i and oracle sql *plus.
1.
This is a table 'spj'
SID PID JID QTY
---------- ---------- ---------- ----------
1 3 2 5
1 2 2 12
1 7 1 10
2 5 3 5
[code]...
I wish to count all the qty for each pid.This is what I have:
SQL> select pid,sum(qty) from spj group by pid;
PID SUM(QTY)
---------- ----------
1 5
2 12
3 29
4 10
5 5
6 6
7 10
and it seems to work fine. But I now I want to display another column in the table called 'pname' which is a primary key in another table 'p'. 'spj.pid' is the foreign key.
2. Another query I can't perform needs the table s
SID SNAME STATUS CITY
---------- -------------------- -------------------- -------------------
1 s1 y asansol
2 s2 y durgapur
3 s3 y durgapur
4 s4 y asansol
5 s5 y kolkata
6 s6 y asansol
7 s7 y tarakeshwar
the question says:
Quote: List supplier names(snames) who supply at least all parts supplied by supplier(sname) S2.
How may I achieve it.
how to display the dupicate record in table
View 1 Replies View Related