Re: How to use dynamic sql? - Where's some documentation ?

Hi

    You can find the documentation about dynamic sql in Oracle magzine march/April . To something the dynamic sql is available ony in version 7.1 onwards. It is a package that can be used in plsql code. I dont know wether it can be used with pro*C or not.

Best of Luck

Regards

Sushil

------------------------------------------Orignal Query
Date:          Thu, 29 Feb 1996 17:00:26 +0100
Reply-to:      "ORACLE database mailing list." <ORACLE-L_at_CCVM.SUNYSB.EDU>
From:          Susanne Stolpe <Susanne.Stolpe_at_RZ.RUHR-UNI-BOCHUM.DE>
Subject:       How to use dynamic sql? - Where's some documentation ?
X-To:          ORACLE-L_at_ccvm.sunysb.edu
To:            Multiple recipients of list ORACLE-L <ORACLE-L_at_CCVM.SUNYSB.EDU>

Hi to all,
eventually I'll try now to get familiar with dynamic sql. Filing through meters of oracle and sql guides I found at last a Chapter 'using Dynamic SQL' in the 'programmer's guide to the Oracle precompilers V 1.5'. The examples I found there were quite different to those I've already seen in some mails which had been sent to this list.

e.g. in this chapter of the oracle guide they used dynamic sql with statements as :

EXEC SQL INCLUDE SQLCA;
display ...
...
EXEC SQL CONNECT :name ....
set upd_stmt = 'Update tab SET val = :v '; ...
EXEC SQL PREPARE sql_stmt FROM :upd_stmt; ...
EXEC SQL EXECUTE sql_stmt USING :value; ...
EXEC SQL COMMIT WORK RELEASE ;
...

which is very different from something like :

CREATE or REPLACE PROCEDURE make_preimm_view
> > ( this_provider IN person.primary_provider%TYPE) IS

> > c1 INTEGER;
> > rc INTEGER;
> > str1 VARCHAR2(800);
> >
> > BEGIN
> > str1 := 'CREATE OR REPLACE VIEW test as select * from providers; ';
> > c1 := dbms_sql.open_cursor;
> > dbms_sql.parse(c1,str1,dbms_sql.native);
> > rc := dbms_sql.execute(c1);
> > dbms_sql.close_cursor(c1);
> > END;
which was a code from another mail sent to the list.

Where can I find something about dynamic sql as it is used in the second example? I'm a bit at a loss now.

Thanks to everybody who can help to end my dynamic-sql-confusion, TIA,
best regards

Susanne Stolpe

Ruhr-Universitaet Bochum
Medizin. Fakultaet
Inst. f. Biomathematik & Informatik
Bochum, FRG
e-mail : Susanne.stolpe_at_rz.ruhr-uni-bochum.de Received on Fri Mar 01 1996 - 00:15:09 CST