Monday, May 25, 2009

ORA-06508: PL/SQL: could not find program unit being called

Cause: An attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or have compiled with errors.
Action: Check that all referenced programs, including their package bodies, exist and are compatible.

Prevent cache in HTML

META tag inside HEAD. Like this :

< HEAD>
< META HTTP-EQUIV="CACHE-CONTROL" CONTENT="NO-CACHE" >
< /HEAD>

Thursday, May 14, 2009

Logo Unity: Veritabanında integer olarak tutalan zaman bilgisinin gösterilmesi

Logo programlarının veritabanına integer olarak kaydettiği zaman bilgisini aşağıdaki function ile zaman biriminden görebilirsiniz.

CREATE FUNCTION [dbo].[EXTRACTINTDATE](@PDATE AS INT)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @FHOUR INT
DECLARE @FMINUTE INT
DECLARE @FSECOND INT
DECLARE @HOURSTR VARCHAR(2)
DECLARE @MINSTR VARCHAR(2)
DECLARE @FSECSTR VARCHAR(2)


SET @FHOUR = CONVERT(INT,@PDATE/16777216,103)
SET @FMINUTE = (@PDATE-(@FHOUR*16777216))/65536
SET @FSECOND = (@PDATE-((@FMINUTE*65536)+(@FHOUR*16777216)))/256

SET @HOURSTR = CONVERT(VARCHAR(2), @FHOUR, 103)
SET @MINSTR = CONVERT(VARCHAR(2), @FMINUTE, 103)
SET @FSECSTR = CONVERT(VARCHAR(2), @FSECOND, 103)

RETURN (@HOURSTR + ':' + @MINSTR + ':' + @FSECSTR)
END


Örnek Kullanım:

select TIME_,[dbo].[EXTRACTINTDATE](TIME_) from LG_XT019_109


Sonuç:

TIME_ NEW_TIME
168634208 10:13:39
168636156 10:13:46
220538930 13:37:40
238367942 14:53:52
238762111 14:59:56

Tuesday, May 12, 2009

Case Problem in C Sharp: Control can't fall through from one case label to another

PROBLEM:
Control can't fall through from one case label to another 4 each case

String status = (String)Session["Status"];

switch (status)
{
case "Book Pending":

string count1 = wucHSRBookModify1.getCount("Offer Pending", "",
"", "", "");
int intcount1 = Convert.ToInt16(count1);
if (intcount1 < 1)
{
wucHSRBookModify1.UpdateBookStatus((String)Session["Status"]);
}

case "Modification Pending":

string count2 = wucHSRBookModify1.getCount("Offer Pending", "Book Pending",
"", "", "");
int intcount2 = Convert.ToInt16(count2);
if (intcount2 < 1)
{
wucHSRBookModify1.UpdateBookStatus((String)Session["Status"]);
}
case "Availability Pending":

string count3 = wucHSRBookModify1.getCount("Offer Pending", "Book Pending",
"Modification Pending", "", "");
int intcount3 = Convert.ToInt16(count3);
if (intcount3 < 1)
{
wucHSRBookModify1.UpdateBookStatus((String)Session["Status"]);
}
}


RESOLUTION:
You need to insert a break; at the end of each case before another case begins.

Monday, May 11, 2009

Prevent unchecking of a checkbox in Java Script

<\input type="checkbox" onclick="if(!this.checked)this.checked=true"\>

ORA-01722: invalid number

Cause:
The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action:
Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

Prevent href in Java Script




ORA-00932: inconsistent datatypes: expected string got string

Cause: One of the following:

An attempt was made to perform an operation on incompatible datatypes. For example, adding a character field to a date field (dates may only be added to numeric fields) or concatenating a character field with a long field.

An attempt was made to perform an operation on a database object (such as a table or view) that is not intended for normal use. For example, system tables cannot be modified by a user. Note that on rare occasions this error occurs because a misspelled object name matched a restricted object's name.
An attempt was made to use an undocumented view.

Action: If the cause is different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.
an object not intended for normal use, then do not access the restricted object.

Create a for Loop in JavaScript

JavaScript, like all programming languages, supports loops or repetition, in its case with the for statement. The for statement in JavaScript comes in two flavors: one loops over values, the other over entries in an object or array.

Loop Over Values in JavaScript
1. Code the for statement, following the syntax below:
for (variable=value; condition; increment) { statement(s) }

2. Loop over a range of values by setting a variable equal to the initial value, specifying for the condition the test that the variable not exceed the maximum value, and incrementing the variable each time through the loop. In the example below, the numbers between 1 and 9 will be displayed in a series of alert boxes. (i++ means to increment the value of the variable i by 1.)
for (i=1; i<10; i++) { alert (i); }

3. Terminate processing of the loop's statements and return to the top for the next iteration, if necessary, by using the continue statement.

4. Break out of the loop, continuing with the statement following it, by coding a break statement if necessary.



Loop Over Object or Array Elements

1. Code the for statement, following the syntax below. "in" is a special JavaScript keyword used for this purpose.
for (variable in object) { statement(s) }

2. Process each element in the object or array as appropriate. The variable given in the for statement will take on the value of each array index, or object field, in turn. Example:
for (i in my_array) { alert ("Value of element " + i + " is " + my_array[i]);

3. Move to the next iteration, or break out of the loop, using the same continue and break statements described in the preceding section.

Tuesday, May 5, 2009

ORA-00257: archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

Prevent multi-row updates with Trigger

The best way to do this would be with a trigger :

create trigger trig_single_updates_only on table_name
for update
as
begin
if (select count(*) from inserted) > 1
begin
raiserror ('You cannot update more than one row at time - please add an appropriete where clause',16,1)
rollback
end
end

ORA-12535 : ORACLE instance terminated. Disconnection forced

Cause: The requested operation could not be completed within the time out period.

Action: Look at the documentation on the secondary errors for possible remedy. See SQLNET.LOG to find secondary error if not provided explicitly. Turn on tracing to gather more information.

How to prevent duplication record?

You can use either of the following Approach:

1. Basically Creating a Unique Constraint suffice.
2. Creating Unique index (Unique Constraint + Index) would help you in case of frequent querying based upon that column.
3. Creating an Identity column to avoid duplication.

Transaction log in sql server

could be a few things affecting... are the transaction logs under a maintenance plan of some description where full backups and frequent ttransaction logs backups for a FULL recovery mode database, or, maybe you are using SIMPLE recovery mode.

do you run a shrink or some other "clean up" process ? has the database files been analysed and optimised for a size and growth ? Is there sufficient disk space ? has the lof been allowed to grow (obviously not), what is the current size ?

all these types of things might come into play as well... not necessarily just a "rogue/unruley/hungry" process sending it into a spin...

one thing you can do if you notice and increase in size:

DBCC OPENTRAN

it will show you the open transactions and the SPID's associated. You can then track back to the SPID's and see the SQL that they are using at the time.

you could automate the whole lot in a stored procedure and schedule it to run every so often... have a look at a couple of code snippets below...



--capture logspace into a table (and a general approach for collecting dbcc outputs)
create table tmp_sqlperf(databaseName varchar(100),LogSize decimal(18,5),LogSpace decimal(18,5),Status int)

insert tmp_sqlperf
exec ( 'dbcc sqlperf (logspace)')
select * from tmp_sqlperf


--check current log activity
select * from fn_dblog(NULL,NULL)
--or
dbcc log('MY_DB',-1)


--check current SPID's with opentrans
dbcc opentran

--capture the SQL /processes belonging to a specific spid
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 57
SELECT * FROM ::fn_get_sql(@Handle)
--or
select * from sys.dm_exec_sql_text(@handle)
Open in New Window Select All Not what you're looking for? Ask an Expert.

Friday, May 1, 2009

Shrink a tablespace in Oracle 10G OEM?

Here are the steps that you can follow to resize a tablespace using Oracle 10G OEM.

1. To log in to Oracle Enterprise Manager Database Control:

Open your Web browser and enter the following URL

http://hostname:port/em

In a default installation, the port number is 1158. If you are unsure of the correct port number to use, look for the following line in the ORACLE_BASE\ORACLE_HOME\install\portlist.ini file:

Enterprise Manager Console HTTP Port (db_name) = port

For example, if you installed the database on a host computer named mgmt42, and the port number listed in the portlist.ini file is 5500, then enter the following URL

http://mgmt42:5500/em
Enterprise Manager displays the Database Control Login Page.

Log in to the database using the SYSMAN database user account. Enterprise Manager displays the Oracle Database home page.

Use the password you specified for the SYSMAN account during the Oracle Database installation.

2. Select/click on Administration link > then on storage link on the next page and select tablespace

3. Click on datafile name then "Edit".

Enter new datafile size in MB or KB and click on apply when finished.

Retreiving data from backup of crashed Oracle

If you you have a hot backup set of all data file, control file, archived log file, do the following:

1. Create the new directories (same or different hiarachy)
2. Copy all datafiles, archived log and init files from backup set to those dirs.
3. Modify the file was created from "backup control file to trace" command. This script will be used to recreate the control file
4. Startup nomount then run the script to recreat the new control file
5. Issue:
SQL>recover database
Oracle will ask you for the archived log, apply until cancel or turn on AUTO to let Oracle find the archived log file by itself
6. Alter database open