Re: Need Dynamic Sql Help Please!!

From: Tom Zamani <tomz_at_redflex.com.au>
Date: Thu, 9 Dec 1999 15:01:25 +1100
Message-ID: <82n9d3$edl$1@perki.connect.com.au>

Here is a procedure which uses dynamic SQL. No bind values have been used.

procedure set_status (v_schema_name in contract.schema_name%type default null)
/*
for the givien schema this procedure will reset the status of all incidents which
have been locked for more than an hour. */
is
v_SqlString varchar2(2000) default null;

    cursor_name INTEGER default 0;
    rows_processed INTEGER default 0;
begin
cursor_name := dbms_sql.open_cursor;
v_SqlString:='update '||v_schema_name||'.traffic_incident  set stts_icws_id =to_number(substr(to_char(STTS_ICWS_ID),3,2)), stts_set_dtm=sysdate

   where stts_icws_id >1000 and
((to_date (to_char(sysdate,''hh24 dd-mm-yyyy''),''hh24 dd-mm-yyyy'')
- to_date (to_char(stts_set_dtm,''hh24 dd-mm-yyyy''),''hh24

dd-mm-yyyy''))*24 )>1';

dbms_sql.parse(cursor_name,v_SqlString,dbms_sql.NATIVE);
rows_processed := dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);

EXCEPTION
WHEN OTHERS THEN
    dbms_sql.close_cursor(cursor_name); end;

Joe Long <joe.long_at_cticallcenter.com> wrote in message news:384EB132.2BA12442_at_cticallcenter.com...
> Every customer at my company has their own set of tables which are
> exactly the same as the next customer's. Example: each has a commission
> table which has the exact same column definitions as every other. To
> process the data, each customer has their own set of identical packages
> and procedures. I am trying to write one set of packages which will run
> through each customer's tables and process their data. Since the table
> names aren't supplied until run time, it requires dynamic sql, which I
> just can't seem to get a hand on. To do something that takes 5 lines in
> SQL worksheet seems to take 5 pages of dynamic sql (only a slight
> exageration there). I figure it has got to be me. Can anyone suggest
> some REALLY good books or references? I have read all I can find in the
> on-line manuals that come with Oracle.
>
> Thanks in advance!
>
> Joe long
> (Remove no_spam from my address to reply directly)
Received on Wed Dec 08 1999 - 22:01:25 CST