Talk:Oracle Database/SQL Cheatsheet

i have a table Table1 i have entered the Date field as Dat now i want to change it to Date how can i do that as alter and modify commands are not helping? -- (Anon)

It is not a good idea to call any field, Date, since DATE is an Oracle reserved word and dealing with that will complicate your life unnecessarily. However if you insist on calling it, Date, you can do so. Whenever you use the name of the field you will have to enclose it in quotes like this -- "Date" -- so that Oracle knows that you don't mean DATE, you really mean Date. Is it really worth the hassle when you could have avoided it by choosing a different name for the field? -- Derek Ross 23:21, 21 September 2006 (UTC)Reply

On another issue, I notice that the article defines functions and procedures using IS. My experience is that this never works: you have to use AS instead. Comments ? Am I doing something wrong or does the article need fixing? -- Derek Ross 23:21, 21 September 2006 (UTC)Reply

You are probably thinking of a "view" instead of a "procedure", which works like the following:

CREATE OR REPLACE VIEW employee_view (EMPLOYEE_ID,NAME,EMAIL)
AS
SELECT employee_id, name, email FROM employee
/

I've been using PL/SQL procedures for a couple years and am certain that the use of IS is correct.

-- Adreamsoul 21:37, 25 September 2006 (UTC)Reply

Need some discussion of IN/OUT/IN OUT - is it copy in/copy out, or call by address (for OUT and IN/OUT), or even Algol60 style call-by-name which requires thunks!

Is an OUT parameter always overwritten on return to the caller even if not assigned within the procedure? What happens when you read from an OUT parameter before you first write to it in a procedure? -- <asked someone who didn't sign>

In/Out are designates that a parameter is or is not able to be modified within a procedure. Example:

In the CALLING procedure (assuming variables declared to match, wherever they need declaring)

... code snippet MyVar1 := 'Jane Doe'; -- possible employee name MyVar2 := 0; -- age in years of employee MyVar3 := '234598' ; -- an employee id Some_Proc_Name (MyVar1, MyVar3, MyVar2); ... end of snippet

MyVar1 may now still be "Jane Doe" (if that is employee #234598's name). MyVar2 now has Jane's current age.

In the CALLED procedure can be found stuff like ...

(CREATE OR REPLACE) Procedure Some_Proc_Name (Param1 in out ParamType, -- here this is Varchar2

                         Param2 in ParamType,      -- either Varchar2 or integer
                         Param3 in out nocopy ParamType) -- this is number

-- nocopy tells compiler to use same external memory location for this variable -- no need to take more memory for a second copy within the procedure IS ... Variable declarations var_date_Birth Date; -- because Params1 and 3 are "in out" they can be treated as variables WITHIN the procedure -- and there is no need for additional variables.

BEGIN -- start of whole procedure

 Begin -- isolated trap for retrieval of data
   select first_name||' '||last_name, DOB into Param1, var_date_Birth
     from Employees
    where EmpID = Param2
 exception
   when NO_DATA_FOUND then Param1 := 'Emp no longer exists.'; -- age still zero
   when others then Param1 := SQLERRM; -- passes back info to calling proc
 end;
 if var_date_Birth is not null then -- calculate age
   Param3 := CASE Sysdate > var_date_Birth then (Sysdate - var_date_birth)
             ELSE 0 END; -- just in case someone has entered a bad date.

-- you can fine tune the date calc to just be years, but I can't remember how just now.

 end if;

END Some_Proc_name;

-- <answered someone else who didn't sign>

This is the best collection of simplified SQL syntax, that I’ve come across. I would move sections 1.5.1 Setting Constraints on a Table, 1.5.2 Unique Index on a Table, and 1.6 Sequences to the DDL section. Wkeen Wkeen (discuss • contribs) 17:24, 23 May 2011 (UTC)Reply