multiple PL/SQL statements in cfquery
Ive run into an issue where I need to run 2 PL/SQL statements on Oracle in a single cfquery tag. In my SQL developer, I can run 2 at a time using a semi-colon as a delimiter but cfquery errors out on me saying the semi-colon is an invalid character. Heres the query that Im trying to run: ALTER SESSION SET NLS_DATE_FORMAT = '-MM-DD HH24:MI:SS'; SELECT SYSDATE as theDate, startDate, TO_CHAR(startdate, '-MM-DD HH24:MI:SS') as maskedDate FROMbryanlghcollege.dmEvent; This is just a test to see if the date is being formatted correctly. I read that if I save the PL/SQL statements as a single variable and run it, it would work but it didnt. Heres the code for that: cfsavecontent variable=sql ALTER SESSION SET NLS_DATE_FORMAT = '-MM-DD HH24:MI:SS'; SELECT SYSDATE as theDate, startDate, TO_CHAR(startdate, '-MM-DD HH24:MI:SS') as maskedDate FROM bryanlghcollege.dmEvent; /cfsavecontent cfquery name=pleaseRun datasource=#application.dsn# #PreserveSingleQuotes(sql)# /cfquery Does anyone have any ideas for me? _ Jake Churchill CF Webtools 11204 Davenport, Ste. 200b Omaha, NE 68154 HYPERLINK http://www.cfwebtools.comhttp://www.cfwebtools.com 402-408-3733 x103 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.11.19/953 - Release Date: 8/14/2007 5:19 PM ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286251 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: multiple PL/SQL statements in cfquery
On Wednesday 15 Aug 2007, [EMAIL PROTECTED] wrote: ALTER SESSION SET NLS_DATE_FORMAT = 'MMDD HH24:MI:SS'; SELECT SYSDATE as theDate, startDate, TO_CHAR(startdate, 'MMDD HH24:MI:SS') as maskedDate Data formating is not the job of the database. Return a proper date, and format as needed in your display. Then you won't need the ALTER SESSION. -- Tom Chiverton Helping to apprehensively maintain front-end methodologies on: http://thefalken.livejournal.com This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286264 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: How to Fetch records from a Ref Cursor returned by a PL/SQL procedure
I have supported other applications with the ability to do this (Java, Delphi). Was that with a Java Web application ( JSP) or a standalone application ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:279654 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: How to Fetch records from a Ref Cursor returned by a PL/SQL procedure
Rule of thumb, let the database do the work and not the web server. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:279656 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: How to Fetch records from a Ref Cursor returned by a PL/SQL procedure
Dave O wrote: They call one of our stored procedures which dynamically builds a SQL statement and returns to them via a RefCursor. The problem we are having is that the query could possibly return hundreds of thousands, or maybe a million or more rows. From a pl/sql point of view, after opening a cursor you must FETCH the rows from the Cursor, using program logic to determine when to continue and when to stop fetching(threshhold hit or end of cursor), then close the cursor. I don't know if Oracle or the Oracle JDBC drivers do something extra, but standard cursors can work the same with CF. See for instance: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:49577#265064 They claim that once they open the cursor it automatically fetches all rows until the end of the cu rsor, then closes it. That is the default behaviour of the CF implementation of a stored procedure call. If you want something else, you need to write it yourself as a bunch of queries in a transaction instead of using the cfstoredproc tag. Since they had issues with too much data crashing the Coldfusion server (out of memory) they have code in the pls/sql procedure that builds the dynamic sql statment for the ref cursor, that only fetches 100 rows at a time for display, using a (rownum BETWEEN 1 and 100) where clause delimiter. When the user clicks page 2, they call the stored procedure again and use (rownum BETWEEN 101 and 200), click page 4 and another call using (rownum BETWEEN 301 and 400). Unfortunately, oracle must process everything all over again (group by, sort, etc.) just to return the next 100 rows. For the larger queries that take up to 90 seconds to compute the results, this hit happens for every page (100 rows a page) that is selected. I am in utter shock that they are processing data like this. Could someone please let me know if its possible to programatically fetch records from the ref cursor, fetching rows as needed, say 100 at a time, then closing the cursor after the user is finished browsing the data and exits. You are missing an important point of web applications: once the first page has loaded in the browser, the user is finished. There is no way of knowing whether there will be another request for the second page or not. Web applications do not have a persistent connection between the browser and the server like desktop applications, so every request stands on its own. Jochem PS Please use an email client that wraps your lines at some sensible number of characters, this is really hard to read. ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:279189 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
How to Fetch records from a Ref Cursor returned by a PL/SQL procedure
Hi. To start with I am an Oracle DBA, I know little to nothing about ColdFusion. I am supporting an application using ColdFusion MX7. They call one of our stored procedures which dynamically builds a SQL statement and returns to them via a RefCursor. The problem we are having is that the query could possibly return hundreds of thousands, or maybe a million or more rows. It's necessary to do the query processing on the DB server as the processing involves complex joins, group by, sorting, etc. processing. From a pl/sql point of view, after opening a cursor you must FETCH the rows from the Cursor, using program logic to determine when to continue and when to stop fetching(threshhold hit or end of cursor), then close the cursor. The Coldfusion developers claim they have no idea how to do this. I have supported other applications with the ability to do this (Java, Delphi). They claim that once they open the cursor it automatically fetches all rows until the end of the cursor, then closes it. Since they had issues with too much data crashing the Coldfusion server (out of memory) they have code in the pls/sql procedure that builds the dynamic sql statment for the ref cursor, that only fetches 100 rows at a time for display, using a (rownum BETWEEN 1 and 100) where clause delimiter. When the user clicks page 2, they call the stored procedure again and use (rownum BETWEEN 101 and 200), click page 4 and another call using (rownum BETWEEN 301 and 400). Unfortunately, oracle must process everything all over again (group by, sort, etc.) just to return the next 100 rows. For the larger queries that take up to 90 seconds to compute the results, this hit happens for every page (100 rows a page) that is selected. I am in utter shock that they are processing data like this. Could someone please let me know if its possible to programatically fetch records from the ref cursor, fetching rows as needed, say 100 at a time, then closing the cursor after the user is finished browsing the data and exits. If possible, does anyone have a code sample I could give them. Thanks. ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:279158 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: How to Fetch records from a Ref Cursor returned by a PL/SQL procedure
Unfortunately, the standard tags for this will fetch the whole result from the ref cursor or the first n rows only (as specified in the cfprocresult tag). Were the apps you've seen this done in desktop apps, not web apps? CF effectively ends the DB connection after each request (sure it can be pooled, but as far as the each request is concerned a new connection is provided from this pool), which is what you'd expect from a web app. However, you can use Java directly inside CF so it's possible you may be able to get somewhere with a native Java class. On 5/25/07, Dave O [EMAIL PROTECTED] wrote: Hi. To start with I am an Oracle DBA, I know little to nothing about ColdFusion. I am supporting an application using ColdFusion MX7. They call one of our stored procedures which dynamically builds a SQL statement and returns to them via a RefCursor. The problem we are having is that the query could possibly return hundreds of thousands, or maybe a million or more rows. -- mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:279168 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
OT: PL/SQL to loop over list
Hey All, I'm converting some data in Oracle 9i... Specifically a filed that currently holds *gasp* a comma-seperated value list Does anyone have a function handy that would accept the list as an argument and then loop over the elements in the list so I can insert each value into a new tabe where each value will be in a seperate record like it should be?? The CF equivalent is: cfset MyListColumn = 1,2,3,4,5,6,7,8 cfloop list=#MyListColumn# index=currVal INSERT INTO MyTable ( MyField ) VALUES ( #currVal# ) /cfloop So I need some PL/SQL to take a column in as MyListColumn and then loop over that list referencing each element (I can handle the insert myself). Thanks in advance Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213931 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: PL/SQL to loop over list
From: Bryan Stevenson [mailto:[EMAIL PROTECTED]
@VAR_ARRAY varchar(8000),
@VAR_SEPARATOR char(1)
AS
SET NOCOUNT ON
DECLARE @LOCAL_SEPARATOR_POSITION int
DECLARE @LOCAL_ARRAY_VALUE varchar(1000)
SET @VAR_ARRAY = @VAR_ARRAY + @VAR_SEPARATOR
WHILE PATINDEX('%' + @VAR_SEPARATOR + '%' , @VAR_ARRAY) 0
BEGIN
SELECT
@LOCAL_SEPARATOR_POSITION = PATINDEX('%' + @VAR_SEPARATOR + '%' ,
@VAR_ARRAY)
SELECT
@LOCAL_ARRAY_VALUE = LEFT(@VAR_ARRAY, @LOCAL_SEPARATOR_POSITION - 1)
INSERT INTO
TBLNAME
(
CONTRACT_TYPE_ID
)
VALUES
(
@LOCAL_ARRAY_VALUE
)
SELECT @LOCAL_ARRAY_VALUE
SELECT @VAR_ARRAY = STUFF(@VAR_ARRAY, 1, @LOCAL_SEPARATOR_POSITION, '')
END
SET NOCOUNT OFF
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213935
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: OT: PL/SQL to loop over list
This is for dealing with a list, probably could modify it for your needs. Insert Statement: * -- Insert the Manager lookup records * INSERT INTO LCD_MANAGER_LOOKUP (EMPLOYEE_ID, MANAGER_ID) SELECT * FROM TABLE( Managerparser( CURSOR( SELECT DIRECTORY_ID, MANAGER FROM LCD_PERSON WHERE MANAGER IS NOT NULL ) ) ); Collection Type: CREATE OR REPLACE TYPE ITM_Manager_Tabletype AS TABLE OF ITM_Manager_ScalarType / Object Type: CREATE OR REPLACE TYPE ITM_Manager_ScalarType AS OBJECT ( EMPLOYEE_ID NUMBER, MANAGER_ID NUMBER ) / Function code: CREATE OR REPLACE FUNCTION Managerparser( p_cursor IN sys_refcursor ) RETURNITM_Manager_Tabletype pipelined AS l_row ITM_Manager_ScalarType := ITM_Manager_ScalarType( NULL, NULL ); l_endsection NUMBER; l_piece LONG; l_rcd LONG; l_comma NUMBER; l_space NUMBER; l_employee_id NUMBER; BEGIN LOOP FETCH p_cursor INTO l_employee_id, l_rcd; EXIT WHEN p_cursor%NOTFOUND; l_row.employee_ID := l_employee_id; l_rcd := l_rcd || ', '; LOOP l_endsection := INSTR( l_rcd, ', ' ); EXIT WHEN NVL(l_endsection,0) = 0; l_piece := SUBSTR( l_rcd, 1, l_endsection-1 ); l_rcd := SUBSTR( l_rcd, l_endsection+1 ); l_comma := INSTR( l_piece, ',' ); l_piece := SUBSTR( l_piece, 1, l_comma-1 ); l_space := INSTR(l_piece, ' ', -1); l_row.manager_id := TO_NUMBER(SUBSTR(l_piece, l_space)); pipe ROW (l_row); END LOOP; END LOOP; CLOSE p_cursor; RETURN; END; / On 8/5/05, Bryan Stevenson [EMAIL PROTECTED] wrote: Hey All, I'm converting some data in Oracle 9i... Specifically a filed that currently holds *gasp* a comma-seperated value list Does anyone have a function handy that would accept the list as an argument and then loop over the elements in the list so I can insert each value into a new tabe where each value will be in a seperate record like it should be?? The CF equivalent is: cfset MyListColumn = 1,2,3,4,5,6,7,8 cfloop list=#MyListColumn# index=currVal INSERT INTO MyTable ( MyField ) VALUES ( #currVal# ) /cfloop So I need some PL/SQL to take a column in as MyListColumn and then loop over that list referencing each element (I can handle the insert myself). Thanks in advance Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com http://www.electricedgesystems.com ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213937 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: PL/SQL to loop over list
-Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED] What I sent was for SQL Server... Sorry about that. See if somneone can help translate it into PL/SQL. Mike ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213936 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: PL/SQL to loop over list
hehe...thanks Michael...I though that looked like T-SQL ;-) Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213938 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: PL/SQL to loop over list
I gave the same snippet to a buddy last year who wanted it for Oracle. I think he wount up using INSTR and didn't have to modify too much. Good luck. From: Bryan Stevenson [mailto:[EMAIL PROTECTED] hehe...thanks Michael...I though that looked like T-SQL ;-) ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213939 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: PL/SQL to loop over list
I tried sending a reply earlier but did not notice it coming through. I have a function for something similar that you could probably modify for this. I will plug it into a file and send it to you offlist. On 8/5/05, Bryan Stevenson [EMAIL PROTECTED] wrote: hehe...thanks Michael...I though that looked like T-SQL ;-) Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com http://www.electricedgesystems.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213943 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 PL/SQL connection through ColdFusion
I will give a shot. Thank you. Try the Native Oracle driver in CF Enterprise, not the ODBC driver. BTW, this all works perfectly in CFMX. -Original Message- From: Wach, Samson [mailto:[EMAIL PROTECTED] Sent: Wednesday, 6 October 2004 1:35 To: CF-Talk Subject: Oracle PL/SQL connection through ColdFusion The organization where I work is currently using ColdFusion 5 Enterprise on the Windows 2000 Advanced Server platform. We are accessing an Oracle database using an ODBC driver from Data Direct. When I attempt to add PL/SQL commands (ex. BEGIN) I receive a message indicating that the SQL expects SELECT UPDATE etc. Does anybody know how to run PL/SQL in a ColdFusion environment? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle PL/SQL connection through ColdFusion
I'm not sure what you're attempting to do is possible then. I'm fairly certain that the cfquery tag only supports native SQL statements only (SELECT, INSERT, UPDATE, DELETE). I don't believe it supports proprietary procedural code such as PL/SQL or Transact-SQL. I don't think even CFMX supports that. Maybe there's a different tag that allows it, but if so, I'm not aware of it. Sorry. // YEX // _ From: Samson Wach [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 05, 2004 4:36 PM To: CF-Talk Subject: Re: Oracle PL/SQL connection through ColdFusion Thanks for the suggestion, but we of the Web development team do not have access to create stored procedures in the database.We need to be able to pass the PL/SQL from the ColdFusion module. Samson Wach cfquery datasouce=#dsn# CREATE OR REPLACE VIEW MONTHLYTOTALS AS SELECT * /cfquery I am able to run the above pl/sql query by using the oracle thin client JDBC rather then the odbc connection.You may need to update your drivers to get this kind of functionality. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice:This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Oracle PL/SQL connection through ColdFusion
It has been a very long time since I have worked with MSSQL/CF and had to do anything special with the queries, but I seem to recall with MSSQL you can use T-SQL in your CFQUERIES. On Wed, 6 Oct 2004 09:09:58 -0400, Yexley Robert D Contr AFRL/PROE [EMAIL PROTECTED] wrote: I'm not sure what you're attempting to do is possible then. I'm fairly certain that the cfquery tag only supports native SQL statements only (SELECT, INSERT, UPDATE, DELETE). I don't believe it supports proprietary procedural code such as PL/SQL or Transact-SQL. I don't think even CFMX supports that. Maybe there's a different tag that allows it, but if so, I'm not aware of it. Sorry. // YEX // _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Oracle PL/SQL connection through ColdFusion
I think what you are trying to do, by defenition will not work, plus it doesnt make much sense at all. I believe you can execute DML and DDL through cfquery, but why would you ever want to execute PL/SQL? If you need an IF or LOOP statement just use CF. What benefits are you expecting from PL/SQL via cfQuery? -Adam On Tue, 05 Oct 2004 16:35:38 -0400, Samson Wach [EMAIL PROTECTED] wrote: Thanks for the suggestion, but we of the Web development team do not have access to create stored procedures in the database.We need to be able to pass the PL/SQL from the ColdFusion module. Samson Wach cfquery datasouce=#dsn# CREATE OR REPLACE VIEW MONTHLYTOTALS AS SELECT * /cfquery I am able to run the above pl/sql query by using the oracle thin client JDBC rather then the odbc connection.You may need to update your drivers to get this kind of functionality. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice:This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle PL/SQL connection through ColdFusion
CFMX does indeed support it as I've used it myself (as a quick test of some code). Of course I then created the procedure in an Oracle package, but it still worked in a standard cfquery. -Original Message- From: Yexley Robert D Contr AFRL/PROE [mailto:[EMAIL PROTECTED] Sent: Wednesday, 6 October 2004 9:10 To: CF-Talk Subject: RE: Oracle PL/SQL connection through ColdFusion I'm not sure what you're attempting to do is possible then. I'm fairly certain that the cfquery tag only supports native SQL statements only (SELECT, INSERT, UPDATE, DELETE). I don't believe it supports proprietary procedural code such as PL/SQL or Transact-SQL. I don't think even CFMX supports that. Maybe there's a different tag that allows it, but if so, I'm not aware of it. Sorry. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle PL/SQL connection through ColdFusion
Calling a procedure in a built-in package without the call() syntax is only possible this way, if the user is restricted from creating their own procedures as in this case. -Original Message- From: Adrocknaphobia [mailto:[EMAIL PROTECTED] Sent: Wednesday, 6 October 2004 10:41 To: CF-Talk Subject: Re: Oracle PL/SQL connection through ColdFusion I think what you are trying to do, by defenition will not work, plus it doesnt make much sense at all. I believe you can execute DML and DDL through cfquery, but why would you ever want to execute PL/SQL? If you need an IF or LOOP statement just use CF. What benefits are you expecting from PL/SQL via cfQuery [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle PL/SQL connection through ColdFusion
I should have added, if named arguments are necessary. -Original Message- From: James Holmes Sent: Thursday, 7 October 2004 12:05 To: CF-Talk Subject: RE: Oracle PL/SQL connection through ColdFusion Calling a procedure in a built-in package without the call() syntax is only possible this way, if the user is restricted from creating their own procedures as in this case. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle PL/SQL connection through ColdFusion
Would you be able to provide a code sample of an anonymous PL/SQL block within a cfquery tag? Particularly one that requires arguments if possible. I'd be interested in seeing an example of that approach, as I've never seen any sort of documentation on how to implement this. I think that's exactly what the original message was requesting as well, so I think it would help a number of people out. Thanks very much for straightening me out.;) // YEX // _ From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 06, 2004 12:01 PM To: CF-Talk Subject: RE: Oracle PL/SQL connection through ColdFusion CFMX does indeed support it as I've used it myself (as a quick test of some code). Of course I then created the procedure in an Oracle package, but it still worked in a standard cfquery. -Original Message- From: Yexley Robert D Contr AFRL/PROE [mailto:[EMAIL PROTECTED] Sent: Wednesday, 6 October 2004 9:10 To: CF-Talk Subject: RE: Oracle PL/SQL connection through ColdFusion I'm not sure what you're attempting to do is possible then. I'm fairly certain that the cfquery tag only supports native SQL statements only (SELECT, INSERT, UPDATE, DELETE). I don't believe it supports proprietary procedural code such as PL/SQL or Transact-SQL. I don't think even CFMX supports that. Maybe there's a different tag that allows it, but if so, I'm not aware of it. Sorry. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle PL/SQL connection through ColdFusion
Here's the simplest example (the test I originally performed, which syncs an Oracle Text index using the built-in package ctx_ddl): cfset MyIndexName = idx_fulltext cfquery datasource=My_DSN name=QUpdateTextIndex BEGIN ctx_ddl.sync_index(cfqueryparam cfsqltype=cf_sql_varchar value=#MyIndexName#); END; /cfquery OK, so that wasn't all that interesting, but it does work in CFMX. It's more worthwhile when you need named parameters, for one of the alternative forms of an overloaded prodecure. -Original Message- From: Yexley Robert D Contr AFRL/PROE [mailto:[EMAIL PROTECTED] Sent: Thursday, 7 October 2004 12:17 To: CF-Talk Subject: RE: Oracle PL/SQL connection through ColdFusion Would you be able to provide a code sample of an anonymous PL/SQL block within a cfquery tag? Particularly one that requires arguments if possible. I'd be interested in seeing an example of that approach, as I've never seen any sort of documentation on how to implement this. I think that's exactly what the original message was requesting as well, so I think it would help a number of people out. Thanks very much for straightening me out.;) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Oracle PL/SQL connection through ColdFusion
The organization where I work is currently using ColdFusion 5 Enterprise on the Windows 2000 Advanced Server platform. We are accessing an Oracle database using an ODBC driver from Data Direct. When I attempt to add PL/SQL commands (ex. BEGIN) I receive a message indicating that the SQL expects SELECT UPDATE etc. Does anybody know how to run PL/SQL in a ColdFusion environment? Thanks, Samson Wach [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle PL/SQL connection through ColdFusion
Put the PL/SQL in the database in the form of a function, procedure or package, and then use cfstoredproc ... / to run it. // YEX // _ From: Wach, Samson [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 05, 2004 1:35 PM To: CF-Talk Subject: Oracle PL/SQL connection through ColdFusion The organization where I work is currently using ColdFusion 5 Enterprise on the Windows 2000 Advanced Server platform. We are accessing an Oracle database using an ODBC driver from Data Direct. When I attempt to add PL/SQL commands (ex. BEGIN) I receive a message indicating that the SQL expects SELECT UPDATE etc. Does anybody know how to run PL/SQL in a ColdFusion environment? Thanks, Samson Wach _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle PL/SQL connection through ColdFusion
cfquery datasouce=#dsn# CREATE OR REPLACE VIEW MONTHLYTOTALS AS SELECT * /cfquery I am able to run the above pl/sql query by using the oracle thin client JDBC rather then the odbc connection.You may need to update your drivers to get this kind of functionality. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice:This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle PL/SQL connection through ColdFusion
That's not PL/SQL though. That's simply an oracle object create statement, which only requires that you have the CREATE privilege selected in your DSN within the CF Administrator for the connection, which, as you stated, is based on the driver you're using. But it's not PL/SQL. // YEX // _ From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 05, 2004 3:12 PM To: CF-Talk Subject: RE: Oracle PL/SQL connection through ColdFusion cfquery datasouce=#dsn# CREATE OR REPLACE VIEW MONTHLYTOTALS AS SELECT * /cfquery I am able to run the above pl/sql query by using the oracle thin client JDBC rather then the odbc connection.You may need to update your drivers to get this kind of functionality. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice:This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Oracle PL/SQL connection through ColdFusion
Thanks for the suggestion, but we of the Web development team do not have access to create stored procedures in the database.We need to be able to pass the PL/SQL from the ColdFusion module. Samson Wach cfquery datasouce=#dsn# CREATE OR REPLACE VIEW MONTHLYTOTALS AS SELECT * /cfquery I am able to run the above pl/sql query by using the oracle thin client JDBC rather then the odbc connection.You may need to update your drivers to get this kind of functionality. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice:This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle PL/SQL connection through ColdFusion
Try the Native Oracle driver in CF Enterprise, not the ODBC driver. BTW, this all works perfectly in CFMX. -Original Message- From: Wach, Samson [mailto:[EMAIL PROTECTED] Sent: Wednesday, 6 October 2004 1:35 To: CF-Talk Subject: Oracle PL/SQL connection through ColdFusion The organization where I work is currently using ColdFusion 5 Enterprise on the Windows 2000 Advanced Server platform. We are accessing an Oracle database using an ODBC driver from Data Direct. When I attempt to add PL/SQL commands (ex. BEGIN) I receive a message indicating that the SQL expects SELECT UPDATE etc. Does anybody know how to run PL/SQL in a ColdFusion environment? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Oracle PL/SQL and CLOBS
This is somewhat off topic I guess...But if someone can help me out, I would be grateful. I have a stored procedure that exports data from a series of tables to a specific format. The file is fixed with and there is data layout for the mapping.Anyway.My question is... Each row contains about 40,000 columns and there are thousands of rows.I am building this row in a clob, I just keep concatenating it until the next row starts.Basic stuff.This is very slow, I am guessing because I am using clobs.In my testing with a varchar2(4000) the procedure is very fast. Obviously, when I test with the varchar, I have to limit the data that I retrieve to under 4000 chars.What I need to know is, is there a better way to handle this? My first thought is to create 10 varchar variables and spread the data across them and bring it together in the end.Or I could create a table with a varchar field and spread it across multiple rows and bring that together in the end. The clobs work fine when exporting just a few rows but when I try to export a few hundred; The program takes about an hour and temp table space grows by 768 megs. Thanks, David [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Oracle PL/SQL and CLOBS
Hi David, You've explained what you're trying to implement but not enough of why you've chosen to implement things this way. CLOBS are slow so you should try to avoid them unless necessary, but I don't have enough of an understanding of what problem you're trying to solve to say whether or not you CAN avoid them. Could you give an explanation of what problem you're trying to solve? --- [EMAIL PROTECTED] wrote: This is somewhat off topic I guess...But if someone can help me out, I would be grateful. I have a stored procedure that exports data from a series of tables to a specific format. The file is fixed with and there is data layout for the mapping. Anyway.My question is... Each row contains about 40,000 columns and there are thousands of rows.I am building this row in a clob, I just keep concatenating it until the next row starts.Basic stuff.This is very slow, I am guessing because I am using clobs.In my testing with a varchar2(4000) the procedure is very fast. Obviously, when I test with the varchar, I have to limit the data that I retrieve to under 4000 chars. What I need to know is, is there a better way to handle this? My first thought is to create 10 varchar variables and spread the data across them and bring it together in the end.Or I could create a table with a varchar field and spread it across multiple rows and bring that together in the end. The clobs work fine when exporting just a few rows but when I try to export a few hundred; The program takes about an hour and temp table space grows by 768 megs. Thanks, David = I-Lin Kuo Macromedia CF5 Advanced Developer Sun Certified Java 2 Programmer __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Oracle PL/SQL and CLOBS
I tried not to go into too much detail because the application is complicated and I was afraid that I would spend too much time explaining the application.I'm not looking for syntax, just an Idea. I have a string of data that can be more that 40,000 chars long and I am using a CLOB while building this string.I will be building thousands of these strings per fixed width export.I know that I can avoid excessive use of CLOBS by storing the data in multiple varchars and adding them to a CLOB at the end.I just dont know if this will be any faster.I cannot avoid the CLOB completely but what I can do is avoid concatenating the CLOB over and over again by spreading the data across many varchars.Basically, if you or anyone else had to build a string that was 40,000 chars long how would you all do it? Right now I build the entire sting in a CLOB then insert it into a table.Perhaps, it would be better to concatenate the data in the actual table and just keep adding to the field instead of maintaining a clob variable in PL/SQL? Thanks, David -Original message- From: I-Lin Kuo [EMAIL PROTECTED] Date: Fri, 23 Jul 2004 12:53:26 -0400 To: CF-Talk [EMAIL PROTECTED] Subject: Re: Oracle PL/SQL and CLOBS Hi David, You've explained what you're trying to implement but not enough of why you've chosen to implement things this way. CLOBS are slow so you should try to avoid them unless necessary, but I don't have enough of an understanding of what problem you're trying to solve to say whether or not you CAN avoid them. Could you give an explanation of what problem you're trying to solve? --- [EMAIL PROTECTED] wrote: This is somewhat off topic I guess...But if someone can help me out, I would be grateful. I have a stored procedure that exports data from a series of tables to a specific format. The file is fixed with and there is data layout for the mapping. Anyway.My question is... Each row contains about 40,000 columns and there are thousands of rows.I am building this row in a clob, I just keep concatenating it until the next row starts.Basic stuff.This is very slow, I am guessing because I am using clobs.In my testing with a varchar2(4000) the procedure is very fast. Obviously, when I test with the varchar, I have to limit the data that I retrieve to under 4000 chars. What I need to know is, is there a better way to handle this? My first thought is to create 10 varchar variables and spread the data across them and bring it together in the end.Or I could create a table with a varchar field and spread it across multiple rows and bring that together in the end. The clobs work fine when exporting just a few rows but when I try to export a few hundred; The program takes about an hour and temp table space grows by 768 megs. Thanks, David = I-Lin Kuo Macromedia CF5 Advanced Developer Sun Certified Java 2 Programmer __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Oracle PL/SQL and CLOBS
David, Assuming this is Oracle, if you're using the Macromedia drivers with a stored procedure that has an output parameter type of varchar2, then even though the varchar2 datatype can hold up to 32k the Macromedia Oracle driver will only return the first 4k.If you use the Oracle Thin Client instead, then you can retrieve all 32k. I just discovered a related bug this week where if the out parameter of a stored procedure is clob, and you use the Macromedia Oracle driver, then if the CF_SQL_TYPE for the out is set to clob you will get an array of characters rather than a contiguous string.Leaving the SP out parameter as clob but changing the CF_SQL_TYPE to varchar resolved the problem as the clob was returned as a string. My testing for these issues was done with CFMX 6.1, Macromedia driver version 3.3, Oracle 8.1.7. Hopefully, some of this may help. Steven Erat _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 23, 2004 11:39 AM To: CF-Talk Subject: Oracle PL/SQL and CLOBS This is somewhat off topic I guess...But if someone can help me out, I would be grateful. I have a stored procedure that exports data from a series of tables to a specific format. The file is fixed with and there is data layout for the mapping.Anyway.My question is... Each row contains about 40,000 columns and there are thousands of rows.I am building this row in a clob, I just keep concatenating it until the next row starts.Basic stuff.This is very slow, I am guessing because I am using clobs.In my testing with a varchar2(4000) the procedure is very fast. Obviously, when I test with the varchar, I have to limit the data that I retrieve to under 4000 chars.What I need to know is, is there a better way to handle this? My first thought is to create 10 varchar variables and spread the data across them and bring it together in the end.Or I could create a table with a varchar field and spread it across multiple rows and bring that together in the end. The clobs work fine when exporting just a few rows but when I try to export a few hundred; The program takes about an hour and temp table space grows by 768 megs. Thanks, David [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: RE: Oracle PL/SQL and CLOBS
I am using the Oracle 9i driver. Not sure if it's the thin client or not. Funny you should, say that.I think I am using varchar2 as the out parameter and not CLOB but only because I was using varchar2 before the CLOB and just never changed it.Thanks for the heads up. David -Original message- From: Steven Erat [EMAIL PROTECTED] Date: Fri, 23 Jul 2004 14:40:18 -0400 To: CF-Talk [EMAIL PROTECTED] Subject: RE: Oracle PL/SQL and CLOBS David, Assuming this is Oracle, if you're using the Macromedia drivers with a stored procedure that has an output parameter type of varchar2, then even though the varchar2 datatype can hold up to 32k the Macromedia Oracle driver will only return the first 4k.If you use the Oracle Thin Client instead, then you can retrieve all 32k. I just discovered a related bug this week where if the out parameter of a stored procedure is clob, and you use the Macromedia Oracle driver, then if the CF_SQL_TYPE for the out is set to clob you will get an array of characters rather than a contiguous string.Leaving the SP out parameter as clob but changing the CF_SQL_TYPE to varchar resolved the problem as the clob was returned as a string. My testing for these issues was done with CFMX 6.1, Macromedia driver version 3.3, Oracle 8.1.7. Hopefully, some of this may help. Steven Erat _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 23, 2004 11:39 AM To: CF-Talk Subject: Oracle PL/SQL and CLOBS This is somewhat off topic I guess...But if someone can help me out, I would be grateful. I have a stored procedure that exports data from a series of tables to a specific format. The file is fixed with and there is data layout for the mapping.Anyway.My question is... Each row contains about 40,000 columns and there are thousands of rows.I am building this row in a clob, I just keep concatenating it until the next row starts.Basic stuff.This is very slow, I am guessing because I am using clobs.In my testing with a varchar2(4000) the procedure is very fast. Obviously, when I test with the varchar, I have to limit the data that I retrieve to under 4000 chars.What I need to know is, is there a better way to handle this? My first thought is to create 10 varchar variables and spread the data across them and bring it together in the end.Or I could create a table with a varchar field and spread it across multiple rows and bring that together in the end. The clobs work fine when exporting just a few rows but when I try to export a few hundred; The program takes about an hour and temp table space grows by 768 megs. Thanks, David [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Help executing PL/SQL
Same results. cfsavecontent variable=plsql BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfsavecontent cfquery name=davidTest datasource=#mysession.dcname# #plsql# /cfquery results in: ORA-06550: line 1, column 50: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 1, column 7: PL/SQL: SQL Statement ignored SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = ''Welcome to here'' WHERE DATACALL = 2; END; -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 4:55 PM To: CF-Talk Subject: Re: Help executing PL/SQL Okay, what if you take that one (without line breaks) and use cfsavecontent to save it to a variable and try to run it. Does that work? (In other words, I'm trying to figure out if it's a problem with the replace statement, or a problem with the way CF is passing the variable into the cfquery.) - Original Message - From: Semrau Steven Ctr SAF/IE Yes, doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery returns: davidTest (Records=0, Time=94ms) SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; And doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Help executing PL/SQL
Hm. Then I don't know what to tell ya. - Original Message - From: Semrau Steven Ctr SAF/IE Same results. cfsavecontent variable=plsql BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfsavecontent cfquery name=davidTest datasource=#mysession.dcname# #plsql# /cfquery results in: ORA-06550: line 1, column 50: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 1, column 7: PL/SQL: SQL Statement ignored [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Help executing PL/SQL
Deanna, I'm sorry I overlooked your statement before: It does work if it's all on one line, not set as a variable first? (I can't test it, as we don't have ODBC datasources set up.) I am trying to do this with the Oracle Native drivers not the ODBC. -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 4:55 PM To: CF-Talk Subject: Re: Help executing PL/SQL Okay, what if you take that one (without line breaks) and use cfsavecontent to save it to a variable and try to run it. Does that work? (In other words, I'm trying to figure out if it's a problem with the replace statement, or a problem with the way CF is passing the variable into the cfquery.) - Original Message - From: Semrau Steven Ctr SAF/IE Yes, doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery returns: davidTest (Records=0, Time=94ms) SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; And doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Help executing PL/SQL
Hehe, that's okay I appreciate all the help you've given - These are scripts for our development so that drop/recreate tables - pre-populate information - and so on can be done by us without having to go through these 'higher-up' DBAs every time we need to mess with the data. I just wish I could get this figured out so I could tell the dba to just format your scripts like 'this and that' and drop them between the cfquery tags. Steve -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Friday, May 28, 2004 12:29 PM To: CF-Talk Subject: Re: Help executing PL/SQL Hm. Then I don't know what to tell ya. - Original Message - From: Semrau Steven Ctr SAF/IE Same results. cfsavecontent variable=plsql BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfsavecontent cfquery name=davidTest datasource=#mysession.dcname# #plsql# /cfquery results in: ORA-06550: line 1, column 50: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 1, column 7: PL/SQL: SQL Statement ignored _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Help executing PL/SQL
we have native driver with no problems...we don't use ODBC. Pat -Original Message- From: Semrau Steven Ctr SAF/IE [mailto:[EMAIL PROTECTED] Sent: Friday, May 28, 2004 12:36 PM To: CF-Talk Subject: RE: Help executing PL/SQL Deanna, I'm sorry I overlooked your statement before: It does work if it's all on one line, not set as a variable first? (I can't test it, as we don't have ODBC datasources set up.) I am trying to do this with the Oracle Native drivers not the ODBC. -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 4:55 PM To: CF-Talk Subject: Re: Help executing PL/SQL Okay, what if you take that one (without line breaks) and use cfsavecontent to save it to a variable and try to run it. Does that work? (In other words, I'm trying to figure out if it's a problem with the replace statement, or a problem with the way CF is passing the variable into the cfquery.) - Original Message - From: Semrau Steven Ctr SAF/IE Yes, doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery returns: davidTest (Records=0, Time=94ms) SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; And doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
Won't work like intended. You need to put the order by for lastdate in the inner select. ROWNUM is calculated before the order by. -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED] Sent: donderdag 27 mei 2004 1:24 To: CF-Talk Subject: Re: PL/SQL stumper select provider, lastDate from (SELECT d.provider, MAX(m.date_submitted) AS lastDate FROM fsa_detail d, fsa_master m WHERE d.masterid = m.id AND m.ssn = '123-45-6789' GROUP BY d.provider) where rownum = 7 ORDER BY lastDate desc, provider [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
This is as close as I can get, it looks like, but it still doesn't work quite right.When I dump the records used by this query and sort them in date order, I have a value that is second in the list that does not make the final top 7.I don't understand why but I'll keep chopping at it. Thanks all, Chris -Original Message- From: Pascal Peters [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 5:38 PM To: CF-Talk Subject: RE: PL/SQL stumper SELECT * FROM ( SELECT d.provider, MAX(m.date_submitted) AS lastDate FROM fsa_detail d, fsa_master m WHERE d.masterid = m.id AND m.ssn = '123-45-6789' GROUP BY d.provider ORDER BY MAX(m.date_submitted) DESC ) WHERE ROWNUM = 7 ORDER BY provider DON'T use rownum in your inner query! -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: woensdag 26 mei 2004 21:13 To: CF-Talk Subject: RE: PL/SQL stumper All right, I guess I'll need to provide actual database info to figure this out.Here's part of Jochem's suggestion, with mods to fit the actual schema (and to fix a grouping error): SELECT d.provider, MAX(m.date_submitted) AS lastDate, rownum AS rn FROM fsa_detail d, fsa_master m WHERE d.masterid = m.id AND m.ssn = '123-45-6789' GROUP BY d.provider, rownum ORDER BY MAX(m.date_submitted) DESC The above query returns this recordset: PROVIDER LASTDATERN Dr. Milton25-MAY-04 15 Dr. Uptagraff25-MAY-04 13 Duncan Health Care25-MAY-04 28 Eckerd Drugs25-MAY-04 27 Hillman's Pharmacy25-MAY-04 22 Home Depot25-MAY-04 10 Publix25-MAY-04 24 Publix Pharmacy25-MAY-04 14 Timmons Drugs25-MAY-04 8 Xylophone Inc25-MAY-04 11 Williams and Assoc.25-MAY-04 17 Wellness Center25-MAY-04 12 Walmart25-MAY-04 26 Walgreens25-MAY-04 25 Walgreen's25-MAY-04 18 Timmons Drugs25-MAY-04 16 Smith's Health Supplies 25-MAY-04 21 Publix25-MAY-04 20 Hush Puppy Shoes25-MAY-04 23 Mease Clinic25-MAY-04 19 Publix Pharmacy24-MAY-04 9 Walmart24-MAY-04 7 Treebeard14-MAY-04 6 Publix14-MAY-04 4 Walmart14-MAY-04 5 Publix14-MAY-04 2 Treebeard14-MAY-04 1 Walmart14-MAY-04 3 What I need is the first 7 (or any arbitrary number) DISTINCT providers from this recordset (ie, the 7 most recent providers) in alpha order.All my solutions so far required grouping, which put the providers in alpha order first and then I pulled the first 7.How can I get the 7 first and THEN sort by alpha? Thanks for your patience and help! Chris _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
It looks like this is as close as I can get but it still doesn't work quite right.When I dump the records used by this query and sort them in date order, I have a value that is second in the list that does not make the final top 7...I don't understand why but I'll keep chopping at it. Thanks all, Chris -Original Message- From: Pascal Peters [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 5:38 PM To: CF-Talk Subject: RE: PL/SQL stumper SELECT * FROM ( SELECT d.provider, MAX(m.date_submitted) AS lastDate FROM fsa_detail d, fsa_master m WHERE d.masterid = m.id AND m.ssn = '123-45-6789' GROUP BY d.provider ORDER BY MAX(m.date_submitted) DESC ) WHERE ROWNUM = 7 ORDER BY provider DON'T use rownum in your inner query! [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
A tip: run the inner SELECT and dump that (if there aren't too many records). The final query should give you the first 7 of this SELECT. If you have several providers with the same date, they may not come in the same order in the inner select as in the sorted table. -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: donderdag 27 mei 2004 15:51 To: CF-Talk Subject: RE: PL/SQL stumper It looks like this is as close as I can get but it still doesn't work quite right.When I dump the records used by this query and sort them in date order, I have a value that is second in the list that does not make the final top 7... I don't understand why but I'll keep chopping at it. Thanks all, Chris [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
Yes, the final query gives the first seven of the inner SELECT.It is the inner SELECT that does not appear to be sorting as expected.I don't understand why a record that is second overall when sorted by date does not make it into the first 7 when using MAX() on the date and grouping by provider... Thanks for your help, Chris -Original Message- From: Pascal Peters [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 10:27 AM To: CF-Talk Subject: RE: PL/SQL stumper A tip: run the inner SELECT and dump that (if there aren't too many records). The final query should give you the first 7 of this SELECT. If you have several providers with the same date, they may not come in the same order in the inner select as in the sorted table. -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: donderdag 27 mei 2004 15:51 To: CF-Talk Subject: RE: PL/SQL stumper It looks like this is as close as I can get but it still doesn't work quite right.When I dump the records used by this query and sort them in date order, I have a value that is second in the list that does not make the final top 7... I don't understand why but I'll keep chopping at it. Thanks all, Chris [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
Because this is up to the database. You only specify: sort on date, you don't specify how records with the same date are sorted. The database may (and probably will) do that differently if you have a group by than without a group by. If it really needs to be in that order you may have to do 3 (or more) subselects or even write a storedproc Pascal -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: donderdag 27 mei 2004 16:38 To: CF-Talk Subject: RE: PL/SQL stumper Yes, the final query gives the first seven of the inner SELECT.It is the inner SELECT that does not appear to be sorting as expected.I don't understand why a record that is second overall when sorted by date does not make it into the first 7 when using MAX() on the date and grouping by provider... Thanks for your help, Chris [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: PL/SQL stumper
ROWNUM is calculated before the order by. Good to know. Thanks. Guess it was a fluke that I actually got back what looked like the expected result set. ~Dina [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Help executing PL/SQL
Here is my situation: my DBA is having problems getting the his scripts run on the Oracle server (he does not have direct access and we have to pass off anything to a higher dba to run WHENEVER he may have a chance) and would like me to create some pages where he can drop his PL/SQL scripts between CFQUERY tags to run. I know I've seen posting in the past where people have done this and I have even reviewed the OraFusion (http://www.orafusion.com) site with their examples and work-around for CF5: Running Anonymous PL/SQL blocks: Anonymous PL/SQL blocks can be executed through cfquery with ODBC drivers - you simply enclose your code block within a matching pair of cfquery tags with the appropriate data source attributes. If you try this with native drivers, however, you will get a ORA-06550 error. There is a workaround which was pointed out to me by Mike Morris of KLA - Tencor (Thanks, Mike!) : assign the code block to a ColdFusion variable and then output the variable contents within cfquery, as shown below. cfset plsql_code= declare cursor c_table is select * from your_table; begin for r_table in c_table loop null; end loop; end; cfquery name=yourQuery datasource=yourNativeDSN username=yourUsername password=yourPwd #plsql_code# /cfquery When I tried to something as simple as: cfset plsqlcode= BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; cfquery name=davidTest datasource=#mysession.dcname# #plsqlcode# /cfquery It in fact returns an ORA-06550 error. However, if I do this (put the entire pl/sql program on one line): !--- set PL/SQL code to be on a SINGLE line instead of multiple lines when assigning to a variable --- cfset plsqlcode=BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2 END; cfquery name=davidTest datasource=#mysession.dcname# #plsqlcode# /cfquery The query runs the PL/SQL code just fine. Okay great!Unfortunately the PL/SQL scripts that the dba wants to run are NOT SIMPLE but near (and over) 100+ lines when viewed in SQL+ this could be a nightmare trying to get that amount of PL/SQL code onto one line and subsequently edit. Yes I realize this is nothing more then a simple UPDATE statement that really doesn't need to have the 'BEGIN' and 'END' as part of it. (But you don't want (or need) to read 100+ lines of pl/sql code to get the idea of the problem.) So, What I'm looking for is the 'correct' syntax to use within the CFQUERY tags for PL/SQL code. I hope I've expressed my problem a bit more thoroughly and that it's not really the SIMPLE EXAMPLE I'm trying to resolve but what the true coding technique should be to do such a thing. Specs: WinNT / CF5 / Oracle 9i Thanks in advance, - Steven Semrau SRA International, Inc. Web Solutions Group Senior Member, Professional Staff [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Help executing PL/SQL
Did you try my suggestion? Here, let me be more verbose: !--- Write your nicely formatted pl/sql code --- cfsavecontent variable=plsql BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfsavecontent !--- strip out the line breaks --- cfset plsql = replacelist(plsql, #chr(10)#,#chr(13)#, , ) !--- run it --- cfquery... #plsql# /cfquery - Original Message - From: Semrau Steven Ctr SAF/IE Here is my situation: my DBA is having problems getting the his scripts run on the Oracle server (he does not have direct access and we have to pass off anything to a higher dba to run WHENEVER he may have a chance) and would like me to create some pages where he can drop his PL/SQL scripts between CFQUERY tags to run. I know I've seen posting in the past where people have done this and I have even reviewed the OraFusion (http://www.orafusion.com) site with their examples and work-around for CF5: [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Help executing PL/SQL
Deanna, thanks for the quick reply I tried what you suggested below but it returned the same results: Oracle Error Code = 6550 ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol when expecting one of the following: begin case declare exit for go to if loop mod null pragma raise return select update while with close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = ''Welcome to here'' WHERE DATACALL = 2; END; I tried replacing the 3rd attribute with just a (space) instead of the (comma) - same error results I tried removing the var setting for replaceList() - same error results Argh, this is aggravating - I know it shouldn't be this hard to accomplish :( any other suggestions? -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 2:56 PM To: CF-Talk Subject: Re: Help executing PL/SQL Did you try my suggestion? Here, let me be more verbose: !--- Write your nicely formatted pl/sql code --- cfsavecontent variable=plsql BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfsavecontent !--- strip out the line breaks --- cfset plsql = replacelist(plsql, #chr(10)#,#chr(13)#, , ) !--- run it --- cfquery... #plsql# /cfquery - Original Message - From: Semrau Steven Ctr SAF/IE Here is my situation: my DBA is having problems getting the his scripts run on the Oracle server (he does not have direct access and we have to pass off anything to a higher dba to run WHENEVER he may have a chance) and would like me to create some pages where he can drop his PL/SQL scripts between CFQUERY tags to run. I know I've seen posting in the past where people have done this and I have even reviewed the OraFusion (http://www.orafusion.com) site with their examples and work-around for CF5: _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Help executing PL/SQL
It does work if it's all on one line, not set as a variable first? (I can't test it, as we don't have ODBC datasources set up.) - Original Message - From: Semrau Steven Ctr SAF/IE Oracle Error Code = 6550 ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol when expecting one of the following: begin case declare exit for go to if loop mod null pragma raise return select update while with close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Help executing PL/SQL
Yes, doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery returns: davidTest (Records=0, Time=94ms) SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; And doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery returns: Oracle Error Code = 6550 ORA-06550: line 1, column 7: PLS-00103: Encountered the symbol when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; How irritating is that!?Hehehe - man I'm at wits end trying to figure this out. -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 3:41 PM To: CF-Talk Subject: Re: Help executing PL/SQL It does work if it's all on one line, not set as a variable first? (I can't test it, as we don't have ODBC datasources set up.) - Original Message - From: Semrau Steven Ctr SAF/IE Oracle Error Code = 6550 ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol when expecting one of the following: begin case declare exit for go to if loop mod null pragma raise return select update while with close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Help executing PL/SQL
just use a variable to build your pl/sql... and concat it. Pat -Original Message- From: Semrau Steven Ctr SAF/IE [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 3:50 PM To: CF-Talk Subject: RE: Help executing PL/SQL Yes, doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery returns: davidTest (Records=0, Time=94ms) SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; And doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery returns: Oracle Error Code = 6550 ORA-06550: line 1, column 7: PLS-00103: Encountered the symbol when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; How irritating is that!?Hehehe - man I'm at wits end trying to figure this out. -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 3:41 PM To: CF-Talk Subject: Re: Help executing PL/SQL It does work if it's all on one line, not set as a variable first? (I can't test it, as we don't have ODBC datasources set up.) - Original Message - From: Semrau Steven Ctr SAF/IE Oracle Error Code = 6550 ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol when expecting one of the following: begin case declare exit for go to if loop mod null pragma raise return select update while with close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Help executing PL/SQL
Pat, thanks for your reply.If you look at my original posting I have tried that route as suggested by OraFusion ( http://www.orafusion.com).I have also tried using the CFSAVECONTENT, then replacing chr(10)chr(13) with a space;I have also tried by putting BR tags at the end of each line and then replacing the BR tags with a space - same results every time. Steve -Original Message- From: Whittingham, P [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 4:17 PM To: CF-Talk Subject: RE: Help executing PL/SQL just use a variable to build your pl/sql... and concat it. Pat -Original Message- From: Semrau Steven Ctr SAF/IE [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 3:50 PM To: CF-Talk Subject: RE: Help executing PL/SQL Yes, doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery returns: davidTest (Records=0, Time=94ms) SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; And doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery returns: Oracle Error Code = 6550 ORA-06550: line 1, column 7: PLS-00103: Encountered the symbol when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; How irritating is that!?Hehehe - man I'm at wits end trying to figure this out. -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 3:41 PM To: CF-Talk Subject: Re: Help executing PL/SQL It does work if it's all on one line, not set as a variable first? (I can't test it, as we don't have ODBC datasources set up.) - Original Message - From: Semrau Steven Ctr SAF/IE Oracle Error Code = 6550 ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol when expecting one of the following: begin case declare exit for go to if loop mod null pragma raise return select update while with close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Help executing PL/SQL
Okay, what if you take that one (without line breaks) and use cfsavecontent to save it to a variable and try to run it. Does that work? (In other words, I'm trying to figure out if it's a problem with the replace statement, or a problem with the way CF is passing the variable into the cfquery.) - Original Message - From: Semrau Steven Ctr SAF/IE Yes, doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery returns: davidTest (Records=0, Time=94ms) SQL = BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; And doing this: cfquery name=davidTest datasource=#mysession.dcname# BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2; END; /cfquery [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
PL/SQL stumper
I have a table like the following.With just CFML (v5) and plain Oracle PL/SQL--ie, no temp tables or stored procedures--is there a way to get, say, the 7 most recent distinct providers?I've tried every which way, even using QofQ with MAXROWS=7, but that only returned the first 7 distinct providers alphabetically.I need to get the first 7 distinct providers in date order with newest first.Can it be done without getting fancy? Thanks, Chris PROVIDER DATESTAMP Eckerd Drugs 25-MAY-04 Walmart 25-MAY-04 Walgreens 23-MAY-04 Publix 23-MAY-04 Hush Puppy Shoes 23-MAY-04 Publix 22-MAY-04 Walgreen's 20-MAY-04 Timmons Drugs 20-MAY-04 Publix Pharmacy 19-MAY-04 Walmart 16-MAY-04 Publix Pharmacy 16-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
a Top-N styled SQL...something like SELECT * FROM (SELECT * FROM tablename OREDER BY datestamp ) WHERE rownum 8 Doug -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 12:09 PM To: CF-Talk Subject: PL/SQL stumper I have a table like the following.With just CFML (v5) and plain Oracle PL/SQL--ie, no temp tables or stored procedures--is there a way to get, say, the 7 most recent distinct providers?I've tried every which way, even using QofQ with MAXROWS=7, but that only returned the first 7 distinct providers alphabetically.I need to get the first 7 distinct providers in date order with newest first.Can it be done without getting fancy? Thanks, Chris PROVIDER DATESTAMP Eckerd Drugs 25-MAY-04 Walmart 25-MAY-04 Walgreens 23-MAY-04 Publix 23-MAY-04 Hush Puppy Shoes 23-MAY-04 Publix 22-MAY-04 Walgreen's 20-MAY-04 Timmons Drugs 20-MAY-04 Publix Pharmacy 19-MAY-04 Walmart 16-MAY-04 Publix Pharmacy 16-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
I have a table like the following.With just CFML (v5) and plain Oracle PL/SQL--ie, no temp tables or stored procedures--is there a way to get, say, the 7 most recent distinct providers?I've tried every which way, even using QofQ with MAXROWS=7, but that only returned the first 7 distinct providers alphabetically.I need to get the first 7 distinct providers in date order with newest first.Can it be done without getting fancy? Thanks, Chris PROVIDER DATESTAMP Eckerd Drugs 25-MAY-04 Walmart 25-MAY-04 Walgreens 23-MAY-04 Publix 23-MAY-04 Hush Puppy Shoes 23-MAY-04 Publix 22-MAY-04 Walgreen's 20-MAY-04 Timmons Drugs 20-MAY-04 Publix Pharmacy 19-MAY-04 Walmart 16-MAY-04 Publix Pharmacy 16-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 Off the top of my head, but does this work?: SELECT provider FROM ( SELECT DISTINCT provider FROM provider_table ORDER BY datestamp DESC ) WHERE rownum = 7 Regards, Dave. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: PL/SQL stumper
Lofback, Chris wrote: I need to get the first 7 distinct providers in date order with newest first.Can it be done without getting fancy? PROVIDER DATESTAMP Eckerd Drugs 25-MAY-04 Walmart 25-MAY-04 Walgreens 23-MAY-04 Publix 23-MAY-04 Hush Puppy Shoes 23-MAY-04 Publix 22-MAY-04 Walgreen's 20-MAY-04 Timmons Drugs 20-MAY-04 Publix Pharmacy 19-MAY-04 Walmart 16-MAY-04 Publix Pharmacy 16-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 SELECT * FROM ( SELECT provider, MAX(datestamp) AS lastDate FROM table ORDER BY MAX(datestamp) DESC ) a WHERE rownum 8 Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
oops...left out the distinct provider part SELECT * FROM (SELECT provider,Max(datestamp) FROM tablename ORDER BY Max(datestamp) ) WHERE rownum 8 Doug -Original Message- From: Knudsen, Douglas Sent: Wednesday, May 26, 2004 12:16 PM To: CF-Talk Subject: RE: PL/SQL stumper a Top-N styled SQL...something like SELECT * FROM (SELECT * FROM tablename OREDER BY datestamp ) WHERE rownum 8 Doug -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 12:09 PM To: CF-Talk Subject: PL/SQL stumper I have a table like the following.With just CFML (v5) and plain Oracle PL/SQL--ie, no temp tables or stored procedures--is there a way to get, say, the 7 most recent distinct providers?I've tried every which way, even using QofQ with MAXROWS=7, but that only returned the first 7 distinct providers alphabetically.I need to get the first 7 distinct providers in date order with newest first.Can it be done without getting fancy? Thanks, Chris PROVIDER DATESTAMP Eckerd Drugs 25-MAY-04 Walmart 25-MAY-04 Walgreens 23-MAY-04 Publix 23-MAY-04 Hush Puppy Shoes 23-MAY-04 Publix 22-MAY-04 Walgreen's 20-MAY-04 Timmons Drugs 20-MAY-04 Publix Pharmacy 19-MAY-04 Walmart 16-MAY-04 Publix Pharmacy 16-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
I thought the same thing at first, but you cannot sort on a field that is not selected when using DISTINCT.And adding the field throws off the records selected because DISTINCT looks at the entire row, not just the one column... Thanks, Chris -Original Message- From: Dave Carabetta [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 12:20 PM To: CF-Talk Subject: RE: PL/SQL stumper I have a table like the following.With just CFML (v5) and plain Oracle PL/SQL--ie, no temp tables or stored procedures--is there a way to get, say, the 7 most recent distinct providers?I've tried every which way, even using QofQ with MAXROWS=7, but that only returned the first 7 distinct providers alphabetically.I need to get the first 7 distinct providers in date order with newest first.Can it be done without getting fancy? Thanks, Chris PROVIDER DATESTAMP Eckerd Drugs 25-MAY-04 Walmart 25-MAY-04 Walgreens 23-MAY-04 Publix 23-MAY-04 Hush Puppy Shoes 23-MAY-04 Publix 22-MAY-04 Walgreen's 20-MAY-04 Timmons Drugs 20-MAY-04 Publix Pharmacy 19-MAY-04 Walmart 16-MAY-04 Publix Pharmacy 16-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 Off the top of my head, but does this work?: SELECT provider FROM ( SELECT DISTINCT provider FROM provider_table ORDER BY datestamp DESC ) WHERE rownum = 7 Regards, Dave. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
This throws an error: ORA-00937: not a single-group group function But it gives me some ideas to work with. Thanks, Chris -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 12:20 PM To: CF-Talk Subject: Re: PL/SQL stumper Lofback, Chris wrote: I need to get the first 7 distinct providers in date order with newest first.Can it be done without getting fancy? PROVIDER DATESTAMP Eckerd Drugs 25-MAY-04 Walmart 25-MAY-04 Walgreens 23-MAY-04 Publix 23-MAY-04 Hush Puppy Shoes 23-MAY-04 Publix 22-MAY-04 Walgreen's 20-MAY-04 Timmons Drugs 20-MAY-04 Publix Pharmacy 19-MAY-04 Walmart 16-MAY-04 Publix Pharmacy 16-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 Publix 14-MAY-04 Walmart 14-MAY-04 SELECT * FROM ( SELECT provider, MAX(datestamp) AS lastDate FROM table ORDER BY MAX(datestamp) DESC ) a WHERE rownum 8 Jochem _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: PL/SQL stumper
Lofback, Chris wrote: This throws an error: ORA-00937: not a single-group group function Forgot the GROUP BY: SELECT * FROM ( SELECT provider, MAX(datestamp) AS lastDate FROM table GROUP BY provider ORDER BY MAX(datestamp) DESC ) a WHERE rownum 8 Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
This doesn't quite do it.The problem is that the recordset from which it gets the first 7 (by rownum) is alphabetical.So the 7 records are the first 7 of the alphabetically sorted group, not the first 7 by date (which could then be sorted alpha using QofQ).I don't know if I'm explaining this clearly--but does that make sense? Thanks again, Chris -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 1:25 PM To: CF-Talk Subject: Re: PL/SQL stumper Lofback, Chris wrote: This throws an error: ORA-00937: not a single-group group function Forgot the GROUP BY: SELECT * FROM ( SELECT provider, MAX(datestamp) AS lastDate FROM table GROUP BY provider ORDER BY MAX(datestamp) DESC ) a WHERE rownum 8 Jochem _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: PL/SQL stumper
Lofback, Chris wrote: This doesn't quite do it.The problem is that the recordset from which it gets the first 7 (by rownum) is alphabetical. That is weird, they are explicitly sorted. How about: SELECT provider, lastDate FROM ( SELECT provider, MAX(datestamp) AS lastDate, rownum AS rn FROM table GROUP BY provider ORDER BY MAX(datestamp) DESC ) a WHERE a.rn 8 Which Oracle version are you using? Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
Are you sure you are doing the sub-select (SELECT * FROM (SELECT ...) ? I do this all the time and it works perfectly. If you only have one select, you get the result you described. Pascal -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: woensdag 26 mei 2004 19:40 To: CF-Talk Subject: RE: PL/SQL stumper This doesn't quite do it.The problem is that the recordset from which it gets the first 7 (by rownum) is alphabetical. So the 7 records are the first 7 of the alphabetically sorted group, not the first 7 by date (which could then be sorted alpha using QofQ).I don't know if I'm explaining this clearly--but does that make sense? Thanks again, Chris -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 1:25 PM To: CF-Talk Subject: Re: PL/SQL stumper Lofback, Chris wrote: This throws an error: ORA-00937: not a single-group group function Forgot the GROUP BY: SELECT * FROM ( SELECT provider, MAX(datestamp) AS lastDate FROM table GROUP BY provider ORDER BY MAX(datestamp) DESC ) a WHERE rownum 8 Jochem _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
Which Oracle version are you using? Oracle8i And I am working on providing a better explanation of the query results.Stay tuned... Thanks, Chris Lofback Web Administrator Ceridian Benefits Services 3201 34th Street S. St. Petersburg, FL 33711 727-395-8881 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
All right, I guess I'll need to provide actual database info to figure this out.Here's part of Jochem's suggestion, with mods to fit the actual schema (and to fix a grouping error): SELECT d.provider, MAX(m.date_submitted) AS lastDate, rownum AS rn FROM fsa_detail d, fsa_master m WHERE d.masterid = m.id AND m.ssn = '123-45-6789' GROUP BY d.provider, rownum ORDER BY MAX(m.date_submitted) DESC The above query returns this recordset: PROVIDER LASTDATERN Dr. Milton25-MAY-04 15 Dr. Uptagraff25-MAY-04 13 Duncan Health Care25-MAY-04 28 Eckerd Drugs25-MAY-04 27 Hillman's Pharmacy25-MAY-04 22 Home Depot25-MAY-04 10 Publix25-MAY-04 24 Publix Pharmacy25-MAY-04 14 Timmons Drugs25-MAY-04 8 Xylophone Inc25-MAY-04 11 Williams and Assoc.25-MAY-04 17 Wellness Center25-MAY-04 12 Walmart25-MAY-04 26 Walgreens25-MAY-04 25 Walgreen's25-MAY-04 18 Timmons Drugs25-MAY-04 16 Smith's Health Supplies 25-MAY-04 21 Publix25-MAY-04 20 Hush Puppy Shoes25-MAY-04 23 Mease Clinic25-MAY-04 19 Publix Pharmacy24-MAY-04 9 Walmart24-MAY-04 7 Treebeard14-MAY-04 6 Publix14-MAY-04 4 Walmart14-MAY-04 5 Publix14-MAY-04 2 Treebeard14-MAY-04 1 Walmart14-MAY-04 3 What I need is the first 7 (or any arbitrary number) DISTINCT providers from this recordset (ie, the 7 most recent providers) in alpha order.All my solutions so far required grouping, which put the providers in alpha order first and then I pulled the first 7.How can I get the 7 first and THEN sort by alpha? Thanks for your patience and help! Chris [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: PL/SQL stumper
SELECT * FROM ( SELECT d.provider, MAX(m.date_submitted) AS lastDate FROM fsa_detail d, fsa_master m WHERE d.masterid = m.id AND m.ssn = '123-45-6789' GROUP BY d.provider ORDER BY MAX(m.date_submitted) DESC ) WHERE ROWNUM = 7 ORDER BY provider DON'T use rownum in your inner query! -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: woensdag 26 mei 2004 21:13 To: CF-Talk Subject: RE: PL/SQL stumper All right, I guess I'll need to provide actual database info to figure this out.Here's part of Jochem's suggestion, with mods to fit the actual schema (and to fix a grouping error): SELECT d.provider, MAX(m.date_submitted) AS lastDate, rownum AS rn FROM fsa_detail d, fsa_master m WHERE d.masterid = m.id AND m.ssn = '123-45-6789' GROUP BY d.provider, rownum ORDER BY MAX(m.date_submitted) DESC The above query returns this recordset: PROVIDER LASTDATERN Dr. Milton25-MAY-04 15 Dr. Uptagraff25-MAY-04 13 Duncan Health Care25-MAY-04 28 Eckerd Drugs25-MAY-04 27 Hillman's Pharmacy25-MAY-04 22 Home Depot25-MAY-04 10 Publix25-MAY-04 24 Publix Pharmacy25-MAY-04 14 Timmons Drugs25-MAY-04 8 Xylophone Inc25-MAY-04 11 Williams and Assoc.25-MAY-04 17 Wellness Center25-MAY-04 12 Walmart25-MAY-04 26 Walgreens25-MAY-04 25 Walgreen's25-MAY-04 18 Timmons Drugs25-MAY-04 16 Smith's Health Supplies 25-MAY-04 21 Publix25-MAY-04 20 Hush Puppy Shoes25-MAY-04 23 Mease Clinic25-MAY-04 19 Publix Pharmacy24-MAY-04 9 Walmart24-MAY-04 7 Treebeard14-MAY-04 6 Publix14-MAY-04 4 Walmart14-MAY-04 5 Publix14-MAY-04 2 Treebeard14-MAY-04 1 Walmart14-MAY-04 3 What I need is the first 7 (or any arbitrary number) DISTINCT providers from this recordset (ie, the 7 most recent providers) in alpha order.All my solutions so far required grouping, which put the providers in alpha order first and then I pulled the first 7.How can I get the 7 first and THEN sort by alpha? Thanks for your patience and help! Chris [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: PL/SQL stumper
select provider, lastDate from (SELECT d.provider, MAX(m.date_submitted) AS lastDate FROM fsa_detail d, fsa_master m WHERE d.masterid = m.id AND m.ssn = '123-45-6789' GROUP BY d.provider) where rownum = 7 ORDER BY lastDate desc, provider [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
executing a PL/SQL Block
Is there some special requirement to executing a PL/SQL block of code in CFMX?I had a block that ran in CF 5 fine.It looked like this:
cfquery name=finalizecycle datasource=#request.datasource#
declare
jobno BINARY_INTEGER;
BEGIN
DBMS_JOB.SUBMIT(jobno,'commcycles.FinalizeCycle(''cfoutput#cc#/cfoutput'',to_date(''cfoutput#cd#/cfoutput'',''MM/DD/ HH12:MI:SS PM''),cfoutput#request.logemplid#/cfoutput);',sysdate);
Commit;
END;
cfquery
The error we get now is a PL/SQL error, which means it is at least executing the query block, but somehow differently.
Error Executing Database Query. [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 8: PLS-00103: Encountered the symbol when expecting one of the following: begin function package pragma procedure subtype type use cursor form current
The error occurred on line 55.
**
The information contained in this message, including attachments, may contain
privileged or confidential information that is intended to be delivered only to the
person identified above. If you are not the intended recipient, or the person
responsible for delivering this message to the intended recipient, ALLTEL requests
that you immediately notify the sender and asks that you do not read the message or its
attachments, and that you delete them without copying or sending them to anyone else.
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: executing a PL/SQL Block
The oracle jdbc driver that coldfusion mx uses does not allow multiple
statments in a single query.Bassically if you need a semi-colon then it
won't work.The only real alternative (aside from acquiring a suitable
third party driver) is to encapsulate the functionality in functions and
stored procedures and then call those.
Tyler Clendenin
GSL Solutions
_
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 20, 2004 11:01 AM
To: CF-Talk
Subject: executing a PL/SQL Block
Is there some special requirement to executing a PL/SQL block of code in
CFMX?I had a block that ran in CF 5 fine.It looked like this:
cfquery name=finalizecycle datasource=#request.datasource#
declare
jobno BINARY_INTEGER;
BEGIN
DBMS_JOB.SUBMIT(jobno,'commcycles.FinalizeCycle(''cfoutput#cc#/cfoutput'
',to_date(''cfoutput#cd#/cfoutput'',''MM/DD/ HH12:MI:SS
PM''),cfoutput#request.logemplid#/cfoutput);',sysdate);
Commit;
END;
cfquery
The error we get now is a PL/SQL error, which means it is at least executing
the query block, but somehow differently.
Error Executing Database Query. [Macromedia][Oracle JDBC
Driver][Oracle]ORA-06550: line 1, column 8: PLS-00103: Encountered the
symbol when expecting one of the following: begin function package pragma
procedure subtype type use cursor form current
The error occurred on line 55.
**
The information contained in this message, including attachments, may
contain
privileged or confidential information that is intended to be delivered only
to the
person identified above. If you are not the intended recipient, or the
person
responsible for delivering this message to the intended recipient, ALLTEL
requests
that you immediately notify the sender and asks that you do not read the
message or its
attachments, and that you delete them without copying or sending them to
anyone else.
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: executing a PL/SQL Block
BEEEautiful. Thanks Tyler.
-Original Message-
From: Tyler Clendenin [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 20, 2004 10:15 AM
To: CF-Talk
Subject: RE: executing a PL/SQL Block
The oracle jdbc driver that coldfusion mx uses does not allow multiple
statments in a single query.Bassically if you need a semi-colon then it
won't work.The only real alternative (aside from acquiring a suitable
third party driver) is to encapsulate the functionality in functions and
stored procedures and then call those.
Tyler Clendenin
GSL Solutions
_
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 20, 2004 11:01 AM
To: CF-Talk
Subject: executing a PL/SQL Block
Is there some special requirement to executing a PL/SQL block of code in
CFMX?I had a block that ran in CF 5 fine.It looked like this:
cfquery name=finalizecycle datasource=#request.datasource#
declare
jobno BINARY_INTEGER;
BEGIN
DBMS_JOB.SUBMIT(jobno,'commcycles.FinalizeCycle(''cfoutput#cc#/cfoutput'
',to_date(''cfoutput#cd#/cfoutput'',''MM/DD/ HH12:MI:SS
PM''),cfoutput#request.logemplid#/cfoutput);',sysdate);
Commit;
END;
cfquery
The error we get now is a PL/SQL error, which means it is at least executing
the query block, but somehow differently.
Error Executing Database Query. [Macromedia][Oracle JDBC
Driver][Oracle]ORA-06550: line 1, column 8: PLS-00103: Encountered the
symbol when expecting one of the following: begin function package pragma
procedure subtype type use cursor form current
The error occurred on line 55.
**
The information contained in this message, including attachments, may
contain
privileged or confidential information that is intended to be delivered only
to the
person identified above. If you are not the intended recipient, or the
person
responsible for delivering this message to the intended recipient, ALLTEL
requests
that you immediately notify the sender and asks that you do not read the
message or its
attachments, and that you delete them without copying or sending them to
anyone else.
_
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Call PL/SQL function from ColdFusion 5?
Is it possible to call a PL/SQL function (not a procedure) using CFSTOREDPROC from CF5?If so, what is the syntax? Thanks, Matt [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Call PL/SQL function from ColdFusion 5?
Matt, if your function returns a string value, you can do something like this: cfquery.. select myFunction(arg) from dual /cfquery Nick Han [EMAIL PROTECTED] 03/03/04 01:52PM Is it possible to call a PL/SQL function (not a procedure) using CFSTOREDPROC from CF5?If so, what is the syntax? Thanks, Matt [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Call PL/SQL function from ColdFusion 5?
-Original Message- From: Nick Han [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 5:00 PM To: CF-Talk Subject: Re: Call PL/SQL function from ColdFusion 5? Matt, if your function returns a string value, you can do something like this: cfquery.. select myFunction(arg) from dual /cfquery Interesting.Unfortunately it has four out parameters that I need to pick up too.For this particular problem, I wrote a procedure to wrap around the function.I'm just curious now if there is any way to get at one from CF. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Call PL/SQL function from ColdFusion 5?
Yeah.I found this out by accident and it is very useful.I have a situation like cfquery.. select myFunction(client_id) from dual /cfquery >From this query I get client's name and address city.I let the database server do most of the work, concatentations, decode(), switch ..whatever. Nick Han [EMAIL PROTECTED] 03/03/04 02:13PM -Original Message- From: Nick Han [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 5:00 PM To: CF-Talk Subject: Re: Call PL/SQL function from ColdFusion 5? Matt, if your function returns a string value, you can do something like this: cfquery.. select myFunction(arg) from dual /cfquery Interesting.Unfortunately it has four out parameters that I need to pick up too.For this particular problem, I wrote a procedure to wrap around the function.I'm just curious now if there is any way to get at one from CF. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
OT: WDDX and PL/SQL
Has anyone worked with WDDX and PL/SQL? I am working with the Oracle XML Developer's Kit for 8i and am getting nowhere fast trying to use xpath to pull data from WDDX packets. I have tested the packages I built using 'simple' xml documents and can return results. But when I supply a wddx packet, everything goes wrong. Help? M ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL vs PL/SQL
If you're just converting queries into Oracle stored procedures, I think it would be fair to estimate a weekend to a week to get up to speed (depending on your experience with SQL and the time you put in to it). But to get good at what you're doing will take a longer... I'd just like to point out that, while closely related, PL/SQL and SQL are two very different languages. PL/SQL is more of a regular procedural programming language and the things you do with it are different than the things you do with straight SQL. PL/SQL is used more for the things you do with CF, Java or any other regular programming language. PL/SQL was just designed for a particular niche, the Oracle database, so it has db specific features, too. SQL is a (fairly) standard way to interact with databases and is more of a functional language. When you write PL/SQL you'll use SQL. I you can download Oracle from their web site (or at least you used to be able to) and play around with it. That and the docs should be a good start... Jeff Polaski The cow is of the bovine ilk; One end is moo, the other, milk. -- Ogden Nash -Original Message- From: Casey C Cook [mailto:ccook22;csc.com] Sent: Thursday, November 14, 2002 8:34 AM To: CF-Talk Subject: OT: SQL vs PL/SQL If someone came to you and said we need someone to work with Oracle and PL/SQL to write some queries, but all you knew was SQL, how much of PL/SQL would you say you already knew, knowing there are some syntax differences between the two query writing languages? Im just trying to gauge how much work I will have to do to to pick up PL/SQL, a weekenda week...a month? Thanks, Casey Cook ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
OT: SQL vs PL/SQL
If someone came to you and said we need someone to work with Oracle and PL/SQL to write some queries, but all you knew was SQL, how much of PL/SQL would you say you already knew, knowing there are some syntax differences between the two query writing languages? Im just trying to gauge how much work I will have to do to to pick up PL/SQL, a weekenda week...a month? Thanks, Casey Cook ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: SQL vs PL/SQL
Do not underestimate it! - I've been using T-SQL for 5 years and know it pretty well. Got a job which was an oracle back end and tried to build the equivalent in Oracle and had a nightmare - however if you are just using SQL code in your CF using CFQueries then there wouldn't be much prob and you should be able to pick up the syntax differences as you go along -Original Message- From: Casey C Cook [mailto:ccook22;csc.com] Sent: 14 November 2002 16:34 To: CF-Talk Subject: OT: SQL vs PL/SQL If someone came to you and said we need someone to work with Oracle and PL/SQL to write some queries, but all you knew was SQL, how much of PL/SQL would you say you already knew, knowing there are some syntax differences between the two query writing languages? Im just trying to gauge how much work I will have to do to to pick up PL/SQL, a weekenda week...a month? Thanks, Casey Cook ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: OT: SQL vs PL/SQL
Casey C Cook wrote: If someone came to you and said we need someone to work with Oracle and PL/SQL to write some queries, but all you knew was SQL, how much of PL/SQL would you say you already knew, knowing there are some syntax differences between the two query writing languages? Im just trying to gauge how much work I will have to do to to pick up PL/SQL, a weekenda week...a month? I would ask if he was sure the requirement was to write queries in PL/SQL and not writing procedures in PL/SQL. If the objective is to convert some existing queries to procedures and then granting only execute rights for procedures and not to use pass-through queries anymore it takes about an hour to learn it. But if you need to write complex procedures it is going to take a long time. Don't forget that PL/SQL is a procedural language, it is not some SQL dialect. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: SQL vs PL/SQL
If it is SQL in ColdFusion, don't worry. An understanding of SELECT, INSERT, UPDATE, etc. will serve you well. If you have to program a complex report mining data out of Oracle and published via the PL/SQL web toolkit over the WWW, may Fate smile upon you. Your best bet is to search for answers to questions you might have, and go to Oracle's web site for some docs. on PL/SQL reference. They're big and (*gasp*) dense, but they'll provide you with more information than you ever wanted. I would say, good rule of thumb, as complexity of said project increases, so does the learning curve. My 2¢, -lincoln -Original Message- From: Casey C Cook [mailto:ccook22;csc.com] Sent: Thursday, November 14, 2002 11:34 AM To: CF-Talk Subject: OT: SQL vs PL/SQL If someone came to you and said we need someone to work with Oracle and PL/SQL to write some queries, but all you knew was SQL, how much of PL/SQL would you say you already knew, knowing there are some syntax differences between the two query writing languages? Im just trying to gauge how much work I will have to do to to pick up PL/SQL, a weekenda week...a month? Thanks, Casey Cook ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: SQL vs PL/SQL
Also, there's a gigantic heavy book that not only impresses people walking past your cubicle (and has its own gravitational field) but also is one of the most indespensable books in my programming reference library. Oracle 8i: The Complete Reference by Loney and Koch http://www.amazon.com/exec/obidos/ASIN/0072123648/qid=1037295710/sr=2-2/ref= sr_2_2/104-3361319-6501550 YMMV, but I pull it out whenever I have a PL/SQL or vanilla SQL problem. Hasn't failed me yet (if the organization is a little scattershot in places). - Jim -Original Message- From: Lincoln Milner [mailto:lmilner;hes.hmc.psu.edu] Sent: Thursday, November 14, 2002 10:45 AM To: CF-Talk Subject: RE: SQL vs PL/SQL If it is SQL in ColdFusion, don't worry. An understanding of SELECT, INSERT, UPDATE, etc. will serve you well. If you have to program a complex report mining data out of Oracle and published via the PL/SQL web toolkit over the WWW, may Fate smile upon you. Your best bet is to search for answers to questions you might have, and go to Oracle's web site for some docs. on PL/SQL reference. They're big and (*gasp*) dense, but they'll provide you with more information than you ever wanted. I would say, good rule of thumb, as complexity of said project increases, so does the learning curve. My 2¢, -lincoln -Original Message- From: Casey C Cook [mailto:ccook22;csc.com] Sent: Thursday, November 14, 2002 11:34 AM To: CF-Talk Subject: OT: SQL vs PL/SQL If someone came to you and said we need someone to work with Oracle and PL/SQL to write some queries, but all you knew was SQL, how much of PL/SQL would you say you already knew, knowing there are some syntax differences between the two query writing languages? Im just trying to gauge how much work I will have to do to to pick up PL/SQL, a weekenda week...a month? Thanks, Casey Cook ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: SQL vs PL/SQL
Here's a small tip. I moved to PL/SQL from plain vanilla SQL (basically using MS Access) and I quickly found there are two critical PL/SQL functions that you must know: NVL() and DECODE() Make sure you learn 'em and use 'em. Chris Lofback Sr. Web Developer TRX Integration 28051 US 19 N., Ste. C Clearwater, FL 33761 www.trxi.com -Original Message- From: Casey C Cook [mailto:ccook22;csc.com] Sent: Thursday, November 14, 2002 11:34 AM To: CF-Talk Subject: OT: SQL vs PL/SQL If someone came to you and said we need someone to work with Oracle and PL/SQL to write some queries, but all you knew was SQL, how much of PL/SQL would you say you already knew, knowing there are some syntax differences between the two query writing languages? Im just trying to gauge how much work I will have to do to to pick up PL/SQL, a weekenda week...a month? Thanks, Casey Cook ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: SQL vs PL/SQL
Actually, depending on what version of Oracle you're using, you might not need decode() anymore. Case statements are available in pl/sql as of 9i, which effectively replace decode(). Also, I would say that the book recomended is geared more toward's oracle SQL than PL/SQL, though the same publisher has an Oracle 8 PL/SQL Programming book. Like others have said, what you will need to do really determines how difficult it is to learn. Basic syntax in PL/SQL is similar to SQL, but it does go way beyond. I pretty much feel like I could do Oracle stuff everyday all day and still not know it all. -d Deanna Schneider Interactive Media Developer [EMAIL PROTECTED] ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
transact(ms sql) VS PL SQL(Oracle)
Does anyone have a good article or experiences on the differences between ms sql and oracle databases... thanks, --- phumes1 [EMAIL PROTECTED] wrote: I don't know whats happening... The contents of my output.txt file still contains the following. The output to the screen (console) is totally different and this is what I want dumped into the output file. The executable is a compiled Perl script. Does anyone know how to dump the output of the perl script to a file? C:\tempsetlocal C:\tempc: C:\tempcd c:\temp c:\tempc:\program\runme.exe c:\temp\filename printer At 11:19 AM 6/7/2002 +0100, you wrote: C:\temptest.bat output.txt I'd suggest putting a location before the output.txt, something like Test.bat c:\output.txt I'm not sure where CFEXECUTE calls the file from... Philip Arnold Technical Director Certified ColdFusion Developer ASP Multimedia Limited Switchboard: +44 (0)20 8680 8099 Fax: +44 (0)20 8686 7911 www.aspmedia.co.uk www.aspevents.net An ISO9001 registered company. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** -Original Message- From: phumes1 [mailto:[EMAIL PROTECTED]] Sent: 06 June 2002 18:27 To: CF-Talk Subject: cfexecute Hi, This is what I put in my batch file: test.bat setlocal c: cd C:\temp c:\tempc:\program\runme.exe c:\temp\filename printer Then from the command prompt I did: C:\temptest.bat output.txt The programs run just fine. The program displays the processing to the screen. When I open the file output.txt this is what it contains: C:\tempsetlocal C:\tempc: C:\tempcd c:\temp c:\tempc:\program\runme.exe c:\temp\filename printer I don't know whats going on. The output.txt file should contain the output thats dumped to the screen but it doesn't. The executable file is a Perl script which was compiled to a .exe file. Could there be something in the Perl script thats stopping the output to a file? Its works to the screen so I can't figure out whats happening. Any ideas? The bat file seem to be working exactly as you tell them to. In the instance below, when you are calling the exe directly, you are rerouting the screen output to the p.log file. It is routing the ouput of the runme command to the log file. In the CFExecute example you posted earlier, you are routing the output from the BAT file to the log file. Not quite the same thing. Try changing the call in the BAT file to match your example below, including the redirected output stream. (and what are the command switches -e /q /v doing for you in the CFExecute example? Is /q quiet mode, which supresses all bat file output?) Now you've got me curious Jerry Johnson [EMAIL PROTECTED] 06/05/02 02:27PM Why is my output file empty? The filename is passed to the executable for processing but shen I specify the output to be redirected to a file its empty. Why? Run from command prompt: c:\tempc:\program\runme.exe c:\temp\filename printer p.log +- --+ Philip Humeniuk [EMAIL PROTECTED] [EMAIL PROTECTED] +- ---+ __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: transact(ms sql) VS PL SQL(Oracle)
What do you want to know? +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis -Original Message- From: Amanda Stern [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 11, 2002 1:52 PM To: CF-Talk Subject: transact(ms sql) VS PL SQL(Oracle) Does anyone have a good article or experiences on the differences between ms sql and oracle databases... thanks, --- phumes1 [EMAIL PROTECTED] wrote: I don't know whats happening... The contents of my output.txt file still contains the following. The output to the screen (console) is totally different and this is what I want dumped into the output file. The executable is a compiled Perl script. Does anyone know how to dump the output of the perl script to a file? C:\tempsetlocal C:\tempc: C:\tempcd c:\temp c:\tempc:\program\runme.exe c:\temp\filename printer At 11:19 AM 6/7/2002 +0100, you wrote: C:\temptest.bat output.txt I'd suggest putting a location before the output.txt, something like Test.bat c:\output.txt I'm not sure where CFEXECUTE calls the file from... Philip Arnold Technical Director Certified ColdFusion Developer ASP Multimedia Limited Switchboard: +44 (0)20 8680 8099 Fax: +44 (0)20 8686 7911 www.aspmedia.co.uk www.aspevents.net An ISO9001 registered company. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** -Original Message- From: phumes1 [mailto:[EMAIL PROTECTED]] Sent: 06 June 2002 18:27 To: CF-Talk Subject: cfexecute Hi, This is what I put in my batch file: test.bat setlocal c: cd C:\temp c:\tempc:\program\runme.exe c:\temp\filename printer Then from the command prompt I did: C:\temptest.bat output.txt The programs run just fine. The program displays the processing to the screen. When I open the file output.txt this is what it contains: C:\tempsetlocal C:\tempc: C:\tempcd c:\temp c:\tempc:\program\runme.exe c:\temp\filename printer I don't know whats going on. The output.txt file should contain the output thats dumped to the screen but it doesn't. The executable file is a Perl script which was compiled to a .exe file. Could there be something in the Perl script thats stopping the output to a file? Its works to the screen so I can't figure out whats happening. Any ideas? The bat file seem to be working exactly as you tell them to. In the instance below, when you are calling the exe directly, you are rerouting the screen output to the p.log file. It is routing the ouput of the runme command to the log file. In the CFExecute example you posted earlier, you are routing the output from the BAT file to the log file. Not quite the same thing. Try changing the call in the BAT file to match your example below, including the redirected output stream. (and what are the command switches -e /q /v doing for you in the CFExecute example? Is /q quiet mode, which supresses all bat file output?) Now you've got me curious Jerry Johnson [EMAIL PROTECTED] 06/05/02 02:27PM Why is my output file empty? The filename is passed to the executable for processing but shen I specify the output to be redirected to a file its empty. Why? Run from command prompt: c:\tempc:\program\runme.exe c:\temp\filename printer p.log +- --+ Philip Humeniuk [EMAIL PROTECTED] [EMAIL PROTECTED] +- ---+ __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Problem calling Oracle stored procedures (PL/SQL functions) by using CFSTOREDPROC
Hello folks, I'm trying to call an Oracle stored procedure (a PL/SQL function) from ColdFusion using CFSTOREDPROC and have been unable to do so. When I call the function, I get the following error: Oracle Error Code = 6550 ORA-06550: line 1, column 7: PLS-00221: 'FSELECTSTATE' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored SQL = TestBed.fSelectState I have created a procedure similar to the function, saving that it does not return a value to the caller. Using that procedure with CFSTOREDPROC succeeds. Questions: 1) Does ColdFusion not support accessing Oracle stored *functions* using CFSTOREDPROC, only *procedures*? (I normally think of PL/SQL functions as a subset of Oracle stored procedures, which is why I'm asking about the distinction.) If it does, does anyone have any suggestions as to what I might be doing incorrectly? If it does not, then on to question #2: 2) I know that I can access Oracle stored functions using CFQUERY (because I've done it before), when they (a) take one argument and (b) return a value. However, the function in question returns a reference cursor as well as a function value, so does anyone have an idea as to the syntax required to access a reference cursor returned by a function using the CFQUERY tag, if it can be done at all (my own attempts having miserably failed)? I've appended the package spec code that I'm using as well as the CFSTOREDPROC statements for them below. The system we're using for ColdFusion is an NT 4.0 Server running CF 4.5.1 SP2, with the native Oracle 8i driver. Thanks again in advance, and Regards, Joel Parramore P.S. --- In the ColdFusion 4.0 Web Application Construction Kit, by Ben Forta, he states (page 474 of the third edition) that Oracle stored functions are not supported by the CFSTOREDPROC tag. He also states that you can't use the CFPROCRESULT tag with Oracle, either, However, I know that you can use CFPROCRESULT in CF 4.5 and above, so, presumably, these are limitations of 4.0. If someone from Allaire/Macromedia would care to clarify the situation vis-a-vis CFSTOREDPROC and Oracle stored functions, that'd be great. = PL/SQL spec code: CREATE OR REPLACE PACKAGE TestBed AS TYPE GenericCursorType IS REF CURSOR; PROCEDURE SelectStates (inStateAbbrev IN VARCHAR2, qSelectStatesList OUT GenericCursorType); PROCEDURE SelectState (inStateAbbrev IN VARCHAR2, outStateName OUT VARCHAR2); FUNCTION fSelectState (inStateAbbrev IN VARCHAR2, outStateName OUT VARCHAR2) RETURN VARCHAR2; FUNCTION fSelectStates (inStateAbbrev IN VARCHAR2, qSelectStatesList OUT GenericCursorType) RETURN VARCHAR2; END TestBed; / Example 1: No reference cursor returned !--- Select Abreviations, States --- CFSET StateAbbrev = FL *** SUCCEEDS *** CFSTOREDPROC PROCEDURE=TestBed.SelectState DATASOURCE=HomesCom RETURNCODE=No CFPROCPARAM TYPE=In CFSQLTYPE=CF_SQL_VARCHAR DBVARNAME=inStateAbbrev VALUE=#StateAbbrev# NULL=No CFPROCPARAM TYPE=Out CFSQLTYPE=CF_SQL_VARCHAR DBVARNAME=outStateName VARIABLE=CF_StateName NULL=No /CFSTOREDPROC *** FAILS *** CFSTOREDPROC PROCEDURE=TestBed.fSelectState DATASOURCE=HomesCom DEBUG=Yes RETURNCODE=No CFPROCPARAM TYPE=In CFSQLTYPE=CF_SQL_VARCHAR DBVARNAME=inStateAbbrev VALUE=#StateAbbrev# NULL=No CFPROCPARAM TYPE=Out CFSQLTYPE=CF_SQL_VARCHAR DBVARNAME=outStateName VARIABLE=CF_StateName NULL=No /CFSTOREDPROC Example 2: Reference cursor returned *** SUCCEEDS *** CFSTOREDPROC PROCEDURE=TestBed.SelectStates DATASOURCE=HomesCom RETURNCODE=No CFPROCPARAM TYPE=In CFSQLTYPE=CF_SQL_VARCHAR DBVARNAME=inStateAbbrev VALUE=#StateAbbrev# NULL=No CFPROCPARAM TYPE=Out CFSQLTYPE=CF_SQL_REFCURSOR DBVARNAME=qSelectStatesList VARIABLE=CF_qSelectStatesList NULL=No CFPROCRESULT NAME=qSelectStatesList /CFSTOREDPROC *** FAILS *** CFSTOREDPROC PROCEDURE=TestBed.fSelectStates DATASOURCE=HomesCom RETURNCODE=No CFPROCPARAM TYPE=In CFSQLTYPE=CF_SQL_VARCHAR DBVARNAME=inStateAbbrev VALUE=#StateAbbrev# NULL=No CFPROCPARAM TYPE=Out CFSQLTYPE=CF_SQL_REFCURSOR DBVARNAME=qSelectStatesList VARIABLE=CF_qSelectStatesList NULL=No CFPROCRESULT NAME=qSelectStatesList /CFSTOREDPROC ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
help returning record sets from PL/SQL
Can Anyone give me guidance on how to return record sets (ideally multiple) from PL/SQL using CFSTOREDPROC. I've got no problem using CFSTOREDPROC, it's the PL/SQL syntax I haven't found documentation on. It's real simple in SQL Server, but I haven't been able to do it in Oracle. Any help is appreciated. Thanks Runar ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: help returning record sets from PL/SQL
Runar, Here's a message on this from Allaire's Damon Cooper. -David - Forwarded message -- From: Damon Cooper [EMAIL PROTECTED] To: "'[EMAIL PROTECTED]'" [EMAIL PROTECTED] Date: Thu, 3 Feb 2000 16:49:07 -0500 Subject: CF 4.5 Supports Oracle Reference Cursors Message-ID: [EMAIL PROTECTED] Just in case the guys looking for Oracle stored proc result set support haven't seen this in the 4.5 docs: ( From http://127.0.0.1/CFDOCS/Allaire_Support/new.htm#curse ) This release supports the return of result sets via Oracle Reference Cursors through the Oracle 8 native driver and the ColdFusion stored procedure interface. The example below shows both the PL/SQL necessary for creating a procedure to return a result set and the CFML necessary for executing the procedure in ColdFusion. The example uses the Oracle SCOTT/TIGER login sample data. The final ColdFusion documentation for this release will contain additional information and examples. Example: Return of all rows in the DEPT table entirely through a package. PL/SQL CREATE OR REPLACE PACKAGE dept_data AS TYPE DeptTyp IS REF CURSOR RETURN dept%ROWTYPE; PROCEDURE refcurproc(pParam1 in out DeptTyp); END dept_data; CREATE OR REPLACE PACKAGE BODY dept_data AS PROCEDURE refcurproc(pParam1 in out deptTyp) IS BEGIN OPEN pParam1 FOR select * from dept; END refcurproc; end dept_data; CFML cfstoredproc procedure="dept_data.refcurproc" datasource="my_oracle80_test" username = scott password = tiger RETURNCODE="no" cfprocparam type="Out" cfsqltype="CF_SQL_REFCURSOR" variable="param1" cfprocresult name = rs1 /cfstoredproc bThe first result set:/bbr hr cfoutput query = rs1 #dname#,#deptno# /cfoutput On Mon, 11 Dec 2000 20:11:18 -0800 "Runar Petursson" [EMAIL PROTECTED] writes: Can Anyone give me guidance on how to return record sets (ideally multiple) from PL/SQL using CFSTOREDPROC. I've got no problem using CFSTOREDPROC, it's the PL/SQL syntax I haven't found documentation on. It's real simple in SQL Server, but I haven't been able to do it in Oracle. Any help is appreciated. Thanks Runar ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: PL/SQL
Sorry can't help you there. Jeff Britts wrote: Basically, we're working on a tool to generate the pl/sql script on the fly so we cant use a stored proc. Any ideas? -Original Message- From: C Frederic Valone [mailto:[EMAIL PROTECTED]] Sent: Friday, December 08, 2000 9:54 AM To: CF-Talk Subject: Re: PL/SQL We use cf and pl/sql stored procs extensively. What exactly are you trying to do? Jeff Britts wrote: Anyone have any luck running PL/SQL through ColdFusion? The simple stuff seems to work ok, but if you throw it something complex it chokes. Jeff Britts ColdFusion Engineer e-Dialog ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: PL/SQL
We use cf and pl/sql stored procs extensively. What exactly are you trying to do? Jeff Britts wrote: Anyone have any luck running PL/SQL through ColdFusion? The simple stuff seems to work ok, but if you throw it something complex it chokes. Jeff Britts ColdFusion Engineer e-Dialog ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: PL/SQL
Basically, we're working on a tool to generate the pl/sql script on the fly so we cant use a stored proc. Any ideas? -Original Message- From: C Frederic Valone [mailto:[EMAIL PROTECTED]] Sent: Friday, December 08, 2000 9:54 AM To: CF-Talk Subject: Re: PL/SQL We use cf and pl/sql stored procs extensively. What exactly are you trying to do? Jeff Britts wrote: Anyone have any luck running PL/SQL through ColdFusion? The simple stuff seems to work ok, but if you throw it something complex it chokes. Jeff Britts ColdFusion Engineer e-Dialog ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: PL/SQL
Can you pass the PL/SQL to a stored procedure and use the dbms_sql package to execute it? I know I've executed simple SQL statements - like truncate which you can't do right in PL/SQL for some reason - but I don't know how complex you can do with dbms_sql. Dan -Original Message- From: Jeff Britts [mailto:[EMAIL PROTECTED]] Sent: Friday, December 08, 2000 8:39 AM To: CF-Talk Subject: RE: PL/SQL Basically, we're working on a tool to generate the pl/sql script on the fly so we cant use a stored proc. Any ideas? -Original Message- From: C Frederic Valone [mailto:[EMAIL PROTECTED]] Sent: Friday, December 08, 2000 9:54 AM To: CF-Talk Subject: Re: PL/SQL We use cf and pl/sql stored procs extensively. What exactly are you trying to do? Jeff Britts wrote: Anyone have any luck running PL/SQL through ColdFusion? The simple stuff seems to work ok, but if you throw it something complex it chokes. Jeff Britts ColdFusion Engineer e-Dialog ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
