Home » SQL & PL/SQL » SQL & PL/SQL » procedure to display all the rows from a table (11g)
procedure to display all the rows from a table [message #580519] Mon, 25 March 2013 11:47 Go to next message
vinoth51115
Messages: 9
Registered: March 2013
Junior Member
Hi , can please treach me 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;
TYPE first_typ IS TABLE OF login_user.full_name%TYPE INDEX BY PLS_INTEGER;
TYPE last_typ IS TABLE OF login_user.login_id%TYPE INDEX BY PLS_INTEGER;
full_names first_typ;
login_ids last_typ;
CURSOR c1 IS SELECT full_name,login_id FROM login_user;
TYPE name_typ IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;
all_names name_typ;
TYPE name_typ_1 IS TABLE OF login_user%ROWTYPE INDEX BY PLS_INTEGER;
all_names_1 name_typ_1;
BEGIN
-- Query a single value and store it in a variable.
SELECT COUNT(*) INTO howmany FROM login_user;
dbms_output.put_line('This schema owns ' || howmany || ' tables.');
-- Query multiple columns from one row, and store them in variables.
SELECT full_name,login_id INTO some_full_name,some_login_id FROM login_user WHERE ROWNUM < 2;
dbms_output.put_line('Random users: ' || some_full_name ||
' ' || some_login_id);
-- Query a single row and store it in a record.
SELECT * INTO some_users FROM login_user WHERE ROWNUM < 2;

-- Query multiple columns from multiple rows, and store them in a collection
-- of records.
SELECT full_name,login_id BULK COLLECT INTO all_names FROM login_user;

-- Query multiple columns from multiple rows, and store them in separate
-- collections. (Generally less useful than a single collection of records.)
SELECT full_name,login_id
BULK COLLECT INTO first_names, login_ids
FROM login_user;

-- Query an entire (small!) table and store the rows
-- in a collection of records. Now you can manipulate the data
-- in-memory without any more I/O.
SELECT * BULK COLLECT INTO all_names_1 FROM login_user;
END;
/

------------------


my table structure:

Name Null Type
---------------- -------- ------------
ID NOT NULL NUMBER(3)
LOGIN_ID VARCHAR2(10)
FULL_NAME VARCHAR2(10)
LAST_UPDATE_BY VARCHAR2(10)
LAST_UPDATE_DATE DATE
DEPT_ID NUMBER(3)
ROW_NUMBER NUMBER

-------------------------------


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:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 4, column 18:
PL/SQL: Item ignored
ORA-06550: line 5, column 15:
PLS-00310: with %ROWTYPE attribute, 'LOGIN_USER' must name a table, cursor or cursor-variable
ORA-06550: line 5, column 15:
PL/SQL: Item ignored
ORA-06550: line 6, column 31:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 6, column 4:
PL/SQL: Item ignored
ORA-06550: line 7, column 30:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 7, column 4:
PL/SQL: Item ignored
ORA-06550: line 13, column 32:
PLS-00310: with %ROWTYPE attribute, 'LOGIN_USER' must name a table, cursor or cursor-variable
ORA-06550: line 13, column 4:
PL/SQL: Item ignored
ORA-06550: line 20, column 35:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 20, column 64:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 20, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 21, column 45:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 21, column 4:
PL/SQL: Statement ignored
ORA-06550: line 24, column 18:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 24, column 29:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 24, column 4:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Re: procedure to display all the rows from a table [message #580520 is a reply to message #580519] Mon, 25 March 2013 11:52 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
select * from login_user;

do NOT do in PL/SQL what can be done in plain SQL.

[Updated on: Mon, 25 March 2013 11:53]

Report message to a moderator

Re: procedure to display all the rows from a table [message #580521 is a reply to message #580520] Mon, 25 March 2013 11:54 Go to previous messageGo to next message
vinoth51115
Messages: 9
Registered: March 2013
Junior Member
ID LOGIN_ID FULL_NAME LAST_UPDATE_BY LAST_UPDATE_DATE DEPT_ID ROW_NUMBER
---------- ---------- ---------- -------------- ---------------- ---------- ----------
205 A5050 VINOTH59 SYSTEM 18/03/13 13 1
204 A999938 VINOTH58 SYSTEM 18/03/13 11 2
206 A5050 VINOTH59 SYSTEM 20/03/13 2 3
207 A9993940 Vino1 SYSTEM 20/03/13 6 4
208 A9993941 Vino2 SYSTEM 20/03/13 5 5
209 A9993942 Vino3 SYSTEM 20/03/13 4 6
210 A9993943 VINOTH51 SYSTEM 20/03/13 3 7
10 A9993944 VINOTH70 SYSTEM 20/03/13 1 8

8 rows selected
Re: procedure to display all the rows from a table [message #580522 is a reply to message #580521] Mon, 25 March 2013 11:56 Go to previous messageGo to next message
vinoth51115
Messages: 9
Registered: March 2013
Junior Member
Please also teach me to do this is as a procedure,thanks....
Re: procedure to display all the rows from a table [message #580523 is a reply to message #580522] Mon, 25 March 2013 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.

Regards
Michel
Re: procedure to display all the rows from a table [message #580524 is a reply to message #580523] Mon, 25 March 2013 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
>TYPE first_typ IS TABLE OF login_user.full_name%TYPE INDEX BY PLS_INTEGER;
>TYPE last_typ IS TABLE OF login_user.login_id%TYPE INDEX BY PLS_INTEGER;
>CURSOR c1 IS SELECT full_name,login_id FROM login_user;
>TYPE name_typ IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;

why do you establish the four objects above & then never use them?
Re: procedure to display all the rows from a table [message #580525 is a reply to message #580524] Mon, 25 March 2013 12:25 Go to previous messageGo to next message
vinoth51115
Messages: 9
Registered: March 2013
Junior Member
thanks , i have modified to make use of those But still getting some error.
Im not sure on how to user cursor here can please assist?

SQL> DECLARE
2 howmany NUMBER;
3 some_full_name login_user.full_name%TYPE;
4 some_login_id login_user.login_id%TYPE;
5 some_users login_user%ROWTYPE;
6 TYPE first_typ IS TABLE OF login_user.full_name%TYPE INDEX BY PLS_INTE
GER;
7 TYPE last_typ IS TABLE OF login_user.login_id%TYPE INDEX BY PLS_INTEGE
R;
8 full_names first_typ;
9 login_ids last_typ;
10 CURSOR c1 IS SELECT full_name,login_id FROM login_user;
11 TYPE name_typ IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;
12 all_names name_typ;
13 TYPE name_typ_1 IS TABLE OF login_user%ROWTYPE INDEX BY PLS_INTEGER;
14 all_names_1 name_typ_1;
15 BEGIN
16 -- Query a single value and store it in a variable.
17 SELECT COUNT(*) INTO howmany FROM login_user;
18 dbms_output.put_line('This schema owns ' || howmany || ' tables.');
19
20 select full_name into full_names from login_user;
21 dbms_output.put_line('Full names ' || howmany);
22
23 select login_id into login_ids from login_user;
24 dbms_output.put_line('Login Ids' || howmany);
25 -- Query multiple columns from one row, and store them in variables.
26 SELECT full_name,login_id INTO some_full_name,some_login_id FROM login
_user WHERE ROWNUM < 2;
27 dbms_output.put_line('Random users: ' || some_full_name ||
28 ' ' || some_login_id);
29 -- Query a single row and store it in a record.
30 SELECT * INTO some_users FROM login_user WHERE ROWNUM < 2;
31
32 -- Query multiple columns from multiple rows, and store them in a collect
ion
33 -- of records.
34 SELECT full_name,login_id BULK COLLECT INTO all_names FROM login_user;

35
36 -- Query multiple columns from multiple rows, and store them in separate
37 -- collections. (Generally less useful than a single collection of record
s.)
38 SELECT full_name,login_id
39 BULK COLLECT INTO first_names, login_ids
40 FROM login_user;
41
42 -- Query an entire (small!) table and store the rows
43 -- in a collection of records. Now you can manipulate the data
44 -- in-memory without any more I/O.
45 SELECT * BULK COLLECT INTO all_names_1 FROM login_user;
46 END;
47 /
some_full_name login_user.full_name%TYPE;
*
ERROR at line 3:
ORA-06550: line 3, column 21:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 3, column 21:
PL/SQL: Item ignored
ORA-06550: line 4, column 20:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 4, column 20:
PL/SQL: Item ignored
ORA-06550: line 5, column 17:
PLS-00310: with %ROWTYPE attribute, 'LOGIN_USER' must name a table, cursor or
cursor-variable
ORA-06550: line 5, column 17:
PL/SQL: Item ignored
ORA-06550: line 6, column 33:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 6, column 6:
PL/SQL: Item ignored
ORA-06550: line 7, column 32:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 7, column 6:
PL/SQL: Item ignored
ORA-06550: line 13, column 34:
PLS-00310: with %ROWTYPE attribute, 'LOGIN_USER' must name a table, cursor or
cursor-variable
ORA-06550: line 13, column 6:
PL/SQL: Item ignored
ORA-06550: line 20, column 28:
PLS-00597: expression 'FULL_NAMES'


SQL>
Re: procedure to display all the rows from a table [message #580526 is a reply to message #580525] Mon, 25 March 2013 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
Realize that we don't have your tables or data.
Therefore we can't run, test, debug or improve posted code.

It appears that you & Oracle have some sort of misunderstanding involving "LOGIN_USER".
For us outside observers what exactly is LOGIN_USER?
Please post DDL that CREATE this object?
Re: procedure to display all the rows from a table [message #580527 is a reply to message #580526] Mon, 25 March 2013 12:35 Go to previous messageGo to next message
vinoth51115
Messages: 9
Registered: March 2013
Junior Member
Hi I have attached the ddl ,Please assist
Re: procedure to display all the rows from a table [message #580528 is a reply to message #580527] Mon, 25 March 2013 12:36 Go to previous messageGo to next message
vinoth51115
Messages: 9
Registered: March 2013
Junior Member
Hi I have attached the ddl ,Please assist

--------------------------------------------------------
-- File created - Tuesday-March-26-2013
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table LOGIN_USER
--------------------------------------------------------

CREATE TABLE "SYSTEM"."LOGIN_USER"
( "ID" NUMBER(3,0),
"LOGIN_ID" VARCHAR2(10 BYTE),
"FULL_NAME" VARCHAR2(10 BYTE),
"LAST_UPDATE_BY" VARCHAR2(10 BYTE),
"LAST_UPDATE_DATE" DATE,
"DEPT_ID" NUMBER(3,0),
"ROW_NUMBER" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- DDL for Index SYS_C0011101
--------------------------------------------------------

CREATE UNIQUE INDEX "SYSTEM"."SYS_C0011101" ON "SYSTEM"."LOGIN_USER" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- DDL for Index LOGIN_USER_IDX0
--------------------------------------------------------

CREATE INDEX "SYSTEM"."LOGIN_USER_IDX0" ON "SYSTEM"."LOGIN_USER" ("FULL_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table LOGIN_USER
--------------------------------------------------------

ALTER TABLE "SYSTEM"."LOGIN_USER" ADD PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;

ALTER TABLE "SYSTEM"."LOGIN_USER" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Trigger LOGIN_USER_BEFORE_INSERT
--------------------------------------------------------

CREATE OR REPLACE TRIGGER "SYSTEM"."LOGIN_USER_BEFORE_INSERT"
before insert
ON login_user
FOR EACH ROW

DECLARE
v_username varchar2(10);
v_row_id number(3);
BEGIN

-- Find username of person performing INSERT into table
SELECT user INTO v_username
FROM dual;
select max(id) into v_row_id
from login_user;
-- Update create_date field to current system date
:new.last_update_date := sysdate;
:new.id :=v_row_id+1;
-- Update created_by field to the username of the person performing the INSERT
:new.last_update_by := v_username;

DBMS_OUTPUT.put_line('login_user_before_insert_trigger - Executed');

END;
/
ALTER TRIGGER "SYSTEM"."LOGIN_USER_BEFORE_INSERT" ENABLE;
--------------------------------------------------------
-- DDL for Trigger USER_DETAILS_BEFORE_INSERT
--------------------------------------------------------

CREATE OR REPLACE TRIGGER "SYSTEM"."USER_DETAILS_BEFORE_INSERT"
before insert
ON login_user
FOR EACH ROW

DECLARE
v_username varchar2(10);
v_row_id number(3);
BEGIN

-- Find username of person performing INSERT into table
SELECT user INTO v_username
FROM dual;
select login_id into v_row_id
from login_user A inner join user_details B on A.id=B.id;
-- Update create_date field to current system date
:new.last_update_date := sysdate;
:new.id :=v_row_id;
-- Update created_by field to the username of the person performing the INSERT
:new.last_update_by := v_username;

END;
/
ALTER TRIGGER "SYSTEM"."USER_DETAILS_BEFORE_INSERT" DISABLE;
--------------------------------------------------------
-- DDL for Trigger INSERT_UPDATE_OUTPUT
--------------------------------------------------------

CREATE OR REPLACE TRIGGER "SYSTEM"."INSERT_UPDATE_OUTPUT"
AFTER INSERT OR DELETE OR UPDATE ON LOGIN_USER
FOR EACH ROW
BEGIN
IF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('NEW DATA: ' ||:new.FULL_NAME ||', OLD DATA: '||:old.FULL_NAME);
DBMS_OUTPUT.PUT_LINE('ID: ' || :new.ID);
my_global_pkg.g_array(:new.ID) := :new.ID;
DBMS_OUTPUT.PUT_LINE('COUNTER: ' || my_global_pkg.g_array.COUNT);
END IF;
END;
/
ALTER TRIGGER "SYSTEM"."INSERT_UPDATE_OUTPUT" ENABLE;
Re: procedure to display all the rows from a table [message #580530 is a reply to message #580528] Mon, 25 March 2013 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
Both SYS & SYSTEM schemas should NEVER be used for any activity unless explicitly directed by Oracle Corp.!
Re: procedure to display all the rows from a table [message #580533 is a reply to message #580530] Mon, 25 March 2013 12:54 Go to previous messageGo to next message
vinoth51115
Messages: 9
Registered: March 2013
Junior Member
But i dropped the table from system and used scott id to created but still getting errors.

table structure

Name Null? Type
----------------------------------------- -------- --------------------------

ID NUMBER(5)
LOGIN_ID VARCHAR2(10)
FULL_NAME VARCHAR2(10)
LAST_UPDATE_BY VARCHAR2(10)
LAST_UPDATE_DATE DATE
DEPT_ID NUMBER(3)




--------------


SQL> DECLARE
2 howmany NUMBER;
3 some_full_name login_user.full_name%TYPE;
4 some_login_id login_user.login_id%TYPE;
5 some_users login_user%ROWTYPE;
6 TYPE first_typ IS TABLE OF login_user.full_name%TYPE INDEX BY PLS_INT
GER;
7 TYPE last_typ IS TABLE OF login_user.login_id%TYPE INDEX BY PLS_INTEG
R;
8 full_names first_typ;
9 login_ids last_typ;
10 CURSOR c1 IS SELECT full_name,login_id FROM login_user;
11 TYPE name_typ IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;
12 all_names name_typ;
13 TYPE name_typ_1 IS TABLE OF login_user%ROWTYPE INDEX BY PLS_INTEGER;
14 all_names_1 name_typ_1;
15 BEGIN
16 -- Query a single value and store it in a variable.
17 SELECT COUNT(*) INTO howmany FROM login_user;
18 dbms_output.put_line('This schema owns ' || howmany || ' tables.');
19
20 select full_name into full_names from login_user;
21 dbms_output.put_line('Full names ' || howmany);
22
23 select login_id into login_ids from login_user;
24 dbms_output.put_line('Login Ids' || howmany);
25 -- Query multiple columns from one row, and store them in variables.
26 SELECT full_name,login_id INTO some_full_name,some_login_id FROM logi
_user WHERE ROWNUM < 2;
27 dbms_output.put_line('Random users: ' || some_full_name ||
28 ' ' || some_login_id);
29 -- Query a single row and store it in a record.
30 SELECT * INTO some_users FROM login_user WHERE ROWNUM < 2;
31
32 -- Query multiple columns from multiple rows, and store them in a collec
ion
33 -- of records.
34 SELECT full_name,login_id BULK COLLECT INTO all_names FROM login_user

35
36 -- Query multiple columns from multiple rows, and store them in separate
37 -- collections. (Generally less useful than a single collection of recor
s.)
38 SELECT full_name,login_id
39 BULK COLLECT INTO first_names, login_ids
40 FROM login_user;
41
42 -- Query an entire (small!) table and store the rows
43 -- in a collection of records. Now you can manipulate the data
44 -- in-memory without any more I/O.
45 SELECT * BULK COLLECT INTO all_names_1 FROM login_user;
46 END;
47 /
some_full_name login_user.full_name%TYPE;
*
ERROR at line 3:
ORA-06550: line 3, column 21:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 3, column 21:
PL/SQL: Item ignored
ORA-06550: line 4, column 20:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 4, column 20:
PL/SQL: Item ignored
ORA-06550: line 5, column 17:
PLS-00310: with %ROWTYPE attribute, 'LOGIN_USER' must name a table, cursor or
cursor-variable
ORA-06550: line 5, column 17:
PL/SQL: Item ignored
ORA-06550: line 6, column 33:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 6, column 6:
PL/SQL: Item ignored
ORA-06550: line 7, column 32:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 7, column 6:
PL/SQL: Item ignored
ORA-06550: line 13, column 34:
PLS-00310: with %ROWTYPE attribute, 'LOGIN_USER' must name a table, cursor or
cursor-variable
ORA-06550: line 13, column 6:
PL/SQL: Item ignored
ORA-06550: line 20, column 28:
PLS-00597: expression 'FULL_NAMES'

Re: procedure to display all the rows from a table [message #580534 is a reply to message #580533] Mon, 25 March 2013 12:56 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
  1  DECLARE
  2  CURSOR CUR1 IS select * from room;
  3  begin
  4  for rec in CUR1 LOOP
  5  dbms_output.put_line(rec.ROOM#||rec.HOTEL_ID||rec.ROOM_TYPE);
  6  end loop;
  7* end;
SQL> /
21Single

PL/SQL procedure successfully completed.

SQL> select * from room;

     ROOM#   HOTEL_ID ROOM_TYPE
---------- ---------- --------------------------------------------------
         2          1 Single

Re: procedure to display all the rows from a table [message #580537 is a reply to message #580533] Mon, 25 March 2013 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 25 March 2013 18:00
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.


Regards
Michel

Re: procedure to display all the rows from a table [message #580538 is a reply to message #580527] Mon, 25 March 2013 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
vinoth51115 wrote on Mon, 25 March 2013 18:35
Hi I have attached the ddl ,Please assist


Show us you deserve to be helped and post as requested.

Regards
Michel

Re: procedure to display all the rows from a table [message #580539 is a reply to message #580538] Mon, 25 March 2013 13:16 Go to previous messageGo to next message
vinoth51115
Messages: 9
Registered: March 2013
Junior Member
Thanks for the example on the cursor.
Appreciate if you could help to resolve the above mentioned error.

Hi Michel,Noted I will follow the forum rule here after thanks a lot.
Re: procedure to display all the rows from a table [message #580540 is a reply to message #580539] Mon, 25 March 2013 13:24 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
>select full_name into full_names from login_user;
FULL_NAMES is a scalar; which holds only a single value.
Since SELECT returns more than 1 row; an error results.

>Appreciate if you could help to resolve the above mentioned error.

whole approach is overly complex; especially for a newbie!
Re: procedure to display all the rows from a table [message #580652 is a reply to message #580540] Tue, 26 March 2013 09:29 Go to previous messageGo to next message
vinoth51115
Messages: 9
Registered: March 2013
Junior Member

SQL> DECLARE
  2   howmany NUMBER;
  3   some_full_name login_user.full_name%TYPE;
  4   some_login_id login_user.login_id%TYPE;
  5   some_users login_user%ROWTYPE;
  6   TYPE first_typ IS TABLE OF login_user.full_name%TYPE INDEX BY PLS_INTEGER;

  7   TYPE last_typ IS TABLE OF login_user.login_id%TYPE INDEX BY PLS_INTEGER;
  8   full_names first_typ;
  9   login_ids last_typ;
 10   CURSOR c1 IS SELECT full_name,login_id FROM login_user;
 11   TYPE name_typ IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;
 12   all_names name_typ;
 13   TYPE name_typ_1 IS TABLE OF login_user%ROWTYPE INDEX BY PLS_INTEGER;
 14   all_names_1 name_typ_1;
 15   BEGIN
 16   -- Query a single value and store it in a variable.
 17   SELECT COUNT(*) INTO howmany FROM login_user;
 18   dbms_output.put_line('This schema owns ' || howmany || ' tables.');
 19   -- Query multiple columns from one row, and store them in variables.
 20   SELECT full_name,login_id INTO some_full_name,some_login_id FROM login_use
r WHERE ROWNUM < 2;
 21   dbms_output.put_line('Random users: ' || some_full_name ||
 22   ' ' || some_login_id);
 23   -- Query a single row and store it in a record.
 24   SELECT * INTO some_users FROM login_user WHERE ROWNUM < 2;
 25
 26  -- Query multiple columns from multiple rows, and store them in a collectio
n
 27   -- of records.
 28   SELECT full_name,login_id BULK COLLECT INTO all_names FROM login_user;
 29
 30  -- Query multiple columns from multiple rows, and store them in separate
 31   -- collections. (Generally less useful than a single collection of records
.)
 32   SELECT full_name BULK COLLECT INTO first_names
 33   FROM login_user where rownum < 2;
 34   SELECT login_ids BULK COLLECT INTO login_ids from login_user where rownum
<2;
 35
 36  -- Query an entire (small!) table and store the rows
 37   -- in a collection of records. Now you can manipulate the data
 38   -- in-memory without any more I/O.
 39   SELECT * BULK COLLECT INTO all_names_1 FROM login_user;
 40   END;
 41   /
 some_full_name login_user.full_name%TYPE;
                *
ERROR at line 3:
ORA-06550: line 3, column 17:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 3, column 17:
PL/SQL: Item ignored
ORA-06550: line 4, column 16:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 4, column 16:
PL/SQL: Item ignored
ORA-06550: line 5, column 13:
PLS-00310: with %ROWTYPE attribute, 'LOGIN_USER' must name a table, cursor or
cursor-variable
ORA-06550: line 5, column 13:
PL/SQL: Item ignored
ORA-06550: line 6, column 29:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 6, column 2:
PL/SQL: Item ignored
ORA-06550: line 7, column 28:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 7, column 2:
PL/SQL: Item ignored
ORA-06550: line 13, column 30:
PLS-00310: with %ROWTYPE attribute, 'LOGIN_USER' must name a table, cursor or
cursor-variable
ORA-06550: line 13, column 2:
PL/SQL: Item ignored
ORA-06550: line 20, column 33:
PLS-00320: the declaration of the t



I have changed but still the same errors.
but would like to understand why it doesnt accept more than one rows ,can explain me the diffrence between the below 2 please.

>some_full_name login_user.full_name%TYPE;

> TYPE first_typ IS TABLE OF login_user.full_name%TYPE INDEX BY PLS_INTEGER;
Re: procedure to display all the rows from a table [message #580654 is a reply to message #580652] Tue, 26 March 2013 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
http://docs.oracle.com/cd/E11882_01/appdev.112/e10766/toc.htm

You should NOT be trying to program, until after you understand the basics & fundamentals; like difference between scalar & array and scoping of variables.
Re: procedure to display all the rows from a table [message #580656 is a reply to message #580652] Tue, 26 March 2013 11:04 Go to previous message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And If you don't know how to format SQL or PL/SQL, learn it using SQL Formatter.

Regards
Michel
Previous Topic: Extracting the data from web service response
Next Topic: Export to Excel Files
Goto Forum:
  


Current Time: Fri Oct 31 07:19:29 CDT 2014

Total time taken to generate the page: 0.15375 seconds