create table demoapp_properties
(
	propname    varchar2(30),
	int_value   int,
	str_value   int,
	description varchar2(255),
	
	primary key(propname)
)
/
-- insert default values for configurations
insert into demoapp_properties values('use_package_variables', 1, null, 'democlient logic: if SQL Statements holding ''call ${PKG:myPkgName}proc'' should be substituted to ''call myPkgName.proc'' or ''call proc'' before the SQL Statement is sent to the server.')
/

------------------------------------------------------------------------------------------
-- PROCEDURE: get_demoapp_property
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_demoapp_property
(
	p_propname        IN VARCHAR2,
	p_int_value       OUT INTEGER,
	p_str_value       OUT VARCHAR2
)
AS
BEGIN
	SELECT int_value, str_value 
	INTO p_int_value, p_str_value
	FROM demoapp_properties 
	WHERE propname = p_propname;

	EXCEPTION
	-- NO-ROWS-FOUND: Set default values for some known options, otherwise throw the exception again...
	WHEN NO_DATA_FOUND THEN
		IF (p_propname = 'use_package_variables') THEN p_int_value := 1; 
		ELSE RAISE; 
		END IF;
	-- For other errors just throw the exception
	WHEN OTHERS THEN RAISE;
END;
/ 



exit
---------------------------------------------------------
-- test code
---------------------------------------------------------
-- get the prop, should return:
--          RPC Return parameter: pos=1, value=1
--          RPC Return parameter: pos=2, value=(NULL)
\call get_demoapp_property('use_package_variables', ?, ?) :(int=0 out, string='' out)
go

-- get unknown prop, should raise error: 
--         ORA-01403: no data found
\call get_demoapp_property('xxx', ?, ?) :(int=0 out, string='' out)
go

-- remove all props
delete from demoapp_properties
go
-- get the prop, should return:
--          RPC Return parameter: pos=1, value=1
--          RPC Return parameter: pos=2, value=(NULL)
\call get_demoapp_property('use_package_variables', ?, ?) :(int=0 out, string='' out)
go

-- insert 0 config
insert into demoapp_properties values('use_package_variables', 0, null, 'democlient logic: if SQL Statements holding ''call ${PKG:myPkgName}proc'' should be substituted to ''call myPkgName.proc'' or ''call proc'' before the SQL Statement is sent to the server.')
go

-- get the prop, should return:
--          RPC Return parameter: pos=1, value=0
--          RPC Return parameter: pos=2, value=(NULL)
\call get_demoapp_property('use_package_variables', ?, ?) :(int=0 out, string='' out)
go

-- check what's in there
select * from demoapp_properties
go

-- cleanup
drop table demoapp_properties
go
drop PROCEDURE get_demoapp_property
go


