Hi Tom, It is my first question on your site and I hope you can help me. I had implemented a procedure that execute dynamic procedure and now I'm trying to write a function that return a ref cursor of a dynamic sql to force the developer team to use bind variable every TIME, but I want to know if I do it as only way I can do or if I can do it better, I use your example of “context”. Please review my code and my questions above.
--test case 1--
CREATE OR REPLACE CONTEXT CTX USING PKG_RUNSQL;
CREATE TABLE MYTAB(
ID_CITY VARCHAR2(4) NOT NULL,
CITY VARCHAR2(40),
ID_GROUP VARCHAR2(1),
TIMESTAMP DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT PK_MYTAB PRIMARY KEY (ID_CITY));

INSERT INTO MYTAB (ID_CITY, CITY, ID_GROUP) VALUES ('30', 'Alajuela', 'D');
INSERT INTO MYTAB (ID_CITY, CITY, ID_GROUP) VALUES ('20', 'San José', 'C');
INSERT INTO MYTAB (ID_CITY, CITY, ID_GROUP) VALUES ('28', 'Heredia', 'C');
INSERT INTO MYTAB (ID_CITY, CITY, ID_GROUP) VALUES ('31', 'Cartago', 'D');
INSERT INTO MYTAB (ID_CITY, CITY, ID_GROUP) VALUES ('26', 'Puntarenas', 'C');
INSERT INTO MYTAB (ID_CITY, CITY, ID_GROUP) VALUES ('32', 'Guanacaste', 'D');
INSERT INTO MYTAB (ID_CITY, CITY, ID_GROUP) VALUES ('23', 'Limón', 'C');
INSERT INTO MYTAB (ID_CITY, CITY, ID_GROUP) VALUES ('29', 'Costa Rica', 'D');
COMMIT;

CREATE OR REPLACE PACKAGE PKG_RUNSQL
AS
FUNCTION SF_EXECUTE_SQL
( tab_name IN VARCHAR2,
sel_column IN VARCHAR2 DEFAULT '*',
con_column IN argType DEFAULT NULL,
con_operat IN argType DEFAULT NULL,
con_value IN argType DEFAULT NULL,
con_union IN argType DEFAULT NULL,
con_join IN VARCHAR2 DEFAULT NULL
) RETURN TYPES.RC;
END PKG_RUNSQL;
/
CREATE OR REPLACE PACKAGE BODY PKG_RUNSQL
AS
FUNCTION SF_EXECUTE_SQL
( tab_name IN VARCHAR2,
sel_column IN VARCHAR2 DEFAULT '*',
con_column IN argType DEFAULT NULL,
con_operat IN argType DEFAULT NULL,
con_value IN argType DEFAULT NULL,
con_union IN argType DEFAULT NULL,
con_join IN VARCHAR2 DEFAULT NULL
) RETURN TYPES.RC IS
v_cursor TYPES.rc;
v_sql LONG := 'SELECT ';
v_where VARCHAR2(255) := ' WHERE '||con_join;
v_bindvar VARCHAR2(255);
BEGIN
IF con_column IS NULL THEN
v_sql := v_sql||sel_column||' FROM '||tab_name;
ELSE
BEGIN
FOR i IN 1 .. con_column.COUNT
LOOP
dbms_session.set_context('CTX', i, con_value(i));
v_where := v_where||con_column(i)||' '||con_operat(i)||' '||
'sys_context( ''CTX'', '''||i||''' )';
IF i < con_column.LAST THEN
v_where := v_where||' '||con_union(i)||' ';
END IF;
END LOOP;
v_sql := v_sql||sel_column||' FROM '||tab_name||v_where;
END;
END IF;
DBMS_OUTPUT.PUT_LINE(v_sql);
OPEN v_cursor FOR v_sql;
RETURN v_cursor;
END SF_EXECUTE_SQL;
END PKG_RUNSQL;
/

9iR2> var r refcursor
9iR2>DECLARE
2 tab_name VARCHAR2(200);
3 sel_column VARCHAR2(200);
4 con_column argType;
5 con_operat argType;
6 con_value argType;
7 con_union argType;
8 BEGIN
9 tab_name := 'MYTAB';
10 sel_column := 'ID_CITY, CITY';
11 con_column := argType('ID_CITY', 'ID_CITY');
12 con_operat := argType('=', '=');
13 con_value := argType(''||'20'||'', ''||'30'||'');
14 con_union := argType('or');
15 :r := PKG_RUNSQL.SF_EXECUTE_SQL( tab_name, sel_column, con_column, con_operat, con_value, con_union);
16 END;
9iR2> /
SELECT ID_CITY, CITY FROM MYTAB WHERE ID_CITY=sys_context( 'CTX', '1' ) OR ID_CITY=sys_context( 'CTX', '2' )

PL/SQL PROCEDURE successfully completed.

9iR2> print r

ID_C CITY
---- ----------------------------------------
30 Alajuela
20 San José

9iR2> DECLARE
2 tab_name VARCHAR2(200);
3 sel_column VARCHAR2(200);
4 con_join VARCHAR2(200);
5 con_column argType;
6 con_operat argType;
7 con_value argType;
8 con_union argType;
9 BEGIN
10 tab_name := 'MYTAB a, MYTAB b';
11 sel_column := 'a.*, b.city';
12 con_column := argType('a.id_city');
13 con_operat := argType('=');
14 con_value := argType(''||'20'||'');
15 con_union := NULL;
16 con_join := 'a.id_city = b.id_city and ';
18 :r := PKG_RUNSQL.SF_EXECUTE_SQL(tab_name, sel_column, con_column, con_operat, con_value, con_union, con_join);
19 END;
20 /
SELECT a.*, b.city FROM MYTAB a, MYTAB b WHERE a.id_city = b.id_city AND a.id_city = sys_context( 'CTX', '1' )

PL/SQL PROCEDURE successfully completed.

9iR2> print r

ID_C CITY I TIMESTAMP CITY
---- ---------------------------------------- - ------------------- ----------------------------------------
20 San José C 17/11/2004 12:28:53 San José
1. How I know that all arguments are bind variable,
2. How I know that all queries use index (explain plan), I see from v$sqltext and I get the next:
select id_city, city from mytab where id_city=sys_context( :"sys_b_0", :"sys_b_1" ) or id_city=sys_context( :"sys_b_2", :"sys_b_3" )
3. How can I change the pkg_runsql.sf_execute_sql to run queries that use "(a=:x or a=:y )" in the "where condition" like:
select a.id_city, a.city, b.id_group, a.timestamp from mytab a, mytab b where a.id_city = b.id_city and (b.id_city = '20' or b.id_group = 'D')
I can write the same function using dbms_sql, but I think that there is noway to retorn data to a ref cursor, isn't it.
I can write the same function using "OPEN v_cursor FOR v_sql USING str_bind;", but I think that there is noway to set the using clause dynamic, isn't it.
I have no tried with execute immediate, but I think don't work.
--test case 2--
CREATE OR REPLACE PACKAGE BODY PKG_RUNSQL
AS
FUNCTION SF_EXECUTE_SQL
( tab_name IN VARCHAR2,
sel_column IN VARCHAR2 DEFAULT '*',
con_column IN argType DEFAULT NULL,
con_operat IN argType DEFAULT NULL,
con_value IN argType DEFAULT NULL,
con_union IN argType DEFAULT NULL,
con_join IN VARCHAR2 DEFAULT NULL
) RETURN TYPES.RC IS
r_cursor TYPES.rc;
v_args argType := argType();
v_cursor PLS_INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
v_status PLS_INTEGER;
v_sql LONG := 'SELECT ';
v_where VARCHAR2(255) := ' WHERE '||con_join;
BEGIN
v_args.EXTEND(con_value.COUNT);
IF con_column IS NULL THEN
v_sql := v_sql||sel_column||' FROM '||tab_name;
ELSE
BEGIN
FOR i IN 1 .. con_column.COUNT
LOOP
v_where := v_where||con_column(i)||con_operat(i)||':'||i;
IF i < con_column.LAST THEN
v_where := v_where||' '||con_union(i)||' ';
END IF;
END LOOP;
v_sql := v_sql||sel_column||' FROM '||tab_name||v_where;
END;
END IF;
DBMS_OUTPUT.PUT_LINE(v_sql);
DBMS_SQL.PARSE(v_cursor, v_sql, dbms_sql.native);
FOR i IN 1 .. con_column.COUNT
LOOP
DBMS_SQL.BIND_VARIABLE(v_cursor, ':'||i, con_value(i), 2000);
END LOOP;
v_status := DBMS_SQL.EXECUTE(v_cursor);
FOR i IN 1 .. con_column.COUNT
LOOP
DBMS_SQL.VARIABLE_VALUE(v_cursor, ':'||i, v_args(i));
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
RETURN r_cursor;
END SF_EXECUTE_SQL;
END PKG_RUNSQL;
/

9iR2> DECLARE
2 tab_name VARCHAR2(200);
3 sel_column VARCHAR2(200);
4 con_column argType;
5 con_operat argType;
6 con_value argType;
7 con_union argType;
8 BEGIN
9 tab_name := 'mytab';
10 sel_column := 'ID_CITY, CITY';
11 con_column := argType('ID_CITY', 'ID_CITY');
12 con_operat := argType('=', '=');
13 con_value := argType('20', '30');
14 con_union := argType('or');
16 :r := PKG_RUNSQL.SF_EXECUTE_SQL( tab_name, sel_column, con_column, con_operat, con_value, con_union);
17 END;
18 /
SELECT ID_CITY, VALUES FROM fondos WHERE ID_CITY=:1 OR ID_CITY=:2

PL/SQL PROCEDURE successfully completed.

I think is a good idea to retrieve a ref cursor from DMS_SQL, maybe on feature!

Ups, the output of the test case 2 is:
SELECT ID_CITY, VALUES FROM mytab WHERE ID_CITY=:1 OR ID_CITY=:2

JO,

Thanks, Tom

I want to know if is useful to use my code to get common ref cursors like "select a, b from T1" or "select t1.a, t1.b, t2.c from t1, t2 where t2.a=t1.a and t2.b=t1.b" rather than "use plsql stored procedures
returning a ref cursor...", and "use plsql stored procedures returning a ref cursor..." for more complex queries???

umm, would it not be easier to tell coders "use plsql stored procedures
returning a ref cursor and make sure to BIND the call to the procedure???"

(rhetorical question, that is the correct approach here)

do not use dynamic sql unless there isn't any other way to do it -- here there
clearly is.

don't build a query building, build (develop) better programmers who follow the
corporate standards.

Thanks, I have ordered your book.
But, I create a plsql stored package returning a ref cursor to be used by the coders, and all the sql stmt will be there and not in the client.
Please check the code and tell me your opinions.
CREATE OR REPLACE PACKAGE PKG_RC
AS
-- for all sql stmt without WHERE condition
PROCEDURE SP_RC
( prefcur OUT SYS_REFCURSOR,
prc in pls_integer
);
-- for all sql stmt with WHERE condition
PROCEDURE SP_RCF
( prefcur OUT SYS_REFCURSOR,
prc in pls_integer,
pparam1 in varchar2 default null,
pparam2 in varchar2 default null,
...
);
-- for all sql stmt with WHERE condition and joins
PROCEDURE SP_RCJ
( prefcur OUT SYS_REFCURSOR,
prc in pls_integer,
pparam1 in varchar2 default null,
pparam2 in varchar2 default null
...
);
END PKG_RC;
/

CREATE OR REPLACE PACKAGE BODY PKG_RC
AS
PROCEDURE SP_RC
( prefcur OUT SYS_REFCURSOR,
prc in pls_integer
) IS
BEGIN
CASE WHEN prc=1 THEN
OPEN prefcur FOR
select ...;
WHEN prc=2 THEN
OPEN prefcur FOR
select ...order by ...;
WHEN prc=3 THEN
.
.
.
END CASE;
END SP_RC;
PROCEDURE SP_RCF
( prefcur OUT SYS_REFCURSOR,
prc in pls_integer,
pparam in varchar2 default null
) IS
BEGIN
CASE WHEN prc=1 THEN
OPEN prefcur FOR
'select ... where column = :vb1'
using pparam;
WHEN prc=2 THEN
OPEN prefcur FOR
select ... where codusu = user;
END CASE;
END SP_RCF;
PROCEDURE SP_RCJ
( prefcur OUT SYS_REFCURSOR,
prc in pls_integer,
pparam1 in varchar2 default null,
pparam2 in varchar2 default null
) IS
BEGIN
CASE WHEN prc=1 THEN
OPEN prefcur FOR
'select ... from ... where a.col = b.col and a.col = :vb1';
...
END CASE;
END SP_RCJ;
END PKG_RC;
/

Is these the right way to do it? Is there another better way to store the queries (not views!) and return ref cursors when called them?

I don't understand how to identified the cursor I had to open without a “when case” or an “if … then”, using it I only had to call it like “exec pkg_rc.sp_rcf(:refcur,3,:bv1,:bv2,…);”, and I know that I'll get ref cursor that I want for.

You said "...why not separate entry points.", How is it?

So, I create N meaningfully named functions that open a single cursor each on a package by owner? or in stored functions (I thing package)?.

Is it the same for update stmts and delete stmts to take it out from the client code and store it in the database?

and to insert stmts use a package like pkg.do_insert

Hi Tom
I have a search functionality, there is 7 dropdowns, the user can select one dropdown or more than one. Each dropdown contains 35 fields all these comes from 8 tables.
Basing on his/her search cryteria we need to fetch the results.Here we dont know the even the tables from which we need to do the searching. so everything is dynamic tables and as well as fields, this problem is killing me i tried so many alternatives but none canot help me to resolve this problem.

Thanks,
shreecanth

Tom, you tell me that I "...should provide TRANSACTIONS, not simple little not-useful insert/update/delete routines", I have never work with this, so I have some questions about and I hope you can help me --as always!

Tell we have a schema with 10 tables, so
A) I should have a package with about 30 meaningful named transactions -- insert/update/delete?
If only one table has constant transactions and the others has occasionally, Do I should create a new package to hold the transactions for that table?
It the above is right, I thing that the way to do it well is to have a package per table with their own transactions?
I’m talking about that transactions that only do insert/update/delete with some validations, not those transactions that do some process data.
B) One of those tables has 22 columns that can be updated, I have seen that developers code an update like “update table1 set a:=nvl(p_a, a), b:= nvl(p_b, b), c:= nvl(p_c, c), …where key = p_key,”, Is it the only way to code a transaction where I can update one, tow, tree or more columns at a time? –without using dynamic sql

Thanks Tom, I get it!

Could you give me an example of how you do "... a transaction with a set of
inputs and an option set of outputs" --bout the above b)

Tom,
the developers wants to code all transactions where they need to update a row like "update table1 set a:=nvl(p_a, a), b:= nvl(p_b, b), c:= nvl(p_c, c), ... where key = p_key;", but I don't feel that this is the way to go, I thing that the right way is to modify only the modified columns from a row(s), what do you think?

I only can see a do_update(table_name, list_columns, list_values, list_pk, list_pk_values) routine that is called in a transaction where the end user can modify one, tow, three or more columns at the same time --using dynamic sql, but I don't want to code that until I find that there is no other way,

If the better way to do that is the developers way, I’ll do it,
If it is using a do_update() routine --because I don’t know which column(s) the user may modify,
If you have a better way I appreciate if you can give me an example

I assume that you are somehow fetching the values and displaying them to the user. When they want to save them, all these are passed back. In general, you need to pass all the values back, even the NULL ones. In addition, your update statement cannot, in general, use the NVL. What if the user set the value to NULL, when it had a value? The NVL will leave the DB value there, when the intent was to set it to null.

The way I do this is to requery the database (hopefully you have a good PK) into a record. Then there is a big block of
IF statements, and a conditional update.

in pseudocode...

fetch all columns from table into staging_rec with a cursor for update
set flag-that-data-changed = false;

IF (staging_rec.col is null and newvalue is not null) or
(staging_rec.col is not null and newvalue is null) or
staging_rec.col <> newvalue) then
staging_rec.col := newvalue;
flag-that-data-changed :=true;
end if
<repeat for each column>

if flag-that-data-changed then
update table using staging_record where current of cursor;
end if

This is just about the same amount of work at run-time as the update-with-nvl, but it has the distinct advantage of being correct.

The query that would have been done in the update was still done, a few more comparisons were perhaps done (NVL vs a 3-part if-statement on each column). However, if nothing was changed, no update was even attempted. Big savings there.

If you are using Oracle9 or later, you can use the

update <TABLE> set row = <RECORD> where current of <CURSOR>

syntax and still only have one statement in the cache.

The only thing you have to do is type and verify and double check that every column is compared [hint: put them in alpha order]. A bit tedious, but that's the price.

Dan, can you share us a little case with a three columns table where you implement that transactional api. That pseudocode is implemented at the database or client? How can I send a cursor (new values columns) to this procedure (transactional api) from the client (.NET)?
Tom, your suggestions are welcome