The following code will concatenate two strings str1 and str

The following code will concatenate two strings str1 and str2

CREATE OR REPLACE PROCEDURE concatenate_strings as

str1 VARCHAR2(25) := \'begin\';

str2 VARCHAR2(25) := \'end\';

result VARCHAR2(50); BEGIN result := str1 || \'_\' || str2; dbms_output.put_line(\'The result is \' || result); dbms_output.put_line(SYSDATE);

END; /

SET SERVEROUTPUT ON;

EXECUTE concatenate_strings;

Modify this procedure to accept two string parameters.

Example of the output for EXECUTE concatenate_strings(\'big\',\'dog\');

The result is big_dog 26-Jan-16

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(\'big\',\'dog\') FROM DUAL;

Will return big_dog 27-JAN-16

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

First, the table ProductTable, then product with ID 302 will be located. Product name of that product will be concatenated with the price and current date.

Will return Wind-Up Water Swimmers_ $2.00 27-JAN-16

Create a procedure that accepts product ID as a parameter and returns the price from ProductTable table. Add exception handling to catch if product ID is not in the table. The table ProductTable already exists (see sample code in this module).

Needs to be completed in SQL Developer and please provide code.

Solution

Create or replace procedure concatenate_strings (

Str1 IN VARCHAR2(25)

Str2 IN VARCHAR2(25)) as

Result VARCHAR2(50);

BEGIN

Result:=str1 || ‘_’ || str2;

Dbms_output.put_line(‘result is’||result);

Dbms_output.put_line(SYSDATE);

END;

SET SERVEROUTPUT ON;

EXECUTE concatenate_strings(‘big’,’dog’);

Procedure to accept two string parameters.

Example o/p:

EXECUTE concatenate_strings(‘big’,’dog’);

Result: big_dog 26-Jan-16

SELECT concatenate_strings(ProductName,to_char(ListPrice,’$999.99’))

For ProductTable WHERE ProductID=302;

output: Wind-up Water Swimmers_$2.00 27-JAN-16

Create or replace procedure concatenate_strings (

Str1 IN VARCHAR2(25)

Str2 IN VARCHAR2(25)) as

Result VARCHAR2(50);

BEGIN

Result:=str1 || ‘_’ || str2;

Dbms_output.put_line(‘result is’||result);

Dbms_output.put_line(SYSDATE);

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line (\'A SELECT...INTO did not return any row.\');

END;