CREATE VIEW through pl/sql WITH parameters

Hi,

I came back with my last question. The view could be created now but without parameters sent as procedure arguments.

I would have liked to see running something like (it's just a simplified form):

CREATE OR REPLACE PROCEDURE sql_test (emp_name) AS
BEGIN
EXECUTE IMMEDIATE
'create or replace view ACTIVES as select * from salary
where emp like :e_name||''%'' '
USING emp_name ;
EXCEPTIONS
when others then
dbms_output.put_line(SQLERRM);
END sql_test;
/

But it seems do not be accepted something like :e_name in the create view.

The error message is
ORA-01027: bind variables not allowed for data definition operations

Have you found a way to avoid this error message and to send the parameter for creating the view in this way?

Thank you very much,

A.
kitiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anand_2000vCommented:
CREATE OR REPLACE PROCEDURE sql_test (emp_name) AS
BEGIN
EXECUTE IMMEDIATE
'create or replace view ACTIVES as select * from salary
where emp like '''||emp_name||'%'' ' ;
EXCEPTIONS
when others then
dbms_output.put_line(SQLERRM);
END sql_test;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MathiasMagnussonCommented:
The reason is that your are creating an object and to do that you need to give Oracle the full text. The bind variable is only useful for SQL you execute. You are not executing that select, you are defining a view that uses it.
jpkempCommented:
You should never need to create views from within PL/SQL - especially in this example the view name is static, so why create it in PL/SQL when you can create a view once that will serve the same need?

If you create a view like this:

CREATE OR REPLACE VIEW ACTIVES AS SELECT * FROM SALARY;

You can then apply predicates dynamically from anywhere in your application, e.g.

SELECT * FROM ACTIVES WHERE EMP LIKE 'blabla%';

The cost-based optimizer will usually "push" the predicate "EMP LIKE :a" down to the view definition; so it won't do anything silly like running the SELECT * before applying the predicate.

Jeff
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

kitiAuthor Commented:
Thanks for thinking at my problem, but in fact it is more complicated than I explain and that's way I need to create views in this way, with parameters.

I have a query that is based on 3 views (running in production now but through sql*plus):

- the first one with 2 parameters

- the second one with 3 parameters, based on first view and another 3 tables

- the third one is the most complicated, matrix query, based exclusively on the second one!!

The final select is based on all this.

First I tried to put all in a huge query, that is not impossible, but it doesn't look nice and clear (and not profesional of course).

The query runs very well in sql*plus and fast, but I need to run it in Crystal Report9 as a stored procedure.

When I put that huge query in a stored procedure the error message was PLS-00801: internal error [79598], so that's why I tried to create little pieces one by one and to reconstruct the query exacly as it was done in sql*plus.

Thanks for any other ideas about solving it!

A.
anand_2000vCommented:
If you reference the cursor type instead of the cursor variable, you get PLS-00801: internal error. In which case, define cursor variable and refer them in the procedure/functions,
jpkempCommented:
kiti, it doesn't matter how complex the queries are, you never need to create views dynamically.
kitiAuthor Commented:
Hi everybody,

I have just got a solution for my problem. It was easier finally, as usual :)

So anand_2000v's first solution is good for created views in pl/sql with parameters, but with a little modification:

PL/SQL doesn't understand too many single quotation mark one after another, and that gave me much trouble.

So the line:
EXECUTE IMMEDIATE
'create or replace view ACTIVES as select * from salary
where emp like '''||emp_name||'%'' ' ;

is much clear for compiler and for everybody like this:

EXECUTE IMMEDIATE
'create or replace view ACTIVES as select * from salary
where emp like '||chr(39)||emp_name||'%'||chr(39) ' ;

And this was the key.
After that I created a package and a package body with three procedure for create dynamical the 3 views.
Finally I called all these in a final stored procedure adding create another select based on this views, as I needed, for Crystal Reports.

Unfortunately because the query from stored procedure is very, very long, the pl/sql compiler from Oracle 8i could not create it and the error message PLS-00801: internal error [79598] (system bug!!) stopped me to do something in a normal way. It's possible to be okay in Oracle9.

Thank you all,

A.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.