Home Ask Login Register

Developers Planet

Your answer is one click away!

rls1982 February 2016

Counting the number of documents that a username can view

I have a table created called UserPermissions.

CREATE TABLE UserPermissions
(
UserName varchar(255) NOT NULL PRIMARY KEY,
Document varchar(255) NOT NULL,
)

INSERT INTO UserPermissions (Document, UserName)
VALUES ('Policy','SYSTEM');

INSERT INTO UserPermissions (Document)
VALUES ('Menu','JDOW');

INSERT INTO UserPermissions (Document)
VALUES ('W2','USAM');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('Permissions','SYSTEM');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('W2','JDOW');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('Form 1040','USAM');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('Policy','JDOW');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('W2','SYSTEM');

I then had to write a PL/SQL stored function that takes username as input and returns number of documents that user has permissions to view. If username is not in the table, your procedure should return, “User not found”. This is what I have so far and it does compile.

 CREATE OR REPLACE
  FUNCTION user_documents_func(UserName VARCHAR2)
  RETURN int 
  AS
  num_views int;
  BEGIN
  SELECT count(*) INTO num_views from UserPermissions WHERE UserName LIKE                             

  'SYSTEM' || 'JDOW' || 'USAM';  
  IF num_views > 0 THEN
  RETURN UserName || ' can view ' || num_views || ' document(s).';
  ELSE
  dbms_output.put_line('Username not found!');
  END IF;
  END;
  /
  SET SERVEROUTPUT ON;

I then ran the following select statement

 SELECT user_documents_func('SYSTEM') FROM dual
 UNION
 SELECT user_documents_func('JDOW') FROM dual
 UNION
 SELECT user_documents_func('USAM') FROM dual;

and I received the following error:

ORA-06503: PL/SQL: Function returned without value ORA-06512: at "SYSTEM.USER_DOCUMENTS_FUNC", line 12 06503. 00000 - "PL/SQL: Function returned without value" *Cause:

Answers


patelb February 2016

Gordon is correct. You need to rewrite your function. The below should work fine. It will produce the number of documents that can be read and also output a message. I have also fixed the syntax for you DDL/DML.

create table:

CREATE TABLE UserPermissions(
    UserName varchar2(255) NOT NULL,
    Document varchar2(255) NOT NULL,
CONSTRAINT pk_UserPermissions PRIMARY KEY (UserName, Document));

Insert records:

INSERT INTO UserPermissions (Document, UserName)
VALUES ('Policy','SYSTEM');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('Menu','JDOW');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('W2','USAM');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('Permissions','SYSTEM');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('W2','JDOW');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('Form 1040','USAM');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('Policy','JDOW');

INSERT INTO UserPermissions (Document, UserName)
VALUES ('W2','SYSTEM');

Create function:

CREATE OR REPLACE
  FUNCTION user_documents_func(uname VARCHAR2)
  RETURN int 
  AS
  num_views int;
BEGIN
  SELECT count(*) INTO num_views from UserPermissions WHERE UserName = uname;

IF num_views > 0 THEN
    dbms_output.put_line(uname || ' can view ' || num_views || ' document(s).');
ELSE
      dbms_output.put_line(uname || ' not found!');
END IF;

RETURN num_views;

END;

Run the following sql statement:

SELECT 'SYSTEM',  user_documents_func('SYSTEM') as CNT FROM dual
UNION 
SELECT 'JDOW', user_documents_func('JDOW') FROM dual
UNION 
SELECT 'USAM', user_documents_func('USAM') FROM dual
UNION
SELECT 'RANDOMUSER', user_documents_func('RANDOMUSER') FROM dual;

OUTPUT:

Results:

UserName   | Count
+++++++++++|+++++++
JDOW       |  3 
RANDOMUSER |  0 
SYSTEM     |  3 
USAM       |  2 

Post Status

Asked in February 2016
Viewed 3,021 times
Voted 12
Answered 1 times

Search




Leave an answer


Quote of the day: live life