Re: Creating Oracle Functions in CFQUERY revisited
YEAR is a reserved word in Oracle. Try a different name for the function. Janet. At 09:55 AM 2/4/2005 -0500, you wrote: All four queries execute fine... no errors... then when I try to access the Year() function, I still get the error: [Macromedia][Oracle JDBC Driver][Oracle]ORA-06575: Package or function YEAR is in an invalid state ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193090 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: Creating Oracle Functions with CFQUERY
Also, make sure that the data source you are using allows you to create objects. Under allowed SQL, the create box needs to be checked. On Thu, 03 Feb 2005 11:05:01 -0500, Rick Root [EMAIL PROTECTED] wrote: This was in another thread but I wanted to post it to a separarate thread so more people might see it.. I need to create oracle functions from within CFML. Ie: cfquery ... CREATE OR REPLACE FUNCTION year (MyDate DATE) RETURN NUMBER IS BEGIN RETURN extract(YEAR FROM MyDate); END year; / /cfquery It doesn't generate errors - at least not in coldfusion - (with or without the /), but it does leave the function in an unusable state. Anyone out there ever done this or have ideas for a solution? ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192900 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: Closing Oracle Cursors
Is there a where clause on the query that select quite a few records? What does the query look like? Janet. At 10:10 PM 1/27/2005 -0500, you wrote: Inline via cfquery -Original Message- From: Adrocknaphobia [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 7:35 PM To: CF-Talk Subject: Re: Closing Oracle Cursors Are you executing stored procedures or inline via cfquery? -Adam On Thu, 27 Jan 2005 17:44:12 -0500, Scott Mulholland [EMAIL PROTECTED] wrote: I am connecting to a new database run by another company and have been experiencing an intermittent error: [Oracle]ORA-00604: error occurred at recursive SQL level 1 ORA-01000: maximum open cursors exceeded When I talked to the DBA's who maintain the database they told me: If your app is erroring out with the max opened cursors exceeded error, then its usually an indicator that your app is not explicity closing all cursors that it is opening. Each insert or select sequence statement would be an implicit cursor in Oracle. Coldfusion should close the cursors. Anyone know how to go about this? I worked with Oracle about 3 years ago regularly and don't remember this ever coming up? Is this possibly version dependent? Thanks, Scott --- [This E-mail scanned for viruses by Declude Virus] ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192692 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: Oracle native drivers stop verifying (CF5)
At 08:40 AM 11/9/2004 -0500, you wrote: For some functions we use the native Oracle 8.0 drivers supplied with CF 5.0 Enterprise to access Oracle 8i. They worked fine till Monday, now none of them work and they will not verify. If we submit a query using one of the native data sources it just never comes back - we do not get an error message. Recreating the native data sources does not fix the problem. What are you seeing in the CF logs (server, application, exception) when the lost queries are happening? On the Oracle side, what are they seeing in the listener.log file? ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183724 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: Oracle stored procedure returning a cursor
TK - I think this is a problem with the jdbc thinclient driver.Look at live docs on Macromedia's site (http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b17.htm). IAt 09:27 AM 10/20/2004 -0400, you wrote: I am running into problems when trying to use Oracle 10g stored procedure that returns a cursor. I have done a bit of research on the topic and so far non of the methods that where shown work. I am sure many of you use Oracle and CF 6.1 without any problems, thus I am sure someone will catch the mistake I have in my code. Oracle Stored procedure: (I just changed table names and the query a bit it works fine from SQL Plus so there is no problem with the data the stored procedure returns, it compiles and runs fine): CREATE OR REPLACE PACKAGE pkg_userservice IS TYPE RC_GENERIC IS REF CURSOR; PROCEDURE p_gettabs ( ag_id_in INT, getTabRefCurIN OUT RC_GENERIC ); END pkg_userservice; / CREATE OR REPLACE PACKAGE BODY pkg_userservice IS PROCEDURE p_gettabs ( ag_id_in INT, getTabRefCurIN OUT RC_GENERIC ) IS BEGIN OPEN getTabRefCur FOR ' SELECT d.agf_id, ' ||'g.agf_id p_id, ' ||' FROM bbb.acc_grp c ' ||' INNER JOIN bbb.acc_grp_func d ON (c.ag_id = :ag_id_in) ' ||' ORDER BY d.P_AGF_ID desc USING ag_id_in; EXCEPTION WHEN others THEN NULL; END p_gettabs; END pkg_userservice; Here is my CF code that calls above stored procedure: cfset ag_id_in = 100 CFSTOREDPROC PROCEDURE=jmelnick.pkg_userservice.p_gettabs DATASOURCE=umap cfprocparam type=In cfsqltype=CF_SQL_DECIMAL dbvarname=ag_id_in value=#ag_id_in# !--- cfprocparam type=Out cfsqltype=cf_sql_refcursor variable=searchResults --- CFPROCRESULT NAME=getTabRefCur /CFSTOREDPROC CFDUMP VAR=#getTabRefCur# If I run above as it is now I get: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'P_GETTABS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored If I include out parameter but no result or out and result I get this error: Parameter Type Conflict: sqlType=2006 I am using type definitions from http://www.blinex.com/~sam/CF_SQL_TYPES.cfm Through I played around and changed them a bit just in case. I am using : jdbc:oracle:thin:@(description=(address=(protocol=tcp)(host=dbdev)(port=1521))(connect_data=(sid=dbdev))) To connect to the db server Stored procedure execution is enabled on the server and user executing the stored procedure has full right to it (grant all). ~| The annual ColdFusion User Conference is being held Sat 6/26 - Sun 6/27/04 8am-5pm in the Washington DC Area. http://www.houseoffusion.com/banners/view.cfm?bannerid=44 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181997 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: CREATE command in CFQUERY tag?
The syntax is wrong.It should be CREATE TABLE TempTable. Are you sure this ran okay when you cut and paste it into SQL*Plus? At 09:10 AM 10/7/2004 +0100, you wrote: I'm not an Oracle expert, but it is possible on most drivers/datasources to prohibit certain commands - i.e. when you run it thru SQL PLUS you have permission to run a CREATE command, but through you CF datasource it may be prohibitted. Just a thought. On Wed, 06 Oct 2004 14:53:30 -0400, Adrienne Brown [EMAIL PROTECTED] wrote: Is it possible to execute code that creates a temporary table using a cfquery tag? Here's the code I'm trying to execute: cfquery name=createTable datasource=#db# CREATE TempTable AS (SELECT ENGINEER_SKILLS_ENGINEERS.ES_ENGINEERS_ID, ENGINEER_SKILLS_SKILLSET.SKILLSET_ID, ENGINEER_SKILLS_PROGRAMS.PROGRAM_ID, ENGINEER_SKILLS_CERTIFICATION.CERTIFICATION_ID, ENGINEER_SKILLS_TRAINING.TRAINING_ID FROM ENGINEER_SKILLS_ENGINEERS INNER JOIN ENGINEER_SKILLS_SKILLSET ON ENGINEER_SKILLS_SKILLSET.USR_USER_ID = ENGINEER_SKILLS_ENGINEERS.USR_USER_ID LEFT JOIN ENGINEER_SKILLS_PROGRAMS ON ENGINEER_SKILLS_PROGRAMS.SKILLSET_ID = ENGINEER_SKILLS_SKILLSET.SKILLSET_ID LEFT JOIN ENGINEER_SKILLS_CERTIFICATION ON ENGINEER_SKILLS_CERTIFICATION.SKILLSET_ID = ENGINEER_SKILLS_SKILLSET.SKILLSET_ID LEFT JOIN ENGINEER_SKILLS_TRAINING ON ENGINEER_SKILLS_TRAINING.SKILLSET_ID = ENGINEER_SKILLS_SKILLSET.SKILLSET_ID WHERE ES_ENGINEERS_ID = 1) /cfquery This code runs just find when I copy and paste into SQL Plus, but when I put it in a cfquery tag, I get the following error: Error Executing Database Query. [Macromedia][Oracle JDBC Driver][Oracle]ORA-00901: invalid CREATE command I find it interesting that I'm not getting a Coldfusion error, which leads me to believe that I *should* be able to run this code. Or am I just crazy? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle Stored Procedure - another shot, now with procedure code
Scott -
There is nothing wrong with your code.I recreated your CF code and Oracle
package, etc... in our environment (CFMX, Oracle 8.1.7) and it works just
fine.To solve this, I would look at two things:
1.The Oracle drivers you are using in your CF data sources.
2.If there is more than one version of the TEST01 package and you are
not calling the version that you think you are calling.To do this, I
would verify that you are connecting as the same Oracle user via the CF
data sources that you are connecting with when you are running the
procedure from SQL*Plus.
HTH.
Janet.
At 01:37 PM 9/16/2004 -0400, you wrote:
Thanks to all who have offered advice thus far.I have since been able
to get this running in SQL plus, but still get the same error in CF.
(MX, Enterprise edition, using default Oracle driver)
Error received: [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line
1, column 7: PLS-00306: wrong number or types of arguments in call to
'TESTPCKG' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
CF code used:
cfstoredproc procedure = schema.test01.testpckg dataSource = #ds#
cfprocresult name=rs
/cfstoredproc
cfdump var=#rs#
PACKAGE CODE:
package test01 is
Type ReturnSet is ref cursor;
Procedure TestPckg(curReturnSet Out Returnset);
Procedure TestPhn(curReturnSet Out Returnset);
end test01;
package body test01 is
Procedure TestPckg(curReturnSet out Returnset) As
Begin
open curreturnset for
select name
from allNames
where (ludate between to_date('20040801','MMDD') and
to_date('20040901','MMDD'))
or (insertdate between to_date('20040801','MMDD') and
to_date('20040901','MMDD'));
End;
Procedure TestPhn(curReturnSet Out Returnset) As
Begin
open curreturnset for
select phid, areacode, phonenumber
from allPhones;
End;
end test01;
Anyone spot anything wrong with the package/procedure that could cause
this?
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: Oracle Stored Procedures
Scott - This test would fail because you did not pass a parameter to the procedure that is expecting.It is not the same situation as in your CF code.To adequately test this, you need to set up the call to the procedure from SQL*Plus that passes in a refcursor parameter. Janet. At 03:42 PM 9/14/2004 -0400, you wrote: Adam, I am thinking you may be right based on my last post regarding the EXECUTE privilege. Running it from SQL*PLUS has the same results: SQL exec test01.testpckg; BEGIN test01.testpckg; END; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'TESTPCKG' ORA-06550: line 1, column 7: PL/SQL: Statement ignored [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle Stored Procedures
It might be something to do with the way the refcursor is defined in the package.Would they be willing to change the package so the refcursor is defined like this? CREATE OR REPLACE PACKAGE schemaname.testpckg IS TYPE curreturnset_type_ref_cursor IS REF CURSOR; PROCEDURE testpckg( curreturnset IN OUT curreturnset_type_ref_cursor); END; At 10:57 AM 9/14/2004 -0400, you wrote: Here is the new specification: PROCEDURE TESTPCKG Argument NameTypeIN/OUT Default? CURRETURNSETREF CURSORIN/OUT The code I'm using to call it is: cfstoredproc procedure = Test01.TestPckg dataSource = #ds# cfprocresult name=rAcct /cfstoredproc [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle Stored Procedures
Scott - The other thing you might want to look at is what Oracle drivers are being used.I know there are issues with some of the drivers and Oracle stored procedures. Here isa technote regarding one Oracle driver that mentions support for Oracle refcursors: http://www.macromedia.com/support/coldfusion/ts/documents/tn18344.htm Janet. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle Stored Procedures
Scott - I think you need the IN OUT that Adam mentioned. When you connect via SQL*Plus and successfully run the procedure, are you using the same username and password that is defined in the CF data source?If so, then I do not think it is a permissions problem but I could be wrong.If not, it might be a permissions problem.You could test this by changing your CF code to specify the username and password in the stored procedure call that you use when you successfully run the procedure in SQL*Plus.I think this will override what is stored in the CF data source definition (but I am not sure). The only permission you need is EXECUTE on the package in Oracle.If you can connect to Oracle via SQL*Plus as the user running the stored procedure, you can execute 'select * from user_tab_privs' to see what privileges that user is granted.If the user is granted execute directly on the package, you will see an entry in that table.If the user is granted execute via role, it is harder to figure out because you need to look at the roles granted to the user (select * from user_role_privs) and then what object privileges are granted to the roles (not a single SQL statement). HTH. Janet. At 01:49 PM 9/14/2004 -0400, you wrote: Janet, I have the snippet now from them.This is how it's currently defined (which looks like it's in line with what you posted aside from In Out): Type ReturnSet is ref cursor; Procedure testpckg(curReturnSet Out Returnset); They had switched it before to be In Out for me and I still had the same error.The one thing I did notice is they do not have it defined with a schema name, they just have: package Test01 is Type ReturnSet is ref cursor; Procedure testpckg(curReturnSet Out Returnset); Could that possible cause a permissions issue as Adam had alluded too? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Oracle Stored Procedures
At 02:04 PM 9/14/2004 -0400, you wrote: No, by default this package will be created under the user who compiles it (the schema owner). Janet what are you thoughts on the variable type? I haven't been able to return a ref cursour when its specified as just OUT. Adam - You are correct.The package is owned by the user who creates it and they are the only ones who can execute a procedure in the package until they 'grant execute on the package' to the user executing it or to a role granted to the user or to public (which most DBA's will frown upon). I also think the variable type needs to be of type IN OUT as mentioned previously. Janet. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle Stored Procedures
In SQL*Plus, you can see the package specification and hence the procedure definition by entering the DESC command at the SQL*Plus prompt.For example;DESC schema_name.package_name; The DESC will not show you the code inside the package but it will show you what each procedure expects for parameters. HTH. Janet. At 05:01 PM 9/10/2004 -0400, you wrote: I do not have the package unfortunately. -Original Message- From: Janet Schmitt [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 12:49 PM To: CF-Talk Subject: Re: Oracle Stored Procedures What does the package specification for TESTPCKG look like? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Oracle Stored Procedures
What does the package specification for TESTPCKG look like? At 01:10 PM 9/10/2004 -0400, you wrote: Trying to call a stored procedure from Oracle that takes no parameters and returns a cursor. [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'TESTPROC' ORA-06550: line 1, column 7: PL/SQL: Statement ignored I have read a bunch of the macromedia articles about the changes in MX when working with Oracle cursors but none of them allude to this error. Googling it was not much help either.On the surface the error seems as though the procedure is looking for a parameter coming in, but I have confirmed with the procedure author that that is not the case.Just trying to eliminate any syntax issues on my call and could use another set of eyes. cfstoredproc procedure = TESTPCKG.TESTPROC dataSource = #ds# cfprocresult name=cursorResult /cfstoredproc [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: stored procedures
In Oracle, stored procedures are compiled when you create them (i.e. run the CREATE PROCEDURE/FUNCTION/PACKAGE/TRIGGER statement). The first time you call an Oracle Stored Procedure it is loaded into Oracle's memory (shared pool of the SGA).The stored procedure will stay there until it is paged out (to make room for other stored procedures).Once the procedure has been loaded into memory, it will execute quickly and the copy in memory will be used for subsequent calls.There are some things that can be done by the DBA to keep stored procedures in memory (pinning). In Oracle, execution plans are usually associated with SQL statements not with Stored Procedures. I am not sure how stored procedures and execution plans are used in other database environments or how the term compile is defined for those environments. Janet At 07:43 AM 8/26/2004 -0400, you wrote: Can you really say that for Oracle? Point out any material on the net that talks about it? I'm actually curious about this. So if you have any links that talk further about this that would be great. Ian - Original Message - From: Micha Schopman [EMAIL PROTECTED] Date: Thu, 26 Aug 2004 12:53:28 +0200 Subject: RE: stored procedures To: CF-Talk [EMAIL PROTECTED] Stored procedures are NOT pre-compiled. This is a common made mistake. Only the execution plan gets cached, but the stored procedure is compiled upon execution. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SOT: @@RowCount equivalent in Oracle
Immediately after doing the update, you can use sql%rowcount to determine the number of records processed. Here is an example of using sql%rowcount and the variable i would contain the rows processed: begin update customers set email = email || '2' where email like 'test%'; i:=sql%rowcount; i is a integer variable end; Janet. So I'd like to return the number of records affected... you can do this in SQL Server by using the @@RowCount global variable I guess, but I don't know if there is an equivalent in Oracle or what that equivalent might be.My googling has thus far been unsuccessful. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: the time
Post 8i, use timestamps.
If you are Oracle 8i or earlier, you should probably use a date field.
Here are two options:
1.Use to_date('10:10','HH:mi') to store the time in the Oracle
database.This will insert the current date in the date portion but you
can retreive the time using to_char(date_field 'HH:mi');
2.Use to_date('01/01/0001','MM/DD/') + :hour/24 + :minute/1440to
store the time in the Oracle database.This will allow you to add/subtract
time and get the time difference in fractional days.To retrieve the time
use to_char(datefield, 'HH:mi').
Hope this helps.
Janet.
At 09:01 AM 7/13/2004 -0400, you wrote:
I have to record a time in a DB.It would seem that I'd do that with
createTime.I have just a few simple questions.
In the Oracle DB, do I make a date field as usual and populate it
with yr/mo/da = 0,0,0 and then insert the createTime variable in one
block?Is there maybe a time type for a field? (I suppose not).
IOW, how is just time, where no date is needed, handled?
--
Daniel Kessler
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: Oracle Mutating Table
I am having a problem with oracle's mutating table error.All I want to do is, ON BEFORE UPDATE, Check the number of rows in the table being updated. If the number of rows is 0 set the :New.DateZero to the sysdate.Does anyone know a simple way around this? Tyler - The FOR EACH ROW in your trigger is causing the problem.You need to write the row count to a temporary table or a package variable and then use an AFTER statement trigger.The following Oracle documentation gives more details about how to fix this problem: http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76939/adg13trg.htm#786 Janet. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Sending a disconnect to Oracle through CF code...
Brian - You can determine sessions using Oracle's data dictionary tables.I think the one you would want to look at is V$SESSION.It contains current session information.You would need special privileges to the view. You may need to work with someone with DBA privileges in Oracle to set this up. HTH. Janet. At 10:23 AM 5/28/2004 -0400, you wrote: Has anyone done this before?I have a task that I must check to see if the user already has an Oracle session active when they log on.If they do, do not allow the logon.If they don't, let them continue.The Oracle session lasts for 15 minutes of inactivity.When the user logs out of the application, I need to log them out of Oracle as well. Thanks in advance Brian Yager [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Sending a disconnect to Oracle through CF code...
Brian - One more thought on this I would recommend, if it is possible in your environment, to let Oracle handle the login restrictions and session killing. Here is what you would do via Oracle: -If you want Oracle to limit the sessions for you rather than via CF code, a profile can be set up with concurrent sessions set to 1 (the init.ora parameter resource_limit must be set to TRUE) which will prevent an Oracle user from logging on more than once. -If you want Oracle to automatically kill a session after so many minutes of inactivity, you can set the idle_time parameter to the desired time in a profile. Profiles are defined by the Oracle DBA and assigned to Oracle accounts when they are created.Profiles can contain information about resource usage and password restrictions. Hope this helps. Janet. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SOT: alphabetizing nested sets
Deanna - A little late in the discussion, but what are you trying to sort by? If is it category, how about this? SELECT child.category, COUNT(child.lft) AS lvl, child.lft, child.rgt, child.categoryid FROM categoryparent, categorychild WHERE child.lft BETWEEN parent.lft AND parent.rgt GROUP BY child.lft, child.rgt, child.category, child.categoryid ORDER BY lvl, child.category Janet. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: More Oracle/CF fun!
Why are you using a cursor?Won't the select statement return a single value, the count of records matching the criteria specified in the where clause? Janet. At 01:39 PM 3/12/2004 +, you wrote: shouldn't it be: mailInfo IN OUT types.cursorType The ref cursor should be 'IN OUT' not just 'OUT'. -Adam -Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 11:53 PM To: 'CF-Talk' Subject: More Oracle/CF fun! Consider this stored procedure (part of package DLC): PROCEDURE dlc_mail_countMsg ( studentID IN number, mailInfo OUT types.cursorType ) AS BEGIN OPEN mailInfo FOR SELECT count(*) AS msgCount FROM tblMail WHERE mIndividID = studentID AND mToType = 1 AND mRecipActive = '1' AND mRead IS NULL; END dlc_mail_countMsg; ...and this CFSTOREDPROC call: cfstoredproc datasource=DLCampus procedure=dlc.dlc_mail_countMsg cfprocparam type=in value=#cookieID# cfsqltype=cf_sql_integer cfprocresult name=msgCount /cfstoredproc ...and, finally, this error message: Error Executing Database Query. [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DLC_MAIL_COUNTMSG' ORA-06550: line 1, column 7: PL/SQL: Statement ignored In tblMail, the datatypes are: mIndividIDnumber mToTypenumber mRecipActivechar(1) mReaddate I can't see the error.It looks like all of the paramters are being passed, and nothing unusual is going on.I've Googled but couldn't find anything that looks relevant. Anyone have any ideas? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Richard - What is the format of the table tblStudentInfo?What are the datatypes specified for sFirst, sLast, sOrient and studentId? What is the CFSTOREDPROC code that was used to call this procedure? Janet. At 09:45 AM 3/9/2004 -0800, you wrote: Janet, Thanks for the suggestion.I tried it, but continue to receive the unsupported data conversion error. This is getting mighty frustrating. Janet Schmitt wrote: I would try something like this for the Oracle portion: CREATE OR REPLACE PACKAGE schemaname.studentpackage IS TYPE student_type_ref_cursor IS REF CURSOR; PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number, student_cursor IN OUT student_type_ref_cursor); END; Create or replace package body schemaname.studentpackage IS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number, student_cursor IN OUT student_type_ref_cursor) is begin open student_cursor for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; end; END studentpackage; -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
I wonder if it does not like the way you are passing the value 1881.Try to set a variable to 1881 and pass that as a parameter. Something like the following (I am not sure if I have the syntax exactly right): cfset cstudentid=1881 cfProcParam type=In value=#cstudentid#cfSqlType=cf_sql_numeric variable=studentID Also, I am not sure about your cursor reference.Try using the ref cursor definition by doing the following: 1.Add this line to the package specification: TYPE student_type_ref_cursor IS REF CURSOR; 2.Change the procedure definition in the package spec and body from: PROCEDURE test_dlc_sp_getstudentinfo ( studentID IN integer, studentInfo OUT types.cursorType ); to PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number, student_cursor IN OUT student_type_ref_cursor); 3.Remove the close cursor statement from the package body. Janet. At 10:59 AM 3/9/2004 -0800, you wrote: Janet Schmitt wrote: Richard - What is the format of the table tblStudentInfo?What are the datatypes specified for sFirst, sLast, sOrient and studentId? What is the CFSTOREDPROC code that was used to call this procedure? Janet. Janet, Here is the table definition: SIDNUMBERNOT NULL SFIRSTVARCHAR2(30)NOT NULL SLASTVARCHAR2(50)NOT NULL SORIENTCHAR(1)NOT NULL There are a few other fields in the table, but I'm not referencing them, so they don't seem relevant. Here is the CFSTOREDPROC code: cfstoredproc datasource=DLCampus procedure=dlc.test_dlc_sp_getStudentInfo cfprocparam type=in value=1881 cfsqltype=cf_sql_integer cfprocresult name=getName /cfstoredproc And, just for completeness's sake, here is the package definition, including the SP: CREATE OR REPLACE PACKAGE dlc AS PROCEDURE test_dlc_sp_getstudentinfo ( studentID IN integer, studentInfo OUT types.cursorType ); END dlc; / CREATE OR REPLACE PACKAGE BODY dlc AS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN integer, studentInfo OUT types.cursorType ) as sFirst varchar2(30); sLast varchar2(50); sOrient char(1); begin open studentInfo for select sFirst, sLast, sOrient from tblStudentInfo where sid = 1881; CLOSE studentInfo; END test_dlc_sp_getStudentInfo; END dlc; At 09:45 AM 3/9/2004 -0800, you wrote: Janet, Thanks for the suggestion.I tried it, but continue to receive the unsupported data conversion error. This is getting mighty frustrating. Janet Schmitt wrote: I would try something like this for the Oracle portion: CREATE OR REPLACE PACKAGE schemaname.studentpackage IS TYPE student_type_ref_cursor IS REF CURSOR; PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number, student_cursor IN OUT student_type_ref_cursor); END; Create or replace package body schemaname.studentpackage IS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number, student_cursor IN OUT student_type_ref_cursor) is begin open student_cursor for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; end; END studentpackage; -- [ -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Distinct
Try this: select email, max(customerid) ci from customers group by email order by ci desc; At 02:57 PM 3/8/2004 -0600, you wrote: Perhaps a subquery: CFQuery name=getcustomer datasource=printprices SELECT email, customerid FROM customers Where customer != 'test' AND email in(select distinct email from customers) Order by customerid desc /cfquery -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Monday, March 08, 2004 2:08 PM To: CF-Talk Subject: SOT: Distinct CFQuery name=getcustomer datasource=printprices SELECT distinct email, customerid FROM customers Where customer != 'test' Order by customerid desc /cfquery In the above Query, how do I distinct just the email and not the customerid. Because of the Order by SQL requires me to put customer id in the Distinct part of Select. This throws things off since some customers have distinct customerid's but their emails are similar. How do I do an order by customerid, but still display just distinct email addresses not duplicates? Thx. Robert O. HWW _ -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
I would try something like this for the Oracle portion: CREATE OR REPLACE PACKAGE schemaname.studentpackage IS TYPE student_type_ref_cursor IS REF CURSOR; PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number, student_cursor IN OUT student_type_ref_cursor); END; Create or replace package body schemaname.studentpackage IS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number, student_cursor IN OUT student_type_ref_cursor) is begin open student_cursor for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; end; END studentpackage; [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
