Home » SQL & PL/SQL » SQL & PL/SQL » dynamic sql issue (Oracle, 10g, Win XP(client) Solaris10(server))
dynamic sql issue [message #388938] Thu, 26 February 2009 13:02 Go to next message
kowalsky
Messages: 37
Registered: May 2003
Member
Hi all,
I'm having trouble with a dynamic sql query. I am executing everything in SQL*Plus as
anonymous blocks.
I am trying to select a value in a range of columns named col2_XX_value with the XX
from 00 to 30. I will eventually get the cnt as a parameter and I am trying to build
the name of the column in my qry string:


declare
cnt varchar2(2) := '00';
qry varchar2(200);
begin
qry := 'select col1,
col2_'||hrc||'_value
from testtable
where col3=''ABC''
and col4 = ''11-MARCH-2009''
and col5=976';
execute immediate qry;
DBMS_OUTPUT.PUT_LINE('AAAAAAAAAAA');
DBMS_OUTPUT.PUT_LINE('===========================================================');
DBMS_OUTPUT.PUT_LINE(qry);
DBMS_OUTPUT.PUT_LINE('===========================================================');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
end;

If I execute I get:
AAAAAAAAAAA
===========================================================
select col1, col2_00_value
from testtable
where col3='ABC'
and col4 = '11-MARCH-2009'
and col5=976
===========================================================
0

PL/SQL procedure successfully completed.


It tells me that the procedure executed successfully, however the select did not return any rows,
rowcount is 0. I know for a fact that's not true, if I execute the actual query with the col2 name substituted,
it is going to run:

select col1, col2_00_value
from testtable
col3=''ABC''
and col4 = ''11-MARCH-2009''
and col5=976';

and the result is:

COL1 COL2_00_VALUE
------- -------------
ABCcode 14

Any idea why my block is not working as expected?
thanks,
kowaslky
Re: dynamic sql issue [message #388939 is a reply to message #388938] Thu, 26 February 2009 13:05 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

>Any idea why my block is not working as expected?
Your expectations are not correct.

[Updated on: Thu, 26 February 2009 13:07]

Report message to a moderator

Re: dynamic sql issue [message #388940 is a reply to message #388938] Thu, 26 February 2009 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: dynamic sql issue [message #388943 is a reply to message #388939] Thu, 26 February 2009 13:19 Go to previous messageGo to next message
kowalsky
Messages: 37
Registered: May 2003
Member
the table I'm trying to query dynamically:
name: testtable
columns: col1 varchar2
col2_00_value number
col2_01_value number
....................
col2_30_value number
col3 varchar2
col4 date
col5 number

I have a row that has the following values:
col1: ABCcode
col2_00_value: 14
col2_01_value: null
...................
col2_30_value: null
col3: ABC
col4:11-MARCH-2009
col5:976

when I execute
select col1, col2_00_value from testtable where col3='ABC'
and col4 = '11-MARCH-2009' and col5=976;
I get
COL1 COL2_00_VALUE
------- -------------
ABCcode 14

when I do the same thing through the anonymous block where I dynamically build the name of the column col2_00_value by concatenating col2_ to hrc and to _value I get nothing.
That is, according to the code in my first post, I expected to see printed:
AAAAAAAAAAA
===========================================================
select col1, col2_00_value
from testtable
where col3='ABC'
and col4 = '11-MARCH-2009'
and col5=976
===========================================================
1

PL/SQL procedure successfully completed.

NOTICE THE 1 FOR SQL%ROWCOUNT below the second row of =======; I GET 0 now (and I don't know why, hence my question)

Thanks,
kowlasky
Re: dynamic sql issue [message #388944 is a reply to message #388938] Thu, 26 February 2009 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
How many rows are returned by every/any invocation of EXECUTE IMMEDIATE statement?
Re: dynamic sql issue [message #388945 is a reply to message #388943] Thu, 26 February 2009 13:25 Go to previous messageGo to next message
kowalsky
Messages: 37
Registered: May 2003
Member
let me indent the code:


declare
   cnt varchar2(2) := '00';
   qry varchar2(200);
begin
   qry := 'select col1, col2_'||hrc||'_value
           from testtable
           where col3=''ABC''
           and col4 = ''11-MARCH-2009''
           and col5=976';
   execute immediate qry;
   DBMS_OUTPUT.PUT_LINE('AAAAAAAAAAA');
   DBMS_OUTPUT.PUT_LINE ('================================');
   DBMS_OUTPUT.PUT_LINE(qry);
   DBMS_OUTPUT.PUT_LINE('=================================');
   DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
end;

thanks,
kowalsky
Re: dynamic sql issue [message #388946 is a reply to message #388938] Thu, 26 February 2009 13:29 Go to previous messageGo to next message
kowalsky
Messages: 37
Registered: May 2003
Member
BlackSwan,
there are no rows returned, as shown by the value of the SQL%ROWCOUNT value.
Actually, I replaced the dynamically built qry with a static SQL that has the col2_00_value name of the column instead of the concat and the SQL%ROWCOUNT is coming back 1.

What I can't figure out is why the qry seems to be OK, namely I copy what the block peints, I paste at the cursor and execute and I get the correct answer. When execute immediate, though, I get zilch,

thanks,
kowalsky
Re: dynamic sql issue [message #388947 is a reply to message #388938] Thu, 26 February 2009 13:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
EXECUTE IMMEDIATE SQL runs within its own session; not within the context of the invoking session.

[Updated on: Thu, 26 February 2009 13:40]

Report message to a moderator

Re: dynamic sql issue [message #388948 is a reply to message #388938] Thu, 26 February 2009 13:47 Go to previous messageGo to next message
kowalsky
Messages: 37
Registered: May 2003
Member
I'm not sure I understand - you are saying that SQL%ROWCOUNT will always be 0 even though the code executes correctly?

If I were to select 'col2'||hrc||'_value' into a variable would I get 14 in it? I doubt but I can try.

thanks,
kowlasky
Re: dynamic sql issue [message #388949 is a reply to message #388938] Thu, 26 February 2009 13:50 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#sthref1559

>If I were to select 'col2'||hrc||'_value' into a variable would I get 14 in it?

EXECUTE IMMEDIATE SQL runs within its own session; not within the context of the invoking session.
Re: dynamic sql issue [message #388951 is a reply to message #388949] Thu, 26 February 2009 14:10 Go to previous messageGo to next message
kowalsky
Messages: 37
Registered: May 2003
Member
BlackSwan,
I am trying to get your message however if I did not understand you the first time, it is a lack of imagination from your part to try the same answer again.
If you can tell me what needs to be changed in my code and prove it is working, thank you very much, if you want to throw links around, don't waste your time anymore, anyone can google,

thanks,
kowalsky
Re: dynamic sql issue [message #388953 is a reply to message #388938] Thu, 26 February 2009 14:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
run the following from SQLPLUS & use CUT & PASTE to show whole session.

SET TERM ON ECHO ON
declare
   begin
   DBMS_OUTPUT.ENABLE(1000000);
   execute immediate 'select * from user_objects';
   DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
   execute immediate 'select * from user_TABLES';
   DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
end;
/
Re: dynamic sql issue [message #388954 is a reply to message #388953] Thu, 26 February 2009 14:36 Go to previous messageGo to next message
kowalsky
Messages: 37
Registered: May 2003
Member
ok,
I was wrong about getting SQL%ROWCOUNT=1 ever, as per your instructions I executed and this is whatI got:
SQL> declare
  2     begin
  3     DBMS_OUTPUT.ENABLE(1000000);
  4     execute immediate 'select * from user_objects';
  5     DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
  6     execute immediate 'select * from user_TABLES';
  7     DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
  8  end;
  9  /
0
0

PL/SQL procedure successfully completed.

SQL> select count(*) from user_objects;

  COUNT(*)
----------
        10

SQL> select count(*) from user_tables;

  COUNT(*)
----------
         3

SQL> 
Re: dynamic sql issue [message #388955 is a reply to message #388954] Thu, 26 February 2009 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sql%rowcount gives you the number of rows you last fetched, there you fetched nothing.

Regards
Michel
Re: dynamic sql issue [message #388957 is a reply to message #388955] Thu, 26 February 2009 15:08 Go to previous messageGo to next message
kowalsky
Messages: 37
Registered: May 2003
Member
The SQL%ROWCOUNT called after any dml statement should return the number of rows affected. This is the assumption I make now - if this not correct, please let me know. With this assumption valid, the numbers that should have been printed at the end of the code sent by BlackSwan should have been 10 and 3.
Instead they were 0 and 0 - the execute immediate is not working as I expected,
thanks,
kowalsky
Re: dynamic sql issue [message #388958 is a reply to message #388957] Thu, 26 February 2009 15:17 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
But it's working exactly as I expected.

The "execute immediate" DOESN'T return any rows. It just executes an SQL statement, but in a different environment than you look at the SQL%ROWCOUNT.

An execute immediate is not a normal DML statement.

And the normal advice regarding "execute immediate" in 99.9% of cases is: don't use it.

It gets impossible to debug and maintain sooner or later most of the time.



Re: dynamic sql issue [message #388961 is a reply to message #388958] Thu, 26 February 2009 15:31 Go to previous messageGo to next message
kowalsky
Messages: 37
Registered: May 2003
Member
ok,
i'm slowly getting it - i cannot expect to see affected rows reflected in the value of sql%rowcount because EXECUTE IMMEDIATE SQL runs within its own session; not within the context of the invoking session as previuosly said and now understood.

I also realize that the fetches are executed only for dml statements like INSERT, UPDATE, DELETE and SELCT INTO but not simple SELECT ..., therefore SELECT somecol FROM sometable will also not modify the value of sql%rowcount.

Ok,
then how do I solve my problem? I need to update some fields as per some parameter passed to a procedure that will update column col2_XX_value where the XX is the value passed. Also I need to update if there is already a row with any of these columns holding a value or insert a new row if I don't find it - I was planning to do this by updating and examining the sql%rowcount - if it is zero it means the row is not there, hence I insert!

The only thing not working is the dynamic sql ... what would be the alternative?
thanks,
kowalsky
Re: dynamic sql issue [message #388963 is a reply to message #388961] Thu, 26 February 2009 16:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

where the XX is the value passed



Which is also a bad idea regarding dynamic SQL. someone might pass "; drop table xxx;" in the variable or some such.

I have something here which basically does the "update" part on a table with a similar bad design. Although there I know the row is already there, so I only need to do an update.

You could perhaps add an additional insert at the start, after you checked if the row is in there or not. Or just try the insert in an exception block if your where clause matches the the primary key or an unique index.

CREATE OR REPLACE  PROCEDURE set_amount (
   v_id        NUMBER, 
   v_column_no NUMBER, 
   v_row       NUMBER, 
   v_amount    NUMBER) IS

BEGIN


    CASE
        WHEN v_column_no = 1 THEN
            UPDATE amount_rows
               SET MG_01 = v_amount
             WHERE ID = v_id
               AND ZEILE = v_row;
        WHEN v_column_no = 2 THEN
            UPDATE amount_rows
               SET MG_02 = v_amount
             WHERE ID = v_id
               AND ZEILE = v_row;
        WHEN v_column_no = 3 THEN
            UPDATE amount_rows
               SET MG_03 = v_amount
             WHERE ID = v_id
               AND ZEILE = v_row;
        WHEN v_column_no = 4 THEN
            UPDATE amount_rows
               SET MG_04 = v_amount
             WHERE ID = v_id
               AND ZEILE = v_row;
        WHEN v_column_no = 5 THEN
            UPDATE amount_rows
               SET MG_05 = v_amount
             WHERE ID = v_id
               AND ZEILE = v_row;
        WHEN v_column_no = 6 THEN
            UPDATE amount_rows
               SET MG_06 = v_amount
             WHERE ID = v_id
               AND ZEILE = v_row;
        WHEN v_column_no = 7 THEN
            UPDATE amount_rows
               SET MG_07 = v_amount
             WHERE ID = v_id
               AND ZEILE = v_row;
        WHEN v_column_no = 8 THEN
            UPDATE amount_rows
               SET MG_08 = v_amount
             WHERE ID = v_id
               AND ZEILE = v_row;
        WHEN v_column_no = 9 THEN
            UPDATE amount_rows
               SET MG_09 = v_amount
             WHERE ID = v_id
               AND ZEILE = v_row;
        WHEN v_column_no = 10 THEN
            UPDATE amount_rows
               SET MG_10 = v_amount
             WHERE ID = v_id
               AND ZEILE = v_row;


    END CASE;

END;
/












Re: dynamic sql issue [message #388968 is a reply to message #388938] Thu, 26 February 2009 16:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

What Business Problem are you really trying to solve?
Does this application comply with Third Normal Form (or higher)?



>Also I need to update if there is already a row with any of these columns holding a value or insert a new row if I don't find it
This is what the MERGE command does.
Re: dynamic sql issue [message #389096 is a reply to message #388961] Fri, 27 February 2009 04:52 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could use MERGE to do your Insert/Update.

Admittedly, a dynamic merge will cause the developers who have to maintain your code to curse your name, but it will work.

On other matters, I don't think 'another session' is the correct way to describe how execute immediate works.

SQL run via dynamic SQL can see all of your uncommitted changes, so it's absolutely not a different session.
A better way would be to say that it has it's own, private session state or execution context, which can see none of the session variables of the calling session.

Previous Topic: ORA-01461
Next Topic: query
Goto Forum:
  


Current Time: Fri Dec 09 04:01:53 CST 2016

Total time taken to generate the page: 0.06158 seconds