Home » SQL & PL/SQL » SQL & PL/SQL » How to specify IN and OUT in PL/SQL
How to specify IN and OUT in PL/SQL [message #37040] Fri, 11 January 2002 10:16 Go to next message
Dewana
Messages: 6
Registered: October 2001
Junior Member
HI,
I am trying to pass a value to an procedure and like to have three values back in return. but i am having difficulty doing it... I have just one table and doing select on three columns when i pass a single value to a procedure...
I have following setup..
create table test1(
WorkID varchar(10),
IndexPG varchar(50),
CoLogo varchar(255))
;

insert into test1 values ('111','mysite.com/index1.html','first.jpg');
insert into test1 values ('222','mysite.com/index2.html','second.jpg');
insert into test1 values ('333','mysite.com/index3.html','third.jpg');

create or replace PROCEDURE PROC_TEST1 (
p_workid IN OUT test1.workid%TYPE,
p_indexPG IN test1.indexPG%TYPE,
p_CoLogo IN test1.CoLogo%Type)
AS
Cursor cTest IS select workid , indexPG, CoLogo
from test1 where workid='p_workid';
v_workid test1.workid%TYPE;
v_indexPg test1.INDEXPG%TYPE;
v_CoBrandLogo test1.CoBrandLogo%Type;

BEGIN
select workid , indexPG, CoBrandLogo
into p_workid, p_indexPG, p_CoBrandLogo
from test1 where workid='p_workid';
END PROC_TEST1;

But when i execute the procedure itgave me error as follows.
BEGIN proc_test1('111'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PROC_TEST1'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Please help. i do not know what i am doing wrong.
Thanks In advance.
Re: How to specify IN and OUT in PL/SQL [message #37041 is a reply to message #37040] Fri, 11 January 2002 10:43 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
create or replace PROCEDURE PROC_TEST1 (
p_workid IN OUT test1.workid%TYPE,
p_indexPG OUT test1.indexPG%TYPE,
p_CoLogo OUT test1.CoLogo%Type)
AS

BEGIN
select workid , indexPG, CoBrandLogo
into p_workid, p_indexPG, p_CoLogo
from test1 where workid=p_workid;
exception
when OTHERS then
dbms_output.put_line(sqlerrm);
END PROC_TEST1;

SQL> var p1 number;
SQL> var p2 number;
SQL> var p3 varchar2(20);

-- declare variables p1,p2,p3 with proper datatypes
SQL>exec :p1=111;
SQL> exec proc_test1(:p1,:p2,:p3);
SQL> print p1
SQL> print p2

OR

write pl/sql block

SQL>set serveroutut on

declare
p1 test1.workid%TYPE;
p2 test1.indexPG%TYPE;
p3 test1.CoLogo%Type;

begin
p1:=111;
proc_test1(p1,p2,p3);

dbms_output.put_line(p1||' '||p2||' '||p3);

end;
/
Re: How to specify IN and OUT in PL/SQL [message #37062 is a reply to message #37040] Mon, 14 January 2002 09:49 Go to previous message
Dewana
Messages: 6
Registered: October 2001
Junior Member
Thanks man...
Previous Topic: Tablespace Growth When Adding New Tables
Next Topic: CRYPT DATA IN TABLES
Goto Forum:
  


Current Time: Thu Aug 06 07:43:30 CDT 2020