PL/SQL :: Inserting Values In Table From Other Table
Aug 3, 2012
I have a table AUD$ (containts some data) and aud_new(exactly structure same as AUD$ table with no data)
i want to schedule a job which will insert all data from AUD$ into table aud_new$ from aud$ table on next day again data from aud$ should be append to aud_new table and aud$ table should be truncated again.
The job should run on every day at midnight at 2 am
(note : If insertion in table aud_new get fail due to any reason , the aud$ table must not get truncated )
I am trying to insert the following values in a table called as inf_content
The table INF_CONTENT has following two cols (REQUEST_ID NUMBER(10,0) , FLTER_SET_XML "SYS"."XMLTYPE" NOT NULL ENABLE); =========================================================================================== INSERT INTO inf_content
[code]...
I am getting the error:
Error: SQL Error: ORA-06553: PLS-307: too many declarations of 'XMLTYPE' match this call
LPAD not behaving as expected. the main thing I'm trying to accomplish here is reading values from one table, and inserting them into another... but in the "other" table, they need to be inserted as 11 characters long, with leading zeros. in it's most basic form, this is the cursor I'm using:
CODEDECLARE CURSOR update_mpi_cur IS select distinct A.epn_nbr, A.mrn_nbr, B.mpi_nbr from table1 A, table2 B where B.external_id = A.epn_nbr and B.identifier_type = 'EPN'; [code]....
should have mentioned that this is Oracle 10.2.0.3, on HPUX. not sure if that matters for this issue or not, but wanted to throw that out there.
I need to insert data in Table A from Table B where most of the fields are identical and might some of the fields will be more in Table A.
ex: Table A: a,b,c,d,e,f Table B: a.b,c,g,h
How to insert this using user_tab_columns in cursor and if I am giving the i/P as my table names . This needs to be configurable and reusable rather i mention all the fields in my logic.
I have got two tables emp_dtl and iou_tab. i have already made entries i.e booking no, emp_cd, emp_name etc in emp_dtl snc its my master table. I want to retrieve the booking nos through lov in iou_tab which are generated in emp_dtl and corresponding info of emp_cd and emp_name should come in the respected fields in iou_tab.
select * from test_1 IDNameTotal ----------- 1A100 2B100 3C100 4D100
test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2. select * from test_2 ID ---- 1,2,3
My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.
SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)
create table test_1 (id number, name varchar2(100), total number) create table test_2(id varchar2(100)) insert into test_1 values (1,'A',100) insert into test_1 values (2,'B',100) insert into test_1 values (3,'C',100) insert into test_1 values (4,'D',100)
My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.
This is my table:
DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE; CREATE TABLE BookingDM ( hNo NUMBER(3), gNo NUMBER(5), dFrom DATE NOT NULL, [code]......
This is the first set I'm attempting to insert hNo = 148 gNo = 11169 dFrom = 09/03/2009 dTo = 09/10/2009 rNo = 202
This is my attempt to insert the set:
SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202'); INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202') * ERROR at line 1: ORA-01843: not a valid month
i am trying to fetch the cursor returned from a stored procedure and insert into a physical table. but i am unable to populate the table. below is how my code looks.
declare p_out sysadm.CGF_PKG_REFCUR.rcDataCursor; a table1.node%TYPE; b table1.acct%TYPE; c table1.descr%TYPE; [code]......
In the above, stored_proc is the stored procedure from OLAP database. Above code i am executing is from EPM database.
p_out returns a result set of (select a,b,c from table2).
I am trying to get the result set from OLAP database and populate a table in the EPM database.
I used decode & pivot insert for this,but the result is a failure.
SQL>INSERT INTO test22 (no,name) SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
SQL> sno sname -------- 1 null null arun
AND
SQL> INSERT ALL 2 INTO test22 VALUES(no) 3 INTO test22 VALUES(name) 4 SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p; INTO test22 VALUES(name) * ERROR at line 3: ORA-00904: "NAME": invalid identifier
I am trying to insert a column into a variable from a trigger.
Here is the code that i have:
CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer FOR EACH ROW DECLARE v_key varchar2(10); BEGIN select ID into v_key from buyer; insert into message_log_table (table_name, message_comments) values ('Buyer', 'Buyer '||v_key||' has been updated'); end; /
When I run the above I get the following compiler error:
Since ID is defined in my BUYER table I do not understand what the error means.
Here is my create table statement:
CREATE TABLE BUYER ( ID VARCHAR(50) NOT NULL PRIMARY KEY, FNAME VARCHAR(50) NOT NULL, LNAME VARCHAR(50) NOT NULL, ADDRESS VARCHAR(50) NOT NULL, CITY VARCHAR(50) NOT NULL, STATE VARCHAR(2) NOT NULL, ZIP_CODE NUMBER(5) NOT NULL );
I want to insert bulk records to the table. I want to insert date rows for next 50 years in table ( from year 2001 to year 2050). I have following columns in my table :
YYYYMMDD MM/DD/YYYY Day of the week ( Monday, Tuesday etc) JulianDate
How to insert more than 30000 records in a table using oracle procedure where I am having a table with number,varchar,data fields and columns like mpno,ename,sal,date of joining,data of leaving.
Data should populate using procedure.is there any way of doing it by procedure
Now i have to insert this xml into DB , the table consist of following columns ( row number , property name , value ) Expected out put is (1,student name,Raymond) ,( 1, studentid , 1) ( 1, studentAge, 11) (1,Studentmark , 0) The challenges here is
1. how to get the tag names and populate the property name column ? 2. The number of properties for a student can be variable , How can i deal with them ?
I have a table of 10 records ,out of 10 records 2 records are having null values by using anonymous block i need to move the successful record(excluding null) into 'abc' tableand null records into 'err'table.
I am exploring the differences between OBJECT & RECORD. As i am still in process of learning, I found that both are structures which basically groups elements of different datatypes or columns of different datatypes, one is used in SQL and other is used in PL/SQL. Below i am trying to insert data into an table of type object but i am unsuccessful.
CREATE OR REPLACE type sam as OBJECT ( v1 NUMBER, v2 VARCHAR2(20 CHAR) );
---Nested Table--- create or replace type t_sam as table of sam; --Inserting data---- insert into table(t_sam) values(sam(10,'Dsouza')); Error Message: [code]........
I am trying to insert huge data into another huge table which is almost taking around 2-3 hrs. See my below query
INSERT /*+ APPEND *//*+ NOLOGGING */ INTO DB1.Table1 SELECT * FROM DB2.Table2 ; COMMIT;
Both Table1 and Table2 have same structure and table1 is master table having 100 Billion records and table2 having 30 Million records. This is a direct insert where each day this operation carried.
I want to insert into two separate tables using the following logic :
If date1 is not null or no1 is not null then insert into target_table1(id,date1,no1) If date2 is not null or no2 is not null then insert into target_table2(id,date2,no2)
I have a form that has one text box in it, and I want this value to be inserted into a table called Staff Name, which has only one column, but im unsure as to how I would get my SQL statement to pick up this value.
I tried:
begin insert into StaffName values ('addstaffmember'); end;
Add staff member is the name of the text box. this statement compiled but when I ran the form and tried to click save it would not work.
Ive just been trying to create an add member procedure to retrieve an object that is in another table. But before i get the ref to bring the toy across i wanted to make sure i could insert an object into the new table. I keep getting theres too many arguments, the lack of sqlplus code, the spool function isnt working.
DROP TABLE completed_toys; / CREATE OR REPLACE TYPE comp_toyobj AS OBJECT ( completed_id NUMBER(5), request_id REF toy_obj,
[Code].....
Here is the error on the procedure call
Error report: ORA-06550: line 4, column 9: PLS-00306: wrong number or types of arguments in call to 'ADD_COMPLETED' ORA-06550: line 4, column 9: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s: %s" *Cause: Usually a PL/SQL compilation error. *Action:
I was trying to insert a row into Oracle database consisting 20 columns through IBM Web Experience Factory. I am getting "Array index out of range: 20" error message. To my knowledge insert is working fine till 7 columns. Is it a driver problem or do i need to install any fix pack?
