DBMS_SQL Dynamically creating views.

From: <sysdev_at_adpsystems.mb.ca>
Date: 1998/08/27
Message-ID: <6s4ega$71n$1@nnrp1.dejanews.com>#1/1

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.
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 Thu Aug 27 1998 - 00:00:00 CDT