Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Converting a SQL Server Function to Oracle

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
-
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.
Answers
-
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.