SQL & PL/SQL :: How To Display All The Rows In A Table
Sep 8, 2010
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.
I want to purge a table which is having more then 98M rows...here are the details...
Purge Process I followed --------------------------------------------- Step 1. Created backup table from Main table with required data only create table abc_98M_purge as select * from abc_98M where trunc(tran_date)>='01-jul-2011' -> table created with 5325411 rows
Step 2. truncate table abc_98M
Step 3. inserted all 5325411 rows back to abc_98M from abc_98M_purge using below procedure
DECLARE TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER; tbrows ARRROWID; row PLS_INTEGER; cursor cur_insert is select rowid from abc_98M_purge order by rowid; BEGIN open cur_insert; loop [code]....
2)select client_id, count(*) from TCLIENT_NOTIFICATION_PACK where client_id=1620560178 group by client_id having count(*) > 40 order by 2 desc client_id count(*) ----------- --------- 16205601785128
3) select client_id,clnt_notification_pack_tid -- bulk collect into v_client_id,v_notif_tid from (select clnt_notification_pack_tid, client_id, clnt_notification_pack_typ_tid, crte_dt,
[code]....
4) Iam using the below proc to delete the rows from table, except the 4 rows returned above
declare v_clnt_notification_pack_tid TCLIENT_NOTIFICATION_PACK.CLNT_NOTIFICATION_PACK_TID%type; tYPE t_client_id is table of TCLIENT_NOTIFICATION_PACK.client_id%type; tYPE t_notif_tid is table of TCLIENT_NOTIFICATION_PACK.clnt_notification_pack_tid%type; v_client_id t_client_id; v_notif_tid t_notif_tid;
[code]....
5) After running this procedure, i shud see 5124 records, but i see zero records.
