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.

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Procedure To Display All The Rows From A Table?

Mar 25, 2013

how to create a procedure to display all the rows in a table.

I wrote the below PL/SQL but im hitting as below.

DECLARE
howmany NUMBER;
some_full_name login_user.full_name%TYPE;
some_login_id login_user.login_id%TYPE;
some_users login_user%ROWTYPE;

[code]....

error :

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:

[code]....

View 19 Replies View Related

SQL & PL/SQL :: Display Rows Existed In T_SOURCE Table?

Jul 2, 2010

How can we display the rows which are existed in T_SOURCE table and which doesn't existed in T_TARGET table using outer join.

My code is as follows.

CREATE TABLE T_SOURCE(sno NUMBER,sname VARCHAR2(20));
CREATE TABLE T_TARGET(sno NUMBER,sname VARCHAR2(20));
INSERT INTO T_SOURCE VALUES(1,'A');
INSERT INTO T_SOURCE VALUES(2,'B');
INSERT INTO T_SOURCE VALUES(3,'C');
INSERT INTO T_SOURCE VALUES(4,'D');
INSERT INTO T_SOURCE VALUES(4,'E');
INSERT INTO T_TARGET VALUES(1,'A');
INSERT INTO T_TARGET VALUES(2,'B');

I tried this but I am getteing all the rows.

select t_source.sno,t_source.sname
FROM t_target,t_source
WHERE t_target.sno(+)=t_source.sno
AND
t_target.sname(+)=t_source.sname

View 2 Replies View Related

Display Different Number Of Rows?

Jan 23, 2009

i have a table displaying location, cost, inner_num and rownum ordered by the descending order of the cost.

the question is: display locations of the highest 10 costs. if the cost of 11th row is the same as 11th, then display 11 rows.

View 3 Replies View Related

PL/SQL :: Display All Columns In Different Rows?

Oct 9, 2013

i want to display all the columns of emp table in rows.O/P should be like below:---------------------------------------------Columns------------empnoenamesaldeptnomgr

View 7 Replies View Related

SQL & PL/SQL :: To Display Null Rows When No Match

Apr 23, 2010

I have table as follows:

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

View 3 Replies View Related

SQL & PL/SQL :: Display Values In A Column Instead Of Rows

Mar 10, 2011

Consider the following (example)table.,

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
---------------------------

View 2 Replies View Related

SQL & PL/SQL :: Query On Single Column To Display As Rows

Aug 5, 2010

I has a table of structure of varchar2 datatype.

NO
----------
1-2
3-4
5-6

desired output is:

SQL>1
2
3
4
5
6

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.

View 10 Replies View Related

SQL & PL/SQL :: How To Display The Status And Record Count In Separate Rows

Mar 6, 2013

My problem is i need to display the status and the record count in separate rows.How can i do it

Example

SELECT 'A' STAT, COUNT(A) CNT,'B' STAT, COUNT(B) CNT,'C' STAT, COUNT(C) CNT
FROM TAB1;

OUTPUT SHUD BE LIKE

STAT COUNT
A A_cnt
B B_CNT
C C_CNT

View 13 Replies View Related

How To Display The Second MAX Value In A Table

Jul 31, 2007

a sample query to display second MAX value from the oracle database

View 1 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

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

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins

Sep 24, 2010

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.

View 11 Replies View Related

SQL & PL/SQL :: Display Some Value When There Is No Record In Table

Jul 8, 2010

I have a following table,

create table test ( col1 date,col2 number);

Data:

col1 Col2
----------
NULL NULL

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 ?

View 9 Replies View Related

PL/SQL :: How To Display All Records In A Table

Oct 3, 2013

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.

View 3 Replies View Related

Oracle - Display Another Column In Table?

Mar 28, 2010

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.

How may I achieve it.

View 5 Replies View Related

SQL & PL/SQL :: How To Display The Duplicate Record In Table

Jun 30, 2011

how to display the dupicate record in table

View 1 Replies View Related

Forms :: Display All Records Of DB In A Table?

Dec 29, 2012

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 ..

View 31 Replies View Related

SQL & PL/SQL :: Display Data From Mst_user Table

Aug 9, 2011

in the below query

select
b.user_code
, b.user_name
, b.user_disabled
, b.user_email
, b.user_created_by
, b.user_created_date
, b.user_approved_by
, b.user_approved_date
, b.flag
, b.user_status
from mst_user_checker b, mst_user a
where a.user_code=b.user_code
and b.flag in ('I','P','R','F')

If the flag is 'F', then I need to display the data from the mst_user table. Where should i modify the above query ?

View 2 Replies View Related

PL/SQL :: How To Display Required Data From Emp Table

Dec 26, 2012

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..

View 9 Replies View Related

Client Tools :: Vertical Display Of Table

Jun 29, 2010

I would like to format my output for the select query.

Example:

SQL>select empno, ename from emp;

empnoename
1987766Jack Blake
187765Greg Ni

But I would like to get the output in the below format

EMPNO:.................1987766
ENAME:.................Jack Blake

EMPNO:.................187765
ENAME:.................Greg Ni

I will need printing the column name. I would like to know how do we address the column name to print.

View 24 Replies View Related

Delete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?

Dec 14, 2007

I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

Forms :: How To Display Table Column Heading On Oracle 6i

Jun 8, 2013

How to display table columns on oracle form?

View 7 Replies View Related

Forms :: Display Department Name In Emp Table Along With Employees Details?

Mar 28, 2013

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

View 1 Replies View Related

SQL & PL/SQL :: Display Total Number Of Employee Working Under President In Emp Table

Apr 27, 2012

display the total number of employee working under president in emp table

View 5 Replies View Related

Application Express :: Display Page Item Values In HTML Table

Sep 7, 2012

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.

View 2 Replies View Related

SQL & PL/SQL :: Rows From Each Table In DB

May 5, 2011

Scenario like this , I have a dB which contain 100 tables, i want to find out each table contain how many rows excluding

system tables ..

how can i write query for this.

View 2 Replies View Related

Purging Table Which Is Having 98 M Rows

Dec 3, 2012

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]....

View 4 Replies View Related

SQL & PL/SQL :: How To Delete Rows From Table

Oct 19, 2011

I am using the below proc to delete some records

1)select client_id,count(*) from TCLIENT_NOTIFICATION_PACK where client_id=1620560178 group by client_id order by 2 desc;

client_id count(*)
----------- ---------
16205601785128

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.

View 23 Replies View Related

SQL & PL/SQL :: Rows Of A Table Updated

Jul 17, 2012

write a query find which rows of a table is updated on 2 days before?

(OR)

In table there r so many rows write a query which two rows r updated last two day before?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved