Home » SQL & PL/SQL » SQL & PL/SQL » INSTR function (merged)
INSTR function (merged) [message #248631] Fri, 29 June 2007 15:55 Go to next message
apuw
Messages: 13
Registered: June 2007
Junior Member
Hi

I am getting invalid object error when I try to call my procedure . there's something wrong with INSTR

SQL> create procedure t (line varchar2 , b number) as
2 begin
3 b := INSTR(line , ',');
4 dbms_output.put_line(b);
5 end;
6 .
SQL> /
Warning: Procedure created with compilation errors.

SQL> declare
c number ;
begin
t ('my,name,is' , c);
dbms_output.put_line('comma' || c);
end;
7 .
SQL> /
t ('my,name,is' , c);
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00905: object Apu.T is invalid
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
" - rest of line ignored.

I dont know why it is not getting position of comma in string given
pls help
thanks
Re: INSTR function [message #248633 is a reply to message #248631] Fri, 29 June 2007 15:57 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
t ('my,name,is' , c);

t ('''my,name,is''' , c);
Re: INSTR function [message #248635 is a reply to message #248631] Fri, 29 June 2007 16:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Warning: Procedure created with compilation errors.

First correct your function T (remove the line with .).

Regards
Michel
Re: INSTR function [message #248636 is a reply to message #248631] Fri, 29 June 2007 16:06 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
And b is supposed to be OUT paraneter:

create procedure t (line varchar2 , b OUT number) as ...


Michael
Re: INSTR function [message #248637 is a reply to message #248633] Fri, 29 June 2007 16:10 Go to previous messageGo to next message
apuw
Messages: 13
Registered: June 2007
Junior Member
Thanks a bunch it worked:)
INSTR function [message #248648 is a reply to message #248631] Fri, 29 June 2007 20:36 Go to previous messageGo to next message
apuw
Messages: 13
Registered: June 2007
Junior Member
when I call procedure with '''I am,SMITH''' and try to get string after comma I get SMITH' instead of just SMITH .How can I get rid off extra apostrophe ' .
Also can someone let me know how to accept string as substitution var when using INSTR function withing a procedure .


code tried:
create procedure t( line varchar2 , b out number )as
comma number ;
last varchar2(20);
begin
b := INSTR( line , ',');
comma := b;
last := SUBSTR (line , comma + 1);
dbms_output.put_line( 'last ' || last);
end;

SQL> declare
2 b number;
3 begin
4 [B] t ('''I am,SMITH''', b);
5 dbms_output.put_line('comma position :' || b);
6 end;
7 .
SQL> /
last SMITH'
comma position :6
Re: INSTR function [message #248649 is a reply to message #248648] Fri, 29 June 2007 20:52 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>How can I get rid off extra apostrophe '
By NOT including using so many as input!
SQL> create or replace procedure t( line varchar2 , b out number )as
  2  comma number ;
  3  last varchar2(20);
  4  begin
  5  b := INSTR( line , ',');
  6  comma := b;
  7  last := SUBSTR (line , comma + 1);
  8  dbms_output.put_line( 'last ' || last);
  9  end;
 10  /

Procedure created.

SQL> declare
  2  b number;
  3  begin
  4  dbms_output.enable(100000);
  5  t ('I am,SMITH', b);
  6  dbms_output.put_line('comma position :' || b);
  7  end;
  8  /
last SMITH
comma position :5

Previous Topic: data in (insert into XXDSC_REVENUE_REPORT_DETAIL_MV) is not inserting
Next Topic: nested if
Goto Forum:
  


Current Time: Thu Dec 08 00:20:40 CST 2016

Total time taken to generate the page: 0.18498 seconds