Re: object-relational help reqd

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 8 Jul 2002 07:26:13 -0700
Message-ID: <92eeeff0.0207080626.58b99517@posting.google.com>

"david" <david_at_blah.blah> wrote in message news:<agbi64$96o$1_at_helle.btinternet.com>...
> I am v new to Oracle and would appreciate help with a couple of very trivial
> teething troubles I am having. thanks
>
> I have tried to create a hello world application in Oracle 9i at the command
> line of SQL plus.
>
> I want to create an object which holds a salutation string (e.g. "hello")
> that has a method that I pass in my name and it returns the concatenated
> string hello David
>
> I have a few questions:
>
> Q1. The last line wont work (I'm not particularly suprised as I dont really
> know what I am doing), what should I be typing to test my method.
> Q2. If I do the "Create Type" and the "Create Type Body" commands separately
> (using SQL*PLUS to type a command at a time and press F5 between each) then
> it creates the type and then the body. If I have them both in the window
> when I press F5 then it comes back with compilation errors. (btw I get
> similar compilation errors when trying to do this via the console but that
> will be the my step when I get this trivial case working)
>
> CREATE TYPE HELLOOBJECT AS OBJECT
> (
> HELLOSTRING VARCHAR2(10),
> MEMBER FUNCTION TALKTOME (MYNAME VARCHAR2) RETURN VARCHAR2
> );
>
> CREATE TYPE BODY HELLOOBJECT AS
> MEMBER FUNCTION TALKTOME (MYNAME VARCHAR2) RETURN VARCHAR2 IS
> BEGIN
> RETURN HELLOSTRING+MYNAME;
> END TALKTOME;
> END;
>
> CREATE TABLE SALUTATIONS OF HELLOOBJECT;
>
> INSERT INTO SALUTATIONS VALUES ('ALRIGHT ');
> INSERT INTO SALUTATIONS VALUES ('Morning ');
> INSERT INTO SALUTATIONS VALUES ('Hi ');
>
> SELECT S.* FROM SALUTATIONS S;
>
> SELECT VALUE(S).TALKTOME(' David') FROM SALUTATIONS
>
> thanks

If you are very new to Oracle then I would suggest you learn the basics first and then try to do the advanced stuff.

1. If you are not familiar with SQL (Structured Query Language) basics then I would start there. SQL is the foundation which you would need.
2. If you are familiar with SQL then the next step would be to learn PLSQL (Oracle's Procedural Language extension to SQL) basics. In PLSQl you can construct IF..END IF statements, LOOPS etc.

There are numerous books out there on SQL and PLSQL from basics to advanced. I would recommend Tom Kyte's book, Expert one on one Oracle, programming techniques and solutions for Oracle at http://www.amazon.com/exec/obidos/ASIN/1861004826/

Now...If you just want to return a concatenated 'Hello David' string from a procedure, you can create this simple function which will do that for you.

SQL> CREATE OR REPLACE FUNCTION hello_func (

        name_  IN VARCHAR2) RETURN VARCHAR2
     IS
     BEGIN
        RETURN 'Hello ' || name_;
     END hello_func;
     /

SQL> Function Created.
SQL> select hello_func('David') "Hello String" from dual;

Hello String

---

Hello David

There are lots of ways out there to solve a problem. Best solution should be easy and simple.

HTH
//Rauf Sarwar Received on Mon Jul 08 2002 - 09:26:13 CDT