Accessing dblink using Dynamic SQL

As I had mentioned in my previous post. After I install oracle 8i machine on my XP Professional succesfully, I should create database link in my 8i and create store procedure using dynamic SQL. I need dynamic SQL because I need to build a database gateway that collect data from many resource of database machine. So, technically the database link is represented as variable i/o in the store procedure.

Here, the example code that contain dynamic sql to query specific data from table x in database link name y:

ds_numrows NUMBER; --as global variable
ds_cursor_id NUMBER; --as global variable
queryString := 'SELECT x.attr1, x.attr2 from x@y';
ds_cursor_id := DBMS_SQL.open_cursor;
DBMS_SQL.parse (ds_cursor_id, queryString, DBMS_SQL.v7);
DBMS_SQL.define_column (ds_cursor_id, 1, queryString, 1024);
ds_numrows := DBMS_SQL.EXECUTE (ds_cursor_id);
LOOP
IF DBMS_SQL.fetch_rows (ds_cursor_id) = 0
THEN
EXIT;
END IF;
DBMS_SQL.column_value (ds_cursor_id, 1, queryString );
END LOOP;
DBMS_SQL.close_cursor (ds_cursor_id);
COMMIT;


Filed under: Uncategorized

install Oracle 8i in Windows XP

Finally, They believe me to develop a project. yattaaaa..
It’s really amazing for me. I’ll do my best..  YOSSSHHH.. hehehe…
I should create a dblink and store procedure that contain dynamic sql to query some data from oracle 8i to oracle 7i. Due to oracle 7i only can be access successfully using dblink that created in oracle 8i so I should install oracle 8i first at my XP Professional OS. It can be accessed using dblink from 9i, but the connection’s too slow.

First step, I should install Oracle 8i in Windows XP before create the dblink. So many times I tried but the 8i setup was still not working. Then, as usual, I’m googling and.. tam.. ra.. ram…
just do this:

1. Copy the content of your Oracle CD into hard disk in your machine. For example Copy from oracle8i CD into D:\Oracle\ …
2. Search the file Symcjit.dll on the D drive (where you have copied Oracle8i. You can found it in:
   • For Oracle8i 1.7:

   D:\Oracle\stage\Components\oracle.swd.jre\1.1.7.30\1\
   DataFiles\Expanded\jre\win32\bin\symcjit.dll

   • For Oracle8i 1.6:

   D:\Oracle\stage\Components\oracle.swd.jre\1.1.7.24\1\
   DataFiles\Expanded\jre\win32\bin\symcjit.dll

3. Rename those file (Example): symcjit_back.dll
4. Execute the Oracle setup.exe from D:\Oracle\setup.exe

Yatta, it’s really works Friend..
Ok, lets go to the next step.. ^o^

Filed under: Oracle , Oracle 8i

Sending mail with PHP using SMTP-auth (PEAR package)

To protect essentials network, servers, and customers from spamming, it requires the use of SMTP-auth for all methods of sending email. The basic PHP Mail function does not support SMTP-auth, therefore cannot be used.

To write your own PHP code to send email using SMTP-auth, there are PHP PEAR Mail package available for usage (which supports smtp-auth). PHP code sample below shows how to use this feature to send an email:


require_once "Mail.php";

/**************************************************
EDIT the following variables for your own use
***************************************************/
$from = "user@domain";
$to = "user2@domain2";

$subject = "Hi!"; //type in subject here

$host = "host_ip"; // Your domain
$username = "user"; // Your user / full email address
$password = "password"; // Password to your email address

/**************************************************
***************************************************/

$body = "Test";

foreach($_POST as $a => $b)
{
$body .= $a .": ". $b . "\n";
}
$headers = array ('From' => $from,
'To' => $to,
'Subject' => $subject);
$smtp = Mail::factory('smtp',
array ('host' => $host,
'auth' => true,
'username' => $username,
'password' => $password));

$mail = $smtp->send($to, $headers, $body);

if (PEAR::isError($mail)) {
/**************************************************
ERROR MESSAGE
***************************************************/
?>

getMessage(); ?>
/**************************************************/
} else {
/**************************************************
SUCCESS MESSAGE
***************************************************/
?>
Message successfully sent!
/**************************************************/
}

?>


grabbed from here

Filed under: PHP , PEAR::MAIL, PHP

How to export and import Oracle Database using command prompt?

First thing you should do is change the tnsnames.ora files with the code below. This .ora file is under directory \client_1\NETWORK\ADMIN\


conn1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP_Address)(PORT = 1521))
)
(CONNECT_DATA =
(SID = XE)
or (SERVICE_NAME = conn1)
)
)


then save the file and open the command prompt (windows key + R, type cmd).

Export:

To export, type this command in cmd:
>> exp user/password@instant
It will show many questions, leave it with default answer using hit enter. But, there are the important question, such as:
- (2)U(sers), or (3)T(ables): (2)U >
2 : to export all database under user that mentioned above
3 : to export selected tables only. If 3 choosed, it will appear question what tables that will be exported
- Export grants (yes/no): yes > (type Y or N)
- Export table data (yes/no): yes > (type Y or N)
- Compress extents (yes/no): yes > (type Y or N)

In the rest, make sure that appear message Export terminated successfully without warnings.

Import:

To import full dmp file type this command in cmd:
>> imp user/password@instant file=emp.dmp log=imp.log full=yes

To import dept table alone type this command in cmd:
>> imp user/password@instant file=emp.dmp log=imp.log fromuser=user touser=user tables=dept

FROMUSER and TOUSER clause are optional. But in case if used, then create a user in the destination database that is mentioned in TOUSER clause then try importing the dmp file.

In the rest, make sure that appear message Export terminated successfully without warnings.
If there are still warnings, please check the dblink connection. If it has a problem connection with dblink, drop the current dblink (result of importing) and create new dblink with simple new name.

Filed under: Oracle , Export and Import Oracle Database

Integration Server didn’t started

If you have some problem with starting a webMethods Integration Server. You should search file LOCK under folder [root installation]/../Integration Server/bin/
If these file already there, and the IS machine still doesn’t start. So delete this file (LOCK file), and start again the IS machine. – solved -

This LOCK file still there, because the IS machine stopped incorrectly. While system stopped, the LOCK file hadn’t deleted.

Filed under: Uncategorized

Hello world!

Hello everyone!!
This weblog will contain all about webMethods technology. webMethods is one of integration system that used in my company. This writing dedicated to everyone who want to learn about webMethods. 

OK, this is the start. I’m still learning too..

Filed under: Uncategorized