Re: DBMS_SQL Dynamically creating views.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/08/30
Message-ID: <35fce5a2.23166992@192.86.155.100>#1/1

A copy of this was sent to Michael Krolewski <vandra_at_u.washington.edu> (if that email address didn't require changing) On Sun, 30 Aug 1998 14:12:47 -0700, you wrote:

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

wow, pretty harsh don't you think.

>introduction of any sql statement. The idea is to create a statement,
>then bind it.
>

not really, he wants to do DDL, DDL doesn't have bind variables. The way to do it is simply create the statement and then execute it -- period. There are no cursors needed to do this, no binding -- its a simply matter of putting the create view into a string and executing it.

the code would look like:

---

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

void process( int rows )
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR stmt[512];
EXEC SQL END DECLARE SECTION;     strcpy( stmt.arr, "create or replace view foo_view " );     strcat( stmt.arr, "as " );
    sprintf( stmt.arr+strlen(stmt.arr),

            "select * from all_users where rownum < %d", rows );     stmt.len = strlen( stmt.arr );

    EXEC SQL EXECUTE IMMEDIATE :stmt;

    /* the rest of this code just dumps the view to show that it works */     EXEC SQL DECLARE C1 CURSOR FOR SELECT USERNAME FROM FOO_VIEW;     EXEC SQL OPEN C1;
    for( ;; )
    {

        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH C1 into :stmt;
        printf( "%.*s\n", stmt.len, stmt.arr );

    }
 EXEC SQL CLOSE C1;
}

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50] = { strlen( "tkyte/tkyte" ), "tkyte/tkyte" }; EXEC SQL END DECLARE SECTION;     EXEC SQL CONNECT :oracleid;

    process( 5 );

    printf( "--------------------\n" );

    process( 10 );

    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

---

that for example runs the create or replace view statement 2 times with 'different' inputs.

>
> I have not do ProC in awhile and do not recall the syntax. Here are the
>steps
>

these are the wrong 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
>

[snip]

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

Thomas Kyte
tkyte_at_us.oracle.com
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.

Received on Sun Aug 30 1998 - 00:00:00 CDT