Stored Procedure execution

[NitinJain]

Hello!

I have executed the following stored procedure without any errors in Oracle 8, but have been getting paranthesis error in the select statement for Oracle9i.

The stored procedure is.............

CREATE OR REPLACE FUNCTION getData (thisNode NUMBER) RETURN VARCHAR IS

nodeIsText BOOLEAN;
result VARCHAR (255);

BEGIN

SELECT **(NodeType = 3 OR NodeType = 4 OR NodeType = 8)*****
INTO nodeIsText
FROM t_Node
WHERE id = thisNode;

-- INVALID_ACCESS_ERR

IF NOT nodeIsText THEN

DOMException(15);

ELSIF nodeIsText IS NULL THEN

raise_application_error (-20211, 'Text type node with id ' || thisNode || ' does not exist');

ELSE

SELECT data
INTO result
FROM t_CharacterData
WHERE id = thisNode;

END IF;

RETURN result;

END getData;

......................

Can anyone suggest something on this issue.

Warm Regards

Nitin

 

[Abhivyakti]

hi Nitin,

I dont know about this althought i know 8i and 9i. But i m pasting here the erro msg and the action to be taken for it. This is from Oracle documentation. May be that helps you to understand . I have also tried to complie the procedure on my mahcine but first, i think its not able to make it with into clause as its probably a boolean value. May be.

ORA-00907 missing right parenthesis

Cause: A left parenthesis has been entered without a closing right parenthesis, or extra information was contained in the parentheses. All parentheses must be entered in pairs.

Action: Correct the syntax and retry the statement.


Abhivyakti Mirajkar

 

[Swany]

you could also do this as:

select nodetype 
  into nodeistext
  from t_node
 where id = thisNode;

if nodeistext is null 
 then
       raise_application_error(...);
 elsif ( (nodeistext != 3) and 
       (nodeistext != 4) and 
       (nodeistext != 8)
       ) then
   domException(15);
 else
      SELECT    data
        INTO    result
        FROM    t_CharacterData
        WHERE   id = thisNode;
      return result;
end if;

.
.. Eat, think and be merry .
... ....................... .

 

[sem]

Nitin, can you specify the full version number of Oracle 8 database, as I tried it on 8.0.5, 8.1.6, 8.1.7 with the same error.