Re: Coldfusion and oracle stored procedure
You need to have the ref cursor as an out parameter:
create or replace
PROCEDURE EMP_REF_CURSOR (v_cur_emp OUT cur_emp)
AS...
This means that your procedure will need to be in a package so you can
declare the ref cursor outside of the procedure.
mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/
2010/1/24 fun and learning funandlrnn...@gmail.com:
Hi All -
I was learning on oracle stored procedures and retrieving the results from
Coldfusion. I am using ref cursor for reading a table in the database. The
procedure is as follows:
create or replace
PROCEDURE EMP_REF_CURSOR
AS
TYPE cur_emp IS REF CURSOR RETURN EMPLOYEES%ROWTYPE;
v_cur_emp cur_emp;
v_employees employees%ROWTYPE;
BEGIN
OPEN v_cur_emp FOR
SELECT * FROM EMPLOYEES;
LOOP
FETCH v_cur_emp
INTO v_employees;
EXIT WHEN v_cur_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('p_employee_id = ' || v_employees.employee_id || ',
p_first_name = ' || v_employees.first_name || ', p_last_name = ' ||
v_employees.last_name);
END LOOP;
CLOSE v_cur_emp;
END EMP_REF_CURSOR;
I am using the following code in coldfusion to access the stored proc:
cfstoredproc datasource=oraclelearn procedure=emp_ref_cursor
cfprocresult name=returnEmployees resultset=1
/cfstoredproc
cfdump var=#returnEmployees#
Coldfusion is throwing an error that it does not find the returnEmployees.
Can anyone please tell me what I am doing wrong. The stored procedure runs
fine in SQL Developer.
Thanks a lot
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330080
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Coldfusion and oracle stored procedure
I also noticed that you've looped over and closed the cursor - if you
do that, CF won't be able to read from it.
Just open it and let CF worry about fetching from it and closing it.
mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/
2010/1/24 James Holmes james.hol...@gmail.com:
You need to have the ref cursor as an out parameter:
create or replace
PROCEDURE EMP_REF_CURSOR (v_cur_emp OUT cur_emp)
AS...
This means that your procedure will need to be in a package so you can
declare the ref cursor outside of the procedure.
mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/
2010/1/24 fun and learning funandlrnn...@gmail.com:
Hi All -
I was learning on oracle stored procedures and retrieving the results from
Coldfusion. I am using ref cursor for reading a table in the database. The
procedure is as follows:
create or replace
PROCEDURE EMP_REF_CURSOR
AS
TYPE cur_emp IS REF CURSOR RETURN EMPLOYEES%ROWTYPE;
v_cur_emp cur_emp;
v_employees employees%ROWTYPE;
BEGIN
OPEN v_cur_emp FOR
SELECT * FROM EMPLOYEES;
LOOP
FETCH v_cur_emp
INTO v_employees;
EXIT WHEN v_cur_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('p_employee_id = ' || v_employees.employee_id || ',
p_first_name = ' || v_employees.first_name || ', p_last_name = ' ||
v_employees.last_name);
END LOOP;
CLOSE v_cur_emp;
END EMP_REF_CURSOR;
I am using the following code in coldfusion to access the stored proc:
cfstoredproc datasource=oraclelearn procedure=emp_ref_cursor
cfprocresult name=returnEmployees resultset=1
/cfstoredproc
cfdump var=#returnEmployees#
Coldfusion is throwing an error that it does not find the returnEmployees.
Can anyone please tell me what I am doing wrong. The stored procedure runs
fine in SQL Developer.
Thanks a lot
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330081
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Coldfusion and oracle stored procedure
Is this error a CF one or an Oracle one? On 2/24/06, vishnu prasad [EMAIL PROTECTED] wrote: Hi i am facing a issue in coldfusion and oracle storedprocedure i am getting invalid number error , below is the detail -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:26 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Coldfusion and oracle stored procedure
Is this error a CF one or an Oracle one? On 2/24/06, vishnu prasad [EMAIL PROTECTED] wrote: Hi i am facing a issue in coldfusion and oracle storedprocedure i am getting invalid number error , below is the detail -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal. com/ Error Reported in CF , in Stored procedure we saved the where cluse build dynamically in aother table, when we ecexute the SQL in SQL plus we were able to get records. But when we access the same via cfstoreproc we are getting invalid number . Do u need table description ?? ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:27 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Coldfusion and oracle stored procedure
If it works in SqlPlus then the proc is probably ok. Since everything is dates and strings I can't see where an error regarding a number is coming from... On 2/24/06, vishnu prasad [EMAIL PROTECTED] wrote: Is this error a CF one or an Oracle one? Error Reported in CF , in Stored procedure we saved the where cluse build dynamically in aother table, when we ecexute the SQL in SQL plus we were able to get records. But when we access the same via cfstoreproc we are getting invalid number -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:28 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Coldfusion and oracle stored procedure
It must be passing a date from CF to raocel storedproce. but i dont know how to overcome this :(( If it works in SqlPlus then the proc is probably ok. Since everything is dates and strings I can't see where an error regarding a number is coming from... On 2/24/06, vishnu prasad [EMAIL PROTECTED] wrote: Is this error a CF one or an Oracle one? Error Reported in CF , in Stored procedure we saved the where cluse build dynamically in aother table, when we ecexute the SQL in SQL plus we were able to get records. But when we access the same via cfstoreproc we are getting invalid number -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal. com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233341 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Coldfusion and oracle stored procedure
Yeah Date is what I am thinking. I have had this problem before with just an
Oracle query. Although looking at your query you are using the TO_CHAR and
TO_DATE stuff which should normally fix this.
I think what I did in the end was to format the date how Oracle was going to
expect it dd-mmm- 24-FEB-2006 for instance.
Something like this might be worth a try. I did have this problem and I did
fix it but I can't remeber completely how I am afraid.
cfprocparamdbvarname=rs_datefrom cfsqltype=CF_SQL_DATE
value=#LSdateFormat(ARGUMENTS.cfromdate,'dd-mmm-')# type=In
I use LSdateFormat so the american months do not get confused though this might
not be an issue for you.
You might then be able to loose this line which seems to be a bit of an
overcook.
TO_DATE(TO_CHAR(TO_DATE('''|| rs_datefrom
||''',''dd-Mon-''),''dd/mm/''),''dd/mm/'')
Good luck
Martin
Hi i am facing a issue in coldfusion and oracle storedprocedure i am
getting invalid number error , below is the detail
Calling Stored procedure from CFM like belwo
cffunction name=asSearchresult access=public returntype=query
cfargument name=cfromdate required=false type=DATE default=
cfargument name=ctodate required=false type=DATE default=
cfargument name=cRS_deleted required=false type=string
default=
cfargument name=cRS_TYPE required=false type=string
default=
cfargument name=cRS_Status required=false type=string
default=
cfargument name=cRS_UPLOADED required=false
type=string
default=
!---
cfargument name=cafs_office_id required=false
type=string
default=
cfargument name=cofs_country_id required=false
type=string
default=
---
cfstoredproc procedure=#sp# datasource=#ds#
cfprocparamdbvarname=rs_datefrom
cfsqltype=CF_SQL_DATE
value=#ARGUMENTS.cfromdate# type=In
cfprocparamdbvarname=rs_dateto
cfsqltype=CF_SQL_DATE
value=#ARGUMENTS.ctodate# type=In
cfprocparamdbvarname=RS_deleted
cfsqltype=CF_SQL_CHAR
value=#ARGUMENTS.cRS_deleted# type=In
cfprocparamdbvarname=RS_type
cfsqltype=CF_SQL_CHAR
value=#ARGUMENTS.cRS_TYPE# type=In
cfprocparamdbvarname=RS_Status
cfsqltype=CF_SQL_CHAR
value=#ARGUMENTS.cRS_Status# type=In
cfprocresult name=searchResults
/cfstoredproc
!---ad ---
cfif searchResults.recordCount gt 0
cfset xresult = searchResults
cfelse
cfset xresult =
/cfif
cfreturn xresult
/cffunction
oracle Stored procedure
CREATE OR REPLACE PACKAGE RSSearch_Package
AS TYPE RSSearch_Type IS REF CURSOR;
END RSSearch_Package;
/
CREATE OR REPLACE PROCEDURE Rssearch_Procedure (
RSSearch_Cursor IN OUT Rssearch_Package.RSSearch_Type,
rs_datefrom IN DATE,
rs_dateto IN DATE,
RS_deleted IN CHAR,
RS_type IN CHAR,
RS_Status IN CHAR
)
AS
WhereClause VARCHAR(5000);
BEGIN
IF ((rs_datefrom IS NOT NULL ) AND (rs_dateto IS NOT NULL)) THEN
--WhereClause :='AND RECORDSHEET.RS_DATE BETWEEN ' ||
TO_DATE(TO_CHAR(TO_DATE('''|| rs_datefrom ||''',''dd-Mon-''),
''dd/mm/''),''dd/mm/'') || || ' AND ' || ||
TO_DATE(TO_CHAR(TO_DATE('''|| rs_dateto ||''',''dd-Mon-''),
''dd/mm/''),''dd/mm/'')||;
--WhereClause :='AND RECORDSHEET.RS_DATE BETWEEN ' ||
'TO_DATE(TO_CHAR(TO_DATE(''' || rs_datefrom || ''',''dd-Mon-''),
''dd/mm/''),''dd/mm/'')' || ' AND ' ||
'TO_DATE(TO_CHAR(TO_DATE('''|| rs_dateto ||''',''dd-Mon-''),
''dd/mm/''),''dd/mm/'')';
--WhereClause :='AND RECORDSHEET.RS_DATE BETWEEN ' ||
||
rs_datefrom || || ' AND ' || || rs_datefrom || ';
--WhereClause := 'AND RECORDSHEET.RS_DATE BETWEEN' || ||
rs_datefrom || || ' AND ' || || rs_dateto || ;
WhereClause := 'AND RECORDSHEET.RS_DATE BETWEEN' || rs_datefrom || '
AND ' || rs_dateto ||;
END IF;
IF (RS_deleted IS NOT NULL) THEN
WhereClause := WhereClause || ' AND RECORDSHEET.DELETED
= ' ||
|| RS_deleted || ;
END IF;
IF (RS_Status IS NOT NULL) THEN
WhereClause := WhereClause || ' AND RECORDSHEET.Status = ' || ||
RS_Status || ;
END IF ;
IF (RS_type IS NOT NULL) THEN
WhereClause := WhereClause || ' AND RECORDSHEET.type = ' || ||
RS_type || ;
END IF ;
INSERT INTO TBLTEMPWHERE (textdata) VALUES (WhereClause);
OPEN RSSearch_Cursor FOR
SELECT
RECORDSHEET.ID,
RECORDSHEET.PIPELINE_ID,
RECORDSHEET.STATUS,
Re: Coldfusion and oracle stored procedure
Hi Tried the code u sent , i got the below error
17-jan-2005 is an invalid date format.
The error occurred in C:\cwhb\Myriad\RecordSheets\component\1bookSearch.cfm:
line 29
27 : cfstoredproc procedure=#sp# datasource=#ds#
28 : !--- cfprocparamdbvarname=rs_datefrom
cfsqltype=CF_SQL_DATE value=#ARGUMENTS.cfromdate# type=In ---
29 :cfprocparam dbvarname=rs_datefrom
cfsqltype=CF_SQL_DATE
value=#LSdateFormat(ARGUMENTS.cfromdate,'dd-mmm-')# type=In
30 :cfprocparam dbvarname=rs_dateto
cfsqltype=CF_SQL_DATE
value=#LSdateFormat(ARGUMENTS.ctodate,'dd-mmm-')# type=In
31 : !--- cfprocparamdbvarname=rs_dateto
cfsqltype=CF_SQL_DATE value=#ARGUMENTS.ctodate# type=In ---
Yeah Date is what I am thinking. I have had this problem before with
just an Oracle query. Although looking at your query you are using
the TO_CHAR and TO_DATE stuff which should normally fix this.
I think what I did in the end was to format the date how Oracle was
going to expect it dd-mmm- 24-FEB-2006 for instance.
Something like this might be worth a try. I did have this problem and
I did fix it but I can't remeber completely how I am afraid.
cfprocparam dbvarname=rs_datefrom cfsqltype=CF_SQL_DATE
value=#LSdateFormat(ARGUMENTS.cfromdate,'dd-mmm-')# type=In
I use LSdateFormat so the american months do not get confused though
this might not be an issue for you.
You might then be able to loose this line which seems to be a bit of
an overcook.
TO_DATE(TO_CHAR(TO_DATE('''|| rs_datefrom ||''',''dd-Mon-''),
''dd/mm/''),''dd/mm/'')
Good luck
Martin
Hi i am facing a issue in coldfusion and oracle storedprocedure i am
getting invalid number error , below is the detail
Calling Stored procedure from CFM like belwo
cffunction name=asSearchresult access=public
returntype=query
cfargument name=cfromdate required=false type=DATE
default=
cfargument name=ctodate required=false type=DATE
default=
cfargument name=cRS_deleted required=false type=string
default=
cfargument name=cRS_TYPE required=false type=string
default=
cfargument name=cRS_Status required=false type=string
default=
cfargument name=cRS_UPLOADED required=false
type=string
default=
!---
cfargument name=cafs_office_id required=false
type=string
default=
cfargument name=cofs_country_id required=false
type=string
default=
---
cfstoredproc procedure=#sp# datasource=#ds#
cfprocparamdbvarname=rs_datefrom
cfsqltype=CF_SQL_DATE
value=#ARGUMENTS.cfromdate# type=In
cfprocparamdbvarname=rs_dateto
cfsqltype=CF_SQL_DATE
value=#ARGUMENTS.ctodate# type=In
cfprocparamdbvarname=RS_deleted
cfsqltype=CF_SQL_CHAR
value=#ARGUMENTS.cRS_deleted# type=In
cfprocparamdbvarname=RS_type
cfsqltype=CF_SQL_CHAR
value=#ARGUMENTS.cRS_TYPE# type=In
cfprocparamdbvarname=RS_Status
cfsqltype=CF_SQL_CHAR
value=#ARGUMENTS.cRS_Status# type=In
cfprocresult name=searchResults
/cfstoredproc
!---ad ---
cfif searchResults.recordCount gt 0
cfset xresult = searchResults
cfelse
cfset xresult =
/cfif
cfreturn xresult
/cffunction
oracle Stored procedure
CREATE OR REPLACE PACKAGE RSSearch_Package
AS TYPE RSSearch_Type IS REF CURSOR;
END RSSearch_Package;
/
CREATE OR REPLACE PROCEDURE Rssearch_Procedure (
RSSearch_Cursor IN OUT Rssearch_Package.RSSearch_Type,
rs_datefrom IN DATE,
rs_dateto IN DATE,
RS_deleted IN CHAR,
RS_type IN CHAR,
RS_Status IN CHAR
)
AS
WhereClause VARCHAR(5000);
BEGIN
IF ((rs_datefrom IS NOT NULL ) AND (rs_dateto IS NOT NULL)) THEN
--WhereClause :='AND RECORDSHEET.RS_DATE BETWEEN ' ||
TO_DATE(TO_CHAR(TO_DATE('''|| rs_datefrom ||''',''dd-Mon-''),
''dd/mm/''),''dd/mm/'') || || ' AND ' || ||
TO_DATE(TO_CHAR(TO_DATE('''|| rs_dateto ||''',''dd-Mon-''),
''dd/mm/''),''dd/mm/'')||;
-- WhereClause :='AND RECORDSHEET.RS_DATE BETWEEN ' ||
'TO_DATE(TO_CHAR(TO_DATE(''' || rs_datefrom || ''',''dd-Mon-''),
''dd/mm/''),''dd/mm/'')' || ' AND ' ||
'TO_DATE(TO_CHAR(TO_DATE('''|| rs_dateto ||''',''dd-Mon-''),
''dd/mm/''),''dd/mm/'')';
-- WhereClause :='AND RECORDSHEET.RS_DATE BETWEEN ' ||
||
rs_datefrom || || ' AND ' || || rs_datefrom || ';
--WhereClause :=
Re: Coldfusion and oracle stored procedure
Try using cf_sql_timestamp instead of cf_sql_date and see if that makes a difference. On 2/24/06, vishnu prasad [EMAIL PROTECTED] wrote: Hi Tried the code u sent , i got the below error 17-jan-2005 is an invalid date format. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233347 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Coldfusion and oracle stored procedure
Yeah, is the field you are passing the date to a DATE or a TIMESTAMP. If using TIMESTAMP you are on Oracle 9.2 or greater. A TIMESTAMP is certainly not a DATE. So use the appropriate cf_sql_type. Also, IIRC in my experience, using bind vars with CF, I don't need to use TO_CHAR or TO_DATE in my SQL or PL/SQL. You may need to use CreateDate() in CF though, can't just pass in a string literal. DK On 2/24/06, Deanna Schneider [EMAIL PROTECTED] wrote: Try using cf_sql_timestamp instead of cf_sql_date and see if that makes a difference. On 2/24/06, vishnu prasad [EMAIL PROTECTED] wrote: Hi Tried the code u sent , i got the below error 17-jan-2005 is an invalid date format. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233361 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
