Обсуждение: Variable not found

On 01/20/2016 07:35 PM, Sachin Srivastava wrote:
> Dear Folks,
>
> I have a question about global variables in Oracle pl/sql package. Where
> are these variables when package is converted to schema from Oracle to
> Postgres through Ora2PG Tool?
>
>
> For example, package <SSJ4_COMPLIANCE_PKG has a global variable
> g_compliance_id. This package is converted to schema
> ssj4_compliance_pkg, but I cannot find where is the variable
> g_compliance_id. It must be somewhere because there is no error in
> functions which reference this variable. Please suggest where I will
> fount it, any idea?

Best guess it is stored in a table in the schema.

>
>
> Regards,
>
> SS
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Hi,

On 01/20/2016 07:35 PM, Sachin Srivastava wrote:

Dear Folks,

I have a question about global variables in Oracle pl/sql package. Where
are these variables when package is converted to schema from Oracle to
Postgres through Ora2PG Tool?

Ora2Pg doesn't export global variable and any other element declared outside a package body. If you want to know how thoses variables was created under Oracle you can simply ask to Ora2Pg to export the source code of the packages. You will get the entire package declaration. To proceed set the PLSQL_PGSQL configuration directive to 0 and perform a PACKAGE export into a different directory:

    ora2pg -c config/ora2pg.conf -t PACKAGE -b path/to/sources/ -o pkgsrc.sql

or if the directive is already disabled remove do not use the -p option.

For example, package <SSJ4_COMPLIANCE_PKG has a global variable
g_compliance_id. This package is converted to schema
ssj4_compliance_pkg, but I cannot find where is the variable
g_compliance_id. It must be somewhere because there is no error in
functions which reference this variable. Please suggest where I will
fount it, any idea?

Best guess it is stored in a table in the schema.

As Adrian says, there's no equivalent with PostgreSQL. You will need to use custom variable in postgresql.conf or a table to store your global information.

Best regards,

-- 
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org

On 01/21/2016 10:48 PM, Sachin Srivastava wrote:
> Dear Adrian,
>
> So, how the effective way to search this because I have around 1300 tables.

See Gilles response. From that I gather global variables are not
automatically transferred and it is up to you to decide where to put
them. Per your original post saying there where no errors in the
function, do you mean when the function was created or used?

>
> Regards,
> SS
>
> On Thu, Jan 21, 2016 at 8:48 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 01/20/2016 07:35 PM, Sachin Srivastava wrote:
>
>         Dear Folks,
>
>         I have a question about global variables in Oracle pl/sql
>         package. Where
>         are these variables when package is converted to schema from
>         Oracle to
>         Postgres through Ora2PG Tool?
>
>
>         For example, package <SSJ4_COMPLIANCE_PKG has a global variable
>         g_compliance_id. This package is converted to schema
>         ssj4_compliance_pkg, but I cannot find where is the variable
>         g_compliance_id. It must be somewhere because there is no error in
>         functions which reference this variable. Please suggest where I will
>         fount it, any idea?
>
>
>     Best guess it is stored in a table in the schema.
>
>
>
>         Regards,
>
>         SS
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com