CREATE TABLE table_partition(sales number,year date,item char(4)) partition by range(year) ( PARTITION p1 VALUES LESS THAN 1980, PARTITION p2 VALUES LESS THAN 1982, PARTITION p3 VALUES LESS THAN 1985 );
The above code will create table with partition.
That is the table is not existed before we are creating table with partition.
But my requirement is the table is already existed with 100000 rows.Now I want to range partition that table.
I am bit confused to display all the rows in a column using stored procedure like
In sqlserver: create procedure test as begin select * from table; end in sqlserver it is working perfectly.
In oracle: create or replace procedure test is begin select * from table; end;
But in oracle i am getting an error like select * into something like that.I understand the error. i.e. i need to move the column values into variables.if it a single row then there will be no problem.i want to display all the rows in the table at one time.i can not use cursors.
Error report: ORA-06550: line 3, column 19: PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope ORA-06550: line 3, column 19: PL/SQL: Item ignored ORA-06550: line 4, column 18:
i want to display all the columns of emp table in rows.O/P should be like below:---------------------------------------------Columns------------empnoenamesaldeptnomgr
create table sample1 ( i number , j date, k number)
insert into sample1 values (1,'23-Apr-2010',11) insert into sample1 values (2,'22-Apr-2010',12) insert into sample1 values (3,'21-Apr-2010',13) insert into sample1 values (4,'19-Apr-2010',14) insert into sample1 values (5,'18-Apr-2010',15) insert into sample1 values (6,'17-Apr-2010',16)
I would like to get nulls , if there is no data for a date. As we can see , here i am missing the data for '20-Apr-2010'.
I did it through "UNIX" , but it's not efficient.
The data might be missing for the complete week also. I need to test in this way only for the last 7 days. I tried something like this:
select i, j , sum(k) from sample1 where j in (select to_date(sysdate - rownum) from dual connect by rownum < = 7) group by i, j
TABLE_A ------------------------------ ID DEPT CRS ------------------------------ 1 CS CS_100 2 SCIENCE SCI_150 3 MATH MATH_400 4 HISTORY HIS_110
[Code]...
To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,
-------------------------------------------- NO DEPT CRS -------------------------------------------- 1 MATH MATH_400, MATH_550, MATH_230 --------------------------------------------
instead of., -------------------------- NO DEPT CRS --------------------------- 1 MATH MATH_400 2 MATH MATH_550 3 MATH MATH_230 ---------------------------
The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.
I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 ( dt DATE ) IS v_sql_error VARCHAR2 (100); -- added by sanjiv v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
select * from test where col2=123 will give me null because It doesnt have any values, But can we display some harcoded value when I do not have anything ?
how to display all the records in a table ,i am passing the table name as in param to the procedure/function suppose if i pass emp table name it will display 14 rec, if i pass dept it will display 4 records.
As far as SQL is concerned, I am using oracle 9i and oracle sql *plus.
1. This is a table 'spj' SID PID JID QTY ---------- ---------- ---------- ---------- 1 3 2 5 1 2 2 12 1 7 1 10 2 5 3 5
[code]...
I wish to count all the qty for each pid.This is what I have: SQL> select pid,sum(qty) from spj group by pid; PID SUM(QTY) ---------- ---------- 1 5 2 12 3 29 4 10 5 5 6 6 7 10
and it seems to work fine. But I now I want to display another column in the table called 'pname' which is a primary key in another table 'p'. 'spj.pid' is the foreign key.
2. Another query I can't perform needs the table s SID SNAME STATUS CITY ---------- -------------------- -------------------- ------------------- 1 s1 y asansol 2 s2 y durgapur 3 s3 y durgapur 4 s4 y asansol 5 s5 y kolkata 6 s6 y asansol 7 s7 y tarakeshwar
the question says:
Quote: List supplier names(snames) who supply at least all parts supplied by supplier(sname) S2.
i want to display all the records of my database in a table with scrollingexample if i have 3records in my DB so a table must have 3records too !!!this is my code :
DECLARE Cursor cur IS SELECT numb_ph AS phone, name_emp AS name
[code]...
but the problem 's all records of my DB are display in one record (to view another record i use the scrolling ..
This is my first post in this portal. I want display the details of emp table.. for that I am using this SQL statement.
select * from emp where mgr=nvl(:mgr,mgr);
when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it isdisplaying all the records except the mgr with null values.
1)I want to display all the records when I won't give any input including nulls 2)I want to display all the records who's mgr is null
Is there any way to incorporate to include all these in a single query..
I used Scott schema.I take 2 blocks ,one block having Deptno,Job and also 1st block is a non database block and Another Block Empno,ename,job,sal,deptno, is a database block,my question is when i enter Deptno,Job then Display Employees Details and also Department name
I would like to have a table in HTML region and to display in the table some Page Items from the page.
I got the code like this: { <table> <tr><td>&P1_ECEMEA_ASSISTANCE</td></tr> <tr><td>&P1_ECEMEA_WIP</td></tr> </table> }
It shows the Page Item names as text, not the values. However this code without the table tags:
{ ECEMEA Request for CQT Assistance: &P1_ECEMEA_ASSISTANCE. ECEMEA CQT Work in Progress: &P1_ECEMEA_WIP. }
works and the Page Item values are displayed.
Is it possible to display the Page Item values in the HTML table at all or I have to use a different method of showing the values in a table? The above is only a snippet of the code. I need to display about 30 Page Items, formatted in a table with headings.
what kind of region is the best to be used or if I should only use some escape symbols in the table code.