Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
Parameterized Views
Comments
-
Maybe a helpful intermediate step would be to enable the usage of (parameterized) PL/SQL cursors as SQL query_table_expressions ?
create or replace package pkg is
cursor c1 (...
end;
select ... from pkg.c1(...)
Mmm, that doesn't work dude.
CREATE OR REPLACE PACKAGE MARTIN_TEST AS
CURSOR C1 (p_Object_Name VARCHAR2) IS
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME = (p_Object_Name);
END MARTIN_TEST;
/
SELECT *
FROM MARTIN_TEST.C1('DUAL')
/
ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 11 Column: 22
You have to OPEN the cursor like this:
DECLARE
C1_REC USER_OBJECTS%ROWTYPE;
--
BEGIN
OPEN MARTIN_TEST.C1('DUAL');
FETCH MARTIN_TEST.C1 INTO C1_REC;
--
<Do Processing Here>
--
CLOSE MARTIN_TEST.C1;
END;
/
-
Mmm, that doesn't work dude.
CREATE OR REPLACE PACKAGE MARTIN_TEST AS
CURSOR C1 (p_Object_Name VARCHAR2) IS
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME = (p_Object_Name);
END MARTIN_TEST;
/
SELECT *
FROM MARTIN_TEST.C1('DUAL')
/
ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 11 Column: 22
You have to OPEN the cursor like this:
DECLARE
C1_REC USER_OBJECTS%ROWTYPE;
--
BEGIN
OPEN MARTIN_TEST.C1('DUAL');
FETCH MARTIN_TEST.C1 INTO C1_REC;
--
<Do Processing Here>
--
CLOSE MARTIN_TEST.C1;
END;
/
Yeah, was meant as a supplemental idea, not a yet available alternative, thanks for clarification.
-
Maybe a helpful intermediate step would be to enable the usage of (parameterized) PL/SQL cursors as SQL query_table_expressions ?
create or replace package pkg is
cursor c1 (...
end;
select ... from pkg.c1(...)
Rainer Stenzel wrote:Maybe a helpful intermediate step would be to enable the usage of (parameterized) PL/SQL cursors as SQL query_table_expressions ?create or replace package pkg iscursor c1 (...end;select ... from pkg.c1(...)
Most likely, you would need to wrap the parameterized PL/SQL cursor with TABLE().
eg
select * from table( pkg.c1( var1,var2) )
With that, anything that provides a collection would follow the same "wrap with TABLE()" syntax
- Pipelined table function
- SQL Collections
- PL/SQL Collections
- PL/SQL Cursors (the proposed alternative idea)
MK
-
SELECT *
FROM TABLE(MARTIN_TEST.C1('DUAL'))
/
ORA-06553: PLS-707: unsupported construct or internal error [2603]
06553. 00000 - "PLS-%s: %s"
-
SELECT *
FROM TABLE(MARTIN_TEST.C1('DUAL'))
/
ORA-06553: PLS-707: unsupported construct or internal error [2603]
06553. 00000 - "PLS-%s: %s"
FatMartinR wrote:SELECT *FROM TABLE(MARTIN_TEST.C1('DUAL'))/ORA-06553: PLS-707: unsupported construct or internal error [2603]06553. 00000 - "PLS-%s: %s"Maybe this tread would be better off in the forum so that we can discuss the usefulness of such a feature/syntax.
Threads in that forum don't have to worry about the syntax working since these are about potential new features and ORA-6553 is the expected result if ran on any current version of Oracle.
MK
-
marginal new feature addendum: 12.2 new feature - TABLE operator not needed for PTF anymore
-
W00T!!!
20c may have Macros! (safe harbor statement)
You can create a Parameterized View with SQL Macros!!
Video by Chris Saxon
-
W00T!!!
20c may have Macros! (safe harbor statement)
You can create a Parameterized View with SQL Macros!!
Video by Chris Saxon
Yes SQL Macros definitly solve some of the problems that we want to solve with parametrized views.
Certainly it will work for the example that @ApexBine showed in the original idea.
However SQL macros seem to come with several restrictions. So there are not that easy to use.
For example if we use a pipelined table function to create a select without the parameter if the parameter is null and otherwise with the parameter binded, then this might not work with SQL macros. The pipelined table function would be a possible way to increase the performance for selects that have several such parameters:
... col1 = nvl(:param1, col1) and col2=nvl(:param2, col2)and col3=nvl(:param3, col3)
as far as I understood SQL macros will not help with such a case (which is quite different from original example).
There was an interesting Ask Tom Office hour in October 2019 about this also from @Chris Saxon-Oracle.
Unfortunatly I don't see a link anymore where the chat window from that session can be seen.
It links to the same video that you already posted.
-
Yes SQL Macros definitly solve some of the problems that we want to solve with parametrized views.
Certainly it will work for the example that @ApexBine showed in the original idea.
However SQL macros seem to come with several restrictions. So there are not that easy to use.
For example if we use a pipelined table function to create a select without the parameter if the parameter is null and otherwise with the parameter binded, then this might not work with SQL macros. The pipelined table function would be a possible way to increase the performance for selects that have several such parameters:
... col1 = nvl(:param1, col1) and col2=nvl(:param2, col2)and col3=nvl(:param3, col3)
as far as I understood SQL macros will not help with such a case (which is quite different from original example).
There was an interesting Ask Tom Office hour in October 2019 about this also from @Chris Saxon-Oracle.
Unfortunatly I don't see a link anymore where the chat window from that session can be seen.
It links to the same video that you already posted.
I believe you can use SQL macros for that scenario Sven - though perhaps I've not understood what you're trying to do!
Unfortunatly I don't see a link anymore where the chat window from that session can be seen.
Could you clarify what you're looking for here? I can probably dig it out
-
Hey all,
what version of Oracle was parameterized views introduced? I'm trying it on 11gR2 but no go.
