Home » SQL & PL/SQL » SQL & PL/SQL » calling pipeline table function in Pro*C
calling pipeline table function in Pro*C [message #193664] Mon, 18 September 2006 18:15 Go to next message
uma_tides
Messages: 2
Registered: September 2006
Junior Member
Hi,
I have created a package crm_src with a function getposlist which returns table as value.

When i tried to call this function in Pro*C it throws an error that
Pro*C/C++: Release 10.1.0.4.0 - Production on Mon Sep 18 17:38:43 2006

Copyright (c) 1982, 2004, Oracle. All rights reserved.

System default option values taken from: /u01/app/oracle/product/10.1.0.4/client_1/precomp/admin/pcscfg.cfg

Error at line 1238, column 13 in file main.pc
SELECT column_value FROM TABLE(cast(CRM_SRC.GetPosList as CRM_SRC. S
rcIdTabType));
............1
PLS-S-00513, PL/SQL function called from SQL must return value of legal SQL type
Error at line 1238, column 13 in file main.pc
SELECT column_value FROM TABLE(cast(CRM_SRC.GetPosList as CRM_SRC. S
rcIdTabType));
............1
PLS-S-00000, SQL Statement ignored
Semantic error at line 1238, column 13, file main.pc:
SELECT column_value FROM TABLE(cast(CRM_SRC.GetPosList as CRM_SRC. S
rcIdTabType));


Please let me know how to resolve this .

Regards,
Uma
Re: calling pipeline table function in Pro*C [message #193675 is a reply to message #193664] Mon, 18 September 2006 21:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What is CRM_SRC.SrcIdTabType a "TABLE OF"? The Error is telling you that it wants a standard SQL Type returned (eg. VARCHAR2, DATE, NUMBER). Yours is probably returning a user-defined Type.

You can make your nested table a TABLE OF VARCHAR2(n), or - if your Type is a scalar - you can CAST it to a SQL type:
SELECT CAST(column_value AS VARCHAR2(10))
FROM ...


Ross Leishman
Re: calling pipeline table function in Pro*C [message #193676 is a reply to message #193664] Mon, 18 September 2006 21:06 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
try "sqlcheck=NONE" on the proc command
Re: calling pipeline table function in Pro*C [message #193890 is a reply to message #193664] Tue, 19 September 2006 16:47 Go to previous messageGo to next message
uma_tides
Messages: 2
Registered: September 2006
Junior Member
Since function returns Table as value it has error'd out as
PLS-S-00513, PL/SQL function called from SQL must return value of legal SQL type


Hence i changed the return type to Object based which supports SQL typeand its working fine while called through Pro*C

Thanks ,
Uma
Re: calling pipeline table function in Pro*C [message #216202 is a reply to message #193890] Thu, 25 January 2007 10:03 Go to previous messageGo to next message
gha2007
Messages: 1
Registered: January 2007
Junior Member
Hi,

Can you let us know the solution more precisely.
What do you mean, "objects" ?

Can you provide us with the pl/sql definition of your pipelined function and the pro*c call ?

Best regards
Re: calling pipeline table function in Pro*C [message #216250 is a reply to message #216202] Thu, 25 January 2007 14:20 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's been over 4 months. Perhaps Uma is on to a different problem by now. Wink
Previous Topic: TO_CHAR(hire_date,'fmDD MONTH YYYY') <> TO_CHAR(hire_date,'DD MONTH YYYY')
Next Topic: sql help
Goto Forum:
  


Current Time: Sun Dec 04 14:48:31 CST 2016

Total time taken to generate the page: 0.05287 seconds