Home » SQL & PL/SQL » SQL & PL/SQL » using variable in a query!!!
using variable in a query!!! [message #227552] Wed, 28 March 2007 10:56 Go to next message
ozzy80
Messages: 55
Registered: June 2005
Member
Hello all,

Can any one plz let me know where am I going wrong....

I have a variable defined in my pl/sql block and its value is...

  v_strcat              varchar2(120);
  v_check               number;

  v_strcat=1000, 111


trying to get the count of available values from a table like this...

select count(*) into v_check from test_reason_code where code in (v_strcat);


expected result...
SQL> select count(*) from test_reason_code where code in (1000, 111);

  COUNT(*)
----------
         1


getting result...

v_check=0


the test_reason_code table is...

SQL> create table test_reason_code ( code varchar2(10), descr varchar2(20));
Table created.
SQL> insert into test_reason_code values ('2222','2222 reason');

1 row created.
SQL> insert into test_reason_code values ('1','1 reason');

1 row created.
SQL> insert into test_reason_code values ('2','2 reason');

1 row created.
SQL> insert into test_reason_code values ('202','202 reason');

1 row created.
SQL> insert into test_reason_code values ('111','111 reason');

1 row created.
SQL> insert into test_reason_code values ('7893939','7893939 reason');

1 row created.
SQL> insert into test_reason_code values ('999','999 reason');

1 row created.


Plz let me know how can I get the count of all the values available in v_strcat that are present in test_reason_code table...

thnx
~ozzy
Re: using variable in a query!!! [message #227558 is a reply to message #227552] Wed, 28 March 2007 11:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Here is a free clue.
First get a clean compile of the PL/SQL code.
Re: using variable in a query!!! [message #227560 is a reply to message #227558] Wed, 28 March 2007 11:33 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
anacedent wrote on Wed, 28 March 2007 11:22
Here is a free clue.
First get a clean compile of the PL/SQL code.



It compiles okay... but the value is 0 instead of 1, that it should be...
Re: using variable in a query!!! [message #227561 is a reply to message #227552] Wed, 28 March 2007 11:35 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
If you say so.
How is anyone supposed to know what the code looks like NOW?
Nobody can debug code they can NOT see!
We are NOT standing behind you & looking over your shoulder.
Give us a clue or two, please.
Re: using variable in a query!!! [message #227563 is a reply to message #227561] Wed, 28 March 2007 11:50 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
anacedent wrote on Wed, 28 March 2007 11:35
If you say so.
How is anyone supposed to know what the code looks like NOW?
Nobody can debug code they can NOT see!
We are NOT standing behind you & looking over your shoulder.
Give us a clue or two, please.

Oh, sorry I get it now...

-- Set SQL*Plus settings
--
SET ECHO OFF
SET SERVEROUTPUT ON
SET VERIFY OFF
SET FEEDBACK ON

-- Exit with an error if an error occurs
WHENEVER sqlerror EXIT 1

DECLARE

  v_reason              test_reason_code.code%TYPE;
  v_char                varchar2(1);
  v_lastchar            varchar2(10);
  v_string              varchar2(120);
  v_strcat              varchar2(120);
  v_count               number;
  v_check               number;
  v_sqlstmt             varchar2(200);


  v_reccount            NUMBER;
-------------------------------------------------------------------------------------------------------
BEGIN

   dbms_output.enable(1000000);
   dbms_output.put_line('TSM - test_inv_del_trigger.sql start: '|| TO_CHAR(SYSDATE, 'Dy Mon DD, YYYY HH24:MI'));

-- initialize the record counter
   v_reccount := 0;

   select x into v_string from test_varchar_kk where id = 1;

      dbms_output.put_line('SUCCESS - v_string='|| v_string ||'--end--');

   select substr(v_string,-1,(instr(v_string,',',-1)-1)) into v_lastchar from dual;

      dbms_output.put_line('SUCCESS - v_lastchar='|| v_lastchar ||'--end--');

   select decode(v_lastchar,' ',substr(v_string,1,(instr(v_string,',',-1)-1)), ',', substr(v_string,1,(instr(v_string,',',-1)
-1)), v_string) into v_strcat from dual;

      dbms_output.put_line('SUCCESS - v_strcat='|| v_strcat ||'--end--');

   select (length(v_strcat) - length(replace(v_strcat,',',''))+1) into v_count from dual;

      dbms_output.put_line('SUCCESS - v_count='|| v_count ||'--end--');

--   v_sqlstmt := 'select count(*) from test_reason_code where code in (:id)';

   execute immediate 'select count(*) from test_reason_code where code in (:test)' into v_check using v_strcat;

        dbms_output.put_line('v_check=' || v_check);

  if v_count = v_check then
        dbms_output.put_line('SUPER SUCCESS !!!!!!! ');
  else
        dbms_output.put_line('NOT MATCH !!!!!!!!! ');
  end if;
  dbms_output.put_line(v_reccount || ' records updated.');
  dbms_output.put_line('TSM - test_inv_del_trigger.sql finish: '|| TO_CHAR(SYSDATE, 'Dy Mon DD, YYYY HH24:MI'));

-------------------------------------------------------------------------------------------------------
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('ERROR! has occured: ' || SQLCODE || ' ' || SQLERRM);
    dbms_output.put_line('TSM - test_inv_del_trigger.sql stopped with error: '|| TO_CHAR(SYSDATE, 'Dy Mon DD, YYYY HH24:MI'))
;
    RAISE;

END;
/

SET SERVEROUTPUT OFF
SET VERIFY ON
SET FEEDBACK ON
SET ECHO ON

EXIT

/* end */


and when I run it...

TSM - test_inv_del_trigger.sql start: Wed Mar 28, 2007 11:43
SUCCESS - v_string=1000, 111--end--
SUCCESS - v_lastchar=1--end--
SUCCESS - v_strcat=1000, 111--end--
SUCCESS - v_count=2--end--
v_check=0
NOT MATCH !!!!!!!!!
0 records updated.
TSM - test_inv_del_trigger.sql finish: Wed Mar 28, 2007 11:43

PL/SQL procedure successfully completed.

SQL>
SQL> EXIT
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production


here is the test_varchar_kk table

CREATE TABLE TEST_VARCHAR_KK
(
  ID  NUMBER,
  X   VARCHAR2(180 CHAR)
)

SQL> insert into test_varchar_kk values ('1','1000,111');
1 row created.
SQL> insert into test_varchar_kk values ('2','202,303,328876,7893939');
1 row created.
SQL> insert into test_varchar_kk values ('3','1,2,999, ');
1 row created.


thnx for looking into it...
Re: using variable in a query!!! [message #227566 is a reply to message #227552] Wed, 28 March 2007 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Here is another free clue.
If you INSIST on writing non-scalable code using EXECUTE IMMEDIATE then at least have the common sense to construct the SQL statement into a single VARCHAR2 variable prior to execution.
Next prior to execution output/display/print the actual SQL statement being handed to EXECUTE IMMEDIATE.
Next cut & paste that SQL statement into SQL*Plus to confirm that it obtains the results you expect.
What you think you are doing is NOT what is actually happening.
Re: using variable in a query!!! [message #227573 is a reply to message #227552] Wed, 28 March 2007 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Everyone should keep in mind that the REAL purpose of EXECUTE IMMEDIATE is to provide the capability to do DDL from within PL/SQL.

IMO, EXECUTE IMMEDIATE should NEVER be used for DML.
PL/SQL provides other much MORE efficient alternatives to do DML.
Re: using variable in a query!!! [message #227582 is a reply to message #227573] Wed, 28 March 2007 13:08 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You are getting zero because

SQL> var v_str varchar2(10);
SQL> exec :v_str := '1,2';

PL/SQL procedure successfully completed.

SQL> select count(*) from test_reason_code where code in :v_str;

  COUNT(*)
----------
         0

SQL> print :v_str;

V_STR
--------------------------------
1,2

SQL> select count(*) from test_reason_code where code in (1,2);

  COUNT(*)
----------
         2

SQL> select count(*) from test_reason_code where code in ('1,2');

  COUNT(*)
----------
         0



So refer the following url. This is what you are after. Variable in List.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

cheers
Re: using variable in a query!!! [message #227584 is a reply to message #227552] Wed, 28 March 2007 13:18 Go to previous message
ozzy80
Messages: 55
Registered: June 2005
Member
thanks guyz!!!
Previous Topic: pl/sql code
Next Topic: VARRAY in PLSQL
Goto Forum:
  


Current Time: Sat Dec 10 12:50:49 CST 2016

Total time taken to generate the page: 0.06560 seconds