DBMS_SQL Dynamically creating views.

DBMS_SQL Dynamically creating views.

Post by Srinivas Chikk » Fri, 28 Aug 1998 04:00:00



Aren't you missing the bind and fetch phases ?
Read any documentation for Dynamic SQL stages.

Hope this helps.
Srinivas.Chikka

Quote:>What's wrong here?
>(END_REF is irrelevant. )

>CREATE OR REPLACE PROCEDURE TEST_DBMS_SQL
>(START_REF IN VARCHAR,
>END_REF IN VARCHAR ) IS
>CURSOR_NAME INTEGER;
>ROWS_PROCESSED INTEGER;
>AIR_COUNT INTEGER;
>SQL_STRING VARCHAR(240);
>BEGIN

>CURSOR_NAME := DBMS_SQL.OPEN_CURSOR;
>SQL_STRING := 'SELECT COUNT(*) INTO :AIR_COUNT FROM ' || START_REF ;

>DBMS_SQL.PARSE (CURSOR_NAME, SQL_STRING, DBMS_SQL.V7);
>ROWS_PROCESSED := DBMS_SQL.EXECUTE(CURSOR_NAME);

>DBMS_OUTPUT.PUT_LINE ('START REF IS:  ' ||  START_REF );
>DBMS_OUTPUT.PUT_LINE ('ROWS PROCESSED IS:  ' || ROWS_PROCESSED );
>DBMS_OUTPUT.PUT_LINE ('AIR_COUNT IS:  ' || AIR_COUNT );

>DBMS_SQL.CLOSE_CURSOR( CURSOR_NAME );
>END TEST_DBMS_SQL;

 
 
 

DBMS_SQL Dynamically creating views.

Post by Yves Roy, IP » Wed, 02 Sep 1998 04:00:00


Hi Rodger,

Here's a way to have a dynamic view:

Use package variable in your where clause of the view.
Then before selecting the view, initialize it.

ex.     create view  daily_sales as
                select * from  your_sales_tables
                where  sales_date = package_x.global_variable_date;

Before selecting the view...     in a pl/sql or a C program
initialyze it     like    package_x.global_variable_date = '1998-08-28'

then  select * from   daily_sales ;
will give you the sales of the '1998-08-28'.

Hope this help.
Yves.


 
 
 

DBMS_SQL Dynamically creating views.

Post by Helmut Hah » Wed, 02 Sep 1998 04:00:00



> Hi Rodger,

> Here's a way to have a dynamic view:

> Use package variable in your where clause of the view.
> Then before selecting the view, initialize it.

> ex.     create view  daily_sales as
>                 select * from  your_sales_tables
>                 where  sales_date = package_x.global_variable_date;

> Before selecting the view...     in a pl/sql or a C program
> initialyze it     like    package_x.global_variable_date = '1998-08-28'

> then  select * from   daily_sales ;
> will give you the sales of the '1998-08-28'.

> Hope this help.
> Yves.


I read this thread and your solution sounds very good (also for my problem).
The only question is about the isolation in the package variable. Is it the same value
for a session although another session changed the variable, but it is still used by
the first session?

Helmut

 
 
 

DBMS_SQL Dynamically creating views.

Post by Helmut Hah » Wed, 02 Sep 1998 04:00:00



> Hi Rodger,

> Here's a way to have a dynamic view:

> Use package variable in your where clause of the view.
> Then before selecting the view, initialize it.

> ex.     create view  daily_sales as
>                 select * from  your_sales_tables
>                 where  sales_date = package_x.global_variable_date;

> Before selecting the view...     in a pl/sql or a C program
> initialyze it     like    package_x.global_variable_date = '1998-08-28'

> then  select * from   daily_sales ;
> will give you the sales of the '1998-08-28'.

> Hope this help.
> Yves.


I read this thread and your solution sounds very good (also for my problem).
The only question is about the isolation in the package variable. Is it the same value
for a session although another session changed the variable, but it is still used by
the first session?

Helmut

 
 
 

DBMS_SQL Dynamically creating views.

Post by Valery A. Soroki » Thu, 03 Sep 1998 04:00:00


Each session has its own package variable values

I hope this will helps.


[snip]
> I read this thread and your solution sounds very good (also for my problem).
> The only question is about the isolation in the package variable. Is it the same value
> for a session although another session changed the variable, but it is still used by
> the first session?

> Helmut

--
Valery A. Sorokin
ProSoft, Russia, Moscow, Information Systems Division
Phone: +7 (095) 234 0636 (6 lines) FAX: +7 (095) 234 0640

http://www.dd.ru
 
 
 

DBMS_SQL Dynamically creating views.

Post by Thomas Ky » Thu, 03 Sep 1998 04:00:00



(if that email address didn't require changing)



>> Hi Rodger,

>> Here's a way to have a dynamic view:

>> Use package variable in your where clause of the view.
>> Then before selecting the view, initialize it.

>> ex.     create view  daily_sales as
>>                 select * from  your_sales_tables
>>                 where  sales_date = package_x.global_variable_date;

>> Before selecting the view...     in a pl/sql or a C program
>> initialyze it     like    package_x.global_variable_date = '1998-08-28'

>> then  select * from   daily_sales ;
>> will give you the sales of the '1998-08-28'.

>> Hope this help.
>> Yves.

>I read this thread and your solution sounds very good (also for my problem).
>The only question is about the isolation in the package variable. Is it the same value
>for a session although another session changed the variable, but it is still used by
>the first session?

>Helmut

Each session has its own package state, session 1 can NEVER see session 2's data
in a package.  This method could be called a 'parameterized view'.

To create a parameterized view, you would do the following:

create package my_params
as
    pragma restrict_references( my_params, wnds, rnds, wnps, rnps );

    function get_param1 return number;
    pragma restrict_references( get_param1, wnds, rnds, wnps );

    (other get functions here with pragmas)

    param1    number;

    (other params here with appropriate types)
end;
/

create package body my_params
as

function get_param1 return number is begin return param1; end;

(other functions here)
end;
/

And then in SQL*Plus for example you would:

SQL> create view demo as select * from emp where empno = ( select
my_params.get_param1 from dual );

SQL> exec my_params.param1 = 7369
SQL> select * from demo;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
DEPTNO      SALES
---------- ---------- --------- ---------- --------- ---------- ----------
---------- ----------
      7369 SMITH      CLERK           7903 17-DEC-80        800
20

Note that you need to use a FUNCTION to get the value since SQL will only
recognize pl/sql functions, not pl/sql variables.  Also, I like to use (select
F(x) from dual ) whenever possible instead of simply f(x).  This will make it so
the pl/sql function is evaluated ONCE per query instead of sometimes once per
ROW per query (slower....)

Thomas Kyte

Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

 
 
 

1. dynamically creating view

friends,
iam trying to create a view dynamically using query
analyser in sql70. But it returns error. The sytax and
objects are fine.



create View ViewTest as
select * from processed_batch
GO'

Server: Msg 214, Level 16, State 2, Procedure sp_execute,
Line 6

expected by procedure.

Thanks in advance.

2. Text Extenders

3. dynamically creating remote view

4. RMI Question - URGENT

5. Dynamically Calling PLSQL Procedures via DBMS_SQL

6. How to get database names without login in as DBA

7. dynamically executing stored procedures using dbms_sql

8. NextRecordset problem

9. DBMS_SQL: How to fetch from dynamically built SELECT?

10. Creating a local view using CREATE SQL VIEW

11. creating a cursor using a dynamically created table name

12. Stuck: Parameter Views and/or DBMS_SQL