Oracle how to write this clause?

2012-02-25  Source: Original Site  Category:Oracle  Views:1 


create type courselist as table of varchar2(20);

create table department (
name varchar2(20),
director varchar2(20),
courses courselist)
nested table courses store as courses_tab;

insert into department values('english','david',courselist('hello','bad'));

now ,i want to update the element 'english' as 'chinese',and delete the 'david' to write it ?

Related articles
  • Oracle how to write this clause? 2012-02-25

    create type courselist as table of varchar2(20); create table department ( name varchar2(20), director varchar2(20), courses courselist) nested table courses store as courses_tab; begin insert into department values('english','david',courselist('hell

  • Oracle How to write this in SQL? 2013-05-01

    Please see if you can solve this simple not-divide-by-zero problem. Here is the solution expressed as pseudo code: variables a, b, c are all numbers. function X(b) { for every a in table A if a = b c := 1; else c := 1 / (a-b); return smallest c; } He

  • Oracle how to write this sql code ? 2012-02-14

    for example below shows: create table temp ( order_type varchar2(30), order_num number); insert into temp values('apple',000001); insert into temp values('banana',000002); insert into temp values('chair',000003); select * from temp where instr(order_

  • Oracle How to write this script 2013-06-05

    A cursor is declared in a procedure, but the table name for cursor is derived from parameter of procedure. How to declare this cursor; select field1 from $tablename I can make sure no matter what table is, it must have field field1 and is type of num


    I want to search out the record that the field>2005-06-07 01:45:12.95 the field is date type. how to write the sql ,

  • Oracle how to write this query using correlation subquery or non exists clause 2014-05-15

    -- Tables description. --step-1- 4 employees present in EMP table. --step-2- each employee having 1 country_no in EMP_DOCS table.3 employees having same country_no(i.e emp's 1,2,3) --step-3- 1 emp document can have multiple items.In this case each em

  • Oracle How to write this kind of a procedure! 2014-10-02

    Friends, I have a table Com_Users with fields User_ID and User_password. User_ID is a numeric field while User_Password is varchar2. I want to write a database procedure, function, cursor, what ever suitable to verify the correct password. If the pas

  • Oracle How to write this SQL query 2014-06-28

    We have a table, the structure as below: (I put a blank row in the middle to make it clear) Product_ID Retailer Retail_Price 1001 A 1.2 1001 B 1.5 1001 C 1.4 1002 B 2.0 1002 C 2.1 1002 E 1.8 1002 F 2.0 1003 A 1.7 1003 C 1.5 Basically we use the table

  • Oracle how to write this as a procedure 2013-03-29

    my procedure passes an input parameter .......inside the procedure this is the following logic how to properly return the result set . please help me with syntax create or replace procedure preference(in_usr number) as begin select count(1) from vw_u

  • Oracle how to write this for..loop 2014-05-07

    here i mention out my question which is i have a sql to query out record which contains data of every month from sales each day 1 row. Let's say my query got 3 rows which is for 1st, 2nd and 3rd of this month, the rest days of the month are not show.

  • Oracle How To Write This Complex Query 2014-05-28

    i have following tables 1. employees 2. departments 3. vaccination_type 4. vaccination_subtype 5. vaccination_given a vaccination suppose 'FLU' is to be given to an employee or an department(all employees of this department) EXAMPLE: vaccination type

  • Oracle How To Write This Trigger 2014-08-27

    I want to write a Trigger which will does the following function: Whenever any user will Drop Any Table that Trigger will Insert the "UserName", "Date", "Time" and "The Dropped Table Name" into a table. So that by Query that the Table I will come to

  • Oracle how to write this complex sql 2012-11-21

    we have two table ,one is master table as follows: APP_CODE AUD_TYPE AUD_ID AUD_LEVEL OP_TYPE operator EMED35 LOGIN 149 HiGHT INSERT han EMED35 LOGOUT 150 HiGHT INSERT han EMED35 POLL 153 HiGHT INSERT han EMED35 VOTE 154 HiGHT INSERT han the other on

  • Oracle how to write this qry 2013-03-11

    I have three tables: tab_id, tab_deal, tab_prod. Structure of the tables is as follows: tab_id ====== id tab_deal ========= deal_no val attrib_id tab_prod ======== prod_id prod_name prod_price prod_desc link between tab_id and tab_deal ==============

  • Oracle How to Write this Query 2013-06-01

    I have a table like this: PK serial_no 101 1 109 2 113 3 122 4 ... The PK is the primary key. I've a serial_no which is supposed to be containing values that are differed by exactly 1 and (ii) following the ordering of the PK. My task is to verify th

  • Oracle How to write this query.. Please Help 2014-12-16

    I have a table that contains all possible leave types, it is a look up table. Table name is Att_Leave_Types and data in the table is: Leavetype_ID Leavetype_Desc 1 Annaul 2 Sick 3 Casual 4 Long 5 Maternity Now, another table Att_Emp_Leaves Contains l

  • Oracle How to write this SQL? 2015-05-04

    Table: dept, emp Want to get the results as below: deptno, dname, employees 10, accounting, clark;king;miller 20, research, smith;adams;ford;scott;jones 30, sales, allen;blake;martin;james;turners in other words, return one record with all concatenat

  • Oracle how to write this procedure 2012-01-20

    i want to create a procedure,which can create view when it works.i do it as follow,but it shows 'compile errors',what's the matter,could someone help me? create or replace procedure p_mc_view (n in varchar) is begin n:=edit1.text; create view view_1

  • Oracle How to write this query in a better way - 2014-04-06

    Select organization_id,organization_name,organization_code from org_organization_definitions ood where organization_id in ( select pose.organization_id_child from per_organization_structures pos ,per_org_structure_elements pose ,per_org_structure_ver

  • how to write this select statement in sql? 2012-06-02

    I have 3 tables tbl1, tbl2, tbl3 Tbl1 contains column: fund, fund name, group (fund is PK) Tbl2 contains column: fund, deal, deal name (fund and deal is PK, fund is FK of tbl1) Tbl3 contains column: id, fund, deal, price, profit. (id is PK, but fund