-4

I have the following scenario:

I need to feed into either a Procedure or Function the Following Parameters:

Link_1_ID, Link_2_ID, Address1, Address2, City, State, Zip, Address_Type

These will be used to query a table (we'll call the Table ADDRESS_INFO) surrounding some logic and then hardcode the variable LOC_CDE with '0001'.

I then need this all to return in the form of a User Defined Table type (though it should only pull one record at a time).The table would output the following:

Link_1_ID, Link_2_ID, Address1, Address2, City, State, Zip, LOC_CDE (no Address_Type)

I am VERY new to PL/SQL and have had little luck in returning anything. I would also prefer to keep ALL code in one defined PACKAGE (which is also causing trouble).

Any advice or help would be appreciated.

JoeS.
  • 73
  • 6
  • It would help greatly to show some code! Show us what you've done so far and maybe we can help. It is unclear what you are asking for I'm afraid. – Gary_W Nov 30 '16 at 21:40
  • My apologies. My code is a real mess as i am continuing to try and figure this out. I will try and add some in a little while. Would a basic shell of what I am trying to achieve help? – JoeS. Nov 30 '16 at 21:46
  • 1
    Try to give a complete, repeatable example so folks that want to help can copy/paste it and try it in order to help. Show before and after examples of data so its clear what you are working with and what you are after. I know it's not always simple but the more info you can provide the more likely you will get meaningful help. – Gary_W Nov 30 '16 at 21:48
  • What is the use case exactly? Do you want to be able to use it in a SQL query (in which case it'll need to be a function returning an object type, i.e. one defined using `create type`), or is it just for processing within PL/SQL code (in which case a plain old record type declared in a package spec will do, although an object type will also be valid)? There's no such thing as a 'User Defined Table', other than a database table, which doesn't seem to be what you mean. – William Robertson Dec 01 '16 at 13:40

1 Answers1

0

You want your own UDT, saying that your function will only return one value; so I believe you may first need to define your type, for example:

create or replace type yourType as object (fieldA number, fieldB varchar2(20), fieldC varchar2(20));

Then you can define your function with all the parameters you need, returning a single instance of your UDT:

create or replace function yourFunction (parA number, parB varchar2) return yourType is
    returnVal yourType;
begin
    select yourType(parA, parB || ' something', 'FIXED_VALUE')
    into returnVal
    from dual;
    return returnVal;
end;

This is only one way to handle your UDT variables; for example you could fill the fields of you UDT explicitly:

create or replace function yourFunction (parA number, parB varchar2) return yourType is
    returnVal yourType;
begin
    returnVal := new yourType(null, null, null); /* you need an initialization here */
    select parA, parB || ' something', 'FIXED_VALUE'
    into returnVal.fieldA, returnVal.fieldB, returnVal.fieldC
    from dual;
    return returnVal;
end;

You could use some other variables to fetch into and then use these variables to fill your return value, or define a constructor to handle the creation of your UDT instances with some custom logic, and so on... this is just a very basic example of simple ways to handle a UDT and use it as a return value of a function.

Aleksej
  • 22,443
  • 5
  • 33
  • 38