SQL & PL/SQL :: Best Method To Populate Calendar Entries On A Table

Jun 2, 2011

I have a table :

Product
A
B
C
D

and I was wondering if there is a quick method of populating it with calendar data so it would look like the following:

Product Year Month
A 2008 Jan
A 2008 Feb
A 2008 Mar
A 2008 Apr
A 2008 May
A 2008 Jun
A 2008 Jul
A 2008 Aug
A 2008 Sep
A 2008 Oct
A 2008 Nov
A 2008 Dec
A 2009 Jan
A 2009 Feb
Etc.

This would be done for all products for 4 years.

View 2 Replies


ADVERTISEMENT

Application Express :: How To Populate Date Picker Value In Calendar Region

Jul 2, 2012

I have a calendar region based on a simple query from a table which has date and name columns. The calendar type is monthly with 3 buttons - previous, today & next. It works perfectly.

I have added a date picker in a calendar region now. If i choose any date, the calendar month should be refreshed accordingly. Ie... by default it would show July 2012 month. When i choose the month as March 2012 in the date picker, it should show March month data.

View 0 Replies View Related

How To Delete Duplicate Entries In A Table

Sep 8, 2008

In oracle 9i ........How to delete duplicate entries in a table ?

if suppose in a table named office, one or more entry(row) is repeated more then twice or minimum twice.
I need a query to delete the multiple entries ....

Note:
--->No constraints applied on the table.
--->No Primary Key
--->You cannot create any object....that is no view or a duplicate table can be created

View 3 Replies View Related

SQL & PL/SQL :: Check Existing Entries In Table

Apr 3, 2012

I am abolute new in PL SQL and I am working on following topic:

I have to check if an entry exists in a table.

- if it exists ...... do something
- if not ...... simply go on

I tried the following:

select Count(*)from products p
where p.productNbr = temp_Nbr
Group By t.produkt;

I fetched the result into a variable entries

if entries > 0 .....

MY problem is the following:

If there are entries (entries>0) --> it works
If there are no entries, entries is not 0 but 'no data found' --> crash

I cannot work with Exceptions because this all happens in a Loop which must go on in both cases!

View 14 Replies View Related

Query Table Entries With A (timestamps) Column?

Jan 11, 2007

I'm trying to generate count of the number of entries in a table for each day.The problem is the date column is of datatype timestamp and looks like this "2006-12-30 18:42:03.0"

How would I generate a report of number of entries in the table for each date (I'm not intrested in the "time" only the "date" i.e YYYY-MM-DD)?

SELECT COUNT(*) FROM my_table_name
WHERE my_date_column LIKE '2006-12-30%'
GO

It returns zero rows ( and I kno there are rows in the table) I'm using Oracle 10g.

View 2 Replies View Related

Application Express :: Compare Entries Of Tabular Form Column Against Lookup Table Or Item

Mar 28, 2013

I use APEX 4.2.0. In Tabular form, I have column of Select list type. This list has too many values and the end user must choose all these values along the tabular form rows. In other words, If the LOV consists of X, Y , Z, The End user should add three rows and choose a unique value for each row.

The problem is : This LOV is supposed to have too many values. What comes to mind is:

1- Whenever the end user picks a value, this value should disappear from the LOV of the second row ? But I have no clue how to do it?
OR
2- Whenever the end use picks a value, I should notify him/her about the remaining values that he should pick . But I have no clue how to do it ?

At the end the purpose is to make the user aware that he still has some rows to add ( values to choose ... )

I simplified the issue in simple example here:

[URL]......

workspace: somefeto
user: test
pwd: test

View 0 Replies View Related

SQL & PL/SQL :: How To Populate A Table

Oct 26, 2010

I have a table like this

Desc Salesperson;
Emp_name varchar2(20),
emp_id number,
begin_date date,
end_date date,
is_current varchar2(3)

This is the first time the table is getting loaded. I get the distinct emp name from table 'X' and insert into teh sales person table to list out all teh emp present in the company and give the corresponding details.

I am not sure how to write a proc to populate the whole table. emp id is sequence generated.

begin date any date say 10/01/2010 (mm/dd/yyyy)
end date is 99/99/9999 indicating that the emp is not terminated
is_current flag = 'Y' if end date is 99/99/9999.

I have done a distinct empname from table 'X' and inserted into the salesperson table. Do i proceed to update the table for individual columns? *is that the right approach*?

View 4 Replies View Related

Server Administration :: Index And Table Rebuild Method

Jan 17, 2012

The best way to rebuild index and table and also give the reason as why we need to use this method?

View 10 Replies View Related

Creating A Trigger To Populate Another Table

Jan 4, 2007

Having trouble creating a trigger to populate another table.

The SQL:

CREATE OR REPLACE TRIGGER "P_M_YES"
AFTER INSERT OR UPDATE ON DOMAIN
REFERENCING NEW AS NEW.P_M AND OLD AS OLD.P_M
FOR EACH ROW
WHEN (NEW.P_M = YES)
BEGIN
INSERT INTO PAGE_MAKER VALUES(:NEW.D_NAME, :NEW.USER_ID);
END P_M_YES;

/
ALTER TRIGGER "P_M_YES" ENABLE
/

I get an invalid trigger specification.

View 3 Replies View Related

SQL & PL/SQL :: How To Populate Distinct Column In A Table

Apr 22, 2013

I had to create a new column in a particular table now i want to insert the values in that column though the other columns are already populated I entered the command (insert into Product(STANDARD_PRICE) values(895.99) when i hit return it says cannot enter null value into (SYSTEM .PRODUCT. PRODUCT_ID) product_id is the PK which is the first column STANDARD_PRICE is the last column in my table...how do i enter the values into that column without receiving this error or having to effect the other columns?

View 3 Replies View Related

Forms :: Populate Table From A Block In 10g?

Dec 3, 2010

TABLE_FROM_BLOCK built-in Package is working in Forms 6i but not in Forms 10g. is there any other built -in available instead of this. how do we populate table from a block in Forms 10g?

View 6 Replies View Related

SQL & PL/SQL :: Dynamic Create And Populate Table

Jan 27, 2012

I am dynamically creating a staging table my_stg, and then populate it. Seems simple, but not sure why i get this error,

create table gtest4(myid varchar2(10), mykey varchar2(10));
create table gtest5(myid varchar2(10), mykey varchar2(10));
insert into gtest4 values(1,3);
insert into gtest4 values(2,7);
insert into gtest5 values(5,3);
insert into gtest5 values (1,7);
commit;

CREATE OR REPLACE PROCEDURE px
IS
TYPE rectype IS RECORD (
myid VARCHAR2 (100),
mykey VARCHAR2 (100)
);

TYPE tabtype1 IS TABLE OF varchar2(100)
INDEX BY BINARY_INTEGER;

TYPE tabtype2 IS TABLE OF varchar2(100)
INDEX BY BINARY_INTEGER;

rec1 tabtype1;
rec2 tabtype2;
cur sys_refcursor;
[code]....

count current exists max min prior sql stddev sum varianc execute forall merge time timestamp interval date

pipe
<an alternatively-quoted string literal

View 11 Replies View Related

Performance Tuning :: Correct Method To Determine Table Actual Size

Aug 9, 2012

Which is the correct method to calculate actual data size in a table? becaue when I serach in google, I saw the below line.

"Oracle thumb rule says (actual space required for a table + 30 % space) will calculate the original space requirement for a table."

Method 1:

actual space = num_rows*avg_row_len

Method 2:

actual space = (Num of rows in a table) * (Avg_row_len) + ((Num of rows in a table) * (Avg_row_len)* 0.3)

View 8 Replies View Related

SQL & PL/SQL :: How To Have Procedure Populate Staging Table Primary Key

Jun 6, 2012

I've a staging table STG_TABLEA which has a primary key discount_seq_no.

I am creating a pl/sql procedure to populate a primary key (discount_seq_no) with a database sequence. The intent is to keep this populated with next value incrementing by 1.

I am using Oracle 11.2.0.2 version.

I've put together the below code, not sure on next steps...

BEGIN
UPDATE STG_TABLEA
SET A.DISCOUNT_SEQ_NO = "INSERT A SEQUENCE HERE AND KEEP INCREMENTING the seq value by 1
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;

View 5 Replies View Related

PL/SQL :: Creating A Script To Populate A Test Table

Nov 1, 2013

I am creating a script to populate a test table. But I cannot auto increment the DATE column,

declare  v_idnumber := 1 ; v_datedate; begin while(v_id < 100 ) loop v_date:= TO_DATE( '07-23-2012', 'MM-DD-YYYY'); INSERTINTO datetest (ID,startdate) values( v_id,v_date);  v_date:= v_date +1 ; v_id:= v_id +1 ; endcommit;end;loop;

View 0 Replies View Related

PL/SQL :: Populate Target Table - Dynamic Rows

Jun 21, 2013

create table src(id number,val number,data varchar2(100)) insert into src values (1,1,'SUN');
insert into src values (2,2,'WED');
insert into src values (3,3,'MON');     
create table trg(id number,val number,data varchar2(100)) required rows to be inserted in the target table. 

  insert into trg values (1,1,'SUNDAY');
insert into trg values (2,0,NULL);
insert into trg values (2,0,NULL);
insert into trg values (3,0,NULL);
insert into trg values (3,0,NULL);insert into trg values (3,0,NULL); 

 {code} based on the column value  of the source table src's column val , i need to populate my target table trg . If the value of val is 1 then  only one target row is created in the target .If the value of val in the source table src is 2 then the target is populated with 2 rows .The values of the target columns are mapped as follow:

1)id -as it is

2)val - if the val of src is 1 then map the val as it is .If the value of val is more than one then create as many rows as the value of val ,id will be as it is and the  value of val and data will be null

3)data - if the val of src is 1 then expand the abbreviation else null . 

View 17 Replies View Related

SQL & PL/SQL :: Add Begin_Dt And End_Dt Columns To EFFDT Table And Populate Them

Mar 31, 2011

I am planning on adding Begin Date and End Date on an EFFDT (Effective dated table). easy method of populating the Begin and end Date columns based on the EFFDT already on the table. Below is an example:

Department Table

From:

DEPTID EFFDT ...DESCRIPTION
12345 1/1/1900 Accounting
12345 1/1/2000 Accounting Exp Unit
12345 2/1/2000 Acct. Expense Unit
12345 5/1/2000 Account Expense Unit
12345 10/1/2000 Account Exp Unit

TO:

DEPTID EFFDT ...DESCRIPTION BEGIN_DT END_DT
12345 1/1/1900 Accounting 1/1/1900 12/31/1999
12345 1/1/2000 Accounting Exp Unit 1/1/2000 1/31/2000
12345 2/1/2000 Acct. Expense Unit 2/1/2000 4/30/2000
12345 5/1/2000 Account Expense Unit 5/1/2000 9/30/2000
12345 10/1/2000 Account Exp Unit 10/1/2000 12/31/9999

View 11 Replies View Related

Create Trigger On Table / Populate With Data From Additional Tables

Nov 7, 2010

I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.

e.g.

NAME_TABLE
-reg_id
-name

ADDRESS_TABLE *trigger to be fired when a new record is created here.
-reg_id
-srv_id

PROCESS_TABLE
-srv_id
-start_time
-end_time

This is what I would like the logging table to look like:

LOGGING_TABLE
-address_table_reg_id
-address_table.srv_id
-name_table.name
-process_table.start_time
-process_table.end_time

How can I go about creating this type of trigger?

View 2 Replies View Related

Forms :: How To Populate Table Column Heading In List Item Of Oracle

Jun 26, 2013

how should i populate table column heading in list items of forms? I've create lov to select the column, then i have 10 separate list items. once i select the table from lov then list item should get populated with selected table column.

View 2 Replies View Related

Forms :: First Form Clear Previous Data And Then Populate Data From Table

May 7, 2012

when i press when button pressed trigger, i want first the form will delete all the previous data and then populate the data from the table, that's why i used clear_block first, but this clear_code is not working here. my coding is given below

go_block('show');
clear_block(NO_VALIDATE);
declare
cursor c1 is select *
from qtr_demand order by 1;
begin
[code]..........

View 26 Replies View Related

SQL & PL/SQL :: Get Coding Right To Truncate Old Entries?

Jun 15, 2010

currently developing a app for MSM in tromsø, that are going to delete the oldest entrys in the database table and then update all the relative Run history entrys in another table.

And i can't get the coding right to truncate the old entrys.Here are the

DECLARE V_SLETT varchar2(125);
V_NYE varchar2(50);
V_SLETT := :SLETT;
V_NYE := :NYESTE;
truncate DELTAKER ( IDNR in (V_SLETT) );
begin
request := UPDATE_RUN;
end;

View 15 Replies View Related

SQL & PL/SQL :: Duplicated Entries In A Valid Unique Index?

May 26, 2010

I'm having problem with my database, which contains more than 1 rows with a same value on a field that has uniqueness contraint.

Here is the log from sqlplus. When I select on RI field, it shows 2 rows. But when I select on SCNUM field, it shows only 1 row. This SCNUM has an unique index on it.

And it is still in VALID state

SQL> set autotrace on
SQL> select ri, scnum from scratch1_p where ri in (536964983, 536955574);
select ri from scratch1_p where scnum='444393975';
RI SCNUM
---------- ----------
536955574 444393975

[code].....

View 14 Replies View Related

Update Oracle Tnsnames.ora File With Some New Entries?

Oct 5, 2011

I need to Update Oracle tnsnames.ora file with the some new entries ....i was looking for ora 92, but could not find a proper link to download.now I have oracle 11 g, but i cant find tnsnames.ora file in that...is it because my installation was not proper...

View 1 Replies View Related

SQL & PL/SQL :: No Entries In Materialized View Log For Insert Append?

Aug 9, 2010

I've created a materialized view log on table with the following statement:

CREATE MATERIALIZED VIEW LOG ON table_a
WITH ROWID, SEQUENCE (column_a, column_b, column_c)
INCLUDING NEW VALUES;

The insert is done with the following statement:

INSERT
/*+ APPEND PARALLEL("table_a") */
INTO
"table_a"
("column_a",
"column_b",
"column_c",
"column_d_sum")
(select
column_a",
"column_b",
"column_c",
"column_d_sum"
from table_B)

But the Log is empty when the insert is finished. When I insert rows without the APPEND hint, rows are created in the log table. So, doesn't the log record bulk loads?

View 6 Replies View Related

SQL & PL/SQL :: Column DA_OBJECT_NAME Is Not Unique / Same Object Name Can Have Multiple Entries

Jul 22, 2011

I have a table DN_ACTIONS with 9.5 million records. Column DA_OBJECT_NAME is not unique and same object name can have multiple entries. Column DA_ACTION_STATUS can have any values between 1 to 5.

I want to find all the DA_OBJECT_NAME in DN_ACTIONS where all of its entries has DA_ACTION_STATUS=3.

CREATE TABLE DN_ACTIONS
(
DA_ID NUMBER,
DA_OBJECT_NAME VARCHAR2(192 BYTE),
DA_ACTION_STATUS NUMBER(1)
);

best way as it does a self join on big table of 9.5 million.

(select distinct a.DA_OBJECT_NAME from DN_ACTIONS a where not exists ( select distinct DA_OBJECT_NAME from DN_ACTIONS b where a.DA_ OBJECT_NAME = b.DA_OBJECT_NAME and b.DA_ACTION_STATUS != 3))

What are the better ways to rewrite this query?

View 2 Replies View Related

Application Express :: Tabular Form Works Fine For First Entries

Sep 28, 2012

Tabular form works fine for first entries, but upon going back to add more records Page 55 has a button that links to the 'Approval' page which is a tabular form (Page 56).A text field at top of this tabular form receives the pass of a varchar2 field from page X which is an identifier (not PK). Tabular form query has condition WHERE ISO_NUMBER = :P56_ISO_NUMBER

When I add records on the first pass to the tabular form and click submit all is fine and dandy.When I get out of the tabular form and go back to it and add additional records I get this error:

Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: ORA-20001: Current version of data in database has changed since user initiated update process. current checksum = "C4B43B9A17A41D287D55DEFE9B035944", item checksum = "607C07E22C9B03E6A45AF44EDC06BB31"., update "SPICE_HUNTER1"."DOC_APPRV_DOC" set "APPRV_DOC_ID" = :b1, "DOC_INFO_ID" = :b2, "ISO_NUMBER" = :b3, "DOC_APPROVER" = :b4, "DOC_APPROVED" = :b5, "DOC_APPROVAL_DT" = :b6, "DOC_COMMENT" = :b7, "APP_USER" = :b8Here's what I've done to try to figure it out:

1. Ran debug and saw it was happening in my MRU; and
2. In the past what I have cleaned out tabular form table data because I've found that when I'm in this testing mode going back and forth with changes gets fouled up, but with a clean slate it still happens.
3. I had Hidden the ID fields, and then remembered this was one of the issues with versions of this error. (I actually logged this one to remember!). So I made them Display as Text (Saves State) and unchecked their boxes.

View 1 Replies View Related

Oracle Listener Log Entries Missing And Database Not Allowing Connections

Mar 20, 2013

We have a custom portal application that uses a bunch of application servers. Our connection pool max size is 1100. For the past two days, we had two incidents when the cpus reached the max limit and the db was not allowing connections to come thro' for 10 minute periods. The alert log does not have any entry and the listener log does not have any entry for that 10 minute period.

View 5 Replies View Related

Client Tools :: Separate User Input List Into One Column Of Entries?

Oct 3, 2011

I am trying to ultimately as the title says separate a user input list into one column of entries. I am doing this through Cognos not a normal SQL editor which is what makes this a little harder to do. So far I have gotten that in general I can use the

SELECT 'First Entry' Asset FROM Dual Union
SELECT 'Second Entry' Asset FROM Dual Union
SELECT 'Third Entry' Asset FROM Dual

and this will give me 3 entries of data in one column. More can be added as long as the last statement doesn't have the union on it. So, the next step it would seem is to have a for loop combined with an if then or case statement that would find the number of entries and loop until we reach the number of entries and give me either SELECT 'First Entry' Asset FROM Dual Union or SELECT 'First Entry' Asset FROM Dual if we are on the last entry. I don't know the lingo to do this though. I have tried to get this to work with a simple test like cat, dog, horse, cow, pig, etc but it's frustrating that I can't get it to work. I can do all the individual steps I just can't seem to get it to work together. I have all the functions I need, I just need to the syntext to do a for loop along with an if then or case statement where the outcome is a valid select statement.

View 5 Replies View Related

SQL & PL/SQL :: Script To Query Index Statistics Data And Update Tracking Entries?

Oct 19, 2010

I have created a table: INDEX_SIZE_TRACKING with the following attributes

Index Name: name of the index. Type: VARCHAR(255). This is the primary key of the table.

Allocated Space: the memory space (in bytes) allocated to the index. Type: NUMBER

Used Space: the memory space used by the index. Type: NUMBER

Last Update: the time when index details are updated to this table. Type: VARCHAR(255)

I want to write a PL/SQL script to query index statistics data and update tracking entries in the INDEX_SIZE_TRACKING table. If there is no existing entry for the index, create a new one; otherwise, update the existing one. have a PL/SQL statement that can do this in oracle XE?

View 1 Replies View Related

SQL & PL/SQL :: Week On Particular Day According To Arabic Calendar

May 26, 2013

As my requirement is that to find week for particular date according to Arabic calender.As per Arabic calender first day for year starts from saturday.

so when i execute query :

select to_char(to_date('05/26/2013','MM/DD/RRRR'),'WW')
from dual;

Then it gives 21,but as per arabic calender it should show 22nd week.

getting result according to Arabic calender as it should return 22,because weak starts from saturday according to Arabic calender.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved