Home » SQL & PL/SQL » SQL & PL/SQL » URGENT:Deciding function or procedure at runtime.. (merged)
URGENT:Deciding function or procedure at runtime.. (merged) [message #383082] Tue, 27 January 2009 04:03 Go to next message
sujits
Messages: 6
Registered: January 2009
Junior Member
Hi ,
Need some urgent help..
i have created a pl-sql block which i would be using in a unix shell script.

This block will check the type of object whether its a procedure or a function,
and then it will call the same

below is the code i have:
declare
  l_object_type varchar2(100);
  l_data varchar2(200) := 'ISTAT_AR_SP' ;
  l_return_val  varchar2(300);
begin
  dbms_output.put_line(l_data);

  SELECT object_type
  INTO l_object_type
  FROM user_objects
  WHERE upper(object_name) like upper(l_data);

  dbms_output.put_line(l_object_type);

IF l_object_type = 'FUNCTION'
THEN
  l_return_val :=ISTAT_AR_SP('10-NOV-2008','10-JAN-2009');
ELSE
  ISTAT_AR_SP('10-NOV-2008','10-JAN-2009');
END IF;


end;

****When i run the above code i get following error message:


**********************************************

ORA-06550: line 17, column 18:
PLS-00222: no function with name 'ISTAT_AR_SP' exists in this scope
ORA-06550: line 17, column 3:
PL/SQL: Statement ignored
**************************************************



Please help me in this issue urgently ...
Thanks in advance

[EDITED by LF: applied [code] tags]

[Updated on: Tue, 27 January 2009 04:21] by Moderator

Report message to a moderator

Re: URGENT:Deciding function or procedure at runtime.. [message #383084 is a reply to message #383082] Tue, 27 January 2009 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Need some urgent help..

http://www.orafaq.com/forum/mv/msg/139554/383052/102589/#msg_383052

Quote:
PLS-00222: no function with name "string" exists in this scope
Cause: An identifier being referenced as a function was not declared or actually represents another object (for example, it might have been declared as a procedure).
Action: Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure.



Regards
Michel
Re: URGENT:Deciding function or procedure at runtime.. (merged) [message #383087 is a reply to message #383082] Tue, 27 January 2009 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And DON'T multipost your question.
Spend your waiting time reading OraFAQ Forum Guide.

Regards
Michel
Re: URGENT:Deciding function or procedure at runtime.. (merged) [message #383090 is a reply to message #383082] Tue, 27 January 2009 04:19 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
declare
l_data varchar2(200) := 'ISTAT_AR_SP' ;
<some declarations>
BEGIN
<some code>
IF l_object_type = 'FUNCTION'
THEN
l_return_val :=ISTAT_AR_SP('10-NOV-2008','10-JAN-2009');
ELSE
ISTAT_AR_SP('10-NOV-2008','10-JAN-2009');
END IF;
END;
/

Just wonder, why did you put the procedure/function name into a variable as you call it statically (not using the variable).

And, as you need it urgently, why do you not rather study the PL/SQL User's Guide and Reference, available with all Oracle documentation e.g. online on http://tahiti.oracle.com/ instead of waiting for answer in the forum.
Re: URGENT:Deciding function or procedure at runtime.. [message #383097 is a reply to message #383084] Tue, 27 January 2009 04:29 Go to previous messageGo to next message
sujits
Messages: 6
Registered: January 2009
Junior Member
Yes , ISTAT_AR_SP is a procedure..

i am writing a unix shell script to which i will pass a function/procedure name.

that shell script will have to execute the supplied procedure/function.

the code i displayed is just a code inside a shell script after substituting the values passed to shell script.

.....
How should i change my logic to serve the purpose.
..

SELECT object_type
INTO l_object_type
FROM user_objects
WHERE upper(object_name) like upper(l_data);
IF l_object_type = 'FUNCTION'
THEN
l_return_val :=ISTAT_AR_SP('10-NOV-2008','10-JAN-2009');
ELSE
ISTAT_AR_SP('10-NOV-2008','10-JAN-2009');
END IF;
Re: URGENT:Deciding function or procedure at runtime.. [message #383098 is a reply to message #383097] Tue, 27 January 2009 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You will faster help (from me) if you read the forum guide and post as requested.

Regards
Michel
Re: URGENT:Deciding function or procedure at runtime.. (merged) [message #383099 is a reply to message #383090] Tue, 27 January 2009 04:31 Go to previous messageGo to next message
sujits
Messages: 6
Registered: January 2009
Junior Member
i am using this code in unix shell script.
i showed the code, substituting the input parameter values.
Re: URGENT:Deciding function or procedure at runtime.. (merged) [message #383128 is a reply to message #383099] Tue, 27 January 2009 06:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You have already been given a hint by @flyboy. You are trying to mix both static and dynamic sql which is not possible.

Search for dynamic sql.

Regards

Raj

P.S : Remember dynamic sql makes your code less maintenable and debugging will be a nightmare. Atleast from my personal experience I will try to avoid dynamic sql as much as I can.
Re: URGENT:Deciding function or procedure at runtime.. (merged) [message #383134 is a reply to message #383128] Tue, 27 January 2009 06:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You have provided only sketch details about what this shell script of your is doing, which makes accurate help difficult.

The problem that you are hitting is that before a piece of pl/sql is executed, it is parsed, to ensure that it is syntactically valid. One part of this process is checking that all the procedures and functions that are called exist, and are valid.

Your code contains calls to the same code object, calling it once as a procedure, and once as a function.
Unless every one of your procedures is overloaded as a function (and vice versa), this will always ersult in invalid and uncompilable code.

You need to either:
1) Read up about dynamic sql, and change the Pl/Sql that your shell script produces to be dynamic, or
2) get your shell sript to determine whether the object to be called is a function or a procedure, and generate a different piece of code in each case.
Re: URGENT:Deciding function or procedure at runtime.. [message #383135 is a reply to message #383097] Tue, 27 January 2009 06:29 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
sujits wrote on Tue, 27 January 2009 11:29
Yes , ISTAT_AR_SP is a procedure..

Then, call it as a procedure, as you hard-coded its name into the PL/SQL block.

Quote:
This block will check the type of object whether its a procedure or a function,
and then it will call the same

This smell as a very bad design; you should urgently change it.

Although you did not answer my question, I will add one more: why do you not care deciding how many parameters (within its types) this procedure/function has? Now, you call it with two VARCHAR2s - you could make it more complicated.
Re: URGENT:Deciding function or procedure at runtime.. [message #383144 is a reply to message #383135] Tue, 27 January 2009 07:01 Go to previous messageGo to next message
sujits
Messages: 6
Registered: January 2009
Junior Member
Please find the below shell script sql_exec.sh, which will have function/procedure name along with its parameters as first input parameter and second input parameter will be database connection identifier.

vi sql_exec.sh

#!/bin/ksh
###############################################################################
# FILENAME : sql_exec.sh
# ##############################################################################
ORACLE_SID=$2
echo Running $1 at `date` - pid $$ >> sql_exec.log
u_objectname=`echo $1 | cut -d'(' -f1`
echo $u_objectname
sqlplus $ORACLE_SID <<EOF >> sql_exec.log
set serveroutput on size 1000000
prompt Beginning Sqlplus execution of $1 - pid $$
declare
l_object_type varchar2(100);
l_data varchar2(200) := '$u_objectname' ;
l_return_val varchar2(300);
begin
dbms_output.put_line(l_data);

SELECT object_type
INTO l_object_type
FROM user_objects
WHERE upper(object_name) like upper(l_data);

dbms_output.put_line(l_object_type);

IF l_object_type = 'FUNCTION'
THEN
l_return_val :=$1;
ELSE
$1;
END IF;

END;
/
exit;
EOF


Please let me know how can i overcome this problem and make this shell script work


Re: URGENT:Deciding function or procedure at runtime.. (merged) [message #383150 is a reply to message #383082] Tue, 27 January 2009 07:23 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you know what the object is called and you know what it's parameters are how come you don't know whether it's a function or a procedure?
Re: URGENT:Deciding function or procedure at runtime.. (merged) [message #383152 is a reply to message #383150] Tue, 27 January 2009 07:28 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
it is possible on beeping other's work
Re: URGENT:Deciding function or procedure at runtime.. (merged) [message #383153 is a reply to message #383152] Tue, 27 January 2009 07:29 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
dr.s.raghunathan wrote on Tue, 27 January 2009 13:28
it is possible on beeping other's work

What does this mean?
Re: URGENT:Deciding function or procedure at runtime.. (merged) [message #383155 is a reply to message #383153] Tue, 27 January 2009 07:33 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
on analysing others procedure and to have better understanding or to derive the logic behind anything means beeping on other's work
may be i am wrong
Previous Topic: How to pass date to the query
Next Topic: Select Statement after deletion
Goto Forum:
  


Current Time: Sun Dec 04 13:07:25 CST 2016

Total time taken to generate the page: 0.12406 seconds