Home » SQL & PL/SQL » SQL & PL/SQL » IN OUT BOOLEAN IN A PROCEDURE
IN OUT BOOLEAN IN A PROCEDURE [message #184401] Wed, 26 July 2006 07:41 Go to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

Hi Gurus,

I am writing a procedure in which facing a problem.
I am trying to get a boolean value in the last parameter of
the procedure.

    create or replace PROCEDURE check_posting(vfrom date, 
    vto date,vnarr varchar2(10),
      vbranch varchar2(10), 
      vgroupcd  varchar2(10),
      vRecord IN OUT BOOLEAN) is
       dtfr date;
       dtto date;
          cursor c1 is select distinct to_date(substr(narr,19,10),'DD-MM-YYYY'),
          to_date(substr(narr,30,10),'DD-MM-YYYY')
          from partytrn where trn_type='I' and
         narr like 'Inte. Debited For%' and
  (party_cd in (select par_code from partymst
         where par_code is not null ));
     begin
       open c1;
       Loop
           fetch c1 into dtfr, dtto;
           exit when c1%notfound;
          if vfrom >= dtfr and vfrom <= dtto then
               vRecord := TRUE;
          end if;
          If vto >= dtfr and vto <= dtto then
               vRecord := TRUE;
          end if;
       end loop;
       CLOSE c1;
 * end check_posting;
SQL> /

Procedure created.

SQL> declare
  2    x date := '01-APR-2006';
  3    y date := '30-APR-2006';
  4    z varchar2(50) := 'test';
  5    a varchar2(10) := 'HO';
  6    b VARCHAR2(10) := 'AG';
  7    c boolean := FALSE;
  8  begin
  9    exec check_posting(x,y,z,a,b,c);
 10   dbms.output.put_line('value of c is : '||c);
 11  end;
 12  /
  exec check_posting(x,y,z,a,b,c);
       *
ERROR at line 9:
ORA-06550: line 9, column 8:
PLS-00103: Encountered the symbol "CHECK_POSTING" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "CHECK_POSTING" to continue.


Pl. Help

Thanks in advance

Anil

Re: IN OUT BOOLEAN IN A PROCEDURE [message #184403 is a reply to message #184401] Wed, 26 July 2006 07:44 Go to previous messageGo to next message
Frank Naude
Messages: 4581
Registered: April 1998
Senior Member
Syntax troubles. Remove the "exec" on line 9.
Re: IN OUT BOOLEAN IN A PROCEDURE [message #184414 is a reply to message #184401] Wed, 26 July 2006 08:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'll give you a shiny new penny if you can get dbms_output to show a boolean. Cool
It can only deal with Varchar (and char) and numbers. By inference, it will deal with anything that can be implicitly converted to one of those types, but Boolean isn't in that list.

If you try and output a boolean, you get:
SQL> declare
  2    v_bool  boolean := true;
  3  begin
  4  dbms_output.put_line(v_bool);
  5  end;
  6  /
dbms_output.put_line(v_bool);
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored


If you try and stick the boolean into a string like you are, you get the same error, just from || this time.:
SQL> declare
  2    v_bool  boolean := true;
  3  begin
  4    dbms_output.put_line('Value is '||v_bool);
  5  end;
  6  /
  dbms_output.put_line('Value is '||v_bool);
                       *
ERROR at line 4:
ORA-06550: line 4, column 24:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
Re: IN OUT BOOLEAN IN A PROCEDURE [message #184421 is a reply to message #184401] Wed, 26 July 2006 09:01 Go to previous message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member



Thanks for your prompt replies & solutions..

Regards,

Anil
Previous Topic: Delete the duplicate rows
Next Topic: Hierarchical Rollup Question
Goto Forum:
  


Current Time: Thu Dec 05 08:09:11 CST 2024