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?
View 14 Repliesshall we stop inserting data into a table before it inserting using Trigger?
View 3 Replies View RelatedI would like to know if we can insert 300 million records into an oracle table using a database link. The target table is inproduction and the source table is in development on different servers.The target table will be empty and have its indexes disabled before the insert. if this can be accomplished in less than 1 hour.
View 26 Replies View RelatedI have two table and trying to update tableA with data from tableB but gives an error. My TableA has columns - colA,colB,colC,colX and table B has columns - colA,colB,colX. Sample data looks like this:
tableA:
XXYY, local,3/19,48
XXYY,ixc,3/19,24
XXYYlocal,3/20,48
XXYY,local,3/21,48
Table B:
XXYY,local,48
XXYY,ixc,24
PPQQ,local,72
this is the query I wrote
update tableA a
set (a.colX)=(select (b.colX) from tableB b where a.colA=b.colA and a.colB=b.colB);
When i run it gives this error:single row subquery returns more than one row.
tableB has only 1 value for colX for each colA,colB record but tableA has mutiple colA and colB repeated but for all of thsoe in table A i want to update the tableB.colX value for matching colA and colB.
i create this trigger to lock inserting transaction for any data greater than 30-04-2013 on a certain table
CREATE OR REPLACE TRIGGER INVALID_DATE_VALUE BEFORE INSERT or UPDATE on ra_cust
FOR EACH ROW
BEGIN
IF :NEW.TRX_DATE >'30-APR-2013'
THEN
-- RAISE_APPLICATION_ERROR('ora-0000','DATE CANNOT FALL IN RANGE...Invalid Date');
RAISE_APPLICATION_ERROR(-20000, 'IT IS NOT ALLOWED TO INSET DATE VALUE GREATER THEN ''30-04-2013''');
END IF;
END;
and this trigger created successfully
when i try to insert data in the table ra_cust even in a range less than 30-apr-2013
i got this error
ORA-20000: IT IS NOT ALLOWED TO INSET DATE VALUE GREATER THEN '30-04-2013'
ORA-06512: at "ARASK_HAGAR.INVALID_DATE_VALUE", line 5
ORA-04088: error during execution of trigger 'ARASK_HAGAR.INVALID_DATE_VALUE'
I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
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.
how to use APEX_ITEM.text for where the source for this comes from user input values and not for an underlying table.I have a report where three columns are coming from an actual table (one being a checkbox for selection) but then also have two additional fields on the screen that are not from a table but placeholder fields for user's to enter data.
For example, my query looks like this:
select apex_item.checkbox2(10,id),
name,
telephone,
apex_item.text(20,NULL) as "Date Contacted",
apex_item.textarea(30,NULL,5,80) as "Comment"
from my_table
I am not seeing any values for the two user fields when entering values and doing a view source?
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 need my dates to be in the format "MM/DD/YYYY".
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 have two tables with two columns of each table in my Oracle Version :Oracle 9.2.0.1.0
TEST22:
-----------
|sno |sname |
-----------
| | |
-----------
TEST22P:
---------------
| col1 | col2 |
|---------------|
| sno | 1 |
| sname | arun|
---------------
Required outcome is
TEST22:
----------
|no | name |
|----------|
|1 |arun |
----------
Also this should be applicable for more than one value in the column col2 of table TEST22P.
ex:-
TEST22P:
--------------------
|col1 | col2 |
|--------------------|
|sno | 1,2..n |
|sname | arun,ajay..n|
--------------------
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 need to insert rows in a table all the columns are have same value except one column i.e.
insert into table values( 'a','b','b','b');
insert into table values( 'b','b','b','b');
insert into table values( 'c','b','b','b');
is there any short cut to insert because there are thousands of records to be inserted.
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:
[Error] ORA-00904 (6: 12): PL/SQL: ORA-00904: "ID": invalid identifier
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
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
I have an insert statement like below.
insert into emp (select empno,ename,sal);
Here I need to exclude the rows having sal<0 from the SELECT query and insert those into some other table simultaneously.
CREATE TABLE tbl_emp
(
name VARCHAR2(20),
price NUMBER,
[Code]...
NAME PRICE DATAENTRD ROWRANK
aaa 9999 24.07.2012 05:56:00 1
aaa 10000 24.07.2012 05:55:58 2
I want to insert this result into another table, how can I do it??
Quote:TABLE
CREATE TABLE tbl_emp_result_set
(
name VARCHAR2(20),
price NUMBER,
dataEntrd date,
rowrank number
)
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
I have a table created with the following code:
CREATE TABLE CLIENT
(
CLIENT_ID NUMERIC(2),
CLIENT_NAME VARCHAR2(25),
CONTACT_LAST_NAME VARCHAR2(15),
[Code]...
I have altered the table to have the following:
ALTER TABLE CLIENT
ADD CLIENT_CITY VARCHAR2(25);
I am trying to insert new data into said table that was updated:
INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1;
Then I get the following error:
Error starting at line 1 in command:
INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1
Error at Command Line:4 Column:15
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
I have an XML of the following format:
<data>
<students>
<student>
<studentname>Raymond<studentname>
<StudentId>1</StudentId>
<StudentAge>11</StudentAge>
<StudentMark>0</StudentMark>
</student>
</students>
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.
View 3 Replies View RelatedI 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 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 )
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 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 have a data from the source table like this :
IdDate1 No1 Date2 No2
12/15/19959292 8/30/1997 11767
25/21/19932812 4/29/2002 2812
39/22/1978
410/11/19781157381 2/8/1979 929137
58/2/19891698430 11/30/1989 1365528
6
711/20/199541 1/23/1997 677
811/20/1995 1/23/1997 680
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 one indexed column in a table.for performance improval purpose i created index to that column .
my doubt is..it may decrease the performance while inserting data into that column?
How to raise a exception when inserting a record in a table using plsql?
View 11 Replies View RelatedI 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.
I have a table with a column type long raw. Now i want to insert an image from directory by an insert statement. Is it possible?
View 2 Replies View Related