dynamically creating view in a stored procedure
How do I create a view in a stored procedure. The stored procedure is passed a parameter, for example an id, I will need to create the view name from this id and create the select statement using the id as a parameter in the where clause. How is this done?
create replace sp_create_view(p_id number)
as
string = 'create view dyn' + p_id ' as
select col1,
col2
from table where table.id = p_id;'
create replace sp_create_view(p_id number)
as
string = 'create view dyn' + p_id ' as
select col1,
col2
from table where table.id = p_id;'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>v_sql LONG;
NEVER use LONG's. Use a CLOB.
>>id='||p_id||';'
I believe adding the semi-colon as part of the actual statement will generate a:
ORA-00911: invalid character
All that said, isn't that pretty much what I posted?
NEVER use LONG's. Use a CLOB.
>>id='||p_id||';'
I believe adding the semi-colon as part of the actual statement will generate a:
ORA-00911: invalid character
All that said, isn't that pretty much what I posted?
>>I will need to create the view name from this id and create the select statement using the id as a parameter in the where clause.
WHY?
what do you possibly gain by building a view, then using that view as part of a select?
just go to the select and bypass the create/replace of the view
seems to me you have the impression that this will (somehow) be more efficient - it won't be
also seems to me you feel that a parameter is a necessary part of efficiency - it isn't
(a 'where clause' on a STATIC view could be just as efficient, perhaps more so)
Would you describe what it is you are trying to achieve please?
WHY?
what do you possibly gain by building a view, then using that view as part of a select?
just go to the select and bypass the create/replace of the view
seems to me you have the impression that this will (somehow) be more efficient - it won't be
also seems to me you feel that a parameter is a necessary part of efficiency - it isn't
(a 'where clause' on a STATIC view could be just as efficient, perhaps more so)
Would you describe what it is you are trying to achieve please?
and although I really don't like the sound of this, if you are going to proceed down this path, use:
CREATE OR REPLACE VIEW ...
CREATE OR REPLACE VIEW ...
slightwv, you posted
string := 'create view dyn' + p_id ' as
select col1,
col2
from table where table.id = ' || p_id;
string := 'create view dyn' + p_id ' as
select col1,
col2
from table where table.id = ' || p_id;
What is the view supposed to save you?
That said, given the example above (you have a typo):
string := 'create view dyn' + p_id ' as
select col1,
col2
from table where table.id = ' || p_id;
--after you build the string
execute immediate string;