?
Solved

PL/SQL function problem

Posted on 2003-03-13
10
Medium Priority
?
1,264 Views
Last Modified: 2012-06-21
Hi I want to create a PL/SQL function, but I don't know how to go about it
Basically I want to feed in a number of variables and I want the function to return me a string saying which of the variables are null.

The call to the function will go as follows
output_string := get_null_values ('Value 1',value_1,'Value 2',value_2,...)

This would return 'Value x, Value y, Value z,... are null'

This would be easy to write for a specified number of input parameters, but I want the function to work no matter how many input parameters I use, as long as they are in the pattern above.

Oracle built in functions such as Greatest, Least etc. seem to work for an arbitrary number of parameters, but I have no idea how I would code one myself.

Any advice would be much appreciated.
TIA
0
Comment
Question by:Haircut
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 

Expert Comment

by:rajanvora
ID: 8131532
What u need is a procedure that process the information that u input and send back the result of the information.

CREATE OR REPLACE PROCEDURE process_values
  (
  val_one IN VARCHAR2,
  val_two IN VARCHAR2,
  return_val INOUT VARCHAR2,

  ) IS

BEGIN

  --
  -- This is where you code ur logic and the final result
  -- is set in the return_val argument
  --
END;

To call this procedure u can build ur own PL/SQL code as:

DECLARE
  val_one VARCHAR2(200);
  val_two VARCHAR2(200);
  return_val VARCHAR2(200);

BEGIN
  val_one  := '1';
  val_two  := '2';
  return_val := NULL;

  process_values ( val_one  , val_two  , return_val);  
  dbms_output.put_line(return_val);
COMMIT;
END;
0
 

Expert Comment

by:wizia
ID: 8131739
Writing a function is very easy and many examples are avaialble in any oracle books
I need to know more details of your requirments
what you want pass? or where the values are stored like valuex,value y etc
if these values are from dtabase we can read them by sql select statement

vijay
0
 

Author Comment

by:Haircut
ID: 8131827
OK, I'll try and explain in a little more detail what I want.

I have a couple of packages, each with several procedures.
Each of the procedures has a number of input parameters, some of which cannot be null.

The way this is currently processed is as follows:

Procedure proc1
(val1 in varchar2
,val2 in number
,error out varchar2)

is

null_input   exception;
errordata    varchar2(50);

begin

if val1 is null then
errordata := 'Val 1';
raise null_input;
end if;

if val2 is null then
errordata := 'Val 2';
raise null_input;
end if;

exception

when null_input then
  error := errordata||' is null';
end;

Obviously here I am just showing the code for checking the null inputs.

What I want to do is create a function that I can call with the values ('Val 1',val1,'Val2,val2) and it will return the string I posted above if any of these are null.

Up to this point it is easy, where I have run into trouble is that I want to compile this function in the package and use the same null checking function for procedure proc2, which may have 3 values I have to check for nullity, or proc3 which might have 6 values to check.

I know I could create several overloaded versions of the function, but this still wouldn't take into account anything I could throw at it, only the different input parameters I have taken care of by overloading.

What I want is a function that can take an arbitrary number of input parameters.
 
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Expert Comment

by:wizia
ID: 8132500
What I understand from your write that, you need to put afunction to return the string with value or text as "string is null'
The inputs fro the function will cahnge ans depends on the procedure you are calling

You need to build the function dynamically depends on the number of inputs from procedure
If your procedure is ahs 4 inputs say v1,v2,v3,v4 then you need to create function as
 My_function (v1,v2,v3,v4,vout) return varchar2.......

try to use dbms_sql
to build function DDL in the package
see  Note:170643.1 in oracle metalink
Hope this may be useful
Vijay
0
 

Expert Comment

by:Terrorist
ID: 8136564
Maybe VARARRAY will helps you to pass different number of your "Values" to function.
0
 

Expert Comment

by:kirk_cameron
ID: 8136955
i am not sure, but maybe your problem can be solved with the oracle-function > NVL <
0
 
LVL 21

Expert Comment

by:oleggold
ID: 8146655
This seems to me simple enough:
Firstly You would need an object type of Your values types,say :
create or replace type NULL_val_type as object
(
    -- Created : 16/03/2003 6:00:19 PM
  -- Purpose : To return NULL values
 
  -- Attributes
  Value_name varchar2(777),
  Value_vrchr2 varchar2(32767),
  Value_num number


)
Then from the main function,call it get_null_values or whatever You call the Oracle STANDARD IS NULL or your pesonnal  IS_Null function as elaborated bellow in the loop for all your parameters in when the parameter is the nested table ,like:
CREATE OR REPLACE PACKAGE BODY SOME_PACKAGE
IS
 TYPE null_array IS TABLE OF NULL_val_type INDEX BY binary_integer ;

function  get_null_values  (PVALARR null_array) return VARCHAR2
is
res_null null_val_type;
res_set varchar2(32767):='';
STARTING BOOLEAN;
RETVAL varchar2(32767);
BEGIN
FOR I IN 1..PVALARR.COUNT
LOOP
res_null:=null_array(null_val_type.new(PVALARR(i).value_name),
null_val_type.new(PVALARR(i).value_vrchr2)) ;                      
IF IS_NULL(res_null.value_vrchr2) THEN
res_set:=res_set||res_null.value_name||',';
END IF;
END LOOP;
RETVAL:=res_set||'ARE NULL';
return RETVAL;
END get_null_values;
END;
As I think ALSO You'd better write 2 functions the first would be one that checks the value for nulls like :
function  IS_NULL (PVAL VARCHAR2) return BOOLEAN
is
BEGIN
IF PVAL IS NULL THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END IS_NULL;
Can add the following overloading for numbers
function  IS_NULL (PVAL NUMBER) return BOOLEAN
is
BEGIN
IF PVAL IS NULL THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END IS_NULL;
then
from the main function,call it get_null_values or whatever You call your IS_Null function in the loop for all your parameters in when the parameter is the nested table ,like:

Hope it was helpfull,Oleg
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 10094075
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ - no points refunded

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

anand_2000v
EE Cleanup Volunteer
0
 
LVL 21

Expert Comment

by:oleggold
ID: 10102158
to help You a little more ,try wizards for functions writing in the Oracle BI tools  like Data Warehouse builder (there it's called transformations) or Discoverer.All detailed info on oracle built-in sql fuctions can be found on http://sqlzoo.napier.ac.uk/big/B10501_01/server.920/a96540/functions2a.htm#80856
The other tool for writing GOOD sql/PLSQL fuctions is PLSQLDEVELOPER found on http://www.allroundautomations.com/
Finaly,when you write a function to be used from SQL you'd better to use purity level like this:
 pragma restrict_references(function_name, WNPS);
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 10148226
PAQed - no points refunded (of 250)

Computer101
E-E Admin
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question