Oracle數(shù)據(jù)庫(kù)開(kāi)發(fā)

1、Oracle數(shù)據(jù)庫(kù)開(kāi)發(fā)(一).Windows下配置使用ProC20070606草木瓜一、提要    ProC是Oracle數(shù)據(jù)庫(kù)提供的開(kāi)發(fā)接口，支持多種語(yǔ)言。ProC雖然調(diào)試維護(hù)起來(lái)十分不便，但是依靠其結(jié)構(gòu)化清晰的嵌入式SQL，也成為了C/C+語(yǔ)言訪問(wèn)控制數(shù)據(jù)庫(kù)的常用方式。    本文首先介紹Windows平臺(tái)下，使用Visual C+ 6.0 和 Visual C+ .Net配置ProC的開(kāi)發(fā)環(huán)境。二、數(shù)據(jù)庫(kù)環(huán)境    確認(rèn)安裝了組件，Oracle - Application Development -

2、 Pro C-C+ 。有時(shí)在Oracle自帶的組件工具中顯示已安裝，但在文件菜單和文件夾無(wú)相應(yīng)組件，解決方法就是重裝。三、使用示例文件   sqlmain.c (擴(kuò)展名是無(wú)所謂的)   -   #include "sqlca.h"   #include <stdio.h>   #include <string.h>   #include <stdlib.h>     /顯示意外錯(cuò)誤

3、   void sql_error(char *msg)        printf("n%s %sn", msg,(char *)sqlca.sqlerrm.sqlerrmc);     EXEC SQL ROLLBACK RELEASE;     exit(0);        void main()      EXEC SQL INCLUDE

4、 sqlca;   EXEC ORACLE OPTION (RELEASE_CURSOR = YES);   EXEC SQL WHENEVER SQLERROR DO sql_error(" <ERROR> ");     EXEC SQL BEGIN DECLARE SECTION;   VARCHAR oraCN30;   EXEC SQL END DECLARE SECTION;     strc

5、py(oraCN.arr,"system/sysunixdb");   oraCN.len = strlen(oraCN.arr);   oraCN.arroraCN.len='0'     EXEC SQL CONNECT :oraCN;   printf("n OK Connected! ");          使用Pro C-C+編譯成C語(yǔ)言文件，成功后

6、會(huì)提示綠色的""：     source.c (文件比較長(zhǎng)，不在此列出)三、Visual C+ 6.0    1.New Project -> Win32 Console Application 即新建一個(gè)空控制臺(tái)項(xiàng)目        2.加入編譯后的source.c文件        3.在 Tools -> Options -> Directories 添加ProC的

7、lib目錄和include目錄。         本機(jī)目錄使用如下：          Library Files           E:ORACLEORA92PRECOMPLIB          Include Files      

8、    E:ORACLEORA92PRECOMPPUBLIC        4.在 Project -> Settings -> Link 的 Object/Library modules 中添加     ProC的鏈結(jié)文件orasql9.lib，VC會(huì)在Project Options自動(dòng)更新。        5.編譯運(yùn)行       

9、 輸入錯(cuò)誤密碼提示：     <ERROR> ORA-01017: invalid username/password; logon denied      連接成功提示：     OK Connected!四、Visual C+ .Net    1.新建項(xiàng)目 -> Visual C+ -> Win32 -> 控制臺(tái) 即新建一個(gè)空控制臺(tái)項(xiàng)目      

10、60; 2.加入編譯后的source.c文件        3.工具 -> 選項(xiàng) -> 項(xiàng)目 -> VC+ 目錄 添加ProC的lib目錄和include目錄。         本機(jī)目錄使用如下：          庫(kù)文件          E:ORACLEORA92PRECOMPLIB&#

11、160;         頭文件          E:ORACLEORA92PRECOMPPUBLIC            4.選中項(xiàng)目 -> 屬性 -> 配置屬性 -> 鏈結(jié)器 -> 輸入 -> 附加依賴項(xiàng) 添加     ProC的鏈結(jié)文件orasql9.lib  

12、;         5.編譯運(yùn)行        輸入錯(cuò)誤密碼提示：     <ERROR> ORA-01017: invalid username/password; logon denied      連接成功提示：     OK Connected!       

13、 五、總結(jié)    本文只是使用了數(shù)據(jù)庫(kù)連接的簡(jiǎn)單例子，具有實(shí)際參考意義的例子可參看：    ProC動(dòng)態(tài)SQL示例(第1,2,3種方法)Oralce數(shù)據(jù)庫(kù)開(kāi)發(fā)(二).Linux下配置使用ProC20070606草木瓜一、提要    上文簡(jiǎn)單介紹了Windows下ProC配置開(kāi)發(fā)，這次我們使用Linux平臺(tái)再次配置Oracle ProC開(kāi)發(fā)環(huán)境(RedHat Linux 9 + Oracle 92)。    ORACLE數(shù)據(jù)庫(kù)開(kāi)發(fā)(一).Windows下配置使用Pro

14、C和ORACLE數(shù)據(jù)庫(kù)開(kāi)發(fā)(二).Linux下配置使用ProC這兩篇文章的目的只是做一些基礎(chǔ)介紹，至于Oracle ProC編譯參數(shù)以及Linux下的ProC Makefile相關(guān)內(nèi)容，將再后續(xù)文章逐步引入。    一言以弊之，先易后難。二、數(shù)據(jù)庫(kù)環(huán)境    與Windows下十分類似，首先確認(rèn)安裝了組件，Oracle - Application Development - Pro C-C+ 。安裝后會(huì)在$ORACLE_HOME/bin生成相應(yīng)可執(zhí)行文件，在$ORACLE_HOME/precomp/demo/proc下也會(huì)生成一些ma

15、kefile文件和示例。三、示例文件    main.pc     -   #include "sqlca.h"   #include <stdio.h>   #include <string.h>   #include <stdlib.h>     void sql_error(char *msg)       prin

16、tf("n%s %sn", msg,(char *)sqlca.sqlerrm.sqlerrmc);    EXEC SQL ROLLBACK RELEASE;    exit(0);        int main()      EXEC SQL INCLUDE sqlca;   EXEC ORACLE OPTION (RELEASE_CURSOR = YES);   EXEC SQL WHEN

17、EVER SQLERROR DO sql_error(" <ERROR> ");     EXEC SQL BEGIN DECLARE SECTION;   VARCHAR oraCN30;   EXEC SQL END DECLARE SECTION;     strcpy(oraCN.arr,"system/managerlinuxdb");   oraCN.len = strlen(oraCN.ar

18、r);   oraCN.arroraCN.len='0'     EXEC SQL CONNECT :oraCN;   printf("n OK Connected! ");     return 0;          代碼其實(shí)是Windows的原版。    四、編譯運(yùn)行    無(wú)需修改任何

19、參數(shù)文件，即安裝后直接創(chuàng)建main.pc，執(zhí)行如下命令：       $ proc parse=none iname=main.pc     Pro*C/C+: Release .0 - Production on Thu Jun 7 14:17:05 2007     Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.     S

20、ystem default option values taken from: /home/ora/ora9/oracle/precomp/admin/pcscfg.cfg     $ gcc -g -o main main.c -I/home/ora/ora9/oracle/precomp/public -L/home/ora/ora9/oracle/lib -lclntsh     $ ./main      <ERROR> ORA-12541: T

21、NS:no listener       成功編譯運(yùn)行，這里也可以使用ProC動(dòng)態(tài)SQL示例(第1,2,3種方法)一文中的示例，將/注釋全部替換為空，即可編譯。        不過(guò)會(huì)有告警提示：   /tmp/ccC7E6qe.o(.text+0xea): In function db_connect':   /home/ora/develop/src/db.c:385: the gets' function is da

22、ngerous and should not be used.        這個(gè)是由于使用了gets函數(shù)所致，見(jiàn)gets的man手冊(cè)：    BUGS       Never use gets(). Because it is impossible to tell without knowing the       data in advance how many characters

23、 gets() will read, and because       gets() will continue to store characters past the end of the buffer, it       is extremely dangerous to use. It has been used to break computer       security. Use fgets() i

24、nstead.       It is not advisable to mix calls to input functions from the stdio       library with low - level calls to read() for the file descriptor asso-       ciated with the input stream; the results will

25、 be undefined and very       probably not what you want.           要解決這個(gè)問(wèn)題，可以使用scanf函數(shù)替換gets，獲取屏幕輸入。如 scanf("%s",cmd)。Oracle數(shù)據(jù)庫(kù)開(kāi)發(fā)(三).Pro*C/C+的編譯參數(shù)2008/07/30 08:21 P.M.Oracle Database Development (3). Introduce to Pr

26、o*C/C+ Precompiler OptionsVert MelonJun 7,20071.PrefaceNow we have already known about the Pro*C/C+ Development Environment in Windowsor Linux, but it is not enough.Anyway, esaier said than done, let's start our trip.2.What is the PrecompilerDuring precompilation, Pro*C/C+ generates C or C+ code

27、 that replaces the SQL statements embedded in your host program. The generated code contains data structuresthat indicate the datatype, length, and address of host variables, as well as other information required by the runtime library, SQLLIB. The generated code also contains the calls to SQLLIB ro

28、utines that perform the embedded SQL operations.The precompiler does not generate calls to Oracle Call Interface (OCI) routines.The paragraph above is copied from Pro*C/C+ Precompiler Programmer's Guide. I'm clever at doing this _ .SQLLIB named "orasql9.lib" in Windows, but named &

29、quot;libclntsh.so.9.0" in Linux.You could get more messages if your find it in the last articles within this series. Oracle Database Development (1). Configuration of ProC in Windows Oracle Database Development (2). Configuration of ProC in Linux3.Use the Precompiler OptionsPrecompiler options

30、enable you to control how resources are used, how errors are reported, how input and output are formatted, and how cursors are managed. In the Windows , most people prefer to use "procui.exe" to precompile host program.Of course , the tool provides "Options" menu which gives us a

31、 platform to edit options.When we use Linux OS, we can appoint the parameters in the command line. For example :$ proc parse=none iname=main.pcDeveloper can get more information by typing command " proc ? " in both Windows and Linux. The method mentioned above is only one of the Using Prec

32、ompiler Option, which i can call it "command line" . In Oralce, there are many methods like this , such as :system configuration file user configuration file command line inline There is a single system configuration file for each installation. The name of the system configuration file is

33、pcscfg.cfg. The directory of the file is "$ORACLE_HOME/precomp/admin" .We can specifies the name of a user configuration file by using the CONFIG option."inline" means that the declaration is in the host program. This statements begin with "EXEC SQL" ,and not every opti

34、on can be declared as "inline".The precedence of option values is explained indisputably in Oracle referential document.I don't want to waste so much time talking about the usage because of the Pro*C/C+ Precompiler Programmer's Guide.ORACLuckily, i am not so stupid to forget my ori

35、ginal purpose. Here comes the key .4.The Important OptionsCONFIG , PARSE and SYS_INCLUDEType the command "proc ?" and take a overview of the description , you will find that the default value of CONFIG is null. Oracle always use the system configuration file except CONFIG is defined .In Li

36、nux OS environment , the default file named "pcscfg.cfg" includes the option "SYS_INCLUDE" . But if PARSE=NONE , this option is irrelevant for the precompilation. That is the reason you can use "proc parse=none iname=main.pc" precompile the host program so easily.When P

37、ARSE=FULL , your must define the value of SYS_INCLUDE , and then you can   declare host variables legally in C.#include "sqlca.h"#include <stdio.h>#include <string.h> #include <stdlib.h>void sql_error(char *msg)    printf("n%s %sn", msg,(char

38、 *)sqlca.sqlerrm.sqlerrmc);    /EXEC SQL ROLLBACK RELEASE;    exit(0); void main() EXEC SQL INCLUDE sqlca;EXEC ORACLE OPTION (RELEASE_CURSOR = YES);EXEC SQL WHENEVER SQLERROR DO sql_error(" <ERROR> ");char oraCN200;strcpy(oraCN, "system/dddunixdb");EXEC

39、SQL CONNECT :oraCN;printf("n OK Connected! ");In Windows OS , maybe the value is "E:Program FilesMicrosoft Visual StudioVC98Include" .Edit the "pcscfg.cfg" file and precomplie the code above . The programe runs   very good.   The case becomes a litte

40、r complex in Linux . $ pwd/home/ora/ora9/oracle/precomp/admin$ cat pcscfg.cfgsys_include=(/usr/include,/usr/lib/gcc-lib/i386-redhat-linux/3.2.2/include)ltype=short$ proc parse=full iname=main.pc$ gcc -g -o main main.c -I/home/ora/ora9/oracle/precomp/public -L/home/ora/ora9/oracle/lib -lclntshIt runs

41、 very good too .-Oracle refrence PARSE=NONEThe value NONE has the following effects:   C preprocessor directives are understood only inside a declare section.    You must declare all host variables inside a Declare Section.    Precompiler release 1.x behavior PARSE=PART

42、IALThe value PARTIAL has the following effects:   All preprocessor directives are understood    You must declare all host variables inside a Declare Section    This option value is the default if CODE=CPPPARSE=FULL   The value FULL has the following effects:&#

43、160;  The precompiler C parser runs on your code.    All Preprocessor directives are understood.    You can declare host variables at any place that they can be declared legally in C.5.PostscriptWhen I used the option "PARSE=FULL" , i found that "EXEC SQL INCL

44、UDE sqlca;" "EXEC SQL ROLLBACK RELEASE; " cannot be precompled correctly together. I have no idea about it . Perhaps it caused by the internal compatibility of SQLCA.I am sure i will work along with it.Oracle數(shù)據(jù)庫(kù)開(kāi)發(fā)(三).ProC的預(yù)編譯參數(shù)草木瓜20070607一、前言我們上面已經(jīng)了解Windows和Linux下的ProC開(kāi)發(fā)環(huán)境，這里我們更進(jìn)一步去簡(jiǎn)要

45、介紹下ProC的預(yù)編譯參數(shù)。二、什么是預(yù)編譯預(yù)編譯過(guò)程中，Pro*C/C+會(huì)自動(dòng)生成C或者C+的代碼，去替代你原來(lái)的嵌入SQL，生成的代碼包含了一些數(shù)據(jù)結(jié)構(gòu)，其中聲明了數(shù)據(jù)類型，長(zhǎng)度，變量地址以及SQLLIB需要的一些其他信息。生成的代碼還包括了執(zhí)行嵌入SQL操作的一些SQLLIB調(diào)用。SQLLIB Windows下名是 "orasql9.lib" ，Linux下是"libclntsh.so.9.0" 。這方面知識(shí)可以參考先前的文章：   Oracle數(shù)據(jù)庫(kù)開(kāi)發(fā)(一).Windows下配置使用ProC   Orac

46、le數(shù)據(jù)庫(kù)開(kāi)發(fā)(二).Linux下配置使用ProC三、使用預(yù)編譯選項(xiàng)預(yù)編譯選項(xiàng)可以控制編譯資源的使用，錯(cuò)誤提示，標(biāo)準(zhǔn)化輸入輸出以及管理游標(biāo)。在Windows下，多數(shù)人傾向于使用“procui.exe”(Oralce菜單里的ProC*C+工具)進(jìn)行預(yù)編譯，可以通過(guò)Options菜單選擇編譯選項(xiàng)。當(dāng)我們使用Linux平臺(tái)時(shí)，一般會(huì)在命令行下指定參數(shù)，例如：$ proc parse=none iname=main.pc   不管是Windows還是Linux，開(kāi)發(fā)人員可以都鍵入“proc ?”獲取更多的信息。在Oracle中，關(guān)于參數(shù)的使用有不少的方法，如：系統(tǒng)參數(shù)文件用戶參數(shù)文

47、件命令行inlineProC安裝后都會(huì)存在唯一的系統(tǒng)參數(shù)文件“pcscfg.cfg”，所在路徑為“$ORACLE_HOME/precomp/admin”。用戶的參數(shù)文件，我們可以手工指定。命令行很容易理解，上面已有相關(guān)說(shuō)明?！癷nline”的意思是在原始pc文件中通過(guò)“EXEC SQL”顯式聲明，不過(guò)不是所有的參數(shù)可以使用這種方式聲明。關(guān)于這些優(yōu)先級(jí)的說(shuō)明，在Oracle相關(guān)的官方文檔已經(jīng)說(shuō)得很明確。具體內(nèi)容可參看Pro*C/C+開(kāi)發(fā)指南，這里不再贅述。四、一些比較重要的參數(shù)CONFIG，PARSE 和 SYS_INCLUDE在命令行下輸入“proc ?”，簡(jiǎn)單瀏覽下顯示信息，你會(huì)發(fā)現(xiàn)CONF

48、IG的默認(rèn)值是空。ORACLE一直使用的是系統(tǒng)的參數(shù)文件，除非你手工去指定config選項(xiàng)。在Linux環(huán)境下，默認(rèn)的系統(tǒng)參數(shù)文件“pcscfg.cfg”中含有“SYS_INCLUDE”的選項(xiàng)，但是如果“PARSE=NONE"，這個(gè)“SYS_INCLUDE”的選項(xiàng)會(huì)被忽略。所以你可以使用“proc parse=none iname=main.pc”（上篇文章）輕松編譯。不過(guò)當(dāng)我們指定PARSE值為FULL時(shí)，你就必須正確定義“SYS_INCLUDE”，這時(shí)可以在ProC的源文件.pc中，直接使用C語(yǔ)法規(guī)則定義宿主變量。可以看下面的例子：#include "sqlca.h&q

49、uot;#include <stdio.h>#include <string.h> #include <stdlib.h>void sql_error(char *msg)    printf("n%s %sn", msg,(char *)sqlca.sqlerrm.sqlerrmc);    /EXEC SQL ROLLBACK RELEASE;    exit(0); void main() EXEC SQL INCLUDE sqlca;EXEC ORACLE OPTION

50、(RELEASE_CURSOR = YES);EXEC SQL WHENEVER SQLERROR DO sql_error(" <ERROR> ");char oraCN200;strcpy(oraCN, "system/dddunixdb");EXEC SQL CONNECT :oraCN;printf("n OK Connected! ");在Windows下，“SYS_INCLUDE"的值類似于“E:Program FilesMicrosoft Visual StudioVC98Include”，即Vc6

51、.0的頭文件目錄，編輯“pcscfg.cfg”，然后預(yù)編譯源代碼，程序運(yùn)行沒(méi)有任何問(wèn)題。在Linux下，可能要稍微復(fù)雜一些。$ pwd/home/ora/ora9/oracle/precomp/admin$ cat pcscfg.cfgsys_include=(/usr/include,/usr/lib/gcc-lib/i386-redhat-linux/3.2.2/include)ltype=short    $ proc parse=full iname=main.pc    $ gcc -g -o main main.c -I/

52、home/ora/ora9/oracle/precomp/public -L/home/ora/ora9/oracle/lib -lclntsh        依上編譯，也沒(méi)有問(wèn)題。        -    Oracle 參考           PARSE=NONE        所有宿主變量必須定義在聲明

53、段中。只有在聲明段中的預(yù)編譯指令才會(huì)        識(shí)別。       PARSE=PARTIAL        可以識(shí)別所有的預(yù)編譯指令，所有宿主變量必須定義在聲明段中。如果        CODE=CPP，這個(gè)是默認(rèn)選項(xiàng)。       PARSE=FULL      

54、;  可以識(shí)別所有的預(yù)編譯指令，可以直接使用C語(yǔ)法定義宿主變量，不必        定義在聲明段中。  五、后記在我使用“PARSE=FULL”選項(xiàng)時(shí)，發(fā)現(xiàn)“EXEC SQL INCLUDE sqlca;” 和 “EXEC SQL ROLLBACK RELEASE; ”不能同時(shí)使用。不知道是為什么，估計(jì)可能是SQLCA的內(nèi)部兼容性問(wèn)題。相信有時(shí)間能解決這個(gè)問(wèn)題。Oracle數(shù)據(jù)庫(kù)開(kāi)發(fā)(四).Linux下ProC的Makefile示例2008/07/30 08:22 P.M.Oracle Database

55、Development (4). Example Makefile for Pro*CVert MelonJun 8,2007<本文主要介紹Linux下使用Makefile編譯ProC程序的方法>It is a pity that there is a few aritcle written in Chinese which talking about the material method of Makefile for Pro*C/C+ .Maybe it's not diffcult to the most Linux developers , but I find

56、lots of questions about the method of compile which follows no answer .I don't know why .1.PrefaceAssumed that you just finished the installation of Oracle Pro*C In Linux OS , you hurry to enter the directory which contains lots of Pro*C examples and type this command " make -f demo_proc.mk

57、 sample1 " to get a first programme. In all probability you could find lots of errors and warnings full of the screen instead of a imaged executable file.Don't worry , let us discover the secrect in it , which is the mainly content of this artical.Oracle DataBase Development Series2.The cau

58、sation of errorsIn the beginning , I was also puzzled with this errors , for i had no idea with Precompiler Options . Everything becomes clear go with the knowing the Precompiler Options.Input your command " proc parse=? " at the command line .   $ proc parse=? You should see the

59、 prompt following .   Pro*C/C+: Release .0 - Production on Fri Jun 8 12:22:36 2007     Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.     System default option values taken from: /home/ora/ora9/oracle/precomp/admin/pcscfg.c

60、fg     Option name   : parse=string    Current value : full   Restrictions : full, partial, none   Description   : Control which non-SQL code is parsed   PCC-F-02135, CMD-LINE: User asked for helpCheck the makefile named

61、 "demo_proc.mk" and the predefined environment file named "env_precomp.mk" , and you will find the variable $(PROCFLAGS) not defined .Oralce used the default value when compiled the sample. According to last article Oracle Database Development (3). Introduce to Pro*C/C+ Precompil

62、er Options, we know that we should set the value of SYS_INCLUDE option. Just follow the instruction , you would get a better result .The key step of the compilers listed on the sceen is perhaps like this :proc iname=sample1 include=. include=/home/ora/ora9/oracle/precomp/public include=/home/ora/ora

63、9/oracle/rdbms/public include=/home/ora/ora9/oracle/rdbms/demo include=/home/ora/ora9/oracle/plsql/public include=/home/ora/ora9/oracle/network/public/usr/bin/gcc -O3 -trigraphs -fPIC -DPRECOMP -I. -I/home/ora/ora9/oracle/precomp/public -I/home/ora/ora9/oracle/rdbms/public -I/home/ora/ora9/oracle/rd

64、bms/demo -I/home/ora/ora9/oracle/plsql/public -I/home/ora/ora9/oracle/network/public -DLINUX -D_GNU_SOURCE -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -DSLTS_ENABLE -DSLMXMX_ENABLE -D_REENTRANT -DNS_THREADS    -c sample1.csample1.c: In function main':sample1.c:241: warning: retu

65、rn type of main' is not int'/usr/bin/gcc -o sample1 sample1.o -L/home/ora/ora9/oracle/lib/ -lclntsh cat /home/ora/ora9/oracle/lib/ldflags   cat /home/ora/ora9/oracle/lib/sysliblist -ldl -lm sample1.o(.text+0x3b6): In function main': the gets' function is dangerous and shoul

66、d not be used.Some warnings comes again , i don't mind , for we have already known what it is !3.Using Oracle defined MakefileHere is the detail below.Copy the file "demo_proc.mk" to your own directory and rename it by yourself .The source file " main.pc " , which have the sa

67、me content with the example mentioned before , is created then .   oraliwei src$ ls   main.pc   proc.mkAdd the "-lclntsh" to the file "$ORACLE_HOME/lib/sysliblist" .   oraliwei lib$ cat sysliblist   -lclntsh -ldl -lm -lpthread -ln

68、sl -lircStart to compile .   oraliwei src$ make -f proc.mk main      make -f /home/ora/ora9/oracle/precomp/demo/proc/demo_proc.mk PROCFLAGS="" PCCSRC=main I_SYM=include= pc1   make1: Entering directory /home/ora/develop/src'   proc inam

69、e=main include=. include=/home/ora/ora9/oracle/precomp/public include=/home/ora/ora9/oracle/rdbms/public include=/home/ora/ora9/oracle/rdbms/demo include=/home/ora/ora9/oracle/plsql/public include=/home/ora/ora9/oracle/network/public     Pro*C/C+: Release .0 - Production on Fri Jun 8 16:18:20 2007     Copyright (c) 1982, 2002, Oracle Corporation. All rights r