CREATE TYPE Address_type AS OBJECT(
Location VARCHAR2(20),
House_number NUMBER(6),
Street_name VARCHAR2(30),
Town VARCHAR2(20),
County VARCHAR2(20),
Postcode VARCHAR2(20));
/
CREATE TYPE Contact_type AS OBJECT(
Contact_type VARCHAR2(30),
Home_no VARCHAR2(20),
Mobile_no VARCHAR2(20),
Email VARCHAR2(50));
/
CREATE TABLE Customers(
Customer_id NUMBER(6) NOT NULL,
Firstname VARCHAR2(20),
Familyname VARCHAR2(20),
Gender CHAR DEFAULT 'M',
DOB DATE,
Address address_varray_type,
Contact_details contact_table_type)
NESTED TABLE Contact_details
STORE AS customer_contact_table;
i want to create database. i have created the schema & done all activity like decide foreign & primary key of table all things on paper? when i open oracle 8i & what should i write at front of sql prompt. For enter the value into database should i give the set path? How to store value into particular directory or folder in hard disk? should i directly start with create database?
I know proper syntax. Suppose i want to store the database & values in folder " D:apurva " what should i do. After entering oracle 8i it show sql> so what i do to store database in D:apurva . should i directly start with create table command in front of sql>
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
I have a table EMP having columns User_Id, ENO,Org_ID, Dept_ID.Now I would like to insert values into this EMP table using below conditions.Insert into EMP
(user_seq.nextval,(select empno from employees where empno in(....(empnumbers), (select org_id from organizations where org_name=' XXXXXXXXXX'), (select dept_id from DEPT where dname in ('MANAGER','ANALYST','SALESMAN') ))
I would like INSERT data in a VIEW with a INNER JOIN, like this example:
CODECREATE VIEW MYVIEW (order_id,list_price,customer_id) AS SELECT order_id, list_price, customer_id FROM ORDERS o INNER JOIN PRODUCT_INFO p ON (o.order_id= p.pdt_id);
INSERT INTO MYVIEW VALUES (4,500,10); /* will cause an error*/
But when I try to execute the insert statement, the "SQL Developer" returns a error:
ORA-01779 - "cannot modify a column which maps to a non key-preserved".
Table Structure: Name of Table: info Column: ID, Name, Cell,Cell1,Ref
i want to insert these values in a table through ref (key next item). ----------------------------------------------------------- Key next Item Triger DECLARE A NUMBER; B NUMBER; BEGIN SELECT :CELL,:CELL1 INTO A,B FROM DUAL; [code]....... ------------------------------------------------------- but when i press enter(ref Key_next_item) it will return error:
FRM-40735: KEY_NEXT_ITEM RAISED UNHANLEDED EXCEPTION ERROR: ORA 40102
Insert values into a table. The table is called PurchaseOrder_objtab. Here is the type for the table:
CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT ( PONo NUMBER, CUST_ref REF Customer_objtyp, OrderDate DATE, ShipDate DATE,
[code]...
The LineItemList_ntab is a nested table.
Here is the create code for LineItemList_ntabtyp:
CREATE TYPE LineItem_objtyp AS OBJECT ( LineItemNo NUMBER, Stock_ref REF StockItem_objtyp, Quantity NUMBER, Discount NUMBER )
[code]...
In the above code, the LineItemList_ntab is an empty LineItemList_ntabtyp. I am wanting to add values to this nested table rather than it being empty in the INSERT INTO code.
Here is some of the code I have tried to insert values with:
INSERT INTO PurchaseOrder_objtab SELECT 1008, REF(C), SYSDATE, '12-MAY-1999', LineItemList_ntabtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004, NULL
[code]...
The first insert statement above produces the following error:
SQL Error: ORA-00933: SQL command not properly ended
The second insert statement above produces the following error:
SQL Error: ORA-00936: missing expression
successfully insert values into the LineItemList_ntab nested table?
I am an Oracle beginner and I am having some trouble with the following insert query.
I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.
INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338); INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);
The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"
how can i make this script into a function or procedure which instead of user change the crime_id :=4 or 5 directly it actually can grab the id from a update statment like
UPDATE CRIME_STATUS SET CRIME_STATUS = 'open' WHERE CRIME_ID = 9; pick 9 and insert into the above statment so it runs as normal
I HAVE 2 BLOCKS E.G. MASTER-DETAIL AND i have created it manually then how to insert values thrugh these blocks using a button called "save" using "commit_form;" suppose form fields are
master-block: emp_id emp name age detail block: address city where emp_id is primary key as well foreign key in detail table
How to create this pl/sql process to add elements to a nested table or varray within a loop. Here's the scenario: I have an apex package that has some pl/sql processes and some stored procedures. I am dealing with Inspection Areas. An Inspection Area has several sectors. I already have the loop that lists all the Inspection Areas and a loop inside that loop that lists all the sectors. There is an if statement that determines whether or not the sector name gets stored in the varray or table. I am not sure how to correctly do this and am not sure whether to use a nested table or varray. I've posted somewhat of a pseudo coded example below
If (you_belong_in_table) then variable := store_me_in_varray /* OR */ variable := array_type(sector.sector_name) i := i + 1; end if;
/* Now we output our varray or table */ start loop output(sector names one by one)
end loop I hope this makes sense. I more so just need the syntax to be able to continually added values to a table or varray while I'm already inside a loop; and also how to output those values end the end as well.
My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.
I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.
SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20), 2 v_ASSETTYPE varchar2(20)); 3 / Type created
--- >> create the package
SQL> create or replace package test_arr is 2 type nest_tab is table of obj1; 3 procedure insert_data(t_nest IN nest_tab); 4 end test_arr; 5 /
Package created
SQL> create or replace package body test_arr is 2 procedure insert_data(t_nest IN nest_tab) is 3 begin 4 for i in t_nest.first..t_nest.last loop 5 insert into asset(ASSETID, 6 ASSETTYPE) values (t_nest(i).v_ASSETID,
[code]....
-- >> a block to execute the above package:
SQL> declare 2 type ref_tab is table of obj1; 3 ref_tab1 ref_tab; 4 begin 5 ref_tab1 := ref_tab(1,'a'); 6 test_arr.insert_data(ref_tab1); 7 end; 8 /
But I am getting the below error when executing the package:
ORA-06550: line 5, column 13: PLS-00306: wrong number or types of arguments in call to 'REF_TAB' ORA-06550: line 5, column 13: PLS-00306: wrong number or types of arguments in call to 'REF_TAB' ORA-06550: line 5, column 1:
I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :
I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......
I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........
The code is given below:
CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES (sTraineeNo IN NUMBER, nReturned_O OUT NUMBER) IS ln_insert_cnt PLS_INTEGER :=0; nSequence NUMBER:=0; [code]......
I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.The links are retrieved via a cursor.
However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'..how to handle db_links using a cursor in a pl/sql block? The code is as follows:
DECLARE db_link_rec VARCHAR2(30); CURSOR db_link_cur IS SELECT DB_LINK from MESSAGING_PROD_LIST; BEGIN OPEN db_link_cur; LOOP FETCH db_link_cur INTO db_link_rec; EXIT when db_link_cur%NOTFOUND; [code]....
In first run, 16,36,897 were inserted successfully in around 38 seconds.But in second run, 54,62,952 records had to be inserted, but process failed after 708 seconds with following error :
Error report: ORA-04030: out of process memory when trying to allocate 980248 bytes (PLS non-lib hp,DARWIN) ORA-06512: at line 21 04030. 00000 - "out of process memory when trying to allocate %s bytes (%s,%s)" *Cause: Operating system process private memory has been exhausted *Action:
Here is my code snippet : ....... FORALL i in products_tab.first .. products_tab.last INSERT INTO tab1 VALUES products_tab(i); COMMIT; .........
I think that there should not have been any problem in getting it completed successfully.
Getting Error while uploading payment using API ap_checks_pkg.Insert_Row:
ORA-20001: APP-SQLAP-10000: ORA-01403: no data found occurred in AP_AC_TABLE_HANDLER_PKG.INSERT_ROW<-AP_CHECKS_PKG.INSERT_ROW<- with parameters (ROWID = , CHECK_ID = 39368) while performing the follow
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?
im new to Database i have to count no of messages based on the minuties like 1-5, 6-15,16-30,>30 min how to write query
select count(*) from table1 where ( select ( (extract(day from column2)-extract(day from column1))*24*60 + (extract(hour from column2)-extract(hour from column1))*60 + extract(minute from column2)-extract(minute from column1)) as Processed_time from table1 ) IN (1,5)
I am using oracle 10g to create a user register application. what i want is to insert the user information (like Email ,username, password, etc.) into a table. i am able to insert the data into the table but what i want is to check before inserting that same email,and username doesnt exist. if it does it should return some error like the email or username already exist.
SQL> declare 2 TYPE id_collection is TABLE of number(6); 3 TYPE ename_collection is TABLE of varchar2(20); 4 id ID_COLLECTION; 5 ename ENAME_COLLECTION; 6 cursor c is select empid,name from Nemp; 7 begin 8 open c; 9 loop
[code]....
Here sub_Nemp is my new table in which i have to insert the values from Nemp old table.Both tables are same like below:-
