Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

PL/SQL function problem

Posted on 2003-03-13
10
Medium Priority
?
1,272 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
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
Industry Leaders: 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!

 

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

581 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