PRODUCT TABLE

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);

CREATE OR REPLACE PROCEDURE sqltest AS

V_Category NUMBER;

BEGIN

-- comments

SELECT Category INTO v_Category FROM ProductTable

WHERE ProductId = 300;

dbms_output.put_line('Product category for product #300 is ' || V_Category);

END;

/

set serveroutput on;

exec sqltest;

CONCATENATE PROCEDURE

create or replace procedure concatenate_strings (

str1 varchar2,

str2 varchar2

) is

result varchar(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('big','dog');

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.

Please make sure you check your code before turning in. This must be done in Oracle SQL. Make sure the input/output examples provided work with your code and give the same results.