| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamatic SQL - Where clause
mgogala_at_rocketmail.com schrieb:
>
> In article <36a0301e.0_at_ecn.ab.ca>,
> suisum_at_ecn.ab.ca () wrote:
> > Can anyone show me a good way to use dynamatic SQL if I have more than 1
> > condition in the WHERE clause, please?
> >
> > How can I make it more generic to handle the WHERE clause, I don't want to
> > use DBMS_SQL.BIND_VARIABLE().
> >
> > CREATE OR REPLACE PROCEDURE do_delete(
> > p_table_name IN VARCHAR2,
> > p_column_name IN VARCHAR2,
> > p_column_value IN VARCHAR2)
> > IS
> > v_theCursor INTEGER;
> > v_statement VARCHAR2(8192);
> > v_status INTEGER;
> >
> > BEGIN
> >
> > v_statement := 'begin delete from ' || p_table_name ||
> > ' where ' || p_column_name || ' = :v_value; ' ||
> > ' end;';
> >
> > v_theCursor := DBMS_SQL.OPEN_CURSOR;
> > DBMS_SQL.PARSE (v_theCursor, v_statement, DBMS_SQL.NATIVE);
> > DBMS_SQL.BIND_VARIABLE (v_theCursor, ':v_value', p_column_value);
> > v_status := DBMS_SQL.EXECUTE (v_theCursor);
> > DBMS_SQL.CLOSE_CURSOR(v_theCursor);
> >
> > EXCEPTION
> > WHEN OTHERS THEN
> > IF (DBMS_SQL.IS_OPEN (v_theCursor)) THEN
> > DBMS_SQL.CLOSE_CURSOR(v_theCursor);
> > END IF;
> > DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
> > RAISE;
> >
> > END do_delete;
> > /
> >
> > --
> > Best regards,
> >
> Unfortunately, you have to use bind. If you have a variable in your 'WHERE'
> clause, the only way of passing a value to that variable is to bind that
> variable. No other way.
> Mladen Gogala
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Grampf
If you are using DBMS_SQL-package, why don't you concatenate the
variables!
Try:
v_statement := 'delete from ' || p_table_name ||
' where ' || p_column_name ||
' = '''||p_column_value||''''||
' and 1=1 /* your next clause */; ';
and forget the line with DBMS_SQL.BIND_VARIABLE...
--
Reinhard
Hiermit widerspreche ich der Nutzung oder Uebermittlung meiner Daten fuer Werbezwecke oder fuer die Markt- oder Meinungsforschung gemaess Par. 28 Abs. 3 Bundesdatenschutzgesetz.
 |
 |