OraFAQ Forum: SQL & PL/SQL » Can I use global variable in dynamic sql and outside of sql statment

Home » SQL & PL/SQL » SQL & PL/SQL » Can I use global variable in dynamic sql and outside of sql statment (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can I use global variable in dynamic sql and outside of sql statment [message #603123]

Wed, 11 December 2013 09:11

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

Can I use public global variable in Dynamic SQL inside of procedure and outside of SQL statement.

For example

Select * from test where scd_end_date= etl_ctl. gc_end_of_time

Report message to a moderator

Re: Can I use global variable in dynamic sql and outside of sql statment [message #603125 is a reply to message #603123]

Wed, 11 December 2013 09:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

do you realize & understand that SQL & PL/SQL are entirely different & independent languages?
what is valid in one language is not necessarily valid in the other language.
You can NOT arbitrarily mix & match features between them.

so the short answer is, "NO!"

Report message to a moderator

Re: Can I use global variable in dynamic sql and outside of sql statment [message #603132 is a reply to message #603123]

Wed, 11 December 2013 10:03

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Can I use public global variable in Dynamic SQL inside of procedure

Yes, use a global context.

Quote:

and outside of SQL statement

How a SQL-PL/SQL variable can be accessed outside them.
Can you access a C variable in a Java program outside it?

Quote:

For example

No, a package variable is private (assuming etl_ctl.gc_end_of_time is a package variable).

In the end, explain your need and issue instead of asking such questions. They are just waste of time for everyone.

Report message to a moderator

Re: Can I use global variable in dynamic sql and outside of sql statment [message #603137 is a reply to message #603132]

Wed, 11 December 2013 11:08

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Did you mean to say local context? public global variable scope is session.

SY.

Report message to a moderator

Re: Can I use global variable in dynamic sql and outside of sql statment [message #603139 is a reply to message #603137]

Wed, 11 December 2013 11:15

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Reading your question, it seems I misunderstood the issue.
I thought OP wanted a global variable, global to everyone (by "private" I meant private to a session).

Report message to a moderator

Re: Can I use global variable in dynamic sql and outside of sql statment [message #603141 is a reply to message #603123]

Wed, 11 December 2013 11:58

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Actually, I can't understand your statement. PUBLIC/PRIVATE has nothing to do with scope. Don't confuse scope with visibility. Global vs local variable does define scope. Only global variables have session as their scope. Local variable scope is block (and all inner blocks) it is declared in. Local variable is gone as soon as execution of the block it is declared in completes.

SY.

[Updated on: Wed, 11 December 2013 14:08]

Report message to a moderator

Previous Topic:	dbms_lob.append raising error.
Next Topic:	UPDATE table insert characters into a VARCHAR2 row

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 02 09:28:43 CDT 2025