Home » SQL & PL/SQL » SQL & PL/SQL » Global variable
Global variable [message #194717] Sun, 24 September 2006 23:31 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to declare a global variable.
Can you please let me know if the below statement is correct way of creating a Global variable.

VARIABLE basedate DATE := TRUNC(SYSDATE,'DAY');

Thanks,
Mona

[Updated on: Mon, 25 September 2006 00:00]

Report message to a moderator

Re: Global variable [message #194729 is a reply to message #194717] Mon, 25 September 2006 00:37 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
hmmmm. ':=' is the pure PL/SQL syntax. I don't think you can do that. Why don't you open you SQL Plus and write it there.

Re: Global variable [message #194730 is a reply to message #194729] Mon, 25 September 2006 00:39 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I have a sql file which is used for converting to csv file.
I have to write it in the sql file only. Please advice.

Thanks,
Mona
Re: Global variable [message #194733 is a reply to message #194730] Mon, 25 September 2006 00:51 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks anyway, I have got a way and it works fine.
VARIABLE g_basedate VARCHAR2(10)

EXECUTE :g_basedate := TO_CHAR(TRUNC(SYSDATE,'DAY'),'MM/DD/RRRR');

And I can then use this variable throughout my sql file.

Thanks,
Mona
Re: Global variable [message #194744 is a reply to message #194733] Mon, 25 September 2006 01:23 Go to previous message
Liza79
Messages: 74
Registered: September 2006
Member
ya thats right, what EXECUTE statement does, it just build an internal pl/sql block and put your command inside it, so your statement is actually being executed like this.
BEGIN
  :g_basedate := TO_CHAR(TRUNC(SYSDATE,'DAY'),'MM/DD/RRRR');
END;
/

So it is working, like i already said that it will work in PL/SQL. Smile

Liza
Previous Topic: ORA-01756: quoted string not properly terminated
Next Topic: Create table invalid syntax
Goto Forum:
  


Current Time: Sat Dec 10 12:44:11 CST 2016

Total time taken to generate the page: 0.06888 seconds