package variable

package variable

Post by bori » Sun, 31 Dec 1899 09:00:00

Hi everybody.
In the where clause I compare column with the function in package that
return the package variable. What I need to do that Oracle call this
function once (not for all records)?
thanks a lot.

package variable

Post by Thomas Kyt » Sun, 31 Dec 1899 09:00:00

(if that email address didn't require changing)

>Hi everybody.
>In the where clause I compare column with the function in package that
>return the package variable. What I need to do that Oracle call this
>function once (not for all records)?
>thanks a lot.

where column_name = ( select pkg.function_name from dual )

usually does it.  Another variation on that them that typically works is:

select * from t, ( select pkg.function_name PKG_VALUE from dual )
 where t.column_name = pkg_value

(join to the function instead of comparing to it).

In Oracle8i, release 8.1 there are "deterministic" functions (see for example usage) as well.

Also, in Oracle8i, release 8.1 they've added a feature called application
contexts.  See for example uses
of that.  Basically, with an application context -- I can put values into a
named context and then use them in queries.  for example:

select * from t
where column_name = sys_context( 'Name_Of_My_context',
                                 'Variable_I_set_in_context' );

that query will be rewritten in effect as:

select * from t where column_name = :bind_variable

and the sys_context function will be evaluated once per query, not once per row.


Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation


1. how to reset DTS package variables?

i got my DTS Package working. i did this in my vb code.

oPKG.LoadFromSQLServer "TBSSDB", "sa", "", DTSSQLStgFlag_Default, , , ,
"Upload TBSS Budget"
oPKG.GlobalVariables("sFileName").Value = Trim(txtFileName.Text)
oPKG.GlobalVariables("sTableName").Value = Trim(xlsString)
oPKG.GlobalVariables("sHeaderName").Value = Trim(HeaderString)

the sFileName, sTableName, sHeaderName doesn't change
in my dts package properties. how do i reset these values
as i need the package to work for different text files?

i tried
oPKG.GlobalVariables("sFileName").Value = " "
oPKG.GlobalVariables("sTableName").Value = " "
oPKG.GlobalVariables("sHeaderName").Value = " "

but it didnt work.

*** Sent via Developersdex ***
Don't just participate in USENET...get rewarded for it!

2. The internal password!

3. ActiveX and DTS package variables

4. Can't set up Server Components

5. Accessing global DTS package variables from SQL code in a DTS task

6. Digital Libraries position at Pittsburgh Supercomputing Center

7. Oracle package variable equivalent in SQL Server

8. Major Whinge about Fields...

9. share PL/SQL package variable across sessions?

10. package variable

11. Global package variables and OCI revisited

12. using package variables as shared memory

13. Accessing Package Variables from Forms 4.5