Re: DBMS_SQL Dynamically creating views.

A side note.

Dynamic creation of view/table will require user(s) be given direct privilege (Create table ,create view) not assigned through a role.

HTH
Dave Fowler
Oracle Developer

Michael Krolewski wrote in message
<35E9C04F.31063EAE_at_u.washington.edu>...
>
>
>sysdev_at_adpsystems.mb.ca wrote:
>
>> Hello all,
>>
>> I am writing a program in a ProC program.
>> I want to dynamically create a view.
>> The dates in the WHERE clause would change each time
>> the program would run.
>>
>> ProC will not allow me to put variables in the DDL
>> (Data Definition Language) statements, such as CREATE VIEW.
>
>Obviously you are not thinking. The parse statement allow the
>introduction of any sql statement. The idea is to create a statement,
>then bind it.
>
>char sqlStmt[2000];
>
>
> len = sprintf(sqlStmt," create or replace view X ");
> len = sprintf(sqlStmt + len, " select ..... ");
> ... other statements
> len = sprintf(sqlStmt + len, " stopDate between
>to_date('%s','YYYYMMDD') ", dateString);
>
>
> I have not do ProC in awhile and do not recall the syntax. Here are
 the
>steps
>
> convert this buffer into a varchar
> assign the varchar sql statement buffer to a cursor
> since no other variables are needed, parse the cursor
> execute the cursor.
>
> You can always create new and different sql statements in ProC. It
 is
>one
>of it major advantages.
>
>Mike Krolewski
>
>
>> Thus, I thought I might be able to use DBMS_SQL instead.
>>
>> However, I can't seem to get it to work very well either.
>> The first test was whether I could just print the variables
>> that were past in.
>> The other test was whether I could use the variable past in
>> to specify the table.
>>
>> Is there the equivilent of a macro that could be used?
>>
>> 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;
>>
>> BEGIN
>> test_dbms_sql( 'AIR', 'TESTING');
>> END;
>>
>> Output:
>>
>> Start Ref is: AIR
>> Rows processed is: 0
>> AIR_COUNT is:
>>
>> Is it possible to use DBMS_SQL to do what I want?
>>
>> Rodger
>>
>> P.S.
>> My ISP is really bad with the newsgroups.
>> They post late, and delete early. Emails to:
>> sysdev_at_adpsystems.mb.ca
>> are greatly appreciated.
>>
>> -----== Posted via Deja News, The Leader in Internet Discussion
 ==-----
>> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member
 Forum
>
>
>
Received on Mon Aug 31 1998 - 00:00:00 CEST