Home » SQL & PL/SQL » SQL & PL/SQL » Problem while calling a function in SQL
Problem while calling a function in SQL [message #247722] Wed, 27 June 2007 00:49 Go to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Hi All,

I am calling a function in sql from another function.
As shown below in fn_parent i m calling fn_gtrecord.

create or replace
function  fn_parent (tablename VARCHAR2)
return number
IS
 at_rec nuauthusers%rowtype;
 total number(10,0);
  v_return_cur  SYS_REFCURSOR;
BEGIN
  execute immediate 'select * from fn_GetRecord(:1,:2)' into at_rec
  using tablename,v_return_cur;
 
    dbms_output.put_line(at_rec.username || ' - ' || at_rec.resourcename ||
    ' - ' || at_rec.maskcharacter ||
    ' - ' || TO_CHAR(at_rec.maskshowleading) ||
    ' - ' || TO_CHAR(at_rec.maskshowtrailing));
 total := fn_GetRecord(tablename,v_return_cur);
 dbms_output.put_line('total = '||TO_CHAR(total));
 return 0; 
END ;[CODE]

[/CODE]
create or replace
function fn_GetRecord  (
 v_tablename IN varchar2,
 v_return_cur OUT SYS_REFCURSOR)
 return Number
IS

BEGIN
  OPEN v_return_cur FOR
  'SELECT * FROM  '||  v_tablename;
  return 0;
END ;




error I am getting is as below.....
Connecting to the database test.
ORA-00933: SQL command not properly ended
ORA-06512: at "DBO_TESTDB.FN_PARENT", line 8
ORA-06512: at line 7
Process exited.
Disconnecting from the database test.



Please tell me what is wrong......... Embarassed
Re: Problem while calling a function in SQL [message #247724 is a reply to message #247722] Wed, 27 June 2007 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You can't (directly) have a function in FROM clause.
2/ The function returns number, what is the meaning of "FROM 0"?
3/ In SQL, the function can't have an OUT parameter
4/ Why "execute immediate"? and not directly "SELECT" (in general terms, without looking at the meaning of THIS function)?
5/ You try the bad way to do what you want
...

Regards
Michel


Re: Problem while calling a function in SQL [message #247730 is a reply to message #247722] Wed, 27 June 2007 01:24 Go to previous message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
thanx Michel.

Now i know what wrong i m doing.
Previous Topic: order by clause in select into
Next Topic: sql Functions
Goto Forum:
  


Current Time: Fri Dec 09 19:21:42 CST 2016

Total time taken to generate the page: 0.15394 seconds