ORacle SQL PRocedure

Hi,All!

i need help in defining a procedure in oracle sql , with a recorrent query that sometimes i have tu run in order to provide information to a client.


CREATE OR REPLACE PROCEDURE _USERSLIST_
AS
BEGIN
select distinct a.user_name,
                a.externalcode,
                a.name,
                a.profile,
                a.last_logon,
                a.status,
                a.user_name_alt,
                a.authentication_entity,
                b.code,
                b.value
  from sys_user a, user_default b
 where a.user_name = b.user_name
   and (a.status = '1' or a.status is null)
   and a.user_name in
       (select user_name from user_default where code = 'PLANTS')
   and value not in ('XXXX', '001', '002', '003', 'PLANT', 'X')
   and code not in
       ('C_PLANT', 'D_SC_LIST', 'P_TYPE', 'D_PRINTER','D_USER_REP_LIST');
END;

When i try to compile , it gives me an Error:

Compilation errors for PROCEDURE _USERSLIST_
Error: PLS-00428: an INTO clause is expected in this SELECT statement
Line: 4
Text: select distinct a.user_name,

HELP!

Thaks in advance

Andrew
LVL 2
costafAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Since there is no work being done in the procedure and you want it to just display results, I would think a view would be a better answer than a procedure.

create or replace view PROCEDURE _USERSLIST_
AS
select distinct a.user_name,
                a.externalcode,
                a.name,
                a.profile,
                a.last_logon,
                a.status,
                a.user_name_alt,
                a.authentication_entity,
                b.code,
                b.value
  from sys_user a, user_default b
 where a.user_name = b.user_name
   and (a.status = '1' or a.status is null)
   and a.user_name in
       (select user_name from user_default where code = 'PLANTS')
   and value not in ('XXXX', '001', '002', '003', 'PLANT', 'X')
   and code not in
       ('C_PLANT', 'D_SC_LIST', 'P_TYPE', 'D_PRINTER','D_USER_REP_LIST');
0
 
slightwv (䄆 Netminder) Commented:
In Oracle a select inside a procedure you need to do something with the results.

What are you wanting to return?  You can retunr individual values if the select retrieves one row or an entire cursor if you want a result set returned.

Once you specify what you want returned, we can provide the example.
0
 
costafAuthor Commented:
i want a result set that contains the following fields of 2 database tables:
SELECT ...  a.user_name,
                a.externalcode,
                a.name,
                a.profile,
                a.last_logon,
                a.status,
                a.user_name_alt,
                a.authentication_entity,
                b.code,
                b.value
...

which obey to the conditions in the select statement above.

...where a.user_name = b.user_name
   and (a.status = '1' or a.status is null)...
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
slightwv (䄆 Netminder) Commented:
This doesn't answer what you want done with the results of the select.

Does this select return a single row of multiple rows?
What do you want to pass back to the program that calls it?
0
 
costafAuthor Commented:
Does this select return a single row of multiple rows?  - multiple rows.
What do you want to pass back to the program that calls it ? - i dont know. i was expecting to just define the procedure , and  then "exec the proc" whenever i needed to display the results of the query in pl/sql developer.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>and  then "exec the proc" whenever i needed to display the results of the query in pl/sql developer.

Unfortunately it doesn't work this way.

Try the code below.

CREATE OR REPLACE PROCEDURE _USERSLIST(myoutput out sys_refcursor)
AS
BEGIN
open myoutput for
select distinct a.user_name,
                a.externalcode,
                a.name,
                a.profile,
                a.last_logon,
                a.status,
                a.user_name_alt,
                a.authentication_entity,
                b.code,
                b.value
  from sys_user a, user_default b
 where a.user_name = b.user_name
   and (a.status = '1' or a.status is null)
   and a.user_name in
       (select user_name from user_default where code = 'PLANTS')
   and value not in ('XXXX', '001', '002', '003', 'PLANT', 'X')
   and code not in
       ('C_PLANT', 'D_SC_LIST', 'P_TYPE', 'D_PRINTER','D_USER_REP_LIST');
END;

Open in new window

0
 
costafAuthor Commented:
THX, ALL!
0
 
slightwv (䄆 Netminder) Commented:
Can I ask why the "B" penalty grade?  What additional information would you need for the "A"?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.