Oracle SQL Developer - Create View with parameters

Question

I'm new to Oracle SQL and I'm currently working on an Oracle SQL view in Oracle SQL Developer.

I need two parameters in my View

fruitType (string)
fruitNumber (int)

I tried the following (simplified) statement in Oracle SQL Developer:

CREATE VIEW FRUITSRESULT (fruitType, fruitNumber) AS
SELECT
   fruit_col1 as FruitType,
   fruit_col2 as FruitNumber
   fruit_col3 as FruitInfo
FROM
   FRUITGARDEN
WHERE
   fruit_col1 = fruitType
   fruit_col2 = fruitNumber
;

Unfortunately this does not work, I always get the following error:

Error starting at line : 1 in command - 
...

ORA-00904: "FRUITNUMBER": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Do you know how to solve this error?

Is it actually possible to create a parameterized View in Oracle SQL?

Your current query can be easily used with regular view and where clause on top of it, because predicates are pushed into view. — astentx
– astentx, Commented Mar 18, 2021 at 12:43

MT0 · Accepted Answer · 2021-03-18 10:31:08Z

1

Is it actually possible to create a parameterized View in Oracle SQL?

No, that is not possible.

What you are thinking of as parameters are the column aliases for the output:

CREATE VIEW FRUITSRESULT (fruitType, fruitNumber, FruitInfo) AS
SELECT fruit_col1,
       fruit_col2,
       fruit_col3
FROM   FRUITGARDEN;

Will output the values fruit_col1, fruit_col2 and fruit_col3 with the columns aliased to fruitType, fruitNumber, and FruitInfo (respectively). They are not, as you assumed, input parameters.

edited Mar 18, 2021 at 10:31

answered Mar 18, 2021 at 10:28

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Developer - Create View with parameters

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related