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 am unable to insert the result set of query into corresponding SQL Table type variable where as same functionality can be accomplished by PL/SQL table type variable. Can't we access the same by using SQL type variable?
Ex:
Step 1:
SQL Object type , Table type Objects creation : drop type sql_emp_tab_type ; drop type sql_emp_type ; create or replace type sql_emp_type as object ( empno number, ename varchar2(20),
[code]...
Step 2: Accessing the table type object from PL/SQL block
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 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.
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 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 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.
how to read the excel data and insert into tables without using SQL loader. i tried using OLE2 package,but i am getting an non-oracle exception. even i tried using CSV format. but i couldn't make it.
Our application is using a two instance, one for the live active data and the other for the reports data. We have a process which moves the data from the live instance to reports instance every night. In a single db environment the process is working without any issues. However when we move to the RAC environment the reports db's (insert) in large table get locked and we are unable to insert data to the reports db.
What we are performing is:
Insert into my_table_rpt select * from may_table_live@db_link_to_livedb;
Issues:
my_table_rpt get locked
We have found the workaround by disable locking in destination and subsequent to the insert enable locking
ALTER TABLE my_table_rpt DISABLE TABLE LOCK;
Insert the data to the reports database table
Then
ALTER TABLE my_table_rpt ENABLE TABLE LOCK
Question:
Why does the large destination table (my_table_rpt) get locked in the RAC environment?
I have a table in oracle sql developer showing years, IDs and attendance etc I'm wanting to create a report based on this table but have encountered a problem with the years.
The years currently run from 2006 to 2009 and the problem is that some of the IDs are only present for say 2007 and 2008, thus leaving nothing for 2006 and 09. This in turn creates problems in the report.
What i would like to do is in the table, for each ID that is missing any years, is to insert a single row with just the ID and year so that in the report it will show a blank instead of nothing at all. So for the above example i would have numbers for 2007 and 2008 and for 2006 and 2009 there would just be a space.
I am inserting XMLTYPE data using DBLINK I am getting the following error.
INSERT INTO APSP.SALES_HISTORY@APSP_LINK SELECT * FROM KMBS.SALES_HISTORY
ORA-22804: remote operations not permitted on object tables or user-defined type columns
Source table structure
Name Null? Type ----------------------------------------- -------- ---------------------------- SC_NO NOT NULL NUMBER(25) LT_DATE TIMESTAMP(6) METHOD XMLTYPE
Target table structure(another DB)
Name Null? Type ----------------------------------------- -------- ---------------------------- SC_NO NOT NULL NUMBER(25) LT_DATE TIMESTAMP(6) METHOD XMLTYPE
In my organization, I have a table and in that there is a column named "code".I want to restrict some insertion to that particular column. suppose that code column values are 12 and 1245 then i cant insert the value 12,1245, 1 ,124 and so on but i can insert 2 ,123,15,12456 and so on.
that means the new values should not be any substring of the existing data from left. making that column primary key and then I had a logic to compare the existing value which are longer than the new value and then to perform this.But dont know how to make it happen correctly.
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.
How to Insert the data in Repeating Tables. I mean Suppose One Student has many Addresses like home,office,permanent etc.
There is one column in this table sequence_no. while Inserting a record how to insert this sequence no I don't know It maintains unique sequence no for each student.
If student has 3 addresses then Its seq no is 3.
I am inserting through a procedure where multiple students data is to be inserted.
Excel File Name : Product 01 Excel columns File Send on 13/02/2011
Arrival Date Product Code Gate Pass Quantity Inpection 01/02/2011 00002 Y 2 Y 03/02/2011 00001 Y 10 Y 04/02/2011 00005 Y 14 Y 03/02/2011 00006 Y 74 Y
File Send on 14/02/2011
Arrival Date Product Code Gate Pass Quantity Inpection 01/02/2011 00002 Y 2 Y 03/02/2011 00001 Y 10 Y 04/02/2011 00005 Y 14 Y 03/02/2011 00006 Y 74 Y ---New Updated Data 05/02/2011 00002 Y 2 Y 06/02/2011 00001 Y 10 Y 05/02/2011 00005 Y 14 Y 05/02/2011 00006 Y 74 Y
I just want to insert data according to my structure But if again the same file send with updated data it will only update the new data because previous data is imported.
Oracle Structure Arrival Date Date, Product Code char(5), Quantity Number
CREATE OR REPLACE procedure fast_proc (p_rows out number) is TYPE object_id_tab IS TABLE OF all_objects.object_name%TYPE INDEX BY BINARY_INTEGER lt_object_id object_id_tab; CURSOR c IS
[Code]....
Warning: Procedure created with compilation errors.
T3 --------------------------------------------------------------------------- 24-MAR-11 11.29.04.491927 24-MAR-11 11.29.17.085396
system@id.world> select t4 from t;
T4 --------- 24-MAR-11 24-MAR-11
system@id.world> select * from nls_instance_parameters;
PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_SORT NLS_DATE_LANGUAGE
system@id.world> select * from nls_database_parameters;
PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_TERRITORY AMERICA NLS_CURRENCY $
[Code]...
PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
[Code]...
20 rows selected.
system@id.world> show parameter nls_timestamp_fo
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ nls_timestamp_format string system@id.world> create table newt(t1 timestamp);
system@id.world> insert into newt values('12-jan-2010 11:00:00:068801'); insert into newt values('12-jan-2010 11:00:00:068801') * ERROR at line 1: ORA-01830: date format picture ends before converting entire input string
system@id.world> insert into newt values('12-jan-2010 11.00.00.061');
1 row created.
system@id.world> insert into newt values('12-jan-2010 15.00.00.061');
1) what is significance of '.' here? insert into t values(timestamp'2011-03-24 11:03:00.05','12-mar-2011 11.03.00.055',systimestamp,localtimestamp);
2) while inserting using "values(timestamp'2011-03-24 11:03:00.05'" where this yyyy-mm-dd hh24:mi:ss.FF format comes from?
3)what is role of nls_timestamp_format of nls_database_parameters in this? how it allowed me to insert value "'12-mar-2011 14.03.00.055'" which has hh24 format?
4) Does the format of localtimestamp is decided by nls_database_parameters?
Note : I have not set nls_date_format in my session
I tried to read the link below but could not understand above
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.