• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

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
0
costaf
Asked:
costaf
  • 4
  • 3
2 Solutions
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now