This content has been marked as final. Show 10 replies
As others have been suggesting...
A lot of 'stuff' goes into creating a view. Columns, tables (or other views), joins (inner or outer) and join columns, restrictions (filtering), concatenations, calculations, display formatting (may be). Are you going to code all of this syntax checking and construction into your procedure.
We do not do this kind of thing.
Perhaps you could share your user's thinking here - what is his business problem that he is trying to solve.
Hint: Let your customer tell you what (his business problem is), not how (to solve it) - that's your job.
trento wrote:That's typically what you'd use a reference cursor for.
I need sp for creating view as there are some date params involved specific for each day, as date itself, and table names.
Tx to all
Edited by: trento on May 3, 2010 1:32 PM
Instead of creating a view whenever the user calls your procedure (that's really not a good technical solution, i would HIGHLY advise you to not do this) you would return a cursor to the required SQL.
No need for a view, no need for much of anything. When the user wants the data, they call this function and pass in any date they fancy and get the result.
CREATE OR REPLACE FUNCTION usp_v1 ( p_date_parameter DATE ) RETURN sys_refcursor AS l_out_cursor sys_refcursor; BEGIN open l_out_cursor for select * from tt1 where date = p_date_parameter union select * from tt2 where date = p_date_parameter; return l_out_cursor; end; /
And I see that my compiler doesn't like procedures with Exec Immediate with single quoutes inside the code, I played with " and () but it still doen's work.
create or replace
PROCEDURE usp_2 AS
execute immediate '
CREATE OR REPLACE VIEW VV1 AS
'0' AS ACTION-IND, -- <========== ERROR
WHERE COL1 LIKE 'S%' -- <========== ERROR
SQL> desc vv1 ERROR: ORA-04043: object vv1 does not exist SQL> create table tt1 2 (col1 number 3 ,col2 number); Table created. SQL> create or replace 2 PROCEDURE usp_2 AS 3 BEGIN 4 execute immediate 5 'CREATE OR REPLACE VIEW VV1 AS 6 SELECT COL1, 7 ''0'' AS ACTION_IND, 8 COL2 9 FROM TT1 10 WHERE COL1 LIKE ''S%'' 11 '; 12 end; 13 / Procedure created. SQL> exec usp_2; PL/SQL procedure successfully completed. SQL> desc vv1 Name Null? Type ----------------------- -------- ---------------- COL1 NUMBER ACTION_IND CHAR(1) COL2 NUMBER