Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012

    Unanswered: Help with create function and create operator-Starter

    Hi I am new to oracle and I am trying to create an operator by creating a function.Here is what I want.

    if the natural join of the relations R and S is nonempty, then return the result of the natural join;
    otherwise, return the Cartesian product of R and S.

    Here is how I proceeded.

    create funtion r_join(R SCHEMA,B SCHEMA) RETURN SCHEMA AS
           CASE WHEN EXISTS(select * from R equi join B on R.s=B.c)
                   RETURN (select * from R equi join B on R.s=B.c)
                   RETURN (select * from R,S);
    create operator mas_join
                   RETURN SCHEMA
                         USING r_join;
            ORA-00901: invalid CREATE command
    I am not sure how to proceed or what I errors I did and I am very new to this.Please somebody help me with this.


  2. #2
    Join Date
    Mar 2007

    For the start, you should get acquainted with basic SQL and PL/SQL syntax. It is described in SQL Language Reference and PL/SQL User's Guide and Reference books, which are available with other Oracle documentation books e.g. online on
    Please, consult the ones for your Oracle version. They also contains many examples of sample code.

    From the first look, the function body is not valid PL/SQL block as it is not enclosed between BEGIN END keywords. See some sample e.g. here:
    Also, depending on tool where you are running that code, CREATE FUNCTION statement may need to followed by slash (/) on separate line for executing it.

    From the second look, the function body is total mess. What is EQUI JOIN? You cannot use SELECT directly, only SELECT INTO some variable as separate statement. Anyway, without knowing, what is that function supposed to do and what is the definition of "SCHEMA" data type, it is impossible to propose anything.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts