--

--

/* and */

/* and */

The translator moves any comments before your CREATE PROCEDURE statement to just after the procedure heading and before its parameters (if any). Oracle accepts comments prior to your CREATE PROCEDURE statement, but doesn't include them in the source.

@VAR

MY_VAR

where GROUPTYPE = @GROUPTYPE

where GROUPTYPE = GROUPTYPE

The translator provides a default template that maps @VAR to MY_VAR, but you can provide your own on the command line. Your template requires exactly one percent sign (%) which is where your variable name is placed. The default template is

MY_%

%

%.%

Variable names can contain special characters (like @) if they are quoted. The translator automatically determines if quotes are needed or you can quote the template yourself; for example,

@%

@GROUP

"GROUP"

The translator will preserve case-sensitivity for any reserved words you might use. That is because quoted variable names are case-sensitive.

@VAR = NULL

or

@VAR != NULL

VAR IS NULL

or

VAR IS NOT NULL

Suppose VAR is NULL. The expression

VAR != 'STRING'

VAR = 'STRING'

VAR like '[specifier]'

VAR like '[specifier]'

HTTP.PATINDEX('[specifier]',VAR) > 0

statement

statement;

DECLARE

DECLARE

The translator requires that all declarations occur before the first statement. If you have declarations (other than cursors) after the first statement, you should move them to the outer-most level so they will be translated. Your cursor declarations may remain where they are.

Declarations other than your cursor declarations (which are translated to open statements) are moved to the outer block.

In your declaration comments of the form (--) are preserved. Comments of the form /* and */ are lost.

IF expression
    statement

IF expression THEN
    statement;
END IF;

IF expression THEN
BEGIN
...
END;
END IF;

ELSE

ELSE

END IF;
END IF;
END IF;

WHILE expression
    statement

WHILE expression LOOP
    statement;
END LOOP;

BREAK

EXIT;

GOTO label

...

label:

GOTO label;

...

<<label>>

Be sure that your label points to the beginning of a statement. If your label occurs as the line of your procedure source, you will get a syntax error. You can add NULL or RETURN if this is the case.

CONTINUE

GOTO CONTINUE_1;

'STRING'

'STRING'

PL/SQL is case-insensitive, but the contents of strings are case-sensitive. 'STRING' does not equal 'String'.

The translator does not translate SQL embedded within strings. If you are building dynamic SQL (perhaps because you are using FILTER=CGI), you will have to translate this yourself.

"STRING"

'STRING'

DECLARE variable CURSOR FOR

OPEN variable FOR

OPEN cursor

One problem is that you declare a cursor that depends on the value of a variable that is not set until after the declaration and before your open statement. In this case, you must move the assignment of that variable before your cursor declaration because that is when the variable's value is determined.

DEALLOCATE cursor

RETURN number

RETURN;

BEGIN TRANSACTION [transaction_name]

ROLLBACK TRANSACTION [transaction_name]

COMMIT TRANSACTION

COMMIT WORK;

EXEC PROCNAME

    @NAME = @NAME
,   @VALUE = @VALUE output

PROCNAME
(
    NAME => MY_NAME
,   VALUE => MY_VALUE
)   ;

Notice that the translator does not map parameter names passed to another stored procedure, but does map your parameter values if they are Sybase variable names. Parameter names are (on the whole) preserved with the exception of reserved words (like SIZE) which are mapped to something else (see below) or left untouched (in which case your stored procedure will not compile).

The translator also doesn't touch output parameters. The theory here is that this name is going to be referenced by another stored procedure, and if the name were mapped, it would be impossible to return the local (mapped) declaration to that output parameter. Obviously, output parameters should not be used in a where clause with a table having a column of that name.

Like Sybase you can declare your parameters with default values. Unlike Sybase you can not pass parameters to a stored procedure that do not exist.

EXEC @RSLT = PROCNAME

RSLT:=PROCNAME;

EXEC @RSLT = @PROCNAME

EXECUTE IMMEDIATE
'BEGIN :1=' || PROCNAME || ';END;'
USING OUTPUT RSLT;

SELECT @x = expression

x:=expression;

While parsing string or arithmetic expressions, the translator can keep up as long as it knows the type of variable or literal. If the preceding or succeeding operand is a literal string or number, plus-signs are converted correctly. This also applies to any parameters or local declarations when the translator can determine the type of this expression. If your expression consists of column names in a table, the translator will look its type up in Oracle. In this case it is important that the table exists. If the table doesn't exist and you started editing the Oracle source yourself, you run the risk of missing something that (although compiles) is actually incorrect.

SELECT "<html>"
,      "<body>"

HTTP.WRITE
(
    '<html>'
,   '<body>'
)   ;

WRITE() can accept up to 100 parameters (an arbitrary number I thought sounded good). Each parameter to WRITE() may be as large as 32,767 bytes, but the total number of bytes of all parameters may not exceed 32,767 bytes.

WRITELN() is similar to WRITE() except that it appends a carriage-return to the end of each parameter. This serves no other purpose than making your HTML output look good when you do a view source. Be careful if you use WRITELN() yourself because there are tames when carriage returns are undesirable, like in URL's. The translator always uses WRITE() because it is faster to use than WRITELN().

SELECT 'function check(month)
{
    if (parseInt(month,Base10) == 4
    ||  parseInt(month,Base10) == 6	
    ...	
}'

WRITE
(
    'function check(month)
',  '{
',  'if (parseInt(month,Base10) == 4
',  '||  parseInt(month,Base10) == 6
',  '...
',  '}'
)   ;

WRITELN
(
    'function check(month)'
,   '{'
,   'if (parseInt(month,Base10) == 4'
,   '||  parseInt(month,Base10) == 6'
,   '...'
,   '}'
)   ;

WRITE() will accept strings as long as 32,767 bytes. Your strings are now preserved.

SELECT @variable = column
FROM    table

BEGIN
SQL_ERROR:=0; SQL_ROWCOUNT:=0;
SELECT column
INTO   variable
FROM   table;
EXCEPTION
WHEN NO_DATA_FOUND THEN SQL_ROWCOUNT:=0;
WHEN TOO_MANY_ROWS THEN SQL_ROWCOUNT:=2;
WHEN OTHERS THEN SQL_ERROR:=SQLCODE;
HTTP.WRITE('<h3>',SQLERRM,'</h3>');
END;

The translator executes this SQL statement in its own block and ignores the condition when exactly one row is not returned. Where you now interrogate @@error or @@rowcount, the translator will translate these to SQL_ERROR and SQL_ROWCOUNT. You can use them exactly the way you are used to.

Note that if this statement returns more than one row, SQL_ROWCOUNT (@@rowcount) is set to 2. If you absolutely, positively have to know the number of collisions, then you will have to code a count(*) on your own. If you are only interested in SQL_ROWCOUNT > 1, then all will be well.

select '<tr>'
,      '<td>'
,      NAME
,      '</td>'
,      COLOR
,      '</td>'
,      '</tr>'
from   HTTP_COLORS

BEGIN
DECLARE CURSOR cur IS 
SELECT NAME	   
,      COLOR	
from   HTTP_COLORS;
col_1 varchar(255);
col_2 varchar(255);
BEGIN
    OPEN cur;
    FETCH cur INTO col_1, col_2;
    While (cur%FOUND) LOOP
    BEGIN
        HTTP.WRITE
        (
            '<tr>'      
        ,   '<td>'      
        ,   col_1
        ,   '</td>'       
        ,   col_2
        ,   '</td>'       
        ,   '</tr>'       
        )   ;
        FETCH cur INTO col_1, col_2;
    END;
    END LOOP;
    CLOSE cur;
END;
END;

Sometimes you use this construct because you know exactly one row will be returned. The translator doesn't know this and always creates a cursor declaration. In this case, it is cleaner to issue a SELECT ... INTO to retrieve that single row into local variables. You would then pass those local variables to WRITE().

You definitely don't want to use this as an example of how you should create cursors in Oracle. The translator does this because it is the only way to map your select statement in-line. You will notice that the mapping of Sybase cursors looks very similar to Oracle cursors. Oracle even allows you to fetch all your columns into a "record" instead of individual, local variables. This is extremely useful because there's less for you type (you fetch into the record declaration) and the attributes of the table are retrieved automatically.

If you've ever built a drop-down list dynamically from a table and marked the appropriate SELECTED option, you probably did this with 3 select statements:

select '<option>' + name from table where name < @name
select '<option selected>' + @name
select '<option>' + name from table where name > @name

Notice that the variables col_1 and col_2 are locally declared as varchar(255). This will work for all column types except TEXT and IMAGE columns and is why the translator uses them, but this is not the most efficient use of Oracle as I'm sure you can imagine. col_1 and col2 should really be declared thusly,

col_1 CRON.NAME%TYPE;
col_2 CRON.COLOR%TYPE;

EXISTS (SELECT ...)

HTTP.SYBEXISTS('SELECT ...')

DECLARE SQL_EXISTS int;
BEGIN
SELECT 1
INTO   SQL_EXISTS
FROM   DUAL
WHERE  EXISTS(SELECT ...);
EXCEPTION WHEN OTHERS THEN SQL_EXISTS:=0;
END;
IF (SQL_EXISTS=1) THEN

(SELECT ...)

HTTP.SYBSELECT('SELECT ...')

SYBSELECT() returns a varchar. The translator relies on Oracle's ability to implicitly convert its result to a number or date if needed when the final result is evaluted; for example,

DECLARE I INT;
BEGIN
    I:='2' + 3;
END;

You can easily rewrite this code inline to speed things along:

DECLARE SQL_COUNT INT;
BEGIN
SELECT COUNT(*)
INTO   SQL_COUNT
FROM   TBL;
EXCEPTION WHEN OTHERS THEN SQL_COUNT:=0;
END;
if (SQL_COUNT > 3) THEN

CREATE PROCEDURE P
(
    @NAME varchar(30) = NULL
,   @VALUE varchar(20) = NULL
,   @TIMESTAMP smalldatetime = null
,   @DEFAULT varchar(255) = NULL OUT
)
AS
    SELECT @NAME = "MYSELF"
    SELECT @DEFAULT = "BLUE";
    RETURN 0

CREATE OR REPLACE PROCEDURE P
(
    NAME          IN  VARCHAR2    := NULL
,   VALUE         IN  VARCHAR2    := NULL
,   TIMESTAMP     IN  VARCHAR2    := NULL
,   PREFERENCE    OUT VARCHAR2
)
AS
MY_NAME               varchar(30) := NAME;
MY_VALUE              varchar(30) := VALUE;
MY_TIMESTAMP          DATE        := HTTP.DATENAME(TIMESTAMP);
BEGIN
    MY_NAME:='MYSELF';
    PREFERENCE:='BLUE';
    RETURN;
END;

Input parameters may not be used as the target of an assignment statement. This is probably something you do all the time, but this is not allowed. The translator maps all parameters with the default template (unless you have suppressed this option) so that parameters can not only be used as the assignment of an expression, but also so that their name does not collide with a column name when both are used at the same time.

Output parameters may not specify default values. If you do so, the translator will omit them and give you a warning this has occurred. You might have to rethink this. I've seen one case (and it wasn't I) where the programmer was using an output parameter when they really meant an input parameter. Sybase is forgiving. Oracle is unforgiving.

Oracle likes generic names in the procedure heading, like NUMBER or VARCAHR2 instead of INT or VARCHAR(255). VARCHAR2 can be as large as 32,767 bytes, and you can manipulate it with such things as substring (I mean substr) just like you are used to. Although Oracle support blobs, the maximum column size allowed for a varchar is 4000. The table CRON_PARAMETERS even allows you to pass that large a VALUE to SP_CRON_INSERT_PARAMETER.

Notice too that DEFAULT is actually a reserved word in Oracle. This was mapped to PREFERENCE by the translator. Output parameters never get mapped with the default template because it is impossible to redefine then associate the local declaration with this named parameter.

Although dates can be passed to stored procedures, the translator assumes your date parameters are really strings submitted via an HTML form screen. If no format is specified (and there would be none), strings must match the default format (currently DD-MON-YY). To provide the same flexibility that you now enjoy in Sybase, your datetime parameters are declared as varchar2 in the procedure heading, then passed to HTTP.DATETIME when the parameter name is mapped locally. HTTP.DATETIME recognizes the standard Sybase styles and returns a valid DATE to your program.

Stored procedures may not return a value. If you currently rely on the return status, you might want to consider using an output variable or even more desirable is to create an Oracle function. Oracle functions can return any datatype, not just integers. Passing the option -F to the translator will compile your procedure as a function.

You've probably already figured out that "OR REPLACE" means you don't have to drop the procedure if it already exists. That's very convenient. Even more convenient is that the permissions on your stored procedure are not lost when you submit procedures in this fashion.

DELETE tablename

DELETE FROM tablename;

INSERT tablename

INSERT INTO tablename;

CHARINDEX(char_1,char_2)

INSTR(char_2,char_1)

SUBSTRING()

SUBSTR()

CONVERT(INT,exprepression)

TO_NUMBER(expression)

CONVERT(VARCHAR,expression)

TO_CHAR(expression)

or

RTRIM(expression)

CONVERT(CHAR,expression)

or

CONVERT(CHAR(30),expression)

RPAD(expression,1)

or

RPAD(expression,30)

CONVERT(DATETIME,string)

HTTP.DATETIME(string)

CONVERT(VARCHAR,string,style))

TO_CHAR(string,format)

CONVERT(MONEY,123.456)

TO_CHAR(123.456,'999,999,999,999.99')

CONVERT(NUMERIC,123)

or

CONVERT(NUMERIC(12,2),123)

TO_CHAR(123,'999999999999999999')

or

TO_CHAR(123,'999999999999.99')

CONVERT(INT,123.456)

ROUND(123.456)

@@spid

USERENV('SESSIONID')

@@ERROR

SQLCODE

For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100.

@@ROWCOUNT

SQL%ROWCOUNT

or

SQL_ROWCOUNT

%ROWCOUNT can also be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the latest fetch returned a row.

A =* B

A(+) = B

A *= B

A = B(+)

DATALENGTH()

LENGTHB()

DATETIME

and

SMALLDATETIME

DATE

and

DATE

TEXT

and

IMAGE

CLOB

and

BLOB

You cannot pass a LOB directly to WRITE(), but you can retrieve a LOB in pieces and pass them. See Example: Display the LOB Data Using PL/SQL from the write up on Internal Persistent LOBs.

You can pass a "lob locator" to HTTP_SELECT_CLOB() or HTTP_SELECT_BLOB(). Binary LOB's need to be sent to Sybernet as Hexadecimal strings which are translated to Ascii using the <ASCEncode> and </ASCEncode> tags. HTTP_SELECT_BLOB() does this automatically for you.

DECLARE Locator CLOB;
BEGIN
    SELECT DESCRIPTION
    INTO   Locator
    FROM   HTTP_HELPTEXT
    WHERE  ROW_ID = MY_ROW_ID;
    HTTP.HTTP_SELECT_CLOB(Locator);
END;

DECLARE Locator BLOB;
BEGIN
    SELECT PICTURE
    INTO   Locator
    FROM   HTTP_PICTURES
    WHERE  ID = 'bug';
    HTTP.HTTP_SELECT_BLOB(Locator);
END;

There are also two procedures for writing writing LOB's (HTTP_WRITE_CLOB and HTTP_WRITE_BLOB). Since parameters to a stored procedure can be as large as 32,767 bytes, they have advantage over writing INSERT statements since the largest value allowed here is 4,000 bytes.

DECLARE Locator CLOB;
BEGIN
    INSERT INTO HTTP_HELPTEXT values (MY_ROW_ID,sysdate,EMPTY_CLOB());
    SELECT DESCRIPTION
    INTO   Locator
    FROM   HTTP_HELPTEXT
    WHERE  ROW_ID = MY_ROW_ID;
    FOR    UPDATE;
    HTTP.HTTP_WRITE_CLOB(Locator,'Hello World');
END;

CHAR()

CHR()

ISNULL()

NVL()

USER_ID()

HTTP.USER_ID()

USER_NAME()

HTTP.USER_NAME()

SUSER_NAME()

USER

DB_NAME()

HTTP.DB_NAME

db_name + ".dbo" + "WE_PAGER"

Note: This theory falls apart if your Run Context is User instead of Author. Since most procedures will be compiled with Author, this should work fine. For more information, see the section on Run Context.

GETDATE()

SYSDATE

/

/

%

MOD

REPLICATE(char_exp,n)

HTTP.REPLICATE(char_exp,n)

REVERSE(char_exp)

HTTP.REVERSE(char_exp)

RIGHT(char_exp,n)

HTTP.RIGHT(char_exp,n)

SPACE(n)

HTTP.SPACE(n)

STR(approx_numeric,length,decimal)

HTTP.STR(approx_numeric,length,decimal)

VALID_NAME(name)

HTTP.VALID_NAME(name)

STUFF(char_expr1,start,length,char_expr2)

HTTP.STUFF(char_expr1,start,length,char_expr2)

DATEPART(datepart,date)

HTTP.DATEPART('datepart',date)

DATENAME(datepart,date)

HTTP.DATENAME('datepart',date)

DATEADD(datepart,number,date)

HTTP.DATEADD('datepart',number,date)

While extremely accurate, this function is also extremely slow. You are encouraged to use Oracle datetime arithmetic for speedier results.

DATEDIFF(datepart,date1,date2)

HTTP.DATEDIFF('datepart',date1,date2)

While extremely accurate, this function is also extremely slow. You are encouraged to use Oracle datetime arithmetic for speedier results.

@@sqlstatus = 0

or

@@sqlstatus = 2

or

@@sqlstatus != 1

cursor%FOUND

or

cursor%NOTFOUND

or

cursor%ISOPEN

In the last example, there is no equivalent to @@sqlstatus = 1. In the case where you test for not equal to, the translator assumes that you will later test for found (0) or notfound (2). Thus the expression cursor%ISOPEN should provide the desired effect.

select * into #TEMP from TableName

CREATE TABLE #TEMP

CREATE TABLE TEMP_TEMP

Permanent tables work here because you are in a transaction, and you will be deleting everything from that table before your stored procedure terminates. Even if your procedure raises an error, the information is still erased because Oracle rolls back your transaction.

Many times I have created temporary tables when a permanent table would have worked equally as well, if not better. Oracle recommends adding a SESSION_ID column to your temporary table so that you insure the rows inserted and retrieved go hand-in-hand with the user invoking your stored procedure. SESSION_ID is similar (if not identical) to @@spid. The expression

USERENV('SESSIONID')

Sometimes you create temporary tables because you just want a list of names that you can step through. This is possible with something called a collection (see Collection and Records).

TYPE OBJECT_NAMES IS TABLE OF VARCHAR2(15);
OBJECT_TYPES OBJECT_NAMES := OBJECT_NAMES
(
    'FUNCTION'
,   'PACKAGE'
,   'PACKAGE BODY'
,   'PROCEDURE'
,   'TABLE'
,   'TRIGGER'
,   'VIEW'
)   ;

FOR i IN OBJECT_TYPES.FIRST..OBJECT_TYPES.LAST LOOP
    WRITE(OBJECT_TYPES(i));
END LOOP;

DROP TABLE #TEMP

DELETE FROM TEMP_TEMP;

If you don't drop the table because you expect it to vanish as soon as your procedure exits, then you will need to add the appropriate DELETE statement yourself. So, watch out for this.

DROP TABLE PERMANENT

EXECUTE IMMEDIATE 'DROP TABLE PERMANENT';

OBJECT_ID("PROCNAME")

HTTP.OBJECT_ID('PROCNAME')

HTTP.OBJECT_ID() also recognizes parameters that look like fully qualified Sybase identifiers. This can happen because you built this name dynamically or it was contained in a string. Names that look like

DATABASE . OWNER . NAME

DATABASE . NAME

OBJECT_NAME(object_id)

HTTP.OBJECT_NAME(object_id)

@@PROCID

HTTP.OBJECT_ID('procname')

WAITFOR DELAY "HH:MM:SS"

DBMS_LOCK.SLEEP(seconds);

GRANT ...

EXECUTE IMMEDIATE 'GRANT ...';

Refer to the PL/SQL User's Guide and Reference page for more information on Native Dynamic SQL.

REVOKE ...

EXECUTE IMMEDIATE 'REVOKE ...';

DELTEK.DELTEK.EMPL

or

HRPRD.dbo.PS_PERSONAL_DATA

DELTEK.EMPL

or

HRPRD.PS_PERSONAL_DATA

something

BEGIN
something;
END;

if object_id("sp_html_procname") != NULL
drop procedure sp_html_procname

BEGIN
if HTTP.OBJECT_ID('sp_html_procname') IS NOT NULL
EXECUTE IMMEDIATE 'DROP procedure sp_html_procname';
END IF;
END;

SET ROWCOUNT n

PATINDEX("%pattern%",char_expr)

HTTP.PATINDEX("%pattern%",char_expr)

IDENTITY

NULL

CREATE OR REPLACE TRIGGER CRON_TRIGGER
BEFORE INSERT ON CRON
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE ROW_ID CRON.ROW_ID%TYPE;
BEGIN
    :NEW.ROW_ID:=HTTP.GET_IDENTITY(USER,'CRON','ROW_ID');
    RETURN;
END;

• OWNER (the owner of this table)
• TABLE_NAME (the name of this table)
• COLUMN_NAME (the name of the identity column)
• IDENTITY (the last identity value)

COLUMN_NAME is also used by sp_csv when you copy this table to Sybase (so that it can restore the IDENTITY declaration) or Oracle (so it can build another trigger).

Instead of asking for @@identity (which you are unable to do), the translator will instead ask Oracle to return the identity column when data is inserted.

INSERT INTO CRON
(
    PROCNAME
,   TIMESTAMP
)
VALUES
(
    'sp_html_example'
,   '14-APR-01'
)
RETURNING ROW_ID INTO MY_ROW_ID;

@@IDENTITY

SQL_IDENTITY

IF (@a=1)

    INSERT TABLE VALUES ('123')

ELSE

    INSERT TABLE VALUES ('456')

select @@identity

Since an IDENTITY column can exist anywhere in a table, how does it know the name of your identity column? In general it doesn't know, unless the table was created by the translator or was copied to Oracle with sp_csv Both routines will create an entry in the table HTTP.HTTP_IDENTITIES just for this purpose. If you created the table on your own, you can "seed" this table by calling HTTP.GET_IDENTITY, passing it the owner, table name, and identity column.

DECLARE seed NUMBER(12);
BEGIN
seed:=HTTP.GET_IDENTITY('DELTEK','PROJ','PROJ_ID');
END;

INSERT TABLE VALUES (1,2,3)

INSERT INTO TABLE VALUES (1,2,3)

or

INSERT INTO TABLE (A,B,C) VALUES (1,2,3)
RETURNING identity INTO SQL_IDENTITY

Note that if you do an insert into a table containing an identity column and do not reference @@identity, you will get a syntax from Oracle if all the column names are not supplied.

raiserror 42001 'Cursor failed.'

raise_application_error (-42001,'Cursor failed.');

HOST_NAME()

HTTP.HOST_NAME()

Sybernet maintains this information in the table HTTP.HTTP_USERDATAFILE when your session is started and deletes this information when your session is ended. Thus, only the IP address of Sybernet users can be retrieved with this function.

If a session is abnormally terminated (perhaps because it was killed), Sybernet will not erase this information until a new user attempts to occupy this slot.

sp_getProcname @PROCID, @PROCNAME output

HTTP.SP_GETPROCNAME (PROCID,PROCNAME)

As of this writing these are the columns in HTTP.USERDATAFILE:

• SESSIONID (the session ID)
• USER_NAME (the user name)
• USER_ID (the user ID)
• OBJECT_ID (the object ID of the last procedure that called sp_getProcname)
• HOSTNAME (the IP address or host name for this session)
• HTTP_USER_AGENT (the name if the client software invoking Sybernet)

When a session is ended, this information is erased.

If your variables names (sans the at-sign) are unique and do not match column names, then you can tell the translator to leave your variable names untouched with the following template:

%

%.%

@%

MY_%

If you select Function, you will not be able to register it with the Stored Procedure Register or with the Sybercron Register.

Sybercron no longer uses your return result to determine if your procedure completed successfully (since there is none). Instead, Sybercron uses the built-in function ERRNUM to determine this.

If you are compiling a trigger instead of a stored procedure, that needs to be specified as well, though I think you may be disappointed with some of the translation. I confess I didn't spend too much time on this, and although triggers are very similar to procedures, those constructs that are unique to triggers are not mapped very well (if at all).

User says you want to run under the context of the user that is invoking this procedure. Sybase procedures in sybsystemprocs are like this; for example, the Sybernet Interactive SQL procedure was compiled with a run context of user so that you can only see tables or execute procedures that you are allowed to touch.

An interesting upshot of this facility is that when you are running the Sybernet Interactive SQL, Stored Procedure Editor, Stored Procedure Register, or Sybercron Register, you can't see anything you are not allowed to see. This is different from Sybase where although you may not touch a particular table, you can see that it exists.

• Don't Touch
• uppercase (reserved words are uppercase; non-reserved words are lowercase)
• lowercase (reserved words are lowercase; non-reserved words are uppercase)
• uppercase all (reserved and non-reserved words are uppercase)
• lowercase all (reserved and non-reserved words are lowercase)

The default is "uppercase" which means Oracle reserved words are in uppercase and non-reserved words are lowercase. It's purely cosmetic, so the choice is yours.

You can't pass a string to a DATE variable in Oracle without also specifying the format of that date. For example,

TO_DATE('Jun 12, 2001','Mon DD, YYYY')

If your datetime parameters are really dates because they are being passed to this procedure by another procedure, then you will want to reset this option.

This option determines how many spaces a tab represents. 4 (the default) and 8 are the two most popular. If you select 0, then your tabs are not removed, but then the translator won't know how much to indent the text that it inserts.

#WARNING: @JOB_FAMILY IS DECLARED AS CHAR(2) BUT THIS COLUMN WAS DECLARED AS CHAR(6)

Costpoint, for example, runs on a case in-sensitive server, but Oracle is case-sensitive. Consider the expression

VAR = 'Sybase'

Even if it does compile (or when it does compiles), it is still your responsibility to make sure it works.

create procedure sp_html_colors
as
    select '<table border=1>'
    select '<tr>'
    ,      '<th>name</th>'
    ,      '<th>color</th>'
    ,      '</tr>'
    select '<tr bgcolor=#'
    ,      COLOR
    ,      '>'
    ,      '<td>'
    ,      NAME
    ,      '</td>'
    ,      '<td>'
    ,      COLOR
    ,      '</tr>'
    from   http..HTTP_COLORS
    select '</table>'
    return 0
go

CREATE OR REPLACE procedure SP_HTML_COLORS
as
/*
** YOUR DECLARATIONS GLOBAL TO ALL BLOCKS
*/
SQL_ERROR                       INT;                            -- @@error
SQL_ROWCOUNT                    INT;                            -- @@rowcount
SQL_IDENTITY                    INT;                            -- @@identity
begin
    HTTP.WRITE
    (
        '<table border=1>'
    ,   '<tr>'
    ,   '<th>name</th>'
    ,   '<th>color</th>'
    ,   '</tr>'
    )   ;

    begin
    declare cursor CUR is 
    select COLOR
    ,      NAME
    ,      COLOR    
    from   HTTP_COLORS;
    COL_1 varchar(255);
    COL_2 varchar(255);
    COL_3 varchar(255);
    begin
        open CUR;
        fetch CUR into COL_1, COL_2, COL_3;
        while (CUR%FOUND) loop
        begin
            HTTP.WRITE
            (
                '<tr bgcolor=#'
            ,   COL_1
            ,   '>'
            ,   '<td>'
            ,   COL_2
            ,   '</td>'
            ,   '<td>'
            ,   COL_3
            ,   '</tr>'
            )   ;
            fetch CUR into COL_1, COL_2, COL_3;
        end;
        end loop;
		SQL_ROWCOUNT:=CUR%rowcount;
        close CUR;
    end;
    end;
    HTTP.WRITE
    (
        '</table>'
    )   ;

    return;

end;

VAR = 'string'

VAR != 'string'

NVL(VAR,'NULL') != 'string'

or

VAR is NULL or VAR != 'string'

VAR like '%[a-z]%'

HTTP.PATINDEX('%[a-z]%',VAR) > 0

declare VAR char(4):='Hello';

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

CREATE TABLE #TEMP

Are you expecting your temprary table to vanish automatically when your procedure exits? If you are, then you will want to add the appropriate DELETE statement (since your temporary table is now a permanent table).

DECLARE CURSOR

SET

''

db_name() + ".dbo.WE_PAGER"

database . owner . name

owner . name

Sybernet, however, will treat procedure calls like this (when it can detect them) as DATABASE.NAME. In other words, it strips the owner name when it can, and it assumes that the database name is actually the owner of this procedure. In the above example (assuming it was executed from user DELTEK), the procedure name would be DELTEK.WE_PAGER.

SELECT col
INTO   MY_col
from   TableName

A / B

SELECT "STRING"
FROM   TableName

Workaround: Omit the table.

@NAME = NAME

Workaround: Quote the name.

IF ( ...

Workaround: Fix all syntax errors.

INSERT

or

DELETE

or

UPDATE

Workaround: None required.

SELECT "<br>" "</font>"

Workaround: Add a comma.

EXECUTE XYZ

Workaround: Ask for the return result (i.e., EXEC @i = XYZ).

where B.EFFDT <= convert(char(10),getdate())

Workaround: Convert your string date to a date, perhaps with HTTP.DATETIME.

SELECT BALANCE_YEAR
,      BALANCE_YEAR
FROM   PS_PAY_CAL_BAL_ID
GROUP  by BALANCE_YEAR

Workaround: Alias one of the columns to a different name.

update Distribution 
set    EmpNo=@Empno
from   distribution d, document dd
where  d.Control_No=dd.Control_No
and    dd.project_no=@Project

Workaround: Eliminate the join.

update Distribution
set    EmpNo = @Empno
where  Control_No in
(
    select Control_No
    from   document
    where  project_no = @Project
)

UPDATE temp_task_comments 
SET    assigned_analyst_name = sn 
FROM   temp_task_comments 
,      deltek_of.task_handling_staff 
WHERE  assigned_analyst = employeenumber 

Workaround: Eliminate the join.

UPDATE temp_task_comments 
SET    assigned_analyst_name = 
(
    select sn 
    from   deltek_of.task_handling_staff 
    where assigned_analyst = employeenumber
)

SELECT DISTINCT RPAD(FY_CD,4) || FY_CD
FROM DELTEK.SUB_PD
ORDER BY FY_CD

Workaround: Order by RPAD(FY_CD,4) || FY_CD or use GROUP BY instead of DISTINCT.

if 'ALL' in
(
    select PRIV_ID
    from   deltek.USER_REPORT_PRIV
    where  USER_ID = suser_name()
    and    REC_CD  = 'JE'
)

Workaround: Use EXISTS() instead.

if exists
(
    select 1
    from   deltek.USER_REPORT_PRIV
    where  USER_ID = suser_name()
    and    REC_CD  = 'JE'
    and    PRIV_ID = "ALL"
) 

select @per_cyl_chg_factor =
(
    select sum(amount)
    from   deltek.deltek.gas_scratch
    where file_indicator = "S"
    and pd_indicator = "D"
)
/
(
    select sum(no_of_unit)
    from deltek.deltek.gas_scratch
    where file_indicator = "U"
)

Workaround: Calculate each value separately or use the following in Oracle.

SELECT A.AMOUNT/B.UNIT
INTO   my_per_cyl_chg_factor
from
(
    SELECT SUM(amount) AMOUNT
    FROM   deltek.gas_scratch
    WHERE  file_indicator = 'S' AND pd_indicator='D'
)   A
,
(
    SELECT SUM(no_of_unit) UNIT
    FROM   deltek.gas_scratch
    WHERE file_indicator = 'U'
)   B

select @procname = "deltek.deltek.sp_ap_report"
exec @procname @out = @out output

Workaround: If you do not have to build the name dynamically, then don't.

exec deltek.deltek.sp_ap_report

    @out = @out output

select DESC_CODE
from   TAB1
where  DESC_CODE like "A%"

union

select DESC_CODE
from   TAB1
where  DESC_CODE like "B%"

Workaround: If the order is not important, it may be possible to break this into two seprate statements:

select DESC_CODE
from   TAB1
where  DESC_CODE like "A%"

select DESC_CODE
from   TAB1
where  DESC_CODE like "B%"

ACTION="Sybase.cgi"

Workaround: Change the name to Sybernet.cgi

DECLARE VAR VARCHAR(30) := 'Hello World'

P (VAR => 'Hello' || 'World')

CREATE PROCEDURE P (VAR IN VARCHAR2)

CREATE TABLE T (VAR varchar(4000))

VAR != 'STRING'

NVL(DEPTID,'023')

SELECT * FROM PEOPLESOFT where DEPTID = '023'

SELECT * from PEOPLESOFT where nvl(DEPTID,'023') = '023'

DECLARE VAR CRON.NAME%TYPE;

DECLARE VAR PLHIST%ROWTYPE;

FETCH cursor_name INTO VAR

DECLARE
VAR1 VARCHAR(30):='String';
VAR2 CHAR(30)   :='String';

You'll want to have a look at the PL/SQL User's Guide and Reference on CHAR versus VARCHAR2 Semantics. Not that you will feel any better after reading it, but it's certainly worth your time.

FUNCTIONS

The PL/SQL User's Guide and Reference has a good write up on functions.