From daemon Thu Feb 29 16:13:46 1996 Received: from ccvm.sunysb.edu by alice.jcc.com; (5.65v3.0/1.1.8.2/01Aug94-0142PM) id AA04497; Thu, 29 Feb 1996 16:13:41 -0500 Message-Id: <9602292113.AA04497@alice.jcc.com> Received: from CCVM.SUNYSB.EDU by ccvm.sunysb.edu (IBM VM SMTP V2R3) with BSMTP id 8992; Thu, 29 Feb 96 15:52:50 EST Received: from CCVM.SUNYSB.EDU (NJE origin LISTSERV@SBCCVM) by CCVM.SUNYSB.EDU (LMail V1.2a/1.8a) with BSMTP id 3319; Thu, 29 Feb 1996 15:52:43 -0500 Date: Thu, 29 Feb 1996 15:51:43 EDT Reply-To: "ORACLE database mailing list." Sender: "ORACLE database mailing list." From: Chid Kollengode-1 Subject: Re: How to use dynamic sql? - Where's some documentation ? X-To: Susanne Stolpe X-Cc: Multiple recipients of list ORACLE-L To: Multiple recipients of list ORACLE-L 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@rz.ruhr-uni-bochum.de