I am creating the following two tables...no issues here:
CODECREATE TABLE COURSE_SECTION
(
Csecid NUMBER(8) CONSTRAINT COURSE_SELECTION_NUMBER_pk PRIMARY Key,
Cid NUMBER(6) NOT NULL CONSTRAINTS COURSE_SELECTION_Cid_fk REFERENCES COURSE,
Termid NUMBER(5) NOT NULL CONSTRAINTS COURSE_SELECTION_Termid_fk REFERENCES TERM,
[code]...
The issue I am having is actually inserting data into the table:
CODEINSERT INTO ENROLLMENT
VALUES (100, 1000, 'A' );
INSERT INTO ENROLLMENT
VALUES (100, 1003, 'A' );
[code]...
But I get an ORACLE error of
ORA-02291- integrity constraint (User1.ENROLLMENT_CSECID_FK) violated - parent key not foundHow can the parent key not be found when I have it declared/created in the above statement?
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.
i'm working in an Oracle 10g database on an IBM AIX server.
I have 3 tables (tables A, B and C).
Table A has columns -- product, rate and expiration date.
Table B has columns -- product, rate and deductible.
Table C has columns -- product, rider, gender, age and rate.
I also have a Master table which is used to store the data from Tables A, B and C via the insert statement.
I'm trying to create a dynamic SQL insert statement using a shell script to insert data from the columns in Tables A, B and C into my Master table. Master table does contains all columns from Tables A, B and C, although a column name could be spelled differently. For example, Master table contains a column named "deduct", while Table B has the same column spelled as "deductible".
I build the dynamic query using a for loop in my shell script (see below).
The problem is that i can't get the correct columns in the Master table in the dynamic SQL for the insert because depending on the table i'm selection from, the columns are different. So how do i get the correct columns in the SQL for the Master table?
Example Shell Script
--Archive_Rates.txt contains: Table A, Table B, Table C (but the next time my process runs, Archive_Rates might contain Table D, Table E and Table F -- each which have different column...but all columns are still in the Master table)
for tbl in `more Archive_Rates.txt` do echo 'BEGIN WORK; ' > rc1.sql echo ' ' >> rc1.sql echo 'insert into Master' >> rc1.sql echo '(prod, rate, rate_exp) ' >> rc1.sql
I have a procedure as below. To sum up the procedure in one line it dynamically forms a string to get the values of the type which is passed as an input to the procedure.
I call the procedure as
exec exec_imm( exec_imm_t(1,'asd','1/2-34'));
ERROR: Error starting at line 9 in command: exec exec_imm( exec_imm_t(1,'asd','1/2-34')) Error report: ORA-00904: "P_TYPE_DATA"."ADDRESS": invalid identifier ORA-06512: at "PTK_ADM.EXEC_IMM", line 26 ORA-06512: at line 1 00904. 00000 - "%s: invalid identifier"
NOTE: When I try to execute the procedure with the execute immediate statement I get the above error. But when I execute the select statement which is nothing but the value in v_type_data directly (as seen in the comments in the code below) there is no error. But when the same v_type_data is used in execute immediate, I get an error.
CREATE OR REPLACE procedure exec_imm(p_type_data exec_imm_t) AS v_type_str CLOB := NULL; v_type_data CLOB := NULL; v_type_name VARCHAR2(25) := NULL; BEGIN [code]......
1.display_item (primary key) 2-list_item (foreign key) / / dynamic list :this is the cause of the error 3-lis_item (foreign key) / / dynamic list 4-text_item 5-button (insert commit)
when I click the button, an error is displayed:
FRM-40508: ORACLE error: unable to INSERT record.in detail: ora-02291 integrity constraint Violated - parent key not found
Note:
-the elements of two lists already exist in the parent table!!
I have one query i have create one table T1 in this table i have this three colm (EMPNO,ENAME,HIREDATE) and i have to insert raw from this two table T2 and T3 in T2 table colms are (EMPNO,SAL) and T3 table colm are (EMPNO,MGRID) so which query i have to run
I wanted to print 'null' when the column value is null. Actually, i am doing something like this
select empno||','||''''||ename||'''''||','||comm||','||sal from emp
It gives the following output for example 7369,'pointers',,200 If I use the above values to form a insert statement it throws an error. As 'comm' value is not there.
I wish to get something like 7369,'pointers','',200 or 7369,'pointers',null,200 from the above select query
note I dint copy paste the query exactly from my sql*plus session as I am away from my oracle machine
I read in a book that you can't use subquery in an insert statement . E.g:
1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:
2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');
This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.
i'm executing a load test using the below statement:
INSERT INTO BPMBI.PPROCINSTANCE SELECT * FROM BPMBI.PPROCINSTANCE_BKP WHERE ROWNUM < 501; COMMIT; EXIT;
I've been using the same for a few days without any error while now wehn i try to use the same i get below error:
SQL> insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500; insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500 * ERROR at line 1: ORA-01858: a non-numeric character was found where a numeric was expected.
I've been trying to check what the issue is and when i execute the rownum < 140 works fine while rownum < 150 is showing the same error.
I have a table with unique index, and the application is doing inserts/updates into this table.Suddenly and for about half a minute I faced a high concurrency waits on all the processes running these inserts for one node. I saw this high concurrency wait in the top activity screen of the OEM only on one of the nodes. knowing that the processes doing these inserts are running on both nodes.
All what I have that in this half minute I see high concurrency wait in OEM top activity screen related to this insert statement and when I clicked on the insert I found high "enq: TX - index contention". Again this was only on one node.After this half minute everything went back to normal.What could be the reason and how can I investigate it ?
Given below a block of code, this code compiled successfully on one DB but returns error on other.DB version is same. I know sqlerrm can not be used directly but how it compiled successfully on one DB.
I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )
example of scenario...
date s1 s2 s3
in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.
I want to grant a privilege through an insert statement into a sys table.Why do not grant the privilege through the classic way : grant select on t to l_user; ?
Because I want to do it remotely.I am connected to db1.I want to grant select on t2 to u2_b from u2_a.I assume that all DDL are DML. So a grant is equivalent "somewhere" to an insert.I tried to do my requirement locally, and here is the output.
SQL> conn scott/aa Connecté. SQL> -- step 1 : try to grant "normally" a select on dept to hr from scott SQL> grant select on dept to hr;
Autorisation de privilèges (GRANT) acceptée.
SQL> SQL> conn sys/a as sysdba Connecté. SQL> -- step 2 : Then, we connect to sys to see the row inserted in dba_tab_privs SQL> SQL> col GRANTEE format A10 SQL> col OWNER format A10
[code]...
Then if I can do it locally, I can do it remotely through a db link.
If we insert a row in a database table then the new row stays at database buffer cache in SGA (until commit), right?. The target table is not affected (before commit). The new row is saved after commit.
I saw a concepts at Sybex oracle 10g oca book (Page 406) as follows:
" INSERT statements use little space in an undo segment; only the pointer to the new row is stored in the undo tablespace. To undo an INSERT statement, the pointer locates the new row and deletes it from the table if the transaction is rolled back. "
My question is If the row is not saved at table before commit, if we issue rollback then how oracle delete from table? I think the new row is deleted from database buffer cache in SGA.
