Re: DBMS_SQL Dynamically creating views.

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 [Quoted] 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

[Quoted]     I have not do ProC in awhile and do not recall the syntax. Here are the steps

[Quoted] [Quoted]         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 Sun Aug 30 1998 - 00:00:00 CEST