Home » SQL & PL/SQL » SQL & PL/SQL » create view based on parameters
create view based on parameters [message #386141] Thu, 12 February 2009 19:01 Go to next message
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 #386143 is a reply to message #386141] Thu, 12 February 2009 19:29 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

This is a BAD, BAD, BAD approach.
Have you ever heard about maintaining source code within a repository?

There is NO need to create object on the fly from PL/SQL.
Objects should be created once for any application by SQL maintained within a source code repository.

Write SQL to generate the plain SQL CREATE statements as text!

The errors result from invalid SQL.
Re: create view based on parameters [message #386144 is a reply to message #386143] Thu, 12 February 2009 19:43 Go to previous messageGo to next message
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 #386146 is a reply to message #386141] Thu, 12 February 2009 20:02 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>I am just playing around as I have only started PL/SQL 2 hours ago
>What exactly is invalid about the SQL?
For how long have you been playing with SQL?
Re: create view based on parameters [message #386158 is a reply to message #386146] Thu, 12 February 2009 22:19 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #386163 is a reply to message #386162] Thu, 12 February 2009 22:49 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Here one quote is missing.

''' || FilterName || ''''


insted of this you can use bind variable.

Thanks
Trivnedra
Re: create view based on parameters [message #386176 is a reply to message #386162] Fri, 13 February 2009 00:24 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: LAG Function Logic
Next Topic: SQL & PL/sql
Goto Forum:
  


Current Time: Wed Dec 07 16:22:17 CST 2016

Total time taken to generate the page: 0.28680 seconds