 | Välkommen! | Sep 18, 2006 |
Great developers take it to the next level by not only thinking about their goals, but also visualize it! Hello friends..It seems like I have problems with my multiply page. It consumes so much of time to load every page, every images. I have decided to move to a new blog service. All are invited to take a peek at my two new blogs...I will still attach to multiply, as I have good friends here.  http://pepstroops.blogspot.com/http://peps-hellkitchen.blogspot.com/Do drop by ya...Thank you guys. |  | Shots taken during a short trip to negara jiran....gambar were not that good..wish we had a good quality camera..*sigh* |
/*
Recently we got a major problem in accessing the database
thru the application(.net 2.0) when the team (11 programmers) tried to
access to the database concurrently.
In order to check the connections, we found a script that would
be able to check the connected sessions; which has not been closed.
Execute this one out thru the editor. It really helps.
*/ select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;
.....4 more days to end my 'dara' life....  - still working...aiyo...others would have already mandi lulur by now..me - mandi 'DETTOL'..  ....
Lead or not to lead....at this moment i rather choose not to. There are two types of leader. One whom people will look up to. This type of leader has a knowledge of nearly everything programming, securities, network, new techs. People trust him on his acts and decisions. The other is someone whom people curse a lot. You will know what type of leader can you become. Depends on how people react on each of your decisions and answers. I got a lot of faces and sighs. So I guess I know where I belong at the moment. That really brings down my emotion and mentality. Giving up is not the answer. Programming is something that you really have to be 'GOOD' and 'PASSIONATE' at for the others to hail you up. I will definitely not be labelized as a superb programmer, but as far as i can go, I will just swim among the codes and algorithms. The fate will be decided later on. Having trouble locating the database's Data Source?
Find it here:
C:\oracle\product\10.1.0\db_2\NETWORK\ADMIN\tnsnames.ora Careless mistake that causes severe injury... MSSQL: + EXAMPLE: SELECT * FROM GBL_CIVILANT_MST WHERE ICNO LIKE '%' + SEARCHITEM_ + '%' ORACLE: || EXAMPLE: SELECT * FROM GBL_CIVILANT_MST WHERE ICNO LIKE '%' || SEARCHITEM_ || '%' CREATE OR REPLACE PACKAGE KKB_REPORT_PCKG IS
PROCEDURE SP_GETALLSTATE(PARENTID_ IN GBL_REFCODE.PARENTID%TYPE);
PROCEDURE SP_GETALLDISTRICT(PARENTID_ IN GBL_REFCODE.PARENTID%TYPE);
END KKB_REPORT_PCKG;
/
CREATE OR REPLACE PACKAGE BODY KKB_REPORT_PCKG IS
PROCEDURE SP_GETALLSTATE(PARENTID_ IN GBL_REFCODE.PARENTID%TYPE) IS
TYPEDESCRIPTION_ VARCHAR2(100);
TYPEID_ INTEGER;
Cursor STATECURSOR IS SELECT TYPEDESCRIPTION,TYPEID
FROM gbl_refcode
WHERE PARENTID = PARENTID_;
BEGIN
OPEN STATECURSOR;
LOOP
FETCH STATECURSOR INTO TYPEDESCRIPTION_,TYPEID_;
EXIT WHEN STATECURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TYPEDESCRIPTION_ || ' - ' || TYPEID_);
SP_GETALLKKBACTION(TYPEID_);
END LOOP;
END;
PROCEDURE SP_GETALLDISTRICT(PARENTID_ IN GBL_REFCODE.PARENTID%TYPE) IS
TYPEDESCRIPTION_ VARCHAR2(100);
Cursor STATECURSOR IS SELECT TYPEDESCRIPTION
FROM gbl_refcode
WHERE PARENTID = PARENTID_;
BEGIN
OPEN STATECURSOR;
LOOP
FETCH STATECURSOR INTO TYPEDESCRIPTION_;
EXIT WHEN STATECURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TYPEDESCRIPTION_);
END LOOP;
END;
END KKB_REPORT_PCKG;
/
| Start: | Feb 23, '07 09:00a | | End: | Feb 26, '07 | | Location: | Sungkai, Perak |
Q: What will 2 programmers be doing in the thick jungle? A: ???????  | CONCAT | Feb 16, '07 12:45 AM
for everyone |
SYNTAX:
CONCAT(char1, char2)
PURPOSE:
Returns char1 concatenated with char2. This function is
equivalent to the concatenation operator (||).
EXAMPLE:
This example uses nesting to concatenate three character strings:
SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job"
FROM emp
WHERE empno = 7900
Job
-------------------------
JAMES is a CLERK
A wise man said...it is better to master PL\SQL syntax and command rather than mastering the product itself. One useful command is the database backup and restore command. Oracle 10g has its own Enterprise Manager but since the interface is quite inflexible, we use TOAD from the Quest Software instead. The problem with this tool is that we couldn't find on how to backup the database from the interface given. PL\SQL comes very handy this time. (Oracle is much simpler compare to Postgresql  ) Goto Start > Run > Type cmd :
Commandc:\>exp < username>/< password>@< dbname> file=< destination\filename.dmp> full=yes Examplec:\>exp SA/sa@EPERHILI file=C:\ePerhilitanDBBAK\ePerhili13022007.dmp full=yes RESTORE:
Commandc:\>imp < username>/< password>@< dbname> file=< sourcefile\filename.dmp> full=yes Examplec:\>imp SA/sa@EPERHILI file=C:\ePerhili13022007.dmp full=yes BACKUP(CERTAIN TABLES INCLUDED DATA):
Commandc:\>exp < username>/< password>@< dbname> file=< sourcefile\filename.dmp> tables=<tb1,tb2,tb3.....>
Examplec:\>exp SA/sa@EPERHILI file=C:\WFLOW.dmp tables=(tblProcess,tblTask,tblModule) RESTORE(CERTAIN TABLES INCLUDED DATA):
Command
c:\>imp < username>/< password>@< dbname> file=< sourcefile\filename.dmp> full=yes
Example
c:\>imp SA/sa@EPERHILI file=C:\WFLOW.dmp full=yes
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
v_cursor GetRefCodePckg.refcode_ref_cursor;
v_typeid GBL_REFCODE.TYPEID%TYPE;
v_parentid GBL_REFCODE.PARENTID%TYPE;
v_typedescription GBL_REFCODE.TYPEDESCRIPTION%TYPE;
v_typedetails GBL_REFCODE.TYPEDETAILS%TYPE;
v_typestatus GBL_REFCODE.TYPESTATUS%TYPE;
BEGIN
GetRefCodePckg.SP_GETREFCODE (P_ID => 1,
DESC_REF => v_cursor);
LOOP
FETCH v_cursor
INTO v_typeid, v_parentid, v_typedescription, v_typedetails, v_typestatus;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_typeid || ' | ' || v_parentid || ' | ' || v_typedescription || ' | ' || v_typedetails || ' | ' || v_typestatus);
END LOOP;
CLOSE v_cursor;
END;
/
CREATE OR REPLACE PACKAGE REFERENCE_PCKG IS
TYPE reference_ref_cursor is ref cursor return GBL_REFCODE%ROWTYPE;
TYPE group_ref_cursor is ref cursor return WF_REFGROUP%ROWTYPE;
TYPE species_ref_cursor is ref cursor return IKB_SPECIESNAME%ROWTYPE;
TYPE location_ref_cursor is ref cursor return GBL_LOCATION%ROWTYPE;
--grab from table GBL_REFCODE
PROCEDURE SP_GETREFCODE
(P_ID IN GBL_REFCODE.PARENTID%TYPE,
DESC_REF OUT reference_ref_cursor
);
--grab from table WF_REFGROUP
PROCEDURE SP_GETREFGROUP
(P_ID IN WF_RefGroup.PARENTID%TYPE,
DESC_REF OUT group_ref_cursor
);
--grab from table IKB_SPECIESNAME
PROCEDURE SP_GETSPECIESNAME
( DESC_REF OUT species_ref_cursor
);
--grab from table GBL_LOCATION
PROCEDURE SP_GETLOCATION
(P_ID IN GBL_LOCATION.STATE%TYPE,
DESC_REF OUT location_ref_cursor
);
END REFERENCE_PCKG;
/
CREATE OR REPLACE PACKAGE BODY REFERENCE_PCKG IS
PROCEDURE SP_GETREFCODE
(P_ID IN GBL_REFCODE.PARENTID%TYPE,
DESC_REF OUT reference_ref_cursor) IS
BEGIN
OPEN DESC_REF FOR
SELECT TYPEID, PARENTID, TYPEDESCRIPTION, TYPEDETAILS, TYPESTATUS
FROM GBL_REFCODE
WHERE PARENTID = P_ID;
END;
PROCEDURE SP_GETREFGROUP
(P_ID IN WF_RefGroup.PARENTID%TYPE,
DESC_REF OUT group_ref_cursor) IS
BEGIN
OPEN DESC_REF FOR
SELECT GROUPID, PARENTID, GROUPDESCRIPTION, GROUPDETAILS, GROUPSTATUS
FROM WF_RefGroup
WHERE PARENTID = P_ID;
END;
PROCEDURE SP_GETSPECIESNAME
(DESC_REF OUT species_ref_cursor) IS
BEGIN
OPEN DESC_REF FOR
SELECT *
FROM IKB_SPECIESNAME;
END;
PROCEDURE SP_GETLOCATION
(P_ID IN GBL_LOCATION.STATE%TYPE,
DESC_REF OUT location_ref_cursor) IS
BEGIN
OPEN DESC_REF FOR
SELECT *
FROM GBL_LOCATION
WHERE STATE = P_ID;
END;
END REFERENCE_PCKG;
/
SET SERVEROUTPUT ON
DECLARE
ROW NUMBER;
BEGIN TRNWORKFLOW_PCKG.SP_ADDTRNWORKFLOW(55,1,1,1,1,SYSDATE,NULL,1,NULL,NULL,ROW);
DBMS_OUTPUT.PUT(ROW);
END;
CREATE OR REPLACE PROCEDURE SP_ADDMSTCONSIGNMENTACTION(
CONSIGNMENTID IN NUMBER,
ACTIONSTATUS IN NUMBER,
USERID IN NUMBER,
ACTIONDATE IN DATE,
LOCATIONID IN NUMBER,
CREATEDBY IN NUMBER
)
AS
BEGIN
INSERT INTO KKB_MSTCONSIGNMENTACTION (CONSIGNMENTID,ACTIONSTATUS,USERID,ACTIONDATE,LOCATIONID,CREATEDBY)
VALUES(CONSIGNMENTID,ACTIONSTATUS,USERID,ACTIONDATE,LOCATIONID,CREATEDBY);
end;
--primary key
CREATE SEQUENCE WF_mstAction_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER ;
--table
CREATE TABLE WF_mstAction (ActionID INTEGER NOT NULL, Action VARCHAR(100),
ActionObject VARCHAR(100),
Status CHAR(1),
CreatedBy INTEGER,
CreatedDate DATE,
LastModifiedBy INTEGER,
LastModifiedDate INTEGER
);
--unique key
CREATE UNIQUE INDEX PK_WF_mstAction ON WF_mstAction (ActionID) LOGGING TABLESPACE SYSTEM PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL;
--trigger -> autogenerate number
CREATE OR REPLACE TRIGGER WF_mstAction_TRIG
BEFORE INSERT ON WF_mstAction
FOR EACH ROW
WHEN (
new.ActionID IS NULL
)
BEGIN
SELECT WF_mstAction_seq.NEXTVAL
INTO :new.ActionID
FROM dual;
END;
| |
