Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103: ERROR
PLS-00103: ERROR [message #218582] Thu, 08 February 2007 16:17 Go to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
I am having a problem in my main program:
I am at a loss it sounds like ( according to google search) it is a generic error. I tried to add a null as suggested by askTom, but to no avail.
Please see error below:

Thks in advance.

SQL>

here we go:

create or replace package body my_pkg
as
type cursor_type is ref cursor;
PROCEDURE get_results
(in_t_um IN varchar2,
oresult_set IN OUT CURSOR_TYPE)
is
l_query long := 'select d.l_num,
d.p_num,
trunc(t_date) r_date,
dl.type,
nvl(c_amt,0) amount
from d_tran d,
tl_pages t,
d_tab dl,
d_ttable dt,
recpt r
where t_num = :v_tnum;
begin
if(v_tnum is not null)
then
and d.l_num like ''D%''
and d.l_num = t.l_num
and d.p_num = tl.p_num and d.recpt_num = r.recpt_num
and d.type(+)
and d_tran = dt.t_num(+)
end if;
union
select d.l_num,
d.p_num,
trunc(t_date) r_date,
dl.type,
nvl(m_amt,0) amount
from d_tran dt,
tl_pages tmlp,
d_tab dl,
mttable mt,
recpt r
where t_num = :v_tnum
if(v_tnum is not null)
then
and d.l_num like ''M%''
and d.l_num = tl.l_num
and d.p_num = tl.p_num and d.recpt_num = r.recpt_num
and d.type = dl.type and d.recpt_num = mt.recpt_num(+)
and d.tran = mt.tran(+)';
end if; -- line 47
order by r_date desc';
p(l_query);
open oresult_set for l_query USING V_T_NUM;
null;
end;
end get_results;
end my_pkg;
/
Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY MY_PKG:
LINE/COL ERROR
-------- --------------------------------------------------------
47/4 PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form current cursor

Re: PLS-00103: ERROR [message #218584 is a reply to message #218582] Thu, 08 February 2007 16:58 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you, actually, READ this code? At a first sight it is obvious that it has some errors which would be avoided only if you took care. For example, this one:
if(v_tnum is not null)
then
and d.l_num like ''D%'' 
I'm not going to fix such things; this is NOT a question. This is laziness.
Re: PLS-00103: ERROR [message #218659 is a reply to message #218584] Fri, 09 February 2007 03:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That code makes no sense at all.
You seem to be trying to build a query in a string, and execute it, which is fine, although I can't see any reason why you can't just write that query as a cursor and avoid the whole Dynamic SQL side of things.

Your SQL has semi-colons littered through it, it has bits of Pl/Sql (IF is not a valid piece of SQL), the quotes are mismatched, and there's a stray END in there that doesn't match anything.
Re: PLS-00103: ERROR [message #218721 is a reply to message #218659] Fri, 09 February 2007 09:50 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
I am sorry for all my errors and I am not sure it is laziness but rather lack of knowledge.
This code will run on an 8i env and it was suggested to me dynamic sql will be better in that env.

Here is a clearer picture of what I am doing:

I exec this procedure below 1st whihc in turn call a function to padd that t_num entered by user:
SQL> ed
Wrote file afiedt.buf

1 create or replace procedure convert_tnumber
2 IS
3 v_tnum ttable.t_num%type;
4 begin
5 select distinct padtaxnum('&u_string') into v_tnum FROM ttable;
11* end;

SQL> undefine u_string
SQL> /
Enter value for usr_string: 0900141000200037000
old 5: select distinct padtaxnum('&u_string') into v_tnum FROM ttable;
new 5: select distinct padtaxnum('0900141000200037000') into v_tnum FROM ttable

Procedure created.

The above procedure will call my main procedure get_results (I m not showing that call yet until the procedure for the main qry works.

I cleaned up the code a bit.

create or replace package body my_pkg
as
declare
type cursor_type is ref cursor;
PROCEDURE get_results
(in_t_um IN varchar2,
oresult_set IN OUT CURSOR_TYPE)
is
l_query long := 'select d.l_num,
d.p_num,
trunc(t_date) r_date,
dl.type,
nvl(c_amt,0) amount
from d_tran d,
tl_pages t,
d_tab dl,
d_ttable dt,
recpt r
where t_num = :v_tnum <-- stored input value
and d.l_num like ''D%''
and d.l_num = t.l_num
and d.p_num = tl.p_num and d.recpt_num = r.recpt_num
and d.type(+)
and d_tran = dt.t_num(+)
union
select d.l_num,
d.p_num,
trunc(t_date) r_date,
dl.type,
nvl(m_amt,0) amount
from d_tran dt,
tl_pages tmlp,
d_tab dl,
mttable mt,
recpt r
where t_num = :v_tnum <-- stored input value
and d.l_num like ''M%''
and d.l_num = tl.l_num
and d.p_num = tl.p_num and d.recpt_num = r.recpt_num
and d.type = dl.type and d.recpt_num = mt.recpt_num(+)
and d.tran = mt.tran(+)
order by r_date desc' ; -- qry string ends here
begin
p(l_query) ;-- p is a procedure to get around 255 constraint
open oresult_set for l_query USING V_TNUM;
end;
end get_results;
end my_pkg;
/

I get the following now:

LINE/COL ERROR
-------- --------------------------------------------------------
44/3 PLS-00103: Encountered the symbol "OPEN" when expecting one of the following:
:= . ( % ;


Once the proc for qry works I am done. qry was tested in sql and works fine.
Thanks in advance.

[Updated on: Fri, 09 February 2007 11:46]

Report message to a moderator

Re: PLS-00103: ERROR [message #218727 is a reply to message #218721] Fri, 09 February 2007 10:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hint: Where does your declaration section end and your code section start?
What should be the first keyword of the code section?
Re: PLS-00103: ERROR [message #218734 is a reply to message #218727] Fri, 09 February 2007 11:48 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks for hints. So sorry I made so many chgs, I am at my wits' end. I made recommended chgs.
Please I got to get this to work very soon.
Re: PLS-00103: ERROR [message #219220 is a reply to message #218582] Tue, 13 February 2007 10:42 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Any idea on which direction should I go from here?

Thks.
Re: PLS-00103: ERROR [message #219222 is a reply to message #218582] Tue, 13 February 2007 10:48 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
What problems are you still having? Your amended code seems to compile fine now?
Re: PLS-00103: ERROR [message #219229 is a reply to message #219222] Tue, 13 February 2007 11:09 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
The problem is in my package body:
I get the following error now:

LINE/COL ERROR
-------- --------------------------------------------------------
44/3 PLS-00103: Encountered the symbol "OPEN" when expecting one of the following:
:= . ( % ;

Thanks.
Re: PLS-00103: ERROR [message #219393 is a reply to message #218582] Wed, 14 February 2007 04:11 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I got this to compile OK

create or replace package body my_pkg is
--declare
type cursor_type is ref cursor;
PROCEDURE get_results (in_t_um IN varchar2,
                       oresult_set IN OUT CURSOR_TYPE) is
l_query long := 'select d.l_num,
                  d.p_num,
                  trunc(t_date) r_date,
                  dl.type,
                  nvl(c_amt,0) amount
                  from d_tran d,
                  tl_pages t,
                  d_tab dl,
                  d_ttable dt,
                  recpt r
                  where t_num = :v_tnum <-- stored input value
                  and d.l_num like ''D%''
                  and d.l_num = t.l_num
                  and d.p_num = tl.p_num and d.recpt_num = r.recpt_num
                  and d.type(+)
                  and d_tran = dt.t_num(+)
                  union
                  select d.l_num,
                  d.p_num,
                  trunc(t_date) r_date,
                  dl.type,
                  nvl(m_amt,0) amount
                  from d_tran dt,
                  tl_pages tmlp,
                  d_tab dl,
                  mttable mt,
                  recpt r
                  where t_num = :v_tnum <-- stored input value
                  and d.l_num like ''M%''
                  and d.l_num = tl.l_num
                  and d.p_num = tl.p_num and d.recpt_num = r.recpt_num
                  and d.type = dl.type and d.recpt_num = mt.recpt_num(+)
                  and d.tran = mt.tran(+)
                  order by r_date desc' ; -- qry string ends here
   begin
   --p(l_query) ;-- p is a procedure to get around 255 constraint
   --open oresult_set for l_query USING V_TNUM;
     null;
   end get_results;
end my_pkg;



I commented the declare out - you don't need that in a package.
Obviously I don't have a 'p' package to call so I commented that one out.
You need to declare V_TNUM, that seems to be missing. Is this in another package specification? You would need to prefix the variable name with the package name.

Give these a go, see how you get on.
Re: PLS-00103: ERROR [message #219505 is a reply to message #219393] Wed, 14 February 2007 12:12 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks alot. I got it work last night using the same code but ran it as a procedure instead. I was struggling w/ the above code too much. It is good that you can show me an alternative and I greatly appreciate it.
I am still struggling w/ the line of code below :

where t_num = :v_tnum <-- stored input value

I got it to work by hard coding a value( see function output) which is great, the data is what I want, but it must be a var for it changes so often.


The question you asked as far as v_num is concerned, comes from an other procedure which then call padtaxnum function + store the new val into it. Which is the bind var I used in my where clause.

Many thks!

SQL> /
Enter value for usr_string: 0900141000200037000
old 5: select distinct padtaxnum('&u_string') into v_tnum FROM ttable;
new 5: select distinct padtaxnum('0900141000200037000') into v_tnum FROM ttable

Procedure created.
Re: PLS-00103: ERROR [message #219612 is a reply to message #218582] Thu, 15 February 2007 02:51 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
I'm not sure exactly what problem you are having with that line of code, but looking at what you are doing (and I've not used that particular syntax yet) you haven't constructed your OPEN statement properly.

From what I can tell that statement passes the USING variable into the bind variable in your select statement. However the variable you are trying to pass (V_TNUM) isn't declared. I think (if I have correctly guessed what you are trying to do) you need to pass in IN_T_UM instead. This is your incoming variable with the value you want to pass to your where clause, so you need to use that?
Re: PLS-00103: ERROR [message #219710 is a reply to message #219612] Thu, 15 February 2007 10:56 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
In my new code it is declared as such in my declaration section:
v_tnum varchar2(27) ;

also if you noticed, in that procedure which call padtaxnum below I used v_tnum:

Enter value for usr_string: 0900141000200037000
old 5: select distinct padtaxnum('&u_string') into v_tnum FROM ttable;
new 5: select distinct padtaxnum('0900141000200037000') into v_tnum FROM ttable

Procedure created.
I can't use the in_Tnum param since that is what the user entered which is converted via function's code to become:
0900-141.00-02.00-037.000. this new val is what I need hence the ...into v_tnum FROM ttable. The code compiled fine except I get no row selected when I know I ought to find three or four rows. I tested all of it and I am certain.

I hope that clarifies it all.

Many thks for your reply.

Re: PLS-00103: ERROR [message #219719 is a reply to message #219710] Thu, 15 February 2007 11:52 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
v_tnum seems to be declared only in the function CONVERT_TNUMBER. I don't see why you expect it to be available in MYPKG.GET_RESULTS.

Parameter IN_T_UM doesn't appear to be used anywhere. Why is it there?

I still don't see why this uses dynamic SQL. The first thing I would do would be to simplify it to use regular static SQL.

I also count't see what CONVERT_TNUMBER is supposed to do. You do realise it is SQL*Plus that prompts for substitution variables before submitting the code to the database, right? The value you supplied at the prompt is now permanently part of the function code.

[Updated on: Thu, 15 February 2007 11:54]

Report message to a moderator

Re: PLS-00103: ERROR [message #219729 is a reply to message #219719] Thu, 15 February 2007 13:48 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Hi Scorpio,

1. Ignore the pkg code for now I m using an unnamed block procedure and the is no In_tnum declared there.
2. I am fully aware that I need to set some user environment variable where I undefine usr_string which is my substitution variable as mentioned in your reply and I thank you for the reminder.
3. here is the code for convertT_num procedure:

declare
v_tnum ttable.t_num%type;
sql_Str varchar2(200);
begin
sql_Str := 'select distinct padtaxnum(''&usr_string'') into v_tnum FROM ttable';
dbms_output.put_line(v_tnum);
end;
/
PL/SQL procedure successfully completed.
Perhaps, what I may need to do and I just realize that I am negating the above test by declaring v_tnum again in my main program as such:
v_tnum varchar2(27) ;
I should rather assign it to something like this; don't know if the syntax is correct:
v_tnum := padtaxnum(usr_string);
or I could test for the content of v_tnum after the convert procedure runs. How do I see the content of v_tnum afer itis stored via into v_tnum clause?

4. Lastly dynamic sql for obvious reasons: bind var and usr interactive. Although I am told, performance wise static is better alternative, and the "USING v_tnum is how you pass a value to bind var.

I am so close Yet so far...

Thks for all your help.
Re: PLS-00103: ERROR [message #219731 is a reply to message #219729] Thu, 15 February 2007 14:12 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
oraclenub2006 wrote on Thu, 15 February 2007 13:48
1. Ignore the pkg code for now I m using an unnamed block procedure and the is no In_tnum declared there.

In that case I'm not sure what code we are trying to fix. I thought this was all about a PLS-00103 error in your package.

oraclenub2006 wrote on Thu, 15 February 2007 13:48
2. I am fully aware that I need to set some user environment variable where I undefine usr_string which is my substitution variable as mentioned in your reply and I thank you for the reminder.

Sorry, didn't understand this at all.

oraclenub2006 wrote on Thu, 15 February 2007 13:48
3. here is the code for convertT_num procedure:

declare
v_tnum ttable.t_num%type;
sql_Str varchar2(200);
begin
sql_Str := 'select distinct padtaxnum(''&usr_string'') into v_tnum FROM ttable';
dbms_output.put_line(v_tnum);
end;

That is an anonymous block, not a procedure called "convertt_num".

oraclenub2006 wrote on Thu, 15 February 2007 13:48
4. Lastly dynamic sql for obvious reasons: bind var and usr interactive. Although I am told, performance wise static is better alternative, and the "USING v_tnum is how you pass a value to bind var.

I'm afraid those aren't obvious, or reasons. I don't really understand actually. Are you saying all this dynamic cursor construction is because you have been told that dynamic code is necessary in order to use bind variables? It is not. And "usr interactive" - what does that mean?

Regarding the v_tnum variable, you cannot just declare it in one procedure or an anonymous block and then refer to it in a totally different procedure.
Re: PLS-00103: ERROR [message #219736 is a reply to message #219731] Thu, 15 February 2007 15:04 Go to previous message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Please : See No ROWS SELECTED when using BIND Variable instead as a new topic.
Thks
Previous Topic: Autotrace and explain Plan not found
Next Topic: Execute Immediate and SQL not properly ended.
Goto Forum:
  


Current Time: Wed Dec 07 05:00:04 CST 2016

Total time taken to generate the page: 0.17651 seconds