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