recite : sybase count char from 1 !!

let’s say ’05:00′, to get the hour, should be substring(1, 2)
to get the miniute should be substring(3, 2).

besides, char is fixed length is fixed length, for example char(5) would pad ‘5:00′ with ?? at end of ’00’

Nice wiki on Sybase SQL debugger


Sybase's sql-debugger (sqldbgr) is a tool included with the ASE installation and can be used to debug stored procedures and triggers. This might give you the impression that this tool is mainly for developers and not for DBA's. However, the debugger also allows you to attach to an already running process (another spid), examine or change local variables of that other session and even inspect their temporary tables. For production environments the tool provides just that extra information where Monitoring Tables fall short.

==Example of SQL code that needs to be debugged==
As an example the following code is created into a database. Please note that for readability things like transaction handling, error checking and compilation requirements have been left out. Description of functionality is given below.
 create procedure myProc @a int, @b int
 declare @c int
 select @c = c_value
 from myTable
 where a_value = @a
 select d_value
 into #work
 from myOtherTable
 where b_value = @b
 execute myOtherProc @c

 create procedure myOtherProc @c int
 select *
 from BigTable
 join #work on (BigTable.d_value = #work.d_value)
 where c_column = @c

When the myProc procedure is executed it retrieves a value from a table, populates a worktable and executes the stored procedure myOtherProc. The stored procedure myOtherProc selects data from the worktable #work and a very big table. Once this procedure is running it is impossible to determine the content of the temporary table at run-time (or a cumbersome procedure must be followed). Any inspection of local variables is not possible. Now this is where SQL-debugger kicks in.

==Starting the debugger==
You can start the debugger like this:
 $SYBASE/$SYBASE_ASE/bin/sqldbgr -U <username> -P <password> -S <host:portnumber>
The parameters username and password speak for themselves, host:portnumber should be substituted with the hostname (or IP-address) and the portnumber where your ASE server is listening on.

Make sure to put a space between the command switch and the argument, so put a space after -U, -P and the -S. Check that the SYBASE_JRE or JAVA_HOME environment variable has been set and points to a Java runtime environment.

Once you are logged on a prompt will be shown:
To exit the debugger, just type 'quit'

==Attaching to a session==
To connect to another session you must know its spid. Within the debugger you can execute normal sql commands like sp_who, but you must put "mysql" in front of the statement.
Remember that you are working with a debugger, so the output of e.g. sp_who is not very nicely formatted and you can only type your command on a single line. No "go" is needed to execute the command within the debugger. Just press 'Enter'.
 (sqldbg) mysql sp_who
When you have determined the spid, you can connect to the session with the attach command:
 (sqldbg) attach <spid>
When you get "You cannot debug a task that is not owned by you" the session is using another username than you. See below how to resolve this.
With the detach command you can detach from an attached session.
 (sqldbg) detach <spid>

==Retrieving the call stack==
When you have been attached successfully to the other spid you can see the call-stack from that session with the "where" command
 (sqldbg) where
 (dbo.myOtherProc::5::@c = 2)
 (dbo.myProc::17::@a = 1,@b = 3)
From this we can learn quite a lot:
* At the first line (dbo.myOtherProc::5::@c = 2) it is shown that the procedure dbo.myOtherProc is currently running, it is at line 5 within that stored procedure and a value of 2 has been passed into the @c parameter. Since this is the first line from the "where" output, the level is numbered as 0.
* At the second line (dbo.myProc::17::@a = 1,@b = 3) the procedure dbo.myProc is shown. This means that myProc called myOtherProc at line 17. Two variables were passed to the myProc procedure. Now we are at level 1.
* Finally, (ADHOC::1::null) tells us that the myProc procedure was called from a command line tool (like isql / SQL-Advantage), at line 1 of the batch. This is level 2.

==Viewing local variables==
Local variables can be viewed with the "show variables" command.
 (sqldbg) show variables
In this example no output is shown. This is because "show variables" works default at level 0 and a distinction is made between variables declared within the stored procedure, and parameters that were declared in the "create procedure" statement. Indeed, no local variables were declared within the myOtherProc stored procedure and therefore "show variables" shows nothing.
 (sqldbg) show variables at level 1
 int @c 2
With "show variables at level 1" we can actually see the declared local variables within the myProc procedure, their datatype and value. In this case @c was declared as an int and a value of 2 has been put into it.

Note: Global variables cannot be retrieved for an attached session. For instance, when the running procedure has changed the transaction isolation level this will be reflected in the @@isolation global variable. This change will not be seen within the debugger that has been attached to that particular session.

==Viewing temporary tables==
To view temporary tables use the "sql" command (not the mysql command), like this:
 (sqldbg) sql select * from #work
Now we know that the attached session created a temporary table with just one row. The column d_value has a value of 4.
To select data from a temporary table you must know the name of the table as it is created within the stored procedure. In practice this means that you should have access to the source code of the stored procedure. Alternatively you can run a select on sysobjects in the temporary database and query the name column as in this example:
 select name from tempdb..sysobjects
The output will show the first few characters of the name of the table, followed by the spid.

There is a small problem when the attached session is using a temporary database and that database is not accessible by you. Such a situation can occur when the login/application of the attached session is bound to a specific tempdb. In that case you need to impersonate the other login with the 'setuser' command.

==Modifying local variables or temporary tables==
Local variables (as viewed with the "show variables" command) can be modified with the "set" command. This is only possible for variables at level 0. Example:
 (sqldbg) set @z = 2
Temporary tables can be modified with the "sql" command. Example:
 (sqldbg) sql delete from #work where d_value = 3
Although you are attached to a session this does not mean that you have taken over that session. Any locks set by the session will also affect the debugger. When a temporary table is locked exclusively it cannot be modified through the debugger. Selection of data is always possible, so these locks can indeed by bypassed.

==Resolving "You cannot debug a task that is not owned by you"==
When you try to attach to a session and the error "You cannot debug a task that is not owned by you" is raised, the session is running with another username than yourself. By default you can only attach to a session running with the same username as yourself.
Using the T-sql command "set session authorization" you can impersonate another user and then use the attach command. Before you can execute "set session authorization" you should have been granted privilege to it, even when you have already sa_role or sso_role. To get this privilege, a dba should add you to the master database as a user and then execute "grant set proxy to <your-username>" (preferably with the ‘restricted’ option). Improper usage of "grant set proxy to" can introduce security issues so please read and understand the Sybase documentation with regard to this before using it.
When all requirements have been met you can execute "set session authorization" within the debugger. Example:
 (sqldbg) attach 18
 You cannot debug a task that is not owned by you
 (sqldbg) mysql set session authorization 'joe'
 (sqldbg) attach 18

==Further reading==
Sybase documentation for sqldbgr is here

Sybase documentation for "grant" statement is here


PL/SQL exception handling

Working on our US project recently, which is actually one-tier, simply db access/manipulation using huge chunk of SP.

Exception handling become extremely importantly, which happens missed in our US SP.

Refer to
for good and detailed explanation on exception handling. Sybase and Oracle PL/SQL are similar in such case.

when ..
when ..
else ..

Exception Handling

In this section we will discuss about the following,
1) What is Exception Handling.
2) Structure of Exception Handling.
3) Types of Exception Handling.
1) What is Exception Handling?

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly. When an exception occurs a messages which explains its cause is recieved.
PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message
By Handling the exceptions we can ensure a PL/SQL block does not exit abruptly.
2) Structure of Exception Handling.

The General Syntax for coding the exception section


Declaration section


Exception section


WHEN ex_name1 THEN

-Error handling statements

WHEN ex_name2 THEN

-Error handling statements


-Error handling statements


General PL/SQL statments can be used in the Exception Block.

When an exception is raised, Oracle searches for an appropriate exception handler in the exception section. For example in the above example, if the error raised is ‘ex_name1 ‘, then the error is handled according to the statements under it. Since, it is not possible to determine all the possible runtime errors during testing fo the code, the ‘WHEN Others’ exception is used to manage the exceptions that are not explicitly handled. Only one exception can be raised in a Block and the control does not return to the Execution Section after the error is handled.

If there are nested PL/SQL blocks like this.


Declaration section



Declaration section


Execution section


Exception section



Exception section


In the above case, if the exception is raised in the inner block it should be handled in the exception block of the inner PL/SQL block else the control moves to the Exception block of the next upper PL/SQL Block. If none of the blocks handle the exception the program ends abruptly with an error.
3) Types of Exception.

There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions
a) Named System Exceptions

System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions.

For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.

Named system exceptions are:
1) Not Declared explicitly,
2) Raised implicitly when a predefined Oracle error occurs,
3) caught by referencing the standard name within an exception-handling routine.
Exception Name Reason Error Number


When you open a cursor that is already open.



When you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened.



When a SELECT…INTO clause does not return any row from a table.



When you SELECT or fetch more than one row into a record or variable.



When you attempt to divide a number by zero.


For Example: Suppose a NO_DATA_FOUND exception is raised in a proc, we can write a code to handle the exception as given below.


Execution section



dbms_output.put_line (‘A SELECT…INTO did not return any row.’);


b) Unnamed System Exceptions

Those system exception for which oracle does not provide a name is known as unamed system exception. These exception do not occur frequently. These Exceptions have a code and an associated message.

There are two ways to handle unnamed sysyem exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.

We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT.
EXCEPTION_INIT will associate a predefined Oracle error number to a programmer_defined exception name.

Steps to be followed to use unnamed system exceptions are
• They are raised implicitly.
• If they are not handled in WHEN Others they must be handled explicity.
• To handle the exception explicity, they must be declared using Pragma EXCEPTION_INIT as given above and handled referecing the user-defined exception name in the exception section.

The general syntax to declare unnamed system exception using EXCEPTION_INIT is:


exception_name EXCEPTION;


EXCEPTION_INIT (exception_name, Err_code);


Execution section


WHEN exception_name THEN

handle the exception


For Example: Lets consider the product table and order_items table from sql joins.

Here product_id is a primary key in product table and a foreign key in order_items table.
If we try to delete a product_id from the product table when it has child records in order_id table an exception will be thrown with oracle code number -2292.
We can provide a name to this exception and handle it in the exception section as given below.


Child_rec_exception EXCEPTION;


EXCEPTION_INIT (Child_rec_exception, -2292);


Delete FROM product where product_id= 104;


WHEN Child_rec_exception

THEN Dbms_output.put_line(‘Child records are present for this product_id.’);



c) User-defined Exceptions

Apart from sytem exceptions we can explicity define exceptions based on business rules. These are known as user-defined exceptions.

Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception section.

For Example: Lets consider the product table and order_items table from sql joins to explain user-defined exception.
Lets create a business rule that if the total no of units of any particular product sold is more than 20, then it is a huge quantity and a special discount should be provided.


huge_quantity EXCEPTION;

CURSOR product_quantity is

SELECT p.product_name as name, sum(o.total_units) as units

FROM order_tems o, product p

WHERE o.product_id = p.product_id;

quantity order_tems.total_units%type;

up_limit CONSTANT order_tems.total_units%type := 20;

message VARCHAR2(50);


FOR product_rec in product_quantity LOOP

quantity := product_rec.units;

IF quantity > up_limit THEN

message := ‘The number of units of product ‘ || ||

‘ is more than 20. Special discounts should be provided.

Rest of the records are skipped. ‘

RAISE huge_quantity;

ELSIF quantity up_limit THEN

RAISE huge_quantity;

ELSIF quantity < up_limit THEN

v_message:= 'The number of unit is below the discount limit.';


Dbms_output.put_line (message);



WHEN huge_quantity THEN

raise_application_error(-2100, 'The number of unit is above the discount limit.');



Cool Advise on Using DB cursor


If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.

Here are some alternatives to using a cursor:

Use temp tables
Use derived tables
Use correlated sub-queries
Use the CASE statement
Perform multiple queries

More often than not, there are non-cursor techniques that can be used to perform the same tasks as a SQL Server cursor. [2000, 2005, 2008] Updated 1-29-2009


If you do find you must use a cursor, try to reduce the number of records to process.

One way to do this is to move the records that need to be processed into a temp table first, then create the cursor to use the records in the temp table, not from the original table. This of course assumes that the subset of records to be inserted into the temp table are substantially less than those in the original table.

The lower the number of records to process, the faster the cursor will finish. [2000, 2005, 2008] Updated 1-29-2009


If the number of rows you need to return from a query is small, and you need to perform row-by-row operations on them, don’t use a server-side cursor. Instead, consider returning the entire rowset to the client and have the client perform the necessary action on each row, then return any updated rows to the server. [2000, 2005, 2008] Updated 1-29-2009


If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server.

If you are unable to use a fast-forward cursor, then try the following cursors in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset. [2000, 2005, 2008] Updated 1-29-2009


Avoid using static/insensitive and keyset cursors, unless you have no other choice. This is because they cause a temporary table to be created in TEMPDB, which increases overhead and can cause resource contention issues. [2000, 2005, 2008] Updated 1-29-2009


If you have no choice but to use cursors in your application, try to locate the SQL Server tempdb database on its own physical device for best performance. This is because cursors may use the tempdb for temporary storage of cursor data. The faster your disk array running tempdb, the faster your cursor will be. [2000, 2005, 2008] Updated 1-29-2009


Using cursors can reduce concurrency and lead to unnecessary locking and blocking. To help avoid this, use the READ_ONLY cursor option if applicable, or if you need to perform updates, try to use the OPTIMISTIC cursor option to reduce locking. Try to avoid the SCROLL_LOCKS cursor option, which reduces concurrency. [2000, 2005, 2008] Updated 1-29-2009


When you are done using a cursor, don’t just CLOSE it, you must also DEALLOCATE it. Deallocation is required to free up the SQL Server resources used by the cursor. If you only CLOSE the cursor, locks are freed, but SQL Server resources are not. If you don’t DEALLOCATE your cursors, the resources used by the cursor will stay allocated, degrading the performance of your server until they are released. [2000, 2005, 2008] Updated 1-29-2009


If it is appropriate for your application, try to load the cursor as soon as possible by moving to the last row of the result set. This releases the share locks created when the cursor was built, freeing up SQL Server resources. [2000, 2005, 2008] Updated 1-29-2009


If you have to use a cursor because your application needs to manually scroll through records and update them, try to avoid client-side cursors, unless the number of rows is small or the data is static. If the number of rows is large, or the data is not static, consider using a server-side keyset cursor instead of a client-side cursor. Performance is usually boosted because of a reduction in network traffic between the client and the server. For optimum performance, you may have to try both types of cursors under realistic loads to determine which is best for your particular environment. [2000, 2005, 2008] Updated 1-29-2009


When using a server-side cursor, always try to fetch as small a result set as possible. This includes fetching only those rows and columns the client needs immediately. The smaller the cursor, no matter what type of server-side cursor it is, the fewer resources it will use, and performance will benefit. [2000, 2005, 2008] Updated 1-29-2009


If you need to perform a JOIN as part of your cursor, keyset and static cursors are generally faster than dynamic cursors, and should be used when possible. [2000, 2005, 2008] Updated 1-29-2009


If a transaction you have created contains a cursor (try to avoid this if at all possible), ensure that the number of rows being modified by the cursor is small. This is because the modified rows may be locked until the transaction completes or aborts. The greater the number of rows being modified, the greater the locks, and the higher the likelihood of lock contention on the server, hurting performance. [2000, 2005, 2008] Updated 1-29-2009


In SQL Server, there are two options to define the scope of a cursor. LOCAL and GLOBAL keywords in the DECLARE CURSOR statement are used to specify the scope of a cursor. A GLOBAL cursor can be referenced in any stored procedure or batch executed by a connection. LOCAL cursors are more secure as they cannot be referenced outside the procedure or trigger unless they are passed back to the calling procedure or trigger, or by using an output parameter. GLOBAL cursors must be explicitly deallocated or they will be available until the connection is closed. For optimum performance, you should always explicitly deallocate a cursor when you are done using it. LOCAL cursors are implicitly deallocated when the stored procedure, the trigger, or the batch in which they were created terminates. We can use LOCAL cursors for more security and better scope of the cursor in our application, which also helps to reduce resources on the server, boosting performance. Contributed by Nataraj Prakash. [2000, 2005, 2008] Updated 1-29-2009

sybase SP “set chained off”

Case Description
Symptom 1 – Receiving following error in debug log: “powersoft.powerj.db.java_sql.Query: [jf_pif.query_pif update query in getMoreResults] could not obtain more results due to exception: com.sybase.jdbc.SybSQLException: Stored procedure ‘sp_proc_name’ may be run only in unchained transaction mode. The ‘SET CHAINED OFF’ command will cause the current session to use unchained transaction mode.”

Symptom 2 – Using a stored procedure as a datasource for a datawindow, receiving the following error: “Select error:Stored procedure ‘sp_proc_name’ may be run only in unchained transaction mode. The Set Chained Off command will cause the current session to use unchained transaction mode.”

Tip or Workaround
A stored procedure that executes against an Adaptive Server Enterprise (ASE) database can be set to run in one of three transaction modes: CHAINED, UNCHAINED, and ANY. The sql statement “set chained on/off” will set the chained mode on and off, respectively. The transaction mode ANY will run in both chained or unchained mode.

PowerJ uses jConnect (JDBC) to connect to ASE databases. The chained mode is tied to the AutoCommit() mode of the JDBC connection.

If the stored procedure is set to run in Unchained mode, then set AutoCommit to True, and if the stored procedure is set to run in Chained mode, AutoCommit should be set to False. If the stored procedure is set to run in ANY transaction mode, it doesn’t matter what the AutoCommit mode is.

The AutoCommit mode can be found on the property sheet of the PowerJ transaction object. To adjust the AutoCommit mode, double-click the transaction object in your PowerJ project. Click on the Options tab.

To set AutoCommit to True: Under Initial Settings, choose “Set the following properties” and check (enable) AutoCommit. Click on OK.

Once you have adjusted the AutoCommit mode, re-run the project.

{ASE ships a stored procedure called sp_proc_xmode. It takes two parameters: a stored procedure name, and a transaction mode. This stored procedure can be used to change the transaction mode of any of your existing stored procedures.}
To rectify the ‘Set Chained Off’ error, do the following:

1. Check the transaction mode of the stored procedure

2. If the mode of the stored procedure is Unchained, set AutoCommit to True. If the transaction mode is Chained, set AutoCommit to False.


basically, its
EXEC sp_procxmode ‘dbo.p_CheckHasCashOrder’,’unchained’

if autocommit is set true.