Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic PL/SQL (Oracle 10g on UNIX)
Dynamic PL/SQL [message #342915] Mon, 25 August 2008 08:47 Go to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
There are three variables x1, x2 and x3. The fourth variable var contains either x1, x2 or x3. I want to print the value of the variable contained in var.
Re: Dynamic PL/SQL [message #342919 is a reply to message #342915] Mon, 25 August 2008 09:13 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Read the posting guidelines
Re: Dynamic PL/SQL [message #342922 is a reply to message #342915] Mon, 25 August 2008 09:34 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
The requirements reminded me this thread: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:227413938857#2973344838545.
But, it would be much easier to create one array variable X and pick the required value by given index.
Re: Dynamic PL/SQL [message #342930 is a reply to message #342915] Mon, 25 August 2008 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
COALESCE

Regards
Michel
Re: Dynamic PL/SQL [message #342971 is a reply to message #342922] Mon, 25 August 2008 13:02 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
Based on your suggestion, I tried the following script, But it does not work. It gives an error - PLS-00201: identifier 'VAR2' must be declared

Declare
x1 Number := 1;
x2 Number := 2;
x3 Number := 3;

var varchar2(3) := 'x2';

Begin
EXECUTE IMMEDIATE 'dbms_output.put_line('':x'');' using var2;
End;

Any suggestion?

Thanks



[mod-edit: smiley removed by bb, just check "Disable smiles in this message"}

[Updated on: Mon, 25 August 2008 15:48] by Moderator

Report message to a moderator

Re: Dynamic PL/SQL [message #342972 is a reply to message #342915] Mon, 25 August 2008 13:05 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
I do not know how that smily came to the program!


The program is

Declare
x1 Number := 1;
x2 Number := 2;
x3 Number := 3;

var varchar2(3) := 'x2';

Begin
EXECUTE IMMEDIATE 'dbms_output.put_line(' 'Mad' ');' using var2;
End;
Re: Dynamic PL/SQL [message #342973 is a reply to message #342972] Mon, 25 August 2008 13:06 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
That smily is in fact 'Mad'
Re: Dynamic PL/SQL [message #342974 is a reply to message #342972] Mon, 25 August 2008 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I do not know how that smily came to the program!

Becasue you didn't format it, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Where is var2 declared?
Use SQL*plus and copy and paste your session.

Regards
Michel
Re: Dynamic PL/SQL [message #342975 is a reply to message #342973] Mon, 25 August 2008 13:08 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
I am frustrated over that smily!

It is Quotes, colon, x and quote.
Re: Dynamic PL/SQL [message #342976 is a reply to message #342974] Mon, 25 August 2008 13:12 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
That var2 is a type. In fact it is var only. Now it gives ORA-00900: invalid SQL statement error.

Thanks

Soham
Re: Dynamic PL/SQL [message #342977 is a reply to message #342976] Mon, 25 August 2008 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 25 August 2008 20:07
...please read OraFAQ Forum Guide, especially "How to format your post?" section.
...
Use the "Preview Message" button to verify.
...
Use SQL*plus and copy and paste your session.

Regards
Michel

Re: Dynamic PL/SQL [message #342979 is a reply to message #342915] Mon, 25 August 2008 13:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Did you bother to read the link I posted?
Quote:
You can ONLY do this with package global variables -- not local variables.
You are out of luck here, as you will never get the local variables into the scope of dynamic PL/SQL block.
Quote:
But, it would be much easier to create one array variable X and pick the required value by given index.
Seems to be the only reasonable working alternative. Something like (untested)
declare
  type tt is table of varchar2(10) index by varchar2(10);
  x tt;
  xx varchar2(10) := 'x1';
begin
  x( 'x1' ) := 'abc';
  x( 'x2' ) := 'def';
  select 'ghi' into x( 'x3' ) from dual;

  dbms_output.put_line( x( xx ) );
end;
/
By the way, why were you calling DBMS_OUTPUT dynamically? Also calling PL/SQL code without BEGIN END; enclosement is wrong; consult the documentation found e.g. on http://tahiti.oracle.com/. Of course, the link I provided also contains an example of valid dynamic PL/SQL block.

[Edit: Added the last sentence]

[Updated on: Mon, 25 August 2008 13:34]

Report message to a moderator

Re: Dynamic PL/SQL [message #342985 is a reply to message #342979] Mon, 25 August 2008 14:30 Go to previous messageGo to next message
soham.desai@gmail.com
Messages: 21
Registered: August 2008
Junior Member
Yes I did read the link you sent and got your point that bind variables will not work in my case.

In fact I was trying to simply my problem.

What exactly I want to do is this

From one table I am reading columns code, a, b, c, d, e and f. I want to insert records in another table which has just two fields code and valu. Now for each record, I check the value of code. If it is 1, I have to insert 1 (value of code) and value of a. Similarly (2, value of b), (3, value of c) etc. It may happen that code may not take all the values from 1 to 6. (Though it will be from 1 to 6 only). I can use if or case statement. But this will unnecessarily multiply code. For that, I want to use generalize the code.

Am I making sense?
Re: Dynamic PL/SQL [message #343017 is a reply to message #342985] Mon, 25 August 2008 21:09 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> But this will unnecessarily multiply code.
Do you think that
DECODE(code, 1,a, 2,b, 3,c, 4,d, 5,e, 6,f)
is too large? I do not think tests (which, I guess, shall be processed by this code) have many more (tens or hundreds) options to treat with.

> For that, I want to use generalize the code.
The only "solution" which reminded me will unnecessarily multiply execution time. If you do not mind this, you may dynamically create 6 SQL statements filtering one code and fetching just the desired column in a loop (suppose you have column names in an array). But I am afraid, it will require more code than the simple DECODE above with performance 6 times degraded. I would not take this way.

Good luck.
Previous Topic: previously word
Next Topic: reading uploaded images from website (merged 3)
Goto Forum:
  


Current Time: Mon Dec 05 06:53:01 CST 2016

Total time taken to generate the page: 0.07833 seconds