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

Susanne,

Many people are confused about the various implemenations of SQL.

EXEC SQL INCLUDE SQLCA; is used in C programming and the instructions are included in a free standing program.

CREATE or REPLACE PROCEDURE make_preimm_view is used to create a procedure or program inside the database.

To help clarify:

SQL is the base language that is used to access the database.

PL/SQL is Oracle's procedural language (like ADA) wrapped around SQL. It adds ADA-like programming constructs.

SQL*Plus adds some external NON-SQL commands to SQL and PL/SQL. Commands like formatting columns, setting page sizes, etc.

Pro*C and other Pro* languages are used to add Oracle's SQL to external programs.

You will find lots of documentation in the Windows Help files that come with Oracle's Window based products.

You might check www.ioug.org for a list of publications.

Tom

At 05:00 PM 2/29/96 +0100, you wrote:
>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 Sat Mar 02 1996 - 00:11:39 CST