Forum Stats

  • 3,838,515 Users
  • 2,262,378 Discussions
  • 7,900,681 Comments

Discussions

Converting a SQL Server Function to Oracle

Murray Sobol
Murray Sobol Member Posts: 254
edited Nov 6, 2015 4:30PM in SQLとPL/SQL

I am trying to convert a SQL Server Function to an Oracle Function but am encountering problems.

Here is the SQL Server Function:

/* ========================================================================= */
/* Function:       f_get_business_types                                      */
/*                                                                           */
/* Author:         Jim Reese, dbcSMARTsoftware Inc.                          */
/* Translation:    Murray Sobol, dbcSMARTsoftware Inc.                       */
/* Purpose:        This is a database function which returns in a single     */
/*                 row/column: All Business Types of the Name And Address    */
/*                 profile, separated by ‘,’ if this profile has multiple    */
/*                 Business Types.                                           */
/* Last Modified:  Wednesday March 19, 2008                                  */
/* Parameter(s):   av_name_and_address_id               varchar2(10)         */
/* Return Code(s): SUCCESS      -> Returns Descriptions                      */
/* ========================================================================= */

CREATE FUNCTION f_get_business_types
   (@name_and_address_id       varchar(10))
RETURNS varchar(MAX)
AS
BEGIN
   DECLARE @Descriptions varchar(MAX)

   SELECT @Descriptions = COALESCE(@Descriptions + ', ', '') + s1_business_type.description
     FROM s1_na_business_type
          JOIN s1_business_type
            ON s1_na_business_type.business_type_code = s1_business_type.business_type_code
    WHERE s1_na_business_type.name_and_address_id = @name_and_address_id

   RETURN @Descriptions
END
go

Here is my attempt at an Oracle Function:

CREATE or replace FUNCTION f_get_business_types
   (av_name_and_address_id     IN  varchar2
   )
RETURN VARCHAR
IS
   lv_descriptions             varchar2(2000);
   l_code                      number;
   l_errm                      varchar2(64);

BEGIN
   BEGIN
     SELECT COALESCE(lv_descriptions || ', ', '') + s1_business_type.description
       INTO lv_descriptions
       FROM s1_na_business_type
            JOIN s1_business_type
              ON s1_na_business_type.business_type_code = s1_business_type.business_type_code
    WHERE s1_na_business_type.name_and_address_id = av_name_and_address_id;

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('No Data Found');
       lv_descriptions := NULL;
     WHEN OTHERS THEN
           l_code := SQLCODE;
           l_errm := SUBSTR(SQLERRM, 1, 64);
           DBMS_OUTPUT.PUT_LINE ('Error code ' || l_code || ': ' || l_errm);
       RETURN(NULL);
   END;

-- ============================================================
-- Process finished with SUCCESS
-- ============================================================
   DBMS_OUTPUT.PUT_LINE(lv_descriptions);
   RETURN(lv_descriptions);

END;
/

Here are some table definitions:

CREATE TABLE s1_business_type
(
   business_type_code              varchar2(10)                not null,
   description                     varchar2(50)                null,
   CONSTRAINT pk_business_type PRIMARY KEY (business_type_code)
       using index
       tablespace smartsoft_index
)
tablespace smartsoft_data
/

sample data:

BUSINESS_TYPE_CODE,      DESCRIPTION

110                                         MEAL - DEALER

120                                         MEAL - EXPORT

200                                         FOOD SERVICE DISTRIBUTOR

210                                         BAKERY DISTRIBUTOR

220                                         KEY ACCOUNT

230                                         MARGARINE

240                                         INDUSTRIAL - FOOD

250                                             INDUSTRIAL - NON FOOD

260                                         CO-PACKERS - BOTTLERS/PKGRS

270                                         PACKAGED - EXPORT

280                                         OIL - EXPORT

290                                         BROKERS

300                                         RETAIL - DOMESTIC

310                                         RETAIL - U.S.

320                                         RETAIL - EXPORT

900                                         SUPPLIER - CARRIER

901                                             SUPPLIER - COMMODITY BROKER

902                                         SUPPLIER - COMMODITY SUPPLIER

903                                         SUPPLIER - CUSTOMS BROKER

904                                         SUPPLIER - WAREHOUSE STORAGE

905                                         Elevator

100                                         MEAL - FEED

906                                         Big Producer

CREATE TABLE s1_na_business_type
(
   name_and_address_id             varchar2(10)                not null,
   business_type_code              varchar2(10)                not null,
   notes                           varchar2(250)               null,

   CONSTRAINT pk_na_business_type PRIMARY KEY (name_and_address_id, business_type_code)
       using index
       tablespace smartsoft_index
)
tablespace smartsoft_data
/

and here is data

NAME_AND_ADDRESS_ID,      BUSINESS_TYPE_CODE, NOTES

23                                              906     

ABC                                              905                                    the best in the west

ABC                                         220

100                                         906                                         able to change BT in table and it defaults to the N&A Profile

200                                         906

A1                                              905

2001                                    310                                              and much more

71                                         906                                              dsfads

100                                         210

When I try to run the Function:

set serveroutput on

exec dbms_output.put_line(f_get_business_types('ABC'));

I get this result:

PL/SQL procedure successfully completed.

Error code -1722: ORA-01722: invalid number

I would have expected to see this:

Description:

KEY ACCOUNT

Elevator

The results should be concatenated into one string like this:

'KEY ACCOUNT','Elevator'

I don't understand the ORA-01722 error; I am not working with numbers.

Any assistance would be appreciated.

Murray Sobol

Best Answer

  • Papageno
    Papageno Member Posts: 53 Bronze Badge
    edited Nov 6, 2015 4:30PM Answer ✓

    s1_business_type.description  IS   varchar2(50)

    FIRST VALUE OF lv_descriptions IS ?


    >     SELECT COALESCE(lv_descriptions || ', ', '') + s1_business_type.description

                                                                              ↓?

           SELECT COALESCE(lv_descriptions || ', ', '') || s1_business_type.description


    TABLE s1_na_business_type  COLUMN name_and_address_id 'ABC' HAS 2 RECORDS.

    SELECT COALESCE ... INTO  may be   ORA-01422:TOO MAY ROWS.

    Murray Sobol

Answers

  • Papageno
    Papageno Member Posts: 53 Bronze Badge
    edited Nov 6, 2015 4:30PM Answer ✓

    s1_business_type.description  IS   varchar2(50)

    FIRST VALUE OF lv_descriptions IS ?


    >     SELECT COALESCE(lv_descriptions || ', ', '') + s1_business_type.description

                                                                              ↓?

           SELECT COALESCE(lv_descriptions || ', ', '') || s1_business_type.description


    TABLE s1_na_business_type  COLUMN name_and_address_id 'ABC' HAS 2 RECORDS.

    SELECT COALESCE ... INTO  may be   ORA-01422:TOO MAY ROWS.

    Murray Sobol
This discussion has been closed.