Procedure to create view

hi
i found this link
http://stackoverflow.com/questions/7712702/creating-a-view-using-stored-procedure
the procedure created , but has error always at statement of create view
what is the correct syntax for it ?
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
can you post your SQL scripts here?

13.1.20 CREATE VIEW Syntax
https://docs.oracle.com/cd/E17952_01/refman-5.1-en/create-view.html
NiceMan331Author Commented:
create or replace procedure cr_pur_bsh_sum(v_ac in varchar2,v_cntr in varchar2,v_dtf in date,v_dtt in date) as
begin
 exec 
'CREATE OR REPLACE VIEW PUR_BSH_sum as
select cost_center,acc_no,sup_no,SUP_NAME,sum(net_amount) Amount
from pur_bsh
where cost_center = v_cntr and acc_no = v_ac and inv_date
>= to_date(''' || to_char(v_dtf,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and inv_date <= to_date(''' || to_char(v_dtf,'YYYY-MM-DD') || ''',''YYYY-MM-DD'')
group by cost_center,acc_no,sup_no,SUP_NAME'/;
 

Open in new window

johnsoneSenior Oracle DBACommented:
Using SQL Server syntax in Oracle is going to bite you every time.  The link that you posted is clearly a SQL Server question, so it has a SQL Server answer.

I have no idea why you would be creating a view inside of a procedure.  Sounds like you have an issue with your process.  However, this would be the correct syntax:
create or replace procedure cr_pur_bsh_sum(v_ac in varchar2,v_cntr in varchar2,v_dtf in date,v_dtt in date) as
begin
 execute immediate
'CREATE OR REPLACE VIEW PUR_BSH_sum as
select cost_center,acc_no,sup_no,SUP_NAME,sum(net_amount) Amount
from pur_bsh
where cost_center = v_cntr and acc_no = v_ac and inv_date
>= to_date(''' || to_char(v_dtf,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and inv_date <= to_date(''' || to_char(v_dtf,'YYYY-MM-DD') || ''',''YYYY-MM-DD'')
group by cost_center,acc_no,sup_no,SUP_NAME';
end;
/
 

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NiceMan331Author Commented:
yes , now it is correct
and execute successfully also
but i got wrong data ,
am i under date format error again ?
johnsoneSenior Oracle DBACommented:
I don't see a date format issue.

The parameters are of date data type and the format masks on the conversions are the same.

If there is an issue, it would most likely be with the parameters that you are passing in.

Since you created an object, you can look in DBA_VIEWS to get the text of the view and see what it is doing.

I still don't see why you need a procedure to create a view.  It is a one time operation.
NiceMan331Author Commented:
I still don't see why you need a procedure to create a view.  It is a one time operation.
yes , you are correct , i will explain
if you see the view syntax , it is output :
cost_center,acc_no,sup_no,SUP_NAME,sum(net_amount) Amount
now i want to filter form block based on given both 4  criteria , including date
in form i can use default_where to filter based on both of : cost_center * acc_no
but i can't do it for the date because it is now not a column in the view
markger before , in similar question , suggested to have a procedure to create a table
based on selected data , then the block will based on that table
that works ok , but it takes time for drop the table then insert .. etc
slightwv also suggested to use dynamic function , it works well in SQL , but it not works in the form
hope i explained well
NiceMan331Author Commented:
ah , i found the mistake
i used same v_dtf in both date_from & date_to
johnsoneSenior Oracle DBACommented:
A view only needs to be created once.  A procedure isn't necessary.  The dates shouldn't be in the view definition.  You want to add a calculation to a query, then create the view.  Just once.  Leave off the entire where clause.  Then when selecting from the view, add the where clause.

The other suggestion would be to use a global temporary table.  Again, created only once.  The procedure will populate the table based on the parameters you pass in, then select from the table.  Should be very little overhead.
slightwv (䄆 Netminder) Commented:
johnsone,
Here is the very lengthy previous question mentioned above:
http://www.experts-exchange.com/questions/28689764/Oracle-Forms-Dynamic-Data-Source.html

GTTs were suggested.  Guess there is a time issue with them.
NiceMan331Author Commented:
You want to add a calculation to a query, then create the view.
can you exdplain more on how to do it in the following :
select cost_center,acc_no,sup_no,SUP_NAME,sum(net_amount) Amount
from pur_bsh
group by cost_center,acc_no,sup_no,SUP_NAME';

Open in new window

NiceMan331Author Commented:
sorry , i forget to say that < inv_date is a column of pur_bsh
johnsoneSenior Oracle DBACommented:
I guess I have to stop trying to help and just answer the question.

So, to recap, the answer to how to create the view within a procedure would be:
CREATE OR replace PROCEDURE Cr_pur_bsh_sum(v_ac   IN VARCHAR2, 
                                           v_cntr IN VARCHAR2, 
                                           v_dtf  IN DATE, 
                                           v_dtt  IN DATE) 
AS 
  does_not_exist EXCEPTION; 
  PRAGMA EXCEPTION_INIT(does_not_exist, -942); 
BEGIN 
    BEGIN 
        EXECUTE IMMEDIATE 'drop view pur_bsh_sum'; 
    EXCEPTION 
        WHEN does_not_exist THEN 
          NULL; 
        WHEN OTHERS THEN 
          RAISE; 
    END; 

    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW PUR_BSH_sum as select cost_center,acc_no,sup_no,SUP_NAME,sum(net_amount) Amount from pur_bsh where cost_center = v_cntr and acc_no = v_ac and inv_date >= to_date('''|| To_char(v_dtf, 'YYYY-MM-DD')|| 
    ''',''YYYY-MM-DD'') and inv_date <= to_date('''|| To_char(v_dtf, 
    'YYYY-MM-DD')|| 
    ''',''YYYY-MM-DD'') group by cost_center,acc_no,sup_no,SUP_NAME'; 
END; /

Open in new window

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
johnsoneSenior Oracle DBACommented:
Just be aware that using a view like this will mean that only one user can access the form at a time.  Each user will drop the view out from underneath the other users.
NiceMan331Author Commented:
ok , but when i try to exceut the procedure in the form it didn't work
is there any problem to execute it within the form ?
johnsoneSenior Oracle DBACommented:
A few comments ago in #40970829, you said it was working.
NiceMan331Author Commented:
yes , working in the pl_sql
i just try it in the form it not work
NiceMan331Author Commented:
But if you have better suggestion , pls do as you mention above
NiceMan331Author Commented:
My problem is in the date column which is not included in the view , Now I think to use one of the following 2 scenario :                                                               1- global database date variables ( I don't know if oracle database have it ) , so in the form , 2 non database items after validate them , will update the 2 global variables , and the syntax of the view will be something like : where inv_date between glob_date1 and glob_date2 .                                                          2- if the global variables not possible , then a table with 2 fields date format would be always updated via the form and automatically the view having its creteria                                          Any other suggestions ?
slightwv (䄆 Netminder) Commented:
>>Now I think to use one of the following 2 scenario

There are no global variables.  rows in a table likely won't work either.  I don't see how two people could run the form at the same time.

I've re-read the previous question and I think you are over-complicating this.  The previous question and this one started out with creating a view.

I do not see the need for the view in the first place.  Just use simple QBE on the base tables.  Forms has to be able to do QBE without all the trouble you are going through.

Even if you need the view, add the date columns and just don't use them on the form.

Forms cannot be as complicated as you are trying to make it.

From your other question did the DEFAULT_WHERE not do what you needed?

As you can tell from all your previous Forms questions, there aren't many Forms Experts here.

From the complexity of what you are trying to do, I would strongly suggest hiring a Forms Consultant for a day or two.
NiceMan331Author Commented:
My view is summing fields and grouping by , the date fields not included in the view , it don't see how I can include it in the view and the view still giving summary items
johnsoneSenior Oracle DBACommented:
Getting off topic of creating a view, but you could use a package to create session level variables that you can set and use elsewhere.  I'm not sure forms would be able to access those variables though.

The package would just have a header that defined the variables.  No package body would be necessary.
slightwv (䄆 Netminder) Commented:
>>My view is summing fields and grouping by ...

I understand that from the previous question.

My point is:  Why do you need the view in the first place?
NiceMan331Author Commented:
Can you give an example of that backage
NiceMan331Author Commented:
Slightwv , you mean why I need it in my previous question ?
slightwv (䄆 Netminder) Commented:
>>you mean why I need it in my previous question ?

Why do you need the view from both questions.  This one uses the view as well.

Why doesn't your form query the base tables directly?
NiceMan331Author Commented:
In this question , this view selecting summary records from another view : pur_bsh which is selecting columns from many tables
johnsoneSenior Oracle DBACommented:
Package example code:
create package cr_pur_bsh_sum_pkg as
   frm_dt date;
   to_dt date;
end;
/

Open in new window


PL/SQL example to set the variables:
begin
  cr_pur_bsh_sum_pkg.frm_dt := sysdate;
  cr_pur_bsh_sum_pkg.to_dt := sysdate+1;
end;
/

Open in new window

NiceMan331Author Commented:
Johnson , then how the view will use this packing in where section ?
slightwv (䄆 Netminder) Commented:
>>this view selecting summary records from another view : pur_bsh

Can you not query pur_bsh or the base tables directly?

You typically use views to hide complex logic or columns that you don't want users to be able to see.  I feel you are trying to create one fore per form and aren't including all the fields you need to accomplish it.

In doing so you are creating a lot of complexity and problems.

99.999% of the time when you have the "need" do perform dynamic DDL, your design is flawed.

Even if you get the whole dynamic view thing to work, you still haven't addresses the issue of multiple people using the Form at the same time.
johnsoneSenior Oracle DBACommented:
The question was how to create variables.  One way is a package.  If you want to use those in a where clause, then you need a way to return them.  Something like this:
CREATE OR replace PACKAGE cr_pur_bsh_sum_pkg 
AS 
  frm_dt DATE; 
  to_dt  DATE; 
  FUNCTION Ret_from 
  RETURN DATE; 
  FUNCTION Ret_to 
  RETURN DATE; 
END; 

/ 
CREATE OR replace PACKAGE BODY cr_pur_bsh_sum_pkg 
AS 
  FUNCTION Ret_from 
  RETURN DATE 
  AS 
  BEGIN 
      RETURN cr_pur_bsh_sum_pkg.frm_dt; 
  END; 
  FUNCTION Ret_to 
  RETURN DATE 
  AS 
  BEGIN 
      RETURN cr_pur_bsh_sum_pkg.to_dt; 
  END; 
END; 

/ 

Open in new window

Then in your where clause , include the function call like:

WHERE FROM_DATE >= CR_PUR_BSH_SUM_PKG.RET_FROM();
NiceMan331Author Commented:
The form not accepting select from multiple tables , it only accept table   or view , because of that I created a view
slightwv (䄆 Netminder) Commented:
>>The form not accepting select from multiple tables , it only accept table   or view

Did not know that.

Can you still not query pur_bsh directly?  I don't see where you join it to another table.

It doesn't look easy but this claims there is a way to do a join query that works in 6i:
http://www.oracle.com/technetwork/developer-tools/forms/documentation/blockonajoin-131860.pdf
NiceMan331Author Commented:
johnson
i created the packege & also adjusted the view as per it
but when comes to the form to set a value like this
	cr_pur_bsh_sum_pkg.frm_dt := to_date(to_char(:BASIC_BLOCK.D_from,'YYYY-MM-DD'));
  cr_pur_bsh_sum_pkg.to_dt := to_date(to_char(:BASIC_BLOCK.D_to,'YYYY-MM-DD'));

Open in new window

it sent error :
cannot directly access remote package variable or curosr
NiceMan331Author Commented:
slightwv
i read the documentation , it is clear , i can adjust it
but it seemed that you can only select table names & fields names
i'm not sure if it could accept select statement which is necessary for me to select group by query
2- also i note that where clause in the proberty pallet will contain join creteria of multi tables
the question now , how this will affect the command < default_where which i will use it later to set variable of date
slightwv (䄆 Netminder) Commented:
>>how this will affect the command

I don't know.  Neither of us that are participating are Forms people.

You never posted back on why you cannot just query the pur_bsh without having to create a new view on top of it.
NiceMan331Author Commented:
why you cannot just query the pur_bsh without having to create a new view on top of it.
look at its syntax posted earlier , it has to sum amount grouping by some columns
and i don't know if form block accept to summariz data based on a table or view
slightwv (䄆 Netminder) Commented:
slightwv (䄆 Netminder) Commented:
Here is another one:
Oracle Forms : Data Block Based on multiple Table
http://mahmoudoracle.blogspot.com/2012/06/oracle-forms-data-block-based-on.html
NiceMan331Author Commented:
date issue again
i created table param d_frm,d_to data
then i adjusted the view like this

            AND a.inv_date >= (select TO_DATE (dt_frm, 'YYYY-MM-DD')  from param)
        and a.inv_date <= (select to_date(dt_to,'yyyy-mm-dd') from param)

Open in new window

it gives no row select
i investigate it , i select
select TO_DATE (dt_frm, 'YYYY-MM-DD')  from param
it return
15/jun/17  (i entered it as  2015-06-17)
then i changed the type of those two fields to char
then it works well
is that process correct ( i mean to use char instead of date )?
johnsoneSenior Oracle DBACommented:
If you are going to put dates into the table that holds the parameters, then don't put a TO_DATE conversion on them when you select them out of the table.  It is totally unnecessary to convert a date to a date.

Is this a viable solution?  What if 2 people run that at the same time with different date ranges?
NiceMan331Author Commented:
Actually your solution about the backage is the best , but it not accept to have value within a form , the solution of skightwv also good to select directly from the tables , but I felt into a trouble of where ,  plus the necessity of selecting sum of items , I will try it next week it may be the best solution , regarding my solution , I'm the only user of the form , but it is also temporary solution until if skightwv said he will recommend to hire form expert , so we will see his best
slightwv (䄆 Netminder) Commented:
>>until if skightwv said he will recommend to hire form expert , so we will see his best

Yes, I suggested YOU hire a local consultant.  I don't have one to recommend.  If you cannot find someone local, Oracle has consultants for hire.  Just contact them to se about pricing and availability.

Forms has been around as a development tool for many years.  I cannot believe it is as restrictive and difficult as you are trying to make it.  I just don't have enough knowledge about it to help.  This is where hiring someone with several years of experience will help.  They can show you the correct ways to do what you need.
NiceMan331Author Commented:
Flow01 have wonderful knowledge , he found a lot of smart solutions for me in form itself , so if it is possible to invite him to participate here I will be thanking you
slightwv (䄆 Netminder) Commented:
Feel free to send him a message.  That is the only way I have to contact other members.
NiceMan331Author Commented:
slightwv
i tried today to build a block depending on the steps described in the 2 links you posted above
here , i would like to say that my previous comment were wrong , the form accept any select query
and not necessary from a table or view
the steps seemed easy , i did every thing as described , but the block query no records
i believe that is some setting missed or wrong , i reviewed all steps as per the description it is ok , but still no records in the form
i sent a message to flow01 , i don't know if he will assist
NiceMan331Author Commented:
there is some proceed now
the block return the records
remaining filters by creteria ( date)
i will try it again tomorrow
slightwv (䄆 Netminder) Commented:
I didn't think Forms was that restrictive.

Keep updating us.  I'll try to help where I can.
NiceMan331Author Commented:
thanx God
yes , now it is ok
wonderful solution slightwv
i admit that i been going wrong
and here i benefit alot
not only solve the problem , but also , the query performed faster by using select statement rather than view , and i don't know why
because what i noticed in SQL , the amount of time of querying data from a view is approximately same of querying from the tables
and last more question , here in this form i'm selecting data from another user
are there also a different in time between querying directly from same user ?
NiceMan331Author Commented:
a very small problem , but not a matter
when form based on a table or a view , you can use the wezard to make the relationship between it and another block in same form as master & detail
where you navigate between records in master block and the records in the details automatically filtered based on values on master block
here , where i built the block manually ( not by wizard )  , i built this relation manually by putting a code of filtering detail block in a trigger of : when_mouse_click on master
it works well
but when i put same code in : key_up & key_down , the key not works
any one have a solution for it , or i should open a new topic ?
johnsoneSenior Oracle DBACommented:
There is no difference in query performance based on the owner of the table.  Unless you are talking about a table in another database accessed via a database link.  The owner is simply name resolution.  You can specify the owner for tables you own as well.
NiceMan331Author Commented:
no , it is from user in same database
slightwv (䄆 Netminder) Commented:
>>any one have a solution for it , or i should open a new topic ?

I would open a new question.  Any Expert that knows Forms has likely already seen this question and for whatever reason, chosen to not post.

They may see and post in a new question.
NiceMan331Author Commented:
i opened a new topic
just for my knowledge
what is the   different between calling data by select query from table , and from a view
why it consume more time from view
slightwv (䄆 Netminder) Commented:
>>why it consume more time from view

It shouldn't be noticeable.  Which did you run first?  It is possible the first one had to read the blocks off of disk.  The second query took advantage of the blocks already being in memory.

You would have to generate the execution statistics to confirm this.

For example from sqlplus/SQL Developer:
set autoreace traceonly
select ... the rest of your select;
set autotrace off
NiceMan331Author Commented:
set autoreace traceonly

Open in new window

unknown command "sqlplus" - rest of line ignored.
slightwv (䄆 Netminder) Commented:
>>unknown command "sqlplus" - rest of line ignored.

The set command in the code block shouldn't generate the error you posted.

Did you enter the word sqlplus inside SQL Developer or sqlplus?  If so, don't.
NiceMan331Author Commented:
no , i just post the code as it is
slightwv (䄆 Netminder) Commented:
entering:
set autotrace traceonly

should not generate the error "unknown command "sqlplus" - rest of line ignored."

Please post a screen capture.
johnsoneSenior Oracle DBACommented:
Spelling counts:

set autotrace traceonly
NiceMan331Author Commented:
yes , error in spelling
slightwv (䄆 Netminder) Commented:
Yes. Sorry about the typo.
NiceMan331Author Commented:
i get the attached result of when i select from view , and when i select directly from the tables
but i didn't understand the statics
any how , when i select both of them sql plus , the timing is approximately same
no difference
view.png
select.png
johnsoneSenior Oracle DBACommented:
Those 2 plans are identical.  If you are seeing a difference in performance between those plans, then you are most likely seeing caching.
NiceMan331Author Commented:
may be
any how , i'm really thanking both of you alot
i got more knowledge through this topic
to be fair , you are entitle for 10 times of the points
is it fair for you to split the points equally ?
slightwv (䄆 Netminder) Commented:
>>but i didn't understand the statics

They are in the online documentation.  The main one I was looking at was the physical reads.  Since both of the stats are pretty much identical, I expect the times to be pretty much identical.

Querying a view should not have any noticeable impact on performance compared with the same query.

I cannot help with performance of the forms app.

That said:
WOW.  You are bringing over 100 meg and over 800,000 rows back?  I sure hope you or Forms is paging it in some way so it doesn't try to process all of it at once.
slightwv (䄆 Netminder) Commented:
>>is it fair for you to split the points equally ?

I have no problem with that.
NiceMan331Author Commented:
I have no problem with that.
thanx again
I cannot help with performance of the forms app.
in general , the site has powerful database experts , but is poor of forms experts , i think it is a time to invite more experts in that professions
please add your voice to the site management to future add more forms experts
thanx
slightwv (䄆 Netminder) Commented:
>>please add your voice to the site management to future add more forms experts

I will pass the information along.

We really don't get that many Forms questions.  Until you arrived we would only get a few a year if that.
NiceMan331Author Commented:
We really don't get that many Forms questions.  Until you arrived we would only get a few a year if that.

so i'm the trouble maker for the site , haha
NiceMan331Author Commented:
yes i go with the solution of slightwv
but i selected the solution of johnson as best solution because it is directly related to the question , if someone searching for topic of how to create view from procedure , that code is working perfectly , and they don't need to read the whole topic
thanx for both , johnson & slightwv
johnsoneSenior Oracle DBACommented:
Up until recently there have been very few forms questions that I have seen come through here.  There is one other user that I am aware of that is posting some forms related questions.  Unfortunately, there aren't a lot of forms experts here (markgeer and flow01 come to mind immediately, I believe HuaMinChen is also a forms person, and I'm sure there are others).  Honestly, I don't see a lot of people using the forms product.  As I recall from a long time ago, it is a pretty pricey product as well and people have found and use cheaper alternatives.
NiceMan331Author Commented:
If they are not using oracle forms , what they are using as an interface for their projects ? I don't think that database is enough to use it as end user product
johnsoneSenior Oracle DBACommented:
Custom code.  Typically Java or .Net.  If they want really easy, they go with Microsoft Access, this is a bad option, but I see more and more people using it.
slightwv (䄆 Netminder) Commented:
Just about everything these days is web-based.  I'm not sure what the last fat-client app that I saw was.

Even if Forms can do web-based, I'm not sure I would even consider it as a possibility with APEX out there.  I've heard some good things about APEX.

I'll second .Net but I'm biased because that is what we use and I know enough of it to be dangerous and develop some apps with it.

.Net and others also gives you things like fat-client if you want it.  You can also use it to develop middle-ware like web services.

Learn one language and use it everywhere.

Heck, Oracle now even allows .Net Stored Procedures in addition to Java and C++.

In a nutshell:
Why buy a platform when there are a few free options.  Some of those even top the marketplace, like .Net.
NiceMan331Author Commented:
honestly , i'm not that oracle programmer or developer ,
i have a little  knowledge about oracle, in addition to VBA access .
so , because of that you may see my questions with low level of programming sense or sometimes may be foolish questions
i'm working in a big company as financial analyzer
they are using a low level accounting software based on oracle
which is very poor in professional  useful reports
it has only standard reports and helpless
if i want to get my requested data  from that software , i have to run the available standard report , save it as csv , open it in ms excel , then i have to do some exercises in excel to display a useful data for me
those tasks i have to do it approximately daily bases , which is very hard & wasting my time in just generating the report where i can use my time to analyze the data
that lead me to learn something about oracle to directly see data i want , with zero exercises in excel
for example , the form i designed & mentioned in this topic , contain data from so many tables , useful for me to display main data in master block , then display sub & sub details to many levels  , it is really wonderful , yes it consume time in design but it  will save alot of my daily wasted time
in this regard , if you can advice  me for any professional analyzing tools to read data directly from oracle database else of oracle forms i will be appreciate
slightwv (䄆 Netminder) Commented:
Sounds to me like you need more of a reporting tool than forms.  A perfect fit would be a reporting tool with analytics built in.

Just about everyone has these types of products.  Using Oracle provided solutions isn't always the best way.  Look at Crystal Reports to see if it might work for you.  Experts-Exchange has some World Class Crystal Experts!!!


If you still want to customize stuff, I would really suggest you look at Application Express (APEX).  For one:  It is free where Forms isn't.  From what I hear, it is also pretty simple to get up and running quickly.



I would take a day and talk with your Oracle account team.  I think you will be impressed with what tools they have out of the box that will probably get you want you want without having to write code at all.

http://www.oracle.com/us/solutions/business-analytics/overview/index.html

I'm guessing you want to focus mainly in the Business Intelligence arenas.  Look at the Oracle OBIEE products.

I don't know all the products Oracle has but I've seen some high-level demos that have really impressed me.
flow01Commented:
For this kind of "dynamic" views a use a package with a set procedure and get procedures for each argument
create or replace package  arguments
is
procedure set(p_date1 date, p_date2 date);
function get_date1 return date;
function get_date2 return date;
end;
/

create or replace package body arguments

pck_date1 date;
pck_date2 date;
is
procedure set(p_date1 date, p_date2 date)
is
  pck_date1 := p_date1;
  pck_date2 := p_date2;
end;
function get_date1 return date
is
  return pck_date1;
end;
function get_date2 return date
is
  return pck_date2;
end;
end;
/
define your view using the package functions
 select .. from t1  where t1.date > arguments.get_date1
and
use the arguments.set procedure in the pre_query trigger (or whenever the controlvalue changes)

Be aware that using a pl/sql function in an sql-statement like a view is not the best thing for performance.
NiceMan331Author Commented:
thanx for the advice
johnson already mentioned that package before , but i face a problem to execute the procedure inside the form which is going to assign a value to the function , it fail
flow01Commented:
If you can test your procedure succesfully outside of forms there is no reason it will fail within forms.
Be sure to pass the right format (the default date format can differ within forms and another application you use to approach your oracle datebase).
Be sure the oracle-user that is logged in can also "reach" the procedure (grants, public synonym or qualifying the procedure with the schema-owner of the procedure). Same goes for the view.
NiceMan331Author Commented:
ok , i will do my best to configure it
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.