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


ADVERTISEMENT

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.

CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Pull In Last Loaded Data From Staging Table To Target Table?

Mar 9, 2011

I have a staging table and a target table. How do I pull in last loaded data from staging table to target table?

View 4 Replies View Related

PL/SQL :: Index On A Staging Table

Jul 10, 2012

I have 2 similar tables. Same columns. Data inserted to second table from first for a condition and first table is truncated. Then data moved from second to first and second table truncated. Second table is to store data for a small period of time. First table has indexes. Do I need any index on second table?

The query on second table is always insert into second select * from first. Similarly insert into first select * from second.

View 3 Replies View Related

SQL & PL/SQL :: Exchange Partitions Between Actual Table From It's Corresponding Staging Table

Nov 26, 2012

We have a table with interval partition. This table is accessed very frequently. We are suppose to exchange partitions between this actual table from it's corresponding staging table.

In order to keep the newly created partitions empty, is there a way to restrict other applications from using it before we push data from staging table to it's actual table.

View 4 Replies View Related

SQL & PL/SQL :: Debugging Stored Procedure / Populate Data Warehouse Dimension

Nov 20, 2011

The following code is a stored procedure I plan to use to populate a Data Warehouse dimension using data from two OLTP tables which already exist in my database. Notice that in my cursor select statement, I calculate an attribute using substr and instr, and I also assign a true or false value to a flag using a CASE statement.

CREATE OR REPLACE PROCEDURE populate_product_dimension
AS
v_Count NUMBER := 0;
v_NumRecs NUMBER;
/*Declare a cursor on the following query which returns mulitple rows of data from product and price_hist tables*/
[code]....

In my mind, Product_Code is declared correctly in the Cursor declaration Select statement.

View 10 Replies View Related

Server Utilities :: SQL Loader Loads All Fields With Double Quotes Into Staging Table

Aug 25, 2010

I am having a similar problem like above ONLY in UNIX box where my datafile is delimited by "|". The last field is ITM_CMNT declared as VARCHAR2(60) in Oracle. When I have exactly 60bytes in the last field it rejects the record saying actual 61 and max allowed is 60. If i reduce it to < 60bytes then it is stored as a value enclosed with double quotes. The enclosing double quote is on the next line.

"PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BA"

Expected: the one below is exactly 60bytes.

PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BAT
LOAD DATA
INFILE *
INTO TABLE TMPTLI_LAWSON_ITM_MST
TRUNCATE
FIELDS TERMINATED BY "|"
(ITM_NO, HAZ_MAT_CD, ITM_SHRT_DS, ITM_SON "TRIM(:ITM_SON)", ADDED_DT DATE "YYYY-MM-DD",
AVL_CD , ITM_CST_AMT, ITM_SLL_AMT,
EXCHG_PRC_AMT , ITM_UOM "TRIM(:ITM_UOM)",
PCK_QTY INTEGER EXTERNAL, SPC_HNDL_CD "TRIM(:SPC_HNDL_CD)", EFF_DT DATE "YYYY-MM-DD",
ITM_CMNT "TRIM(:ITM_CMNT)")

View 4 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

PL/SQL :: How To Reset Primary Key to 1 Using Store Procedure

Mar 19, 2013

I have created table and i am using sequence to increment primary key value.

Now i need to delete the table content and reset primary key to 1 using store procedure.

View 18 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

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 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

Performance In Inserting Data From Staging

Feb 13, 2013

I am facing problem while inserting data into Core Table from staging (both Core Table and Staging are on same database but different schema)

I am using below command:

INSERT /*+ APPEND PARALLEL("CORE TABLE", DEFAULT, DEFAULT) */ INTO
SELECT DATA from Staging

CORE TABLE is quit big contains millions of record partition on date and having old stats of 2007. Data fetching from staging is very fast appx 1 million record in 2 mins. we are inserting one day data daily into CORE TABLE from staging and its taking 3 Hrs.

View 2 Replies View Related

Replication Of Production Data To Staging

Aug 30, 2011

We have Development, Staging/UAT ( installed on XX.XX.XX.10 ) and Production ( installed on XX.XX.XX.20) Environment respectively. I have queries regarding getting the data from Production environment into Staging environment. The overall PROD database size is around 250 GB.

STAGING DATABASE DETAILS

SID : STG_DB
Staging Schema Name :schema_UAT
Replication Schema Name :schema_PrdReplica ( This is the schema where the production data gets loaded daily)

PROD DATABASE DETAILS

SID : PROD_DB
Prod Schema Name : schema_PROD

What is happening now:
----------------------------
There is a script (Stored Proc) written on staging ( STG_DB.schema_PrdReplica ) environment which executes daily in NIGHT and does replication. Currently we use DBMS_DATAPUMP to get the ENTIRE data/Meta Data (Everything) from Production to Staging. It is ta king significantly more time. It takes approx 8 Hours to replicate the everything from PROD_DB.schema_PROD to STG_DB.schema_PrdReplica

What I am expecting :
-----------------------------
I want to reduce the replication time.

I have heard about Level 0 (Full BackUp ) and Level 1 ( Incremental Cumulative ) Backups in RMAN. I am planning to take PROD_DB.schema_PROD Full Backup (Level 0) on Sunday and will restore that on STG_DB.schema_PrdReplica immediately. And on weekdays ( Mon - Fri ) I will take Level 1 ( Incremental Cumulative ) and will restore that on STG_DB.schema_PrdReplica

I am assuming by doing so, the overall replication time will be reduced. How can I implement this with script assuming that two different servers are on different machines.

View 1 Replies View Related

All Columns Of A Table Are Part Of Primary Key Column Of That Table?

Feb 22, 2011

if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).

should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.

there is a drawback if a most of the tables of a database contain composite primary keys?

View 3 Replies View Related

Windows :: OUI-10133 / Invalid Staging Area

Oct 5, 2005

I have a prob installing Oracle 10g on Window 98 SE :

OUI-10133:Invalid staging area.

There are no top level components for Windows 98 available for installation in this staging area.

View 8 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

Real Application Clusters :: How To Export Database Statistics From Production To Staging

Jun 7, 2013

i am using 11.2.0.3.0 version db on Linux x86 64-bit...how to export the database statistics from Production to staging .

View 9 Replies View Related

Finding Primary Key In A Table?

Oct 4, 2007

Whats the command to show the name of the primary key in a table using oracle sql plus?

View 1 Replies View Related

SQL & PL/SQL :: Table Partitioning With No Primary Key

Jan 24, 2013

we apply partitioning concept on a table which don't have any primary key ?

I just want to add one more field as primary key with some sequence generated values while partitioning ? Is it possible ?

View 7 Replies View Related

PL/SQL :: How To Write Procedure To Load Data Into Table Using XML File As Input To Procedure

Sep 20, 2013

how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me. 

xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.

View 3 Replies View Related

SQL & PL/SQL :: Exception In Procedure / Created One Procedure Based On One Table Item Master

Mar 6, 2010

I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.

CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....

View 8 Replies View Related

Add Partial Primary Key Into Oracle Table?

Oct 17, 2006

how to add a primary key into the oracle table but how do i add a partial primary key?

View 2 Replies View Related







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