Tags

links:
http://docs.oracle.com/cd/B10500_01/appdev.920/a97269/toc.htm
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-proc.html#transactions
http://programmingexamples.wikidot.com/pro-c-tutorial
http://docs.oracle.com/cd/B10500_01/appdev.920/a97269/pc_13dyn.htm#688
http://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems034.htm
http://ora-pl-sql.blogspot.com/2009/02/exec-sql-at.html

summary:
– EXEC SQL …
– “:hostvariable” to use host var in EXEC SQL statements
– 4 methods of dynamic sql
– m#1, no query no host vars
– m#2, no query with known number of host vars
– m#3, query with known number of host vars
– m#4, query with “unknown” number of host vars
– m#4 (Oracle mode) requires SQLDA sql descriptor area, or select descriptor.
– m#4 (ANSI mode) uses ALLOCATE DESCRIPTOR… instead of SQLDA
– Host variables in the INTO clause of a SELECT or FETCH statement are called “output” host variables because they hold column values output by Oracle. Oracle assigns the column values to corresponding output host variables in the INTO clause.
– All other host variables in a SQL statement are called input host variables because your program inputs their values to Oracle.
– SQLCA = SQL communication area

– cursor example:
EXEC SQL PREPARE stmt1 FROM :stmt;
EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
EXEC SQL OPEN cur1 USING :inputval1;
EXEC SQL FETCH cur1 INTO : outval1 INDICATOR :ind1, : outval2 INDICATOR :ind2;
EXEC SQL CLOSE cur1;

– simple example:
EXEC SQL PREPARE stmt2 FROM :stmt;
EXEC SQL EXECUTE stmt2 USING :inputval1;

– connection example:
EXEC SQL CONNECT user
IDENTIFIED BY password
AT conn1
USING databasename;
then,
EXEC SQL AT conn1
SELECT COUNT(*) FROM sometable;

Advertisements