Question

1. ) Write a function f_concatenate_strings that will do the same as concatenate_strings procedure from #1, but can be called from a select statement. Examples of output:

SELECT f_concatenate_strings('Crypto','Currency') FROM DUAL;

Will return CryptoCurrency Date: 06-JAN-18 user: speltsve

2. SELECT f_concatenate_strings(ProductName, to_char(ListPrice, '$999.99')) from ProductTable WHERE ProductID=302;

Will return Wind-Up Water Swimmers $2.00 06-JAN-18 user: speltsve

Table to use:

CREATE TABLE ProductTable(

    ProductID INTEGER NOT NULL primary key,

    ProductName VARCHAR(50) NOT NULL,

    ListPrice NUMBER(10,2),

    Category INTEGER NOT NULL

);

/

INSERT INTO ProductTable VALUES(299,'Chest',99.99,10);

INSERT INTO ProductTable VALUES(300,'Wave Cruiser',49.99,11);

INSERT INTO ProductTable VALUES(301,'Megaland Play Tent',59.99,11);

INSERT INTO ProductTable VALUES(302,'Wind-Up Water Swimmers',2.00,11);

INSERT INTO ProductTable VALUES(303,'Garmin Pocket or Vehicle GPS Navigator',609.99,12);

Answer 1

Note: Done accordingly. Please comment for any problem. Please Uprate. Thanks.

Code:

CREATE OR REPLACE FUNCTION f_concatenate_strings(x VARCHAR2, y VARCHAR2)
RETURN VARCHAR2
AS
str1 VARCHAR2(100) := x;
STR2 VARCHAR2(100) := Y;
result VARCHAR2(250);
BEGIN
result := str1 || str2 ||' '|| SYSDATE ||' user: '|| SYS_CONTEXT('USERENV','OS_USER');
RETURN result;
END;
/
SELECT F_CONCATENATE_STRINGS('Crypto','Currency') FROM DUAL;
SELECT f_concatenate_strings(ProductName, to_char(ListPrice, '$999.99')) from ProductTable WHERE ProductID=302;

Output: