Member Avatar for Subhankarde

I tried to use CF_SQL_ARRAY in the following but it is not working:

ColdFusion:

<cfset faq = ArrayNew(1)>
<cfset ArrayAppend(faq, "What are ColdFusion arrays")>
<cfset ArrayAppend(faq, "How to create a ColdFusion array?")>
<cfset ArrayAppend(faq, "What are two dimensional arrays?")>
<cfoutput>#faq[2]#</cfoutput>
<cfstoredproc procedure="p_cf_array" datasource="#Application.DSN_VAT#" username="vacdvl01" password="vacl33ds" debug="Yes">
		<cfprocparam type="In" cfsqltype="CF_SQL_ARRAY" dbvarname="array_in" value="#faq#" null="No">
	  <cfprocparam type="Out" cfsqltype="CF_SQL_ARRAY" variable="array_set" dbvarname="array_out" null="No">							
</CFSTOREDPROC>
<cfoutput>#array_set.size#</cfoutput>

Procedure:

create or replace procedure p_cf_array(array_in  in in_array, array_out out out_array) is
begin
  for i in 1 .. array_in.count
loop
-- Apply business logic here 
dbms_output.put_line( array_in(i) );
array_out.extend( array_in(i));
end loop;

end p_cf_array;
  • 2 Contributors
  • 1 Reply
  • 287 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by Johnle
Member Avatar for Johnle

Which version of Coldfusion are you using?
I use MX7 and do not use the CF_SQL_ARRAY datatype.

I use the datatype that corresponds to the datatype
of the data that is being passed to the stored procedure.

Also, I pass a Coldfusion delimited list.

See the following example:

It takes the following variables and return and Excel XSL file to the browser.

<cfif #Form.selUSStateAbbr# eq ''>
		<cfset Session.LocList = "ALL">
	<cfelse>
		<cfset Session.LocList = #Form.selUSStateAbbr#>
	</cfif>
	<cfset Session.colList = UCase(#Form.selUSData#)>
	<cfset Session.userid = "myID">

	<cfstoredproc procedure="[SchemaName.packageName.procedureName]" datasource="[mydataSource]" returncode="No">
		<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="pLocList" value=# Session.LocList# null="No">
		<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="pSelectList" value=#Session.colList# null="No">
		<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="pUserId" value=#Session.userid# null="No">
		<cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" variable="pFName" >
	</cfstoredproc>

I then manipulate the list in my stored proc to create my select statements and where clauses.

Hope this helps

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.