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

From: Chid Kollengode-1 <Chid_Kollengode-1_at_SBPHRD.COM>
Date: Thu, 29 Feb 1996 15:51:43 EDT
Message-Id: <9602292113.AA04497@alice.jcc.com>

Sussane,
The two approaches differ in a subtle but important point.

Both approaches construct the SQL statement that needs to be submitted to the database. The point is where you
want to construct the SQL.

In the DBMS_SQL case, the required SQL statement could be constructed at the client site, say in the forms you have a situation where you want to extract the way the user constructed the query (by entering values in the fields in enter query mode just to take as an example) and then submit that to the server to get a count, then you can execute the query, get the predicate from the system.last_query and then form the statement to submit.

There might be some whitepaper on this, I am not sure.

The other approach that you mentioned in the C program occurs basically at the server side. As the C program runs in the server, you are constructing the SQL statement there. So essentially, both give the same end result, the usage depends on where you create them.

This is my understanding, I have used both these scenarios. If anyone has more ideas on this please share them on the network.

Chid

---

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 Thu Feb 29 1996 - 16:13:46 CST