Include File in PL/SQL?

[Database Administrator]

Hello,

I know it is possible to call other procedures and functions from PL/
SQL. Is it possible in Oracle 10g to having the equivalent of an
"include file"?

In this way you could have the functional equivalent of global
variables (at least constants) without having to opt to use a Package
at every turn.

Thanks,

Michael42

[Database Administrator]

Michael42 wrote:
> Hello,
>
> I know it is possible to call other procedures and functions from PL/
> SQL. Is it possible in Oracle 10g to having the equivalent of an
> "include file"?
>
> In this way you could have the functional equivalent of global
> variables (at least constants) without having to opt to use a Package
> at every turn.
>
> Thanks,
>
> Michael42

Why would you write code and not use packages?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Database Administrator]

>> Why would you write code and not use packages?

To take advantage of a set of constant values without having to get
them from a table. Like an .ini in Windows or a .conf file in UNIX.

Can it be done without opting to use a package?


Thanks,

Michael42

[Database Administrator]

"Michael42" wrote in news:1169945741.025067.201490
@v33g2000cwv.googlegroups.com:

>>> Why would you write code and not use packages?
>
> To take advantage of a set of constant values without having to get
> them from a table. Like an .ini in Windows or a .conf file in UNIX.
>
> Can it be done without opting to use a package?
>
>
> Thanks,
>
> Michael42
>
>

yes, but why is getting data elements from a file superior to getting the
same data from a table?

There is always UTL_FILE to read your *conf or *ini file.

[Database Administrator]

Michael42 schreef:
>>> Why would you write code and not use packages?
>
> To take advantage of a set of constant values without having to get
> them from a table. Like an .ini in Windows or a .conf file in UNIX.
>
> Can it be done without opting to use a package?
>
>
> Thanks,
>
> Michael42
>
Does using a package specification *only* count

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

[Database Administrator]

Thanks for all you replies.

There are many ways to skin this cat as you all pointed out.

* Place this type info in a table.
* Pin the table to memory if performance issue.
* Place it in a file via DIRECTORY_OBJECT or UTIL_FILE dir.

I guess what you are all implying is THE CONSTRUCT OF INCLUDE FILES IN
STANDARD PROCEDURES and FUNCTIONS CANNOT BE DONE IN ORACLE 10g. :-)

Thanks all,

Michael42

[Database Administrator]

On 28 jan, 16:28, "Michael42" wrote:
> Thanks for all you replies.
>
> There are many ways to skin this cat as you all pointed out.
>
> * Place this type info in a table.
> * Pin the table to memory if performance issue.
> * Place it in afilevia DIRECTORY_OBJECT or UTIL_FILE dir.
>
> I guess what you are all implying is THE CONSTRUCT OFINCLUDEFILES IN
> STANDARD PROCEDURES and FUNCTIONS CANNOT BE DONE IN ORACLE 10g. :-)
>

Actually, you can...

Include files are traditionally handled by a preprocessing step.
They usually do not belong to the core language itself - whether
C, C++ or PL/SQL.

In the Oracle system, they are available if you compile your code
with SQL*Plus. Use DEFINE in the header script file to define
your constants, and include the header in the main script files with
@ or @@. See "Substitution Variables" and "Nesting scripts" in
the SQL*Plus manual.

Hope it helps.

--- Raoul

[Database Administrator]

Michael42 wrote:
> Thanks for all you replies.
>
> There are many ways to skin this cat as you all pointed out.
>
> * Place this type info in a table.
> * Pin the table to memory if performance issue.
> * Place it in a file via DIRECTORY_OBJECT or UTIL_FILE dir.
>
> I guess what you are all implying is THE CONSTRUCT OF INCLUDE FILES IN
> STANDARD PROCEDURES and FUNCTIONS CANNOT BE DONE IN ORACLE 10g. :-)
>
> Thanks all,
>
> Michael42

Sure it can. But the point of my original post is that I can not think
of why you would want to write a stand-alone procedure rather than put
your procedural code into a package.

But Ok ... you want an include ... pretty simple actually:

CREATE OR REPLACE FUNCTION include_func (val1 OUT VARCHAR2, val2 OUT
DATE) RETURN BOOLEAN IS

BEGIN
val1 := 'Test';
val2 := SYSDATE+1;
RETURN TRUE;
END include_func;
/

set serveroutput on

DECLARE
myval1 VARCHAR2(20);
myval2 DATE;
x BOOLEAN;
BEGIN
x := include_func(myval1, myval2);

dbms_output.put_line(myval1);
dbms_output.put_line(myval2);
END;
/

But I can think of a single reason why I would use this construct in
place of the initialization section of a package.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[dany_yacoub]

hi i have the same question,
I want to be able to include a script exactly as it is(even if it does not compile alone),
the reason for this is i want the same application to run on oracle and postgresql
so i want to write a function

CREATE FUNCTION (...) RETURN NUMBER IS

##INCLUDE FILE BUSINESS_FILE##

and in postgres
CREATE FUNCTION (...) RETURN NUMBERIC AS
$$
##INCLUDE FILE BUSINESS_FILE##
$$
LANGAGE...

where BUSINESS_FILE is the same.

is it possible to do this?

thank you

[John Peterson]

Originally Posted by dany_yacoub

hi i have the same question,
I want to be able to include a script exactly as it is(even if it does not compile alone)

It doesn't seem like this is possible. The best I could find was the "@File.sql" command that works in SQL Plus and Oracle SQL Developer. But the code has to compile by itself to run so it can't, for example, be used in a package definition like this.

CREATE OR REPLACE PACKAGE BODY emp_actions AS
@hire_employee.sql
@update_salary.sql
END emp_actions;
/