Oracle experiment Summary

(1) network configuration file
# Tnsnames. ora network configuration file: e:/Oracle/Network/admin/tnsnames. ora
# Generated by Oracle configuration tools.

Element1 =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.0.181) (Port = 1521 ))
)
(CONNECT_DATA =
(Server = dedicated)
(SERVICE_NAME = element.com)
)
)
# Host = 192.168.0.181 (the IP address or computer name of the target host)
# SERVICE_NAME is the service name of the target host, cited as element.com.

(2) Oracle Query
Select level empno, ename, Mgr from EMP connect
Prior empno = Mgr start with empno = 7839

Select ename, Sal,
Decode (sign (Sal-3000), 1, 'A ',
Decode (sign (Sal-2000), 1, 'B', 'C '))
Grade from EMP

(3) programming to read records from Oracle databases to files
/***** Exam. PC *********/
# Include <stdio. h>
# Include <string. h>
# Include <stdlib. h>
# Include <sqlca. h>

# Define sqlnotfound 1403
Exec SQL include sqlca;

File * fp_log;

Void connect ()
{
Exec SQL begin declare section;
Varchar username [10], password [10], server [10];
Exec SQL end declare section;

/* Enter the user name, password, and server name */
Printf ("/N input Username :");
Gets (username. Arr );
Username. Len = (unsigned short) strlen (char *) username. Arr );
Printf ("/N input password :");
Gets (password. Arr );
Password. Len = (unsigned short) strlen (char *) password. Arr );
Printf ("/N input server name :");
Gets (server. Arr );
Server. Len = (unsigned short) strlen (char *) server. Arr );
/* Connect to the Oracle server */
Exec SQL CONNECT: username identified by: password using: server;
Printf ("/n successfully connected to the server % s as USER % s! /N ", username. Arr, server. Arr );
}

Void disconnect ()
{
Char temp;
Printf ("/N: Do you want to submit all transactions before disconnecting? (Y/n )");
Scanf ("% C", & temp );
Fflush (stdin );
If (temp! = 'Y' & temp! = 'Y ')
{
/* Roll back the transaction and disconnect. */
Exec SQL rollback work release;
Printf ("/n roll back the transaction, disconnect, and exit the program! /N ");
}
Else
{
/* Submit the transaction and disconnect. */
Exec SQL commit work release;
Printf ("/n commit transaction, disconnect and exit the program! /N ");
Exit (1 );
}
}

Void delspace (char * SSTR)
{
While (strlen (SSTR)> 1 & SSTR [strlen (SSTR)-1] = '')
{
SSTR [strlen (SSTR)-1] = '/0 ';
}
}

Void main (INT argc, char ** argv)
{
Exec SQL begin declare section;
Varchar SQL _text [256];
Char ename [12];
Char job [10];
Char Sal [10];
Exec SQL end declare section;

File * FP;

Char fdataname [64];/* data file name */

Connect ();/* connect to the database */

Memset (fdataname, '/0', 64 );
Strcpy (fdataname, "d: // Oracle // proc // emp.txt ");
If (FP = fopen (fdataname, "A +") = NULL)
{
Printf ("error opening % s/n", fdataname );
Return;
}

Strcpy (SQL _text.arr, "select ename, job, Sal from EMP ");
SQL _text.len = (unsigned short) strlen (char *) SQL _text.arr );

Exec SQL prepare pre_sql1 from: SQL _text;

Exec SQL declare C1 cursor for pre_sql1;

Exec SQL open C1;
While (1)
{
Printf ("debug_1: sqlcode = % d", sqlca. sqlcode );
Exec SQL fetch C1 into: ename,: Job,: Sal;
Printf ("debug_2: sqlcode = % d", sqlca. sqlcode );
If (sqlca. sqlcode = sqlnotfound) | (sqlca. sqlcode <0) break;
Printf ("debug01: ename = % s", ename );
Delspace (ename );
Delspace (job );
Delspace (SAL );
Fprintf (FP, "% s, % s, % s/n ",
Ename, job, Sal );
}
Exec SQL close C1;
Fclose (FP );
/*---------------------------------------------*/
Disconnect ();
}

Proc sqlcheck = Full userid = Scott/tiger @ ora9205 INAME = exam. PC

Generated program: exam. c

Set the environment variables as follows: (install VC ++)
Set Lib = D:/program files/Microsoft Visual Studio/vc98/lib
Set include = D:/program files/Microsoft Visual Studio/vc98/include; D:/Oracle/ora92/precomp/Public

CL exam. c/link D:/Oracle/ora92/precomp/lib/msvc/orasql9.li

Generation: exam.exe

You can import data to emp.txt after running example.