Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SQL: Bind list of values with select statement?
DBMS_SQL: Bind list of values with select statement? [message #226810] Mon, 26 March 2007 11:15 Go to next message
alp0001
Messages: 29
Registered: September 2005
Location: Illinois, USA
Junior Member
I'm trying to bind a list of values into a select statement and then fetch x rows at a time (similar to using bulk collect), but not sure how to do it using 'Method 4 Dynamic Sql'. Basically, I get the error of:
#######################
ERROR at line 1:
ORA-29255: Cursor contains both bind and define arrays which is not permissible
ORA-06512: at line 279
#######################
On a PL/SQL script like the following:
DEFINE
  str_tab DBMS_SQL.NUMBER_TABLE;
  store_tab DBMS_SQL.NUMBER_TABLE;
  lbl_tab DBMS_SQL.VARCHAR2_TABLE;
  cur INTEGER := DBMS_SQL.OPEN_CURSOR;
  fdbk INTEGER;
BEGIN
  store_tab (1) := 3000;
  store_tab (2) := 121;
  DBMS_SQL.PARSE (cur, 'SELECT store_nbr, store_label_state FROM EX_TABLE where store_nbr = :str_lst', DBMS_SQL.NATIVE);
  -- Add all the stores to search by.
  DBMS_SQL.BIND_ARRAY (cur, ':str_lst', store_tab);

  -- Define the columns returned in an array?
  DBMS_SQL.DEFINE_ARRAY (cur, 1, str_tab, 2, -10);
  DBMS_SQL.DEFINE_ARRAY (cur, 2, lbl_tab, 2, -10);
  fdbk := DBMS_SQL.EXECUTE (cur);
...
Anyone have any ideas/examples?
Re: DBMS_SQL: Bind list of values with select statement? [message #226850 is a reply to message #226810] Mon, 26 March 2007 15:04 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Looking at error message:

http://www.pitt.edu/AFShome/h/o/hoffman/public/html/oradoc/server.804/a58312/newch101.htm
it means that it's impossible to use both types in the same statement.

I would advice you to use some global temporary table to store
parameters and JOIN it.

DECLARE
  str_tab DBMS_SQL.NUMBER_TABLE;
  store_tab DBMS_SQL.NUMBER_TABLE;
  lbl_tab DBMS_SQL.VARCHAR2_TABLE;
  cur INTEGER := DBMS_SQL.OPEN_CURSOR;
  fdbk INTEGER;
BEGIN
/*
  Insert to Global TEMPORARY table
*/

DBMS_SQL.PARSE (cur, 'SELECT store_nbr, store_label_state FROM EX_TABLE  ET, GTT_PARAMS P where ET.store_nbr = P.store_nbr', DBMS_SQL.NATIVE);
  -- Define the columns returned in an array?
  DBMS_SQL.DEFINE_ARRAY (cur, 1, str_tab, 2, -10);
  DBMS_SQL.DEFINE_ARRAY (cur, 2, lbl_tab, 2, -10);
  fdbk := DBMS_SQL.EXECUTE (cur);


HTH.
Michael

Re: DBMS_SQL: Bind list of values with select statement? [message #226859 is a reply to message #226810] Mon, 26 March 2007 15:42 Go to previous messageGo to next message
alp0001
Messages: 29
Registered: September 2005
Location: Illinois, USA
Junior Member
Thanks Michael for your suggestion. However, I went a completely different route (and not sure why this didn't work previously!). Since the outer shell calling the PL/SQL is KSH, I'm putting the values in a KSH variable first. Then, I'm using that variable in the cursor select statement. Something like the following to give you an idea:
STORE_LISTING=3000,121
sqlplus -s <<-%%
$DBLOG/$DBPWD
...
DECLARE
   CURSOR cur_TPR_Cnt IS select ... where store_nbr in ($STORE_LISTING) ... ;
BEGIN
...
EXCEPTION
...
END;
/
exit $SQL_SUCCESS
%%

... denotes more code.
Re: DBMS_SQL: Bind list of values with select statement? [message #227025 is a reply to message #226810] Tue, 27 March 2007 02:54 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi,

I would NOT use that solution:
1. It means that you actually concatenating hard-code values in your cursor, so HARD parse must be performed for each statement (and hard parse is bad for performance).
2.SQL injection in possible when using values instead of bind variables.

HTH.
Michael
Re: DBMS_SQL: Bind list of values with select statement? [message #227076 is a reply to message #226810] Tue, 27 March 2007 04:45 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
The dbms_sql package does not support a collection binding. Though there is a bind_array, it is supposed to do a bind of scalar values and execute query multiple times with supplied values.
To do a bind, you may use a package global variable with (select .. from table(cast(my_pkg.get_collection))). Smth like this:

    g_bind_values my_collection;
-- ...
    functions get_binds return my_collection
    begin
        return g_bind_values;
    end;
--...
    procedure some_prc(...)
    begin
        -- dbms_sql routine with query " ... where x in (select /*+ cardinality (...)*/column_value from table(cast(mypkg.get_binds as my_collection)) t)"
        --this is a bind
        g_bind_values := some_collection;
        -- do other things
    end;

AFAIK, in Oracle 11g dbms_sql package will be improved to support collections (like in NDS), so there will be no workarounds.
Re: DBMS_SQL: Bind list of values with select statement? [message #227149 is a reply to message #226810] Tue, 27 March 2007 07:51 Go to previous message
alp0001
Messages: 29
Registered: September 2005
Location: Illinois, USA
Junior Member
Thanks for the suggestions. Let me clarify that I'm currently using Oracle 9.2 and this is a last minute report the boss threw at me. So, to get it out the door ASAP, I'm still sticking with my previous method. I can always impove it later should it be needed. I don't have to worry about SQL injection as 1) I can validate the list of store numbers in my ksh script and 2) This program (and list of provided store numbers) are only accessible by our business user. As such, this isn't something like a website where someone malicious can run along and try whatever they please.

Adam

[Updated on: Tue, 27 March 2007 07:57]

Report message to a moderator

Previous Topic: SQL with and without "Parallel" option
Next Topic: sql help
Goto Forum:
  


Current Time: Sun Dec 11 00:38:33 CST 2016

Total time taken to generate the page: 0.08082 seconds