create view based on parameters [message #386141] |
Thu, 12 February 2009 19:01  |
philb
Messages: 10 Registered: February 2009
|
Junior Member |
|
|
Hi,
I am just learning pl/sql as I go as I need to be able to re-use some table creation code but want to pass parameters in to it to give slightly different results.
I'm starting by trying to create some views based on parameter values. The following code works OK without the WHERE clause but gives me an error when I try to specify what the where clause should be. I suspect its something to do with the quotes. Any help please or suggestions how this could be done better.
My next task is to put it in a loop and set the parameter values differently for each iteration of ther loop.
Thanks in advance.
DECLARE
ViewName VARCHAR2(10) := 'testview';
FilterName VARCHAR2(10) := 'SOCKS';
BEGIN
EXECUTE IMMEDIATE
'CREATE OR REPLACE VIEW ' || ViewName || ' AS
SELECT
COUNT(b.product_category_1_description) AS x
FROM
pb_owner.dm_sku_dim b
WHERE product_category_1_description = ' || FilterName
;
END;
11:49:00 AM Start SQL Editor Execution ...
11:49:00 AM Processing ...
11:49:00 AM DECLARE
11:49:00 AM *
11:49:00 AM ORA-00904: "SOCKS": invalid identifier
11:49:00 AM ORA-06512: at line 7
11:49:00 AM *** Script stopped due to error ***
|
|
|
|
Re: create view based on parameters [message #386144 is a reply to message #386143] |
Thu, 12 February 2009 19:43   |
philb
Messages: 10 Registered: February 2009
|
Junior Member |
|
|
Thanks for the response.
No, I have not heard about maintaining source code in a repository.
I am just playing around as I have only started PL/SQL 2 hours ago as I need to perform a specific task by the end of the week. I'm not particularly bothered about bad practice as long as it works. Once this is done I won't be a regular user.
What exactly is invalid about the SQL? Is it something to do with the quotes as I suspect?
Thanks,
PhilB.
|
|
|
|
Re: create view based on parameters [message #386158 is a reply to message #386146] |
Thu, 12 February 2009 22:19   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
Try With this
DECLARE
viewname VARCHAR2 (10) := 'testview';
filtername VARCHAR2 (10) := 'SOCKS';
l_string VARCHAR2 (32767);
BEGIN
l_string :=
'CREATE OR REPLACE VIEW '
|| viewname
|| ' AS SELECT COUNT(b.product_category_1_description) AS x '
|| ' FROM pb_owner.dm_sku_dim b WHERE product_category_1_description = :filtername';
EXECUTE IMMEDIATE l_string
USING filtername;
END;
Thanks
Trivendra
|
|
|
Re: create view based on parameters [message #386160 is a reply to message #386158] |
Thu, 12 February 2009 22:35   |
philb
Messages: 10 Registered: February 2009
|
Junior Member |
|
|
Thanks,
What I have come up with is this, which works fine for me...
<code>
/* -------------------------------------------------------------------------
CREATE A FUNCTION THAT RETURNS THE SQL AS A STRING.
PARAMETERS TO PUT IN THE SQL ARE PASSES IN AS ARGUMENTS
---------------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION dmine_CREATE_VIEW_TEST(
ViewName IN VARCHAR2
,FilterName IN VARCHAR2
)
return VARCHAR2
IS
theSQL VARCHAR2(1000);
BEGIN
theSQL :=
'CREATE OR REPLACE VIEW ' || ViewName || ' AS
SELECT
COUNT(b.product_category_1_description) AS count_of_' || FilterName || '
FROM
pb_owner.dm_sku_dim b
WHERE product_category_1_description = ''' || FilterName || ''''
;
RETURN theSQL;
END dmine_CREATE_VIEW_TEST;
DECLARE
thisSQL VARCHAR2(1000) := dmine_CREATE_VIEW_TEST('TEST_SOCKS','SOCKS');
BEGIN
DBMS_OUTPUT.put_line(thisSQL);
EXECUTE IMMEDIATE thisSQL;
END;
SELECT * FROM TEST_SOCKS
</code>
|
|
|
Re: create view based on parameters [message #386162 is a reply to message #386160] |
Thu, 12 February 2009 22:44   |
philb
Messages: 10 Registered: February 2009
|
Junior Member |
|
|
sorry, just getting used to the code tags. Anyway, thanks for actually taking the time to correct my problem and highlight things I need to know about PL/SQL rather than just telling me my SQL is questionable.
Below is what I came up with, which will get me by for now.
/* -------------------------------------------------------------------------
CREATE A FUNCTION THAT RETURNS THE SQL AS A STRING.
PARAMETERS TO PUT IN THE SQL ARE PASSES IN AS ARGUMENTS
---------------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION dmine_CREATE_VIEW_TEST(
ViewName IN VARCHAR2
,FilterName IN VARCHAR2
)
return VARCHAR2
IS
theSQL VARCHAR2(1000);
BEGIN
theSQL :=
'CREATE OR REPLACE VIEW ' || ViewName || ' AS
SELECT
COUNT(b.product_category_1_description) AS count_of_' || FilterName || '
FROM
pb_owner.dm_sku_dim b
WHERE product_category_1_description = ''' || FilterName || ''''
;
RETURN theSQL;
END dmine_CREATE_VIEW_TEST;
DECLARE
thisSQL VARCHAR2(1000) := dmine_CREATE_VIEW_TEST('TEST_SOCKS','SOCKS');
BEGIN
DBMS_OUTPUT.put_line(thisSQL);
EXECUTE IMMEDIATE thisSQL;
END;
SELECT * FROM TEST_SOCKS
|
|
|
|
Re: create view based on parameters [message #386176 is a reply to message #386162] |
Fri, 13 February 2009 00:24  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
BlackSwan is right: this is very bad practice. You can tell us that you don't care, but that only makes you a very bad programmer. You would actually do yourself and your application a favor to ask why it is such bad practice and to see what alternatives you have.
|
|
|