SQL & PL/SQL :: Insert Data Into Lookup Table?
Jan 5, 2012
I have 2 tables, TABLE1 AND LOOKUP_TABLE. The requirement is to insert the distinct (AREA,CITY) from
TABLE1 into LOOKUP_TABLE, where LOOKUP_TABLE.LOOKUP_ID = LOOKUP_SEQ.NEXTVAL;
-- Test Case
DROP SEQUENCE lookup_seq;
CREATE SEQUENCE lookup_seq START WITH 1;
DROP TABLE table1;
CREATE TABLE table1
[code]...
Expected data in LOOKUP_TABLE
LOOKUP_ID AREA CITY
--------- --------- -------
1 area1 city1
2 area2 city2
3 area3 city3
4 area4 city4
5 area5 city5
Currently this is what I'm doing.
INSERT INTO lookup_table SELECT sl_no, area, city FROM table1;
DELETE FROM
lookup_table a
WHERE
[code]...
Is there a better way of doing this, using a single insert statement?
View 8 Replies
ADVERTISEMENT
Aug 17, 2012
Ive got a look up table and i want it to display text instead of just the ID number, how would i do that? so for example if
1 = Hello
2 = Bye
it would display "Hello" instead of "1"
View 8 Replies
View Related
Oct 23, 2012
I have two tables:
create table units (id number primary key , agent number,
constraint agnent_fk foreign key (agent) references agent_lookup (id) )create table agent_lookup (id number primary key , agent_name varchar2 (40))INSERT INTO AGENT_LOOKUP (ID, AGENT_NAME) VALUES (1, 'X1');
INSERT INTO AGENT_LOOKUP (ID, AGENT_NAME) VALUES (2, 'X2');
INSERT INTO AGENT_LOOKUP (ID, AGENT_NAME) VALUES (3, 'X3');INSERT INTO UNITS (ID, AGENT) VALUES (100, 1);
INSERT INTO UNITS (ID, AGENT) VALUES (101, 2);
INSERT INTO UNITS (ID, AGENT) VALUES (102, 3);I want to make units table uses user_lookup table instead of agent_lookup table; where user_lookup contains the same names that exist on agent_lookup, but with different IDs.
create table user_lookup ( id number primary key , user_name varchar2 (40))INSERT INTO USER_LOOKUP (ID, USER_NAME) VALUES (10, 'X1');
INSERT INTO USER_LOOKUP (ID, USER_NAME) VALUES (20, 'X2');
INSERT INTO USER_LOOKUP (ID, USER_NAME) VALUES (30, 'X3');So, I need to update the table units and SET the value of Agent to the ID column of the user_lookup table .
I thought of this, but sure it will not work:
update units ut set ut.agent = (select u.id from user_lookup u where agent_lookup.agent_name = u.user_name )
where ut.agent = agent_lookup.id
View 7 Replies
View Related
Aug 15, 2013
i need to get values from lookup table dynamically,i am getting the missing keyword error.
create or replace procedure xyz(cur1 OUT SYS_REFCURSOR)
AS
vsql varchar2(2000);
CURSOR CURSOR1 is SELECT DQS_SRC_COL_NM,LDIC_SEQUENCE FROM look_up WHERE LDIC_SOR ='friend';
BEGIN
[code]....
View 14 Replies
View Related
Apr 16, 2013
I've been successfully setting up text indexes on multiple columns on the same table (using MULTI_COLUMN_DATASTORE preferences), but now I have a situation with a one-to-many data collection table (with a FK to a lookup table), and I need to search columns across both of these tables. Sample code below, more of my chattering after the code block:
CREATE TABLE SUBMISSION
( SUBMISSION_ID NUMBER(10) NOT NULL,
SUBMISSION_NAME VARCHAR2(100) NOT NULL
);
CREATE TABLE ADVISOR_TYPE
[code]...
I've looked at DETAIL_DATASTORE and USER_DATASTORE, but the examples in Oracle Docs for DETAIL_DATASTORE leave me a little bit perplexed. It seems like this should be pretty straightforward. I'm trying to avoid creating new columns, and keeping the trigger adjustments to a minimum.
View 3 Replies
View Related
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
Feb 20, 2013
I have a table with a BLOB column ;
I want read data from table and insert to another table with a cursor
My code is :
procedure read_data is
cursor get_data is
select id,image from picture1;
id1 number;
pic blob;
begin
open get_data;
[code]....
when I run form , error FRM-40734 occurred
error in line " fetch .... "
View 1 Replies
View Related
Mar 25, 2010
I'm trying to insert data into a table, but can't seem to get the actual code right, when I put it in, Here is the code I have that is giving me the error:
INSERT INTO Orders VALUES('00001', 'c001', 'ca01', '20.5', TO_DATE('12032009', 'DDMMYYYY');
View 4 Replies
View Related
Oct 7, 2012
i have one table name called TRXN with 43gb data with primary key...i have other table named TRXN_P with 15gb data...some of the records of TRXN_P are already there in TRXN table....i want to insert that remaining data in TRXN_P into TRXN table?
how can i insert ?
View 6 Replies
View Related
May 4, 2013
i created and inserted data to my database using SQL plus. all tables has been created sucsefully and i can see them in OUI. but all tables rows shown empety(it means under the rows, column is empety not 0). now how can see the data which i inserted to tables ? can i see them in OUI?(web interface)
View 12 Replies
View Related
Jul 2, 2010
use XML package & UTL_FILE package & i had install the software Oracle Database 10g Express Edition, Oracle Client 10g Express Edition & Oracle SQL Developer
I have tried different methods as mentioned below:
XML File : trailxml.xml stored in C:OracleProject
<?xml version = '1.0'?>
<metadata>
<Zipcodes>
[Code].....
View 21 Replies
View Related
May 28, 2010
I need to pass a large data into one of the tables where the column is declared as CLOB before which I was checking with the sample code as below which is throwing an error.
I was trying to insert the CLOB data into the table as below.
create table t1
( x number,
y clob
);
insert into t1(x) values (2)
declare
l_clob t1.y%type;
[code].....
The error I am getting is:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 833
ORA-06512: at line 161
View 1 Replies
View Related
Sep 25, 2013
Would like to ask expert here, how could I insert data into oracle table where the value is 03 ? The case is like that, the column was defined as varchar2(50) data type, and I have a csv file where the value is 03 but when load into oracle table the value become 3 instead of 03.
View 8 Replies
View Related
Dec 26, 2010
I had my previous table model as below.
CREATE TABLE Rel_Module (
Id NUMBER (6) PRIMARY KEY,
Type CHAR (7) NOT NULL,
[Code]...
How I can easily load data into this new REL_MODULE table either using SQL or PL/SQL (e.g., Procedures, Functions, and Triggers). I don't need to write so many INSERT statement.
View 20 Replies
View Related
Oct 10, 2013
I have a table emp as mentioned below:
SELECT * FROM EMP_TEST;
EMP_TEST
-----------------------------------------------------
ENO EFIRSTNAME ESECONDNAME DEPTNO
1 JOHN PAI 10
2 ABC DEF 20
3 EFG GHI 30
Now the primary key in this above table is pk_emp_test_eno on eno.
I have a requirement where i need to dump some dummy data (600000000 numbers of data ) into the emp_test based on these existing data without disabling the constraints (maintaining unique constraint for each record). And while inserting i want to commit after every 1000 insertion.
in bulk inserting dummy datas into the table as it is taking much more time to insert into the same.
View 5 Replies
View Related
Nov 7, 2012
how made a success of insert data into table temporary in job_schedule ? because when i tried to change insert into table permanent always successfully.
CREATE GLOBAL TEMPORARY TABLE YG_PAYMENT_TMP
( SITE_CODE VARCHAR2(100 BYTE),
KBON VARCHAR2(100 BYTE),
FUTURE_DATE DATE
[code]...
why insert into table temporary not successfully in job_submit.
View 3 Replies
View Related
Aug 5, 2010
i have a tabular form select * from emp and i want to create table and store there data in goup select empono,sal,com group by dept i want to insert in another table.
how i insert the data in table by forms front end and then update also when again click the button or any change occur in form insert into a select empono,sal,com group by dept
View 2 Replies
View Related
Feb 4, 2012
how to insert data in two tables in same time
table1 --master table
table2 --transaction table
View 13 Replies
View Related
Apr 25, 2013
I want to select data from different tables and insert this into one table based on some conditions:
SELECT *
FROM welltest_msr
WHERE well_s = 3419740
AND check_ind = 1
[Code]....
So I tried doing this with selecting the data and looping through it to do the insert.
DECLARE
--
-- WELLS
--
CURSOR c_well
[code].....
View 6 Replies
View Related
Feb 1, 2011
i want to insert some data in the transactions table on after insert trigger. Which trigger should i use on form level to accomplish this task.
View 4 Replies
View Related
Oct 15, 2013
I am inserting data using a procedure for 2012 and 2013 year which is using partitioned tables includes crore of data in a partition taking lot of time or taking months. Is there any other way by which I can insert data fast from our query.
View 14 Replies
View Related
Feb 19, 2013
I have a requirement like to validate the data in PL/SQL script dynamically.
I have 4 tables
TEST_TBL: Data available in this table
TEST_VALID_TBL: Contains field names of TEST_TBL and condition
VALID: Need to insert valid records
INVALID: Need to insert invalid records
I have to insert data into valid table when validation are full filled otherwise it should be insert invalid table .
Validation are based on TEST_VALID_TBL
While checking the data validations FIELD_NAME should be passed dynamically,because i have four columns in TEST_TBL but at present I am validating only 3 columns in feature it may be add more columns to validate.
View 4 Replies
View Related
Feb 6, 2013
I have a Clob file as a in parameter in my PROC. . File is comma separated.need procedure that would parse this CLOB variable and populate in oracle table .
View 6 Replies
View Related
Dec 26, 2011
i used sql loader to import data from csv file to my db.but every time the columns places are changed.o i need dynamic way to insert data into correct column in the table.
in csv file contains column name and i insert this data to temp table, after that i want to read data over column name.also i read the column names from (All_Tab_Columns) to make combination of column name between temp table and All_Tab_Columns table to insert data to right place...
View 39 Replies
View Related
Aug 24, 2011
I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;
what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
[code]...
View 4 Replies
View Related
Dec 16, 2008
I want to insert XML data into my ( Oracle 11G Release 1 ) XMLType table using OCCI. I'm getting
ORA-01461: can bind a LONG value only for insert into a LONG column
My XML data size is around 1.5 to 2MB. I have also tried using setMaxParamSize before calling the setString method. But, still I'm getting the same exception.
How to resolve this issue?
View 2 Replies
View Related
Aug 21, 2011
Table contains 10k records,we are going to insert data into another table with FORALL bulk collect limit 1000. if i use 10000 ,it's completed fast compared to 1000 limit.Can u tell me which one is better Limit.
View 4 Replies
View Related
Aug 21, 2008
Is theree any way to generate the insert script (with the data) for an existing table.
In Toad, we can generate the DDL for a particular table. Just curious if it can be done using any Tool.
View 19 Replies
View Related
May 15, 2010
how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.
View 2 Replies
View Related
Feb 27, 2012
when i tried to insert the details from oracle froms..the data inserts twice to the DB..
my table structure:
create table app_sri
(a_id integer primary key,
p_first_name varchar2(30),
p_last_name varchar2(20),
p_age number(3)
);
here a_id can be genarated through simple sequence(pid_seq)...
trigger on app_sri
create or replace trigger pid_trg
[Code]....
form insertion code..
Begin
insert into app_sri(null,'robo','Big',100);
commit
End;
the data inserted...but twice
what is the reason behind the double insertion?
View 8 Replies
View Related
