Example for procedures with INOUT parameters in Oracle, PL/SQL

plsql oracle9i
Bhanu V picture Bhanu V · Nov 10, 2010 · Viewed 36.3k times · Source

How can I use IN OUT parameter in a procedure. I want to give a parameter as both input and get the same out put? can i have an example for this?

Answer

Tony Andrews picture Tony Andrews · Nov 10, 2010

Do this in SQL Plus:

set serverout on

declare
  l_val varchar2(30) := 'hello world';
  procedure myproc (p_val in out varchar2) is
  begin
    dbms_output.put_line('p_val was ' || p_val);
    p_val := 'something else';
  end;
begin
   myproc(l_val);
   dbms_output.put_line('l_val is now ' || l_val);
end;
/

You should see the output:

p_val was hello world
l_val is now something else

Related questions

Oracle PL/SQL - How to create a simple array variable?

I'd like to create an in-memory array variable that can be used in my PL/SQL code. I can't find any collections in Oracle PL/SQL that uses pure memory, they all seem to be associated with tables. I'm looking …

Read More
UTL_FILE.FOPEN() procedure not accepting path for directory?

I am trying to write in a file stored in c:\ drive named vin1.txt and getting this error .Please suggest! > ERROR at line 1: ORA-29280: invalid > directory path ORA-06512: at > "SYS.UTL_FILE", line 18 ORA-06512: at > "…

Read More
Get a list of all functions and procedures in an Oracle database

I'm comparing three Oracle schemas. I want to get a list of all the functions and procedures used in each database. Is this possible via a query? (preferably including a flag as to whether they compile or not) Ideally it …

Read More