54: rc number; -- return code
55: found number;
56: begin
57: found := 0;
58: stmt_cursor := dbms_sql.open_cursor;
59: sys.dbms_sys_sql.parse_as_user(stmt_cursor,
60: 'begin
61: select 1
62: into :found
65: and owner = upper(:item_owner);
66: exception
67: when others then
68: :found := 0;
69: end;', dbms_sql.v7);
70: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
71: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
72: dbms_sql.bind_variable(stmt_cursor, ':found', found);
73: rc := dbms_sql.execute(stmt_cursor);
66: exception
67: when others then
68: :found := 0;
69: end;', dbms_sql.v7);
70: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
71: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
72: dbms_sql.bind_variable(stmt_cursor, ':found', found);
73: rc := dbms_sql.execute(stmt_cursor);
74: dbms_sql.variable_value(stmt_cursor, ':found', found);
67: when others then
68: :found := 0;
69: end;', dbms_sql.v7);
70: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
71: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
72: dbms_sql.bind_variable(stmt_cursor, ':found', found);
73: rc := dbms_sql.execute(stmt_cursor);
74: dbms_sql.variable_value(stmt_cursor, ':found', found);
75: dbms_sql.close_cursor(stmt_cursor);
68: :found := 0;
69: end;', dbms_sql.v7);
70: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
71: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
72: dbms_sql.bind_variable(stmt_cursor, ':found', found);
73: rc := dbms_sql.execute(stmt_cursor);
74: dbms_sql.variable_value(stmt_cursor, ':found', found);
75: dbms_sql.close_cursor(stmt_cursor);
76: return(found <> 0);
69: end;', dbms_sql.v7);
70: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
71: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
72: dbms_sql.bind_variable(stmt_cursor, ':found', found);
73: rc := dbms_sql.execute(stmt_cursor);
74: dbms_sql.variable_value(stmt_cursor, ':found', found);
75: dbms_sql.close_cursor(stmt_cursor);
76: return(found <> 0);
77: end is_table;
70: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
71: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
72: dbms_sql.bind_variable(stmt_cursor, ':found', found);
73: rc := dbms_sql.execute(stmt_cursor);
74: dbms_sql.variable_value(stmt_cursor, ':found', found);
75: dbms_sql.close_cursor(stmt_cursor);
76: return(found <> 0);
77: end is_table;
78:
71: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
72: dbms_sql.bind_variable(stmt_cursor, ':found', found);
73: rc := dbms_sql.execute(stmt_cursor);
74: dbms_sql.variable_value(stmt_cursor, ':found', found);
75: dbms_sql.close_cursor(stmt_cursor);
76: return(found <> 0);
77: end is_table;
78:
79: /********************************************************************/
87: rc number; -- return code
88: found number;
89: begin
90: found := 0;
91: stmt_cursor := dbms_sql.open_cursor;
92: sys.dbms_sys_sql.parse_as_user(stmt_cursor,
93: 'begin
94: select 1
95: into :found
98: and owner = upper(:item_owner);
99: exception
100: when others then
101: :found := 0;
102: end;', dbms_sql.v7);
103: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
104: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
105: dbms_sql.bind_variable(stmt_cursor, ':found', found);
106: rc := dbms_sql.execute(stmt_cursor);
99: exception
100: when others then
101: :found := 0;
102: end;', dbms_sql.v7);
103: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
104: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
105: dbms_sql.bind_variable(stmt_cursor, ':found', found);
106: rc := dbms_sql.execute(stmt_cursor);
107: dbms_sql.variable_value(stmt_cursor, ':found', found);
100: when others then
101: :found := 0;
102: end;', dbms_sql.v7);
103: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
104: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
105: dbms_sql.bind_variable(stmt_cursor, ':found', found);
106: rc := dbms_sql.execute(stmt_cursor);
107: dbms_sql.variable_value(stmt_cursor, ':found', found);
108: dbms_sql.close_cursor(stmt_cursor);
101: :found := 0;
102: end;', dbms_sql.v7);
103: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
104: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
105: dbms_sql.bind_variable(stmt_cursor, ':found', found);
106: rc := dbms_sql.execute(stmt_cursor);
107: dbms_sql.variable_value(stmt_cursor, ':found', found);
108: dbms_sql.close_cursor(stmt_cursor);
109: return(found <> 0);
102: end;', dbms_sql.v7);
103: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
104: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
105: dbms_sql.bind_variable(stmt_cursor, ':found', found);
106: rc := dbms_sql.execute(stmt_cursor);
107: dbms_sql.variable_value(stmt_cursor, ':found', found);
108: dbms_sql.close_cursor(stmt_cursor);
109: return(found <> 0);
110: end is_view;
103: dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
104: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
105: dbms_sql.bind_variable(stmt_cursor, ':found', found);
106: rc := dbms_sql.execute(stmt_cursor);
107: dbms_sql.variable_value(stmt_cursor, ':found', found);
108: dbms_sql.close_cursor(stmt_cursor);
109: return(found <> 0);
110: end is_view;
111:
104: dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
105: dbms_sql.bind_variable(stmt_cursor, ':found', found);
106: rc := dbms_sql.execute(stmt_cursor);
107: dbms_sql.variable_value(stmt_cursor, ':found', found);
108: dbms_sql.close_cursor(stmt_cursor);
109: return(found <> 0);
110: end is_view;
111:
112: /**************************************/
124: name all_synonyms.table_name%type;
125: owner all_synonyms.table_owner%type;
126: db_link all_synonyms.db_link%type;
127: begin
128: stmt_cursor := dbms_sql.open_cursor;
129: sys.dbms_sys_sql.parse_as_user(stmt_cursor,
130: 'begin
131: select table_name, table_owner, db_link
132: into :name, :owner, :db_link
137: when others then
138: :name := NULL;
139: :owner := NULL;
140: :db_link := NULL;
141: end;', dbms_sql.v7);
142: dbms_sql.bind_variable(stmt_cursor, ':csynonym', csynonym);
143: dbms_sql.bind_variable(stmt_cursor, ':cschema', cschema);
144: dbms_sql.bind_variable(stmt_cursor, ':name', name, 2000);
145: dbms_sql.bind_variable(stmt_cursor, ':owner', owner, 2000);
138: :name := NULL;
139: :owner := NULL;
140: :db_link := NULL;
141: end;', dbms_sql.v7);
142: dbms_sql.bind_variable(stmt_cursor, ':csynonym', csynonym);
143: dbms_sql.bind_variable(stmt_cursor, ':cschema', cschema);
144: dbms_sql.bind_variable(stmt_cursor, ':name', name, 2000);
145: dbms_sql.bind_variable(stmt_cursor, ':owner', owner, 2000);
146: dbms_sql.bind_variable(stmt_cursor, ':db_link', db_link, 2000);
139: :owner := NULL;
140: :db_link := NULL;
141: end;', dbms_sql.v7);
142: dbms_sql.bind_variable(stmt_cursor, ':csynonym', csynonym);
143: dbms_sql.bind_variable(stmt_cursor, ':cschema', cschema);
144: dbms_sql.bind_variable(stmt_cursor, ':name', name, 2000);
145: dbms_sql.bind_variable(stmt_cursor, ':owner', owner, 2000);
146: dbms_sql.bind_variable(stmt_cursor, ':db_link', db_link, 2000);
147: rc := dbms_sql.execute(stmt_cursor);
140: :db_link := NULL;
141: end;', dbms_sql.v7);
142: dbms_sql.bind_variable(stmt_cursor, ':csynonym', csynonym);
143: dbms_sql.bind_variable(stmt_cursor, ':cschema', cschema);
144: dbms_sql.bind_variable(stmt_cursor, ':name', name, 2000);
145: dbms_sql.bind_variable(stmt_cursor, ':owner', owner, 2000);
146: dbms_sql.bind_variable(stmt_cursor, ':db_link', db_link, 2000);
147: rc := dbms_sql.execute(stmt_cursor);
148: dbms_sql.variable_value(stmt_cursor, ':name', name);
141: end;', dbms_sql.v7);
142: dbms_sql.bind_variable(stmt_cursor, ':csynonym', csynonym);
143: dbms_sql.bind_variable(stmt_cursor, ':cschema', cschema);
144: dbms_sql.bind_variable(stmt_cursor, ':name', name, 2000);
145: dbms_sql.bind_variable(stmt_cursor, ':owner', owner, 2000);
146: dbms_sql.bind_variable(stmt_cursor, ':db_link', db_link, 2000);
147: rc := dbms_sql.execute(stmt_cursor);
148: dbms_sql.variable_value(stmt_cursor, ':name', name);
149: dbms_sql.variable_value(stmt_cursor, ':owner', owner);
142: dbms_sql.bind_variable(stmt_cursor, ':csynonym', csynonym);
143: dbms_sql.bind_variable(stmt_cursor, ':cschema', cschema);
144: dbms_sql.bind_variable(stmt_cursor, ':name', name, 2000);
145: dbms_sql.bind_variable(stmt_cursor, ':owner', owner, 2000);
146: dbms_sql.bind_variable(stmt_cursor, ':db_link', db_link, 2000);
147: rc := dbms_sql.execute(stmt_cursor);
148: dbms_sql.variable_value(stmt_cursor, ':name', name);
149: dbms_sql.variable_value(stmt_cursor, ':owner', owner);
150: dbms_sql.variable_value(stmt_cursor, ':db_link', db_link);
143: dbms_sql.bind_variable(stmt_cursor, ':cschema', cschema);
144: dbms_sql.bind_variable(stmt_cursor, ':name', name, 2000);
145: dbms_sql.bind_variable(stmt_cursor, ':owner', owner, 2000);
146: dbms_sql.bind_variable(stmt_cursor, ':db_link', db_link, 2000);
147: rc := dbms_sql.execute(stmt_cursor);
148: dbms_sql.variable_value(stmt_cursor, ':name', name);
149: dbms_sql.variable_value(stmt_cursor, ':owner', owner);
150: dbms_sql.variable_value(stmt_cursor, ':db_link', db_link);
151: dbms_sql.close_cursor(stmt_cursor);
144: dbms_sql.bind_variable(stmt_cursor, ':name', name, 2000);
145: dbms_sql.bind_variable(stmt_cursor, ':owner', owner, 2000);
146: dbms_sql.bind_variable(stmt_cursor, ':db_link', db_link, 2000);
147: rc := dbms_sql.execute(stmt_cursor);
148: dbms_sql.variable_value(stmt_cursor, ':name', name);
149: dbms_sql.variable_value(stmt_cursor, ':owner', owner);
150: dbms_sql.variable_value(stmt_cursor, ':db_link', db_link);
151: dbms_sql.close_cursor(stmt_cursor);
152: o_name := name;
145: dbms_sql.bind_variable(stmt_cursor, ':owner', owner, 2000);
146: dbms_sql.bind_variable(stmt_cursor, ':db_link', db_link, 2000);
147: rc := dbms_sql.execute(stmt_cursor);
148: dbms_sql.variable_value(stmt_cursor, ':name', name);
149: dbms_sql.variable_value(stmt_cursor, ':owner', owner);
150: dbms_sql.variable_value(stmt_cursor, ':db_link', db_link);
151: dbms_sql.close_cursor(stmt_cursor);
152: o_name := name;
153: o_owner := owner;
146: dbms_sql.bind_variable(stmt_cursor, ':db_link', db_link, 2000);
147: rc := dbms_sql.execute(stmt_cursor);
148: dbms_sql.variable_value(stmt_cursor, ':name', name);
149: dbms_sql.variable_value(stmt_cursor, ':owner', owner);
150: dbms_sql.variable_value(stmt_cursor, ':db_link', db_link);
151: dbms_sql.close_cursor(stmt_cursor);
152: o_name := name;
153: o_owner := owner;
154: o_db_link := db_link;
147: rc := dbms_sql.execute(stmt_cursor);
148: dbms_sql.variable_value(stmt_cursor, ':name', name);
149: dbms_sql.variable_value(stmt_cursor, ':owner', owner);
150: dbms_sql.variable_value(stmt_cursor, ':db_link', db_link);
151: dbms_sql.close_cursor(stmt_cursor);
152: o_name := name;
153: o_owner := owner;
154: o_db_link := db_link;
155: return(name is NOT NULL or owner is NOT NULL or db_link is NOT NULL);
154: o_db_link := db_link;
155: return(name is NOT NULL or owner is NOT NULL or db_link is NOT NULL);
156: exception
157: when others then
158: if dbms_sql.is_open(stmt_cursor) then
159: dbms_sql.close_cursor(stmt_cursor);
160: end if;
161:
162: raise;
155: return(name is NOT NULL or owner is NOT NULL or db_link is NOT NULL);
156: exception
157: when others then
158: if dbms_sql.is_open(stmt_cursor) then
159: dbms_sql.close_cursor(stmt_cursor);
160: end if;
161:
162: raise;
163: end get_synonym_defn;
176: procowner varchar2(255);
177: procnamel constant number := 255;
178: procname varchar2(255);
179: begin
180: stmt_cursor := dbms_sql.open_cursor;
181: /* resolve name and compose the real package.procedure */
182: sys.dbms_sys_sql.parse_as_user(stmt_cursor,
183: 'declare
184: part1 varchar2(255);
203: exception
204: when others then
205: :procowner := NULL;
206: :procname := NULL;
207: end;', dbms_sql.v7
208: );
209: dbms_sql.bind_variable(stmt_cursor, ':cname', cname);
210: dbms_sql.bind_variable(stmt_cursor, ':procowner', procowner, procownerl);
211: dbms_sql.bind_variable(stmt_cursor, ':procname', procname, procnamel);
205: :procowner := NULL;
206: :procname := NULL;
207: end;', dbms_sql.v7
208: );
209: dbms_sql.bind_variable(stmt_cursor, ':cname', cname);
210: dbms_sql.bind_variable(stmt_cursor, ':procowner', procowner, procownerl);
211: dbms_sql.bind_variable(stmt_cursor, ':procname', procname, procnamel);
212: rc := dbms_sql.execute(stmt_cursor);
213: dbms_sql.variable_value(stmt_cursor, ':procowner', procowner);
206: :procname := NULL;
207: end;', dbms_sql.v7
208: );
209: dbms_sql.bind_variable(stmt_cursor, ':cname', cname);
210: dbms_sql.bind_variable(stmt_cursor, ':procowner', procowner, procownerl);
211: dbms_sql.bind_variable(stmt_cursor, ':procname', procname, procnamel);
212: rc := dbms_sql.execute(stmt_cursor);
213: dbms_sql.variable_value(stmt_cursor, ':procowner', procowner);
214: dbms_sql.variable_value(stmt_cursor, ':procname', procname);
207: end;', dbms_sql.v7
208: );
209: dbms_sql.bind_variable(stmt_cursor, ':cname', cname);
210: dbms_sql.bind_variable(stmt_cursor, ':procowner', procowner, procownerl);
211: dbms_sql.bind_variable(stmt_cursor, ':procname', procname, procnamel);
212: rc := dbms_sql.execute(stmt_cursor);
213: dbms_sql.variable_value(stmt_cursor, ':procowner', procowner);
214: dbms_sql.variable_value(stmt_cursor, ':procname', procname);
215: dbms_sql.close_cursor(stmt_cursor);
208: );
209: dbms_sql.bind_variable(stmt_cursor, ':cname', cname);
210: dbms_sql.bind_variable(stmt_cursor, ':procowner', procowner, procownerl);
211: dbms_sql.bind_variable(stmt_cursor, ':procname', procname, procnamel);
212: rc := dbms_sql.execute(stmt_cursor);
213: dbms_sql.variable_value(stmt_cursor, ':procowner', procowner);
214: dbms_sql.variable_value(stmt_cursor, ':procname', procname);
215: dbms_sql.close_cursor(stmt_cursor);
216: o_procowner := procowner;
209: dbms_sql.bind_variable(stmt_cursor, ':cname', cname);
210: dbms_sql.bind_variable(stmt_cursor, ':procowner', procowner, procownerl);
211: dbms_sql.bind_variable(stmt_cursor, ':procname', procname, procnamel);
212: rc := dbms_sql.execute(stmt_cursor);
213: dbms_sql.variable_value(stmt_cursor, ':procowner', procowner);
214: dbms_sql.variable_value(stmt_cursor, ':procname', procname);
215: dbms_sql.close_cursor(stmt_cursor);
216: o_procowner := procowner;
217: o_procname := procname;
210: dbms_sql.bind_variable(stmt_cursor, ':procowner', procowner, procownerl);
211: dbms_sql.bind_variable(stmt_cursor, ':procname', procname, procnamel);
212: rc := dbms_sql.execute(stmt_cursor);
213: dbms_sql.variable_value(stmt_cursor, ':procowner', procowner);
214: dbms_sql.variable_value(stmt_cursor, ':procname', procname);
215: dbms_sql.close_cursor(stmt_cursor);
216: o_procowner := procowner;
217: o_procname := procname;
218: end name_resolve;
211: dbms_sql.bind_variable(stmt_cursor, ':procname', procname, procnamel);
212: rc := dbms_sql.execute(stmt_cursor);
213: dbms_sql.variable_value(stmt_cursor, ':procowner', procowner);
214: dbms_sql.variable_value(stmt_cursor, ':procname', procname);
215: dbms_sql.close_cursor(stmt_cursor);
216: o_procowner := procowner;
217: o_procname := procname;
218: end name_resolve;
219:
236: nm := substr(n, 1, (rc - 1));
237: else
238: nm := n;
239: end if;
240: stmt_cursor := dbms_sql.open_cursor;
241: sys.dbms_sys_sql.parse_as_user(stmt_cursor,
242: 'select line, text
243: from all_source
244: where name = upper(:n)
242: 'select line, text
243: from all_source
244: where name = upper(:n)
245: and owner = upper(:o)
246: order by type, line', dbms_sql.v7);
247: dbms_sql.bind_variable(stmt_cursor, ':o', o);
248: dbms_sql.bind_variable(stmt_cursor, ':n', nm);
249: dbms_sql.define_column(stmt_cursor, 1, line);
250: dbms_sql.define_column(stmt_cursor, 2, text, 2000);
243: from all_source
244: where name = upper(:n)
245: and owner = upper(:o)
246: order by type, line', dbms_sql.v7);
247: dbms_sql.bind_variable(stmt_cursor, ':o', o);
248: dbms_sql.bind_variable(stmt_cursor, ':n', nm);
249: dbms_sql.define_column(stmt_cursor, 1, line);
250: dbms_sql.define_column(stmt_cursor, 2, text, 2000);
251: rc := dbms_sql.execute(stmt_cursor);
244: where name = upper(:n)
245: and owner = upper(:o)
246: order by type, line', dbms_sql.v7);
247: dbms_sql.bind_variable(stmt_cursor, ':o', o);
248: dbms_sql.bind_variable(stmt_cursor, ':n', nm);
249: dbms_sql.define_column(stmt_cursor, 1, line);
250: dbms_sql.define_column(stmt_cursor, 2, text, 2000);
251: rc := dbms_sql.execute(stmt_cursor);
252: return(stmt_cursor);
245: and owner = upper(:o)
246: order by type, line', dbms_sql.v7);
247: dbms_sql.bind_variable(stmt_cursor, ':o', o);
248: dbms_sql.bind_variable(stmt_cursor, ':n', nm);
249: dbms_sql.define_column(stmt_cursor, 1, line);
250: dbms_sql.define_column(stmt_cursor, 2, text, 2000);
251: rc := dbms_sql.execute(stmt_cursor);
252: return(stmt_cursor);
253: end open_source_cursor;
246: order by type, line', dbms_sql.v7);
247: dbms_sql.bind_variable(stmt_cursor, ':o', o);
248: dbms_sql.bind_variable(stmt_cursor, ':n', nm);
249: dbms_sql.define_column(stmt_cursor, 1, line);
250: dbms_sql.define_column(stmt_cursor, 2, text, 2000);
251: rc := dbms_sql.execute(stmt_cursor);
252: return(stmt_cursor);
253: end open_source_cursor;
254:
247: dbms_sql.bind_variable(stmt_cursor, ':o', o);
248: dbms_sql.bind_variable(stmt_cursor, ':n', nm);
249: dbms_sql.define_column(stmt_cursor, 1, line);
250: dbms_sql.define_column(stmt_cursor, 2, text, 2000);
251: rc := dbms_sql.execute(stmt_cursor);
252: return(stmt_cursor);
253: end open_source_cursor;
254:
255: /************************************************************************/
260: line out number,
261: text out varchar2
262: ) return number is
263: begin
264: if (stmt_cursor >= 0 and dbms_sql.fetch_rows(stmt_cursor) > 0)
265: then
266: dbms_sql.column_value(stmt_cursor, 1, line);
267: dbms_sql.column_value(stmt_cursor, 2, text);
268: return(0);
262: ) return number is
263: begin
264: if (stmt_cursor >= 0 and dbms_sql.fetch_rows(stmt_cursor) > 0)
265: then
266: dbms_sql.column_value(stmt_cursor, 1, line);
267: dbms_sql.column_value(stmt_cursor, 2, text);
268: return(0);
269: else
270: return(-1);
263: begin
264: if (stmt_cursor >= 0 and dbms_sql.fetch_rows(stmt_cursor) > 0)
265: then
266: dbms_sql.column_value(stmt_cursor, 1, line);
267: dbms_sql.column_value(stmt_cursor, 2, text);
268: return(0);
269: else
270: return(-1);
271: end if;
275: /* Function to close cursor for all_source */
276: /*******************************************/
277: procedure close_source_cursor(stmt_cursor in out number) is
278: begin
279: dbms_sql.close_cursor(stmt_cursor);
280: end close_source_cursor;
281:
282: /******************************************************************/
283: /* Procedure to link back to the PL/SQL source for your procedure */
656: item_owner varchar2(255);
657:
658: dummy char(1);
659: begin
660: stmt_cursor := dbms_sql.open_cursor;
661: sys.dbms_sys_sql.parse_as_user(stmt_cursor,
662: 'begin
663: dbms_utility.name_tokenize(:cobject,
664: :a, :b, :c, :dblink, :next_pos);
661: sys.dbms_sys_sql.parse_as_user(stmt_cursor,
662: 'begin
663: dbms_utility.name_tokenize(:cobject,
664: :a, :b, :c, :dblink, :next_pos);
665: end;', dbms_sql.v7);
666: dbms_sql.bind_variable(stmt_cursor, ':cobject', cobject);
667: dbms_sql.bind_variable(stmt_cursor, ':a', a, al);
668: dbms_sql.bind_variable(stmt_cursor, ':b', b, bl);
669: dbms_sql.bind_variable(stmt_cursor, ':c', c, cl);
662: 'begin
663: dbms_utility.name_tokenize(:cobject,
664: :a, :b, :c, :dblink, :next_pos);
665: end;', dbms_sql.v7);
666: dbms_sql.bind_variable(stmt_cursor, ':cobject', cobject);
667: dbms_sql.bind_variable(stmt_cursor, ':a', a, al);
668: dbms_sql.bind_variable(stmt_cursor, ':b', b, bl);
669: dbms_sql.bind_variable(stmt_cursor, ':c', c, cl);
670: dbms_sql.bind_variable(stmt_cursor, ':dblink', dblink, dblinkl);
663: dbms_utility.name_tokenize(:cobject,
664: :a, :b, :c, :dblink, :next_pos);
665: end;', dbms_sql.v7);
666: dbms_sql.bind_variable(stmt_cursor, ':cobject', cobject);
667: dbms_sql.bind_variable(stmt_cursor, ':a', a, al);
668: dbms_sql.bind_variable(stmt_cursor, ':b', b, bl);
669: dbms_sql.bind_variable(stmt_cursor, ':c', c, cl);
670: dbms_sql.bind_variable(stmt_cursor, ':dblink', dblink, dblinkl);
671: dbms_sql.bind_variable(stmt_cursor, ':next_pos', next_pos);
664: :a, :b, :c, :dblink, :next_pos);
665: end;', dbms_sql.v7);
666: dbms_sql.bind_variable(stmt_cursor, ':cobject', cobject);
667: dbms_sql.bind_variable(stmt_cursor, ':a', a, al);
668: dbms_sql.bind_variable(stmt_cursor, ':b', b, bl);
669: dbms_sql.bind_variable(stmt_cursor, ':c', c, cl);
670: dbms_sql.bind_variable(stmt_cursor, ':dblink', dblink, dblinkl);
671: dbms_sql.bind_variable(stmt_cursor, ':next_pos', next_pos);
672: rc := dbms_sql.execute(stmt_cursor);
665: end;', dbms_sql.v7);
666: dbms_sql.bind_variable(stmt_cursor, ':cobject', cobject);
667: dbms_sql.bind_variable(stmt_cursor, ':a', a, al);
668: dbms_sql.bind_variable(stmt_cursor, ':b', b, bl);
669: dbms_sql.bind_variable(stmt_cursor, ':c', c, cl);
670: dbms_sql.bind_variable(stmt_cursor, ':dblink', dblink, dblinkl);
671: dbms_sql.bind_variable(stmt_cursor, ':next_pos', next_pos);
672: rc := dbms_sql.execute(stmt_cursor);
673: dbms_sql.variable_value(stmt_cursor, ':a', a);
666: dbms_sql.bind_variable(stmt_cursor, ':cobject', cobject);
667: dbms_sql.bind_variable(stmt_cursor, ':a', a, al);
668: dbms_sql.bind_variable(stmt_cursor, ':b', b, bl);
669: dbms_sql.bind_variable(stmt_cursor, ':c', c, cl);
670: dbms_sql.bind_variable(stmt_cursor, ':dblink', dblink, dblinkl);
671: dbms_sql.bind_variable(stmt_cursor, ':next_pos', next_pos);
672: rc := dbms_sql.execute(stmt_cursor);
673: dbms_sql.variable_value(stmt_cursor, ':a', a);
674: dbms_sql.variable_value(stmt_cursor, ':b', b);
667: dbms_sql.bind_variable(stmt_cursor, ':a', a, al);
668: dbms_sql.bind_variable(stmt_cursor, ':b', b, bl);
669: dbms_sql.bind_variable(stmt_cursor, ':c', c, cl);
670: dbms_sql.bind_variable(stmt_cursor, ':dblink', dblink, dblinkl);
671: dbms_sql.bind_variable(stmt_cursor, ':next_pos', next_pos);
672: rc := dbms_sql.execute(stmt_cursor);
673: dbms_sql.variable_value(stmt_cursor, ':a', a);
674: dbms_sql.variable_value(stmt_cursor, ':b', b);
675: dbms_sql.variable_value(stmt_cursor, ':c', c);
668: dbms_sql.bind_variable(stmt_cursor, ':b', b, bl);
669: dbms_sql.bind_variable(stmt_cursor, ':c', c, cl);
670: dbms_sql.bind_variable(stmt_cursor, ':dblink', dblink, dblinkl);
671: dbms_sql.bind_variable(stmt_cursor, ':next_pos', next_pos);
672: rc := dbms_sql.execute(stmt_cursor);
673: dbms_sql.variable_value(stmt_cursor, ':a', a);
674: dbms_sql.variable_value(stmt_cursor, ':b', b);
675: dbms_sql.variable_value(stmt_cursor, ':c', c);
676: dbms_sql.variable_value(stmt_cursor, ':dblink', dblink);
669: dbms_sql.bind_variable(stmt_cursor, ':c', c, cl);
670: dbms_sql.bind_variable(stmt_cursor, ':dblink', dblink, dblinkl);
671: dbms_sql.bind_variable(stmt_cursor, ':next_pos', next_pos);
672: rc := dbms_sql.execute(stmt_cursor);
673: dbms_sql.variable_value(stmt_cursor, ':a', a);
674: dbms_sql.variable_value(stmt_cursor, ':b', b);
675: dbms_sql.variable_value(stmt_cursor, ':c', c);
676: dbms_sql.variable_value(stmt_cursor, ':dblink', dblink);
677: dbms_sql.variable_value(stmt_cursor, ':next_pos', next_pos);
670: dbms_sql.bind_variable(stmt_cursor, ':dblink', dblink, dblinkl);
671: dbms_sql.bind_variable(stmt_cursor, ':next_pos', next_pos);
672: rc := dbms_sql.execute(stmt_cursor);
673: dbms_sql.variable_value(stmt_cursor, ':a', a);
674: dbms_sql.variable_value(stmt_cursor, ':b', b);
675: dbms_sql.variable_value(stmt_cursor, ':c', c);
676: dbms_sql.variable_value(stmt_cursor, ':dblink', dblink);
677: dbms_sql.variable_value(stmt_cursor, ':next_pos', next_pos);
678: dbms_sql.close_cursor(stmt_cursor);
671: dbms_sql.bind_variable(stmt_cursor, ':next_pos', next_pos);
672: rc := dbms_sql.execute(stmt_cursor);
673: dbms_sql.variable_value(stmt_cursor, ':a', a);
674: dbms_sql.variable_value(stmt_cursor, ':b', b);
675: dbms_sql.variable_value(stmt_cursor, ':c', c);
676: dbms_sql.variable_value(stmt_cursor, ':dblink', dblink);
677: dbms_sql.variable_value(stmt_cursor, ':next_pos', next_pos);
678: dbms_sql.close_cursor(stmt_cursor);
679:
672: rc := dbms_sql.execute(stmt_cursor);
673: dbms_sql.variable_value(stmt_cursor, ':a', a);
674: dbms_sql.variable_value(stmt_cursor, ':b', b);
675: dbms_sql.variable_value(stmt_cursor, ':c', c);
676: dbms_sql.variable_value(stmt_cursor, ':dblink', dblink);
677: dbms_sql.variable_value(stmt_cursor, ':next_pos', next_pos);
678: dbms_sql.close_cursor(stmt_cursor);
679:
680: if (c is not null)
673: dbms_sql.variable_value(stmt_cursor, ':a', a);
674: dbms_sql.variable_value(stmt_cursor, ':b', b);
675: dbms_sql.variable_value(stmt_cursor, ':c', c);
676: dbms_sql.variable_value(stmt_cursor, ':dblink', dblink);
677: dbms_sql.variable_value(stmt_cursor, ':next_pos', next_pos);
678: dbms_sql.close_cursor(stmt_cursor);
679:
680: if (c is not null)
681: then
674: dbms_sql.variable_value(stmt_cursor, ':b', b);
675: dbms_sql.variable_value(stmt_cursor, ':c', c);
676: dbms_sql.variable_value(stmt_cursor, ':dblink', dblink);
677: dbms_sql.variable_value(stmt_cursor, ':next_pos', next_pos);
678: dbms_sql.close_cursor(stmt_cursor);
679:
680: if (c is not null)
681: then
682: /* For a table, we should see AT MOST owner.table */
769:
770: col_counter := 0;
771: if (ccolumns = '*')
772: then
773: col_cursor := dbms_sql.open_cursor;
774: sys.dbms_sys_sql.parse_as_user(col_cursor,
775: 'select column_name, data_type, character_set_name '||
776: 'from all_tab_columns '||
777: 'where table_name = :t '||
776: 'from all_tab_columns '||
777: 'where table_name = :t '||
778: ' and owner = :o '||
779: 'order by column_id',
780: dbms_sql.v7);
781:
782: dbms_sql.define_column(col_cursor, 1, col_name, 255);
783: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
784: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
778: ' and owner = :o '||
779: 'order by column_id',
780: dbms_sql.v7);
781:
782: dbms_sql.define_column(col_cursor, 1, col_name, 255);
783: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
784: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
785:
786: dbms_sql.bind_variable(col_cursor, ':t', upper(table_resolved));
779: 'order by column_id',
780: dbms_sql.v7);
781:
782: dbms_sql.define_column(col_cursor, 1, col_name, 255);
783: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
784: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
785:
786: dbms_sql.bind_variable(col_cursor, ':t', upper(table_resolved));
787: dbms_sql.bind_variable(col_cursor, ':o', upper(owner_resolved));
780: dbms_sql.v7);
781:
782: dbms_sql.define_column(col_cursor, 1, col_name, 255);
783: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
784: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
785:
786: dbms_sql.bind_variable(col_cursor, ':t', upper(table_resolved));
787: dbms_sql.bind_variable(col_cursor, ':o', upper(owner_resolved));
788:
782: dbms_sql.define_column(col_cursor, 1, col_name, 255);
783: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
784: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
785:
786: dbms_sql.bind_variable(col_cursor, ':t', upper(table_resolved));
787: dbms_sql.bind_variable(col_cursor, ':o', upper(owner_resolved));
788:
789: ignore := dbms_sql.execute(col_cursor);
790:
783: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
784: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
785:
786: dbms_sql.bind_variable(col_cursor, ':t', upper(table_resolved));
787: dbms_sql.bind_variable(col_cursor, ':o', upper(owner_resolved));
788:
789: ignore := dbms_sql.execute(col_cursor);
790:
791: loop
785:
786: dbms_sql.bind_variable(col_cursor, ':t', upper(table_resolved));
787: dbms_sql.bind_variable(col_cursor, ':o', upper(owner_resolved));
788:
789: ignore := dbms_sql.execute(col_cursor);
790:
791: loop
792: if (dbms_sql.fetch_rows(col_cursor) > 0)
793: then
788:
789: ignore := dbms_sql.execute(col_cursor);
790:
791: loop
792: if (dbms_sql.fetch_rows(col_cursor) > 0)
793: then
794: dbms_sql.column_value(col_cursor, 3, col_charset);
795: dbms_sql.column_value(col_cursor, 2, col_dtype);
796: dbms_sql.column_value(col_cursor, 1, col_name);
790:
791: loop
792: if (dbms_sql.fetch_rows(col_cursor) > 0)
793: then
794: dbms_sql.column_value(col_cursor, 3, col_charset);
795: dbms_sql.column_value(col_cursor, 2, col_dtype);
796: dbms_sql.column_value(col_cursor, 1, col_name);
797:
798: col_counter := col_counter + 1;
791: loop
792: if (dbms_sql.fetch_rows(col_cursor) > 0)
793: then
794: dbms_sql.column_value(col_cursor, 3, col_charset);
795: dbms_sql.column_value(col_cursor, 2, col_dtype);
796: dbms_sql.column_value(col_cursor, 1, col_name);
797:
798: col_counter := col_counter + 1;
799: if ((col_dtype = 'VARCHAR2') or (col_dtype = 'CHAR')) and
792: if (dbms_sql.fetch_rows(col_cursor) > 0)
793: then
794: dbms_sql.column_value(col_cursor, 3, col_charset);
795: dbms_sql.column_value(col_cursor, 2, col_dtype);
796: dbms_sql.column_value(col_cursor, 1, col_name);
797:
798: col_counter := col_counter + 1;
799: if ((col_dtype = 'VARCHAR2') or (col_dtype = 'CHAR')) and
800: (col_charset = 'NCHAR_CS')
807: else
808: exit;
809: end if;
810: end loop;
811: dbms_sql.close_cursor(col_cursor);
812: else
813: col_decode := '';
814: col_in_clause := '';
815: col_loc_out := 0;
835: else
836: -- remove trailing ',' from col_in_clause
837: col_in_clause := substr(col_in_clause, 1, length(col_in_clause)-1);
838:
839: col_cursor := dbms_sql.open_cursor;
840: sys.dbms_sys_sql.parse_as_user(col_cursor,
841: 'select column_name, data_type, character_set_name, '||
842: 'decode(column_name'||col_decode||') '||
843: 'from all_tab_columns '||
844: 'where table_name = '''||upper(table_resolved)||''' '||
845: ' and owner = '''||upper(owner_resolved)||''' '||
846: ' and column_name in ('||col_in_clause||') '||
847: 'order by 3',
848: dbms_sql.v7);
849:
850: dbms_sql.define_column(col_cursor, 1, col_name, 255);
851: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
852: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
846: ' and column_name in ('||col_in_clause||') '||
847: 'order by 3',
848: dbms_sql.v7);
849:
850: dbms_sql.define_column(col_cursor, 1, col_name, 255);
851: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
852: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
853: dbms_sql.define_column(col_cursor, 4, col_num, 9);
854:
847: 'order by 3',
848: dbms_sql.v7);
849:
850: dbms_sql.define_column(col_cursor, 1, col_name, 255);
851: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
852: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
853: dbms_sql.define_column(col_cursor, 4, col_num, 9);
854:
855: ignore := dbms_sql.execute(col_cursor);
848: dbms_sql.v7);
849:
850: dbms_sql.define_column(col_cursor, 1, col_name, 255);
851: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
852: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
853: dbms_sql.define_column(col_cursor, 4, col_num, 9);
854:
855: ignore := dbms_sql.execute(col_cursor);
856:
849:
850: dbms_sql.define_column(col_cursor, 1, col_name, 255);
851: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
852: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
853: dbms_sql.define_column(col_cursor, 4, col_num, 9);
854:
855: ignore := dbms_sql.execute(col_cursor);
856:
857: new_row := (dbms_sql.fetch_rows(col_cursor) > 0);
851: dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
852: dbms_sql.define_column(col_cursor, 3, col_charset, 40);
853: dbms_sql.define_column(col_cursor, 4, col_num, 9);
854:
855: ignore := dbms_sql.execute(col_cursor);
856:
857: new_row := (dbms_sql.fetch_rows(col_cursor) > 0);
858: for i in 1..col_counter
859: loop
853: dbms_sql.define_column(col_cursor, 4, col_num, 9);
854:
855: ignore := dbms_sql.execute(col_cursor);
856:
857: new_row := (dbms_sql.fetch_rows(col_cursor) > 0);
858: for i in 1..col_counter
859: loop
860: if (new_row) AND (i = col_num)
861: then
858: for i in 1..col_counter
859: loop
860: if (new_row) AND (i = col_num)
861: then
862: dbms_sql.column_value(col_cursor, 2, col_dtype);
863: dbms_sql.column_value(col_cursor, 3, col_charset);
864: if ((col_dtype = 'VARCHAR2') or (col_dtype = 'CHAR')) and
865: (col_charset = 'NCHAR_CS')
866: then
859: loop
860: if (new_row) AND (i = col_num)
861: then
862: dbms_sql.column_value(col_cursor, 2, col_dtype);
863: dbms_sql.column_value(col_cursor, 3, col_charset);
864: if ((col_dtype = 'VARCHAR2') or (col_dtype = 'CHAR')) and
865: (col_charset = 'NCHAR_CS')
866: then
867: col_dtypes(i) := 'N' || col_dtype;
867: col_dtypes(i) := 'N' || col_dtype;
868: else
869: col_dtypes(i) := col_dtype;
870: end if;
871: new_row := (dbms_sql.fetch_rows(col_cursor) > 0);
872: else
873: col_dtypes(i) := 'VARCHAR2';
874: end if;
875: end loop;
872: else
873: col_dtypes(i) := 'VARCHAR2';
874: end if;
875: end loop;
876: dbms_sql.close_cursor(col_cursor);
877: end if;
878: end if;
879:
880: nnum_cols := col_counter;
968: loop
969: col_sizes(i) := NULL;
970: end loop;
971: else
972: crsr := dbms_sql.open_cursor;
973: sys.dbms_sys_sql.parse_as_user(crsr,
974: 'select '||ccolumns||' from '||ctable||' '||cclauses,
975: dbms_sql.v7);
976:
971: else
972: crsr := dbms_sql.open_cursor;
973: sys.dbms_sys_sql.parse_as_user(crsr,
974: 'select '||ccolumns||' from '||ctable||' '||cclauses,
975: dbms_sql.v7);
976:
977: for col_counter in 1..nnum_cols
978: loop
979: if (col_counter <= num_aliases)
985:
986: col_dtype := col_dtypes(col_counter);
987: if (col_dtype = 'VARCHAR2' OR col_dtype = 'CHAR')
988: then
989: dbms_sql.define_column(crsr, col_counter, vc_var, 2000);
990: else if (col_dtype = 'NUMBER')
991: then
992: dbms_sql.define_column(crsr,
993: col_counter, number_var);
988: then
989: dbms_sql.define_column(crsr, col_counter, vc_var, 2000);
990: else if (col_dtype = 'NUMBER')
991: then
992: dbms_sql.define_column(crsr,
993: col_counter, number_var);
994: else if (col_dtype = 'DATE')
995: then
996: dbms_sql.define_column(crsr,
992: dbms_sql.define_column(crsr,
993: col_counter, number_var);
994: else if (col_dtype = 'DATE')
995: then
996: dbms_sql.define_column(crsr,
997: col_counter, date_var);
998: else if (col_dtype = 'LONG')
999: then
1000: dbms_sql.define_column(crsr, col_counter,
996: dbms_sql.define_column(crsr,
997: col_counter, date_var);
998: else if (col_dtype = 'LONG')
999: then
1000: dbms_sql.define_column(crsr, col_counter,
1001: long_var, 32767);
1002: else if (col_dtype = 'RAW')
1003: then
1004: dbms_sql.define_column_raw(crsr, col_counter,
1000: dbms_sql.define_column(crsr, col_counter,
1001: long_var, 32767);
1002: else if (col_dtype = 'RAW')
1003: then
1004: dbms_sql.define_column_raw(crsr, col_counter,
1005: raw_var, 255);
1006: end if;
1007: end if;
1008: end if;
1009: end if;
1010: end if;
1011: end loop;
1012:
1013: ignore := dbms_sql.execute(crsr);
1014:
1015: row_count := 0;
1016: if (nrow_min is NOT NULL)
1017: then
1016: if (nrow_min is NOT NULL)
1017: then
1018: while (row_count < nrow_min - 1)
1019: loop
1020: if (dbms_sql.fetch_rows(crsr) > 0)
1021: then row_count := row_count+1;
1022: else exit;
1023: end if;
1024: end loop;
1026:
1027: while (nrow_max is NULL) or (row_count < nrow_max)
1028: loop
1029:
1030: if dbms_sql.fetch_rows(crsr) > 0
1031: then
1032: row_count := row_count+1;
1033:
1034: for col_counter in 1..nnum_cols
1035: loop
1036: col_dtype := col_dtypes(col_counter);
1037: if (col_dtype = 'VARCHAR2' OR col_dtype = 'CHAR')
1038: then
1039: dbms_sql.column_value(crsr, col_counter, vc_var);
1040: col_size := nvl(lengthb(vc_var),0);
1041: else if (col_dtype = 'NUMBER')
1042: then
1043: dbms_sql.column_value(crsr, col_counter,
1039: dbms_sql.column_value(crsr, col_counter, vc_var);
1040: col_size := nvl(lengthb(vc_var),0);
1041: else if (col_dtype = 'NUMBER')
1042: then
1043: dbms_sql.column_value(crsr, col_counter,
1044: number_var);
1045: col_size := nvl(lengthb(number_var),0);
1046: else if (col_dtype = 'DATE')
1047: then
1044: number_var);
1045: col_size := nvl(lengthb(number_var),0);
1046: else if (col_dtype = 'DATE')
1047: then
1048: dbms_sql.column_value(crsr, col_counter,
1049: date_var);
1050: col_size := nvl(lengthb(date_var),0);
1051: else if (col_dtype = 'LONG')
1052: then
1049: date_var);
1050: col_size := nvl(lengthb(date_var),0);
1051: else if (col_dtype = 'LONG')
1052: then
1053: dbms_sql.column_value(crsr, col_counter,
1054: long_var);
1055: col_size := nvl(lengthb(long_var),0);
1056: else if (col_dtype = 'RAW')
1057: then
1054: long_var);
1055: col_size := nvl(lengthb(long_var),0);
1056: else if (col_dtype = 'RAW')
1057: then
1058: dbms_sql.column_value_raw(crsr,
1059: col_counter,
1060: raw_var);
1061: col_size := nvl(lengthb(raw_var),0);
1062: else
1084: exit;
1085: end if;
1086: end loop;
1087:
1088: dbms_sql.close_cursor(crsr);
1089: end if;
1090: end;
1091:
1092: /* PRINT_HEADINGS will print the column headings for a table. */
1189:
1190: row_count number;
1191: more_rows boolean := TRUE;
1192: begin
1193: table_cursor := dbms_sql.open_cursor;
1194: sys.dbms_sys_sql.parse_as_user(table_cursor,
1195: 'select '||ccolumns||' from '||ctable||' '||cclauses,
1196: dbms_sql.v7);
1197:
1192: begin
1193: table_cursor := dbms_sql.open_cursor;
1194: sys.dbms_sys_sql.parse_as_user(table_cursor,
1195: 'select '||ccolumns||' from '||ctable||' '||cclauses,
1196: dbms_sql.v7);
1197:
1198: for col_counter in 1..nnum_cols
1199: loop
1200: col_dtype := col_dtypes(col_counter);
1198: for col_counter in 1..nnum_cols
1199: loop
1200: col_dtype := col_dtypes(col_counter);
1201: if (col_dtype = 'VARCHAR2' OR col_dtype = 'CHAR') then
1202: dbms_sql.define_column(table_cursor, col_counter, vc_var, 2000);
1203: elsif (col_dtype = 'NVARCHAR2' OR col_dtype = 'NCHAR') then
1204: dbms_sql.define_column(table_cursor, col_counter, nc_var, 2000);
1205: elsif (col_dtype = 'NUMBER') then
1206: dbms_sql.define_column(table_cursor, col_counter, number_var);
1200: col_dtype := col_dtypes(col_counter);
1201: if (col_dtype = 'VARCHAR2' OR col_dtype = 'CHAR') then
1202: dbms_sql.define_column(table_cursor, col_counter, vc_var, 2000);
1203: elsif (col_dtype = 'NVARCHAR2' OR col_dtype = 'NCHAR') then
1204: dbms_sql.define_column(table_cursor, col_counter, nc_var, 2000);
1205: elsif (col_dtype = 'NUMBER') then
1206: dbms_sql.define_column(table_cursor, col_counter, number_var);
1207: elsif (col_dtype = 'DATE') then
1208: dbms_sql.define_column(table_cursor, col_counter, date_var);
1202: dbms_sql.define_column(table_cursor, col_counter, vc_var, 2000);
1203: elsif (col_dtype = 'NVARCHAR2' OR col_dtype = 'NCHAR') then
1204: dbms_sql.define_column(table_cursor, col_counter, nc_var, 2000);
1205: elsif (col_dtype = 'NUMBER') then
1206: dbms_sql.define_column(table_cursor, col_counter, number_var);
1207: elsif (col_dtype = 'DATE') then
1208: dbms_sql.define_column(table_cursor, col_counter, date_var);
1209: elsif (col_dtype = 'LONG') then
1210: dbms_sql.define_column(table_cursor, col_counter, long_var, 32767); /* Kelly: not sure over 2000 is valid */
1204: dbms_sql.define_column(table_cursor, col_counter, nc_var, 2000);
1205: elsif (col_dtype = 'NUMBER') then
1206: dbms_sql.define_column(table_cursor, col_counter, number_var);
1207: elsif (col_dtype = 'DATE') then
1208: dbms_sql.define_column(table_cursor, col_counter, date_var);
1209: elsif (col_dtype = 'LONG') then
1210: dbms_sql.define_column(table_cursor, col_counter, long_var, 32767); /* Kelly: not sure over 2000 is valid */
1211: elsif (col_dtype = 'RAW') then
1212: dbms_sql.define_column_raw(table_cursor, col_counter, raw_var, 32767);
1206: dbms_sql.define_column(table_cursor, col_counter, number_var);
1207: elsif (col_dtype = 'DATE') then
1208: dbms_sql.define_column(table_cursor, col_counter, date_var);
1209: elsif (col_dtype = 'LONG') then
1210: dbms_sql.define_column(table_cursor, col_counter, long_var, 32767); /* Kelly: not sure over 2000 is valid */
1211: elsif (col_dtype = 'RAW') then
1212: dbms_sql.define_column_raw(table_cursor, col_counter, raw_var, 32767);
1213: end if;
1214: end loop;
1208: dbms_sql.define_column(table_cursor, col_counter, date_var);
1209: elsif (col_dtype = 'LONG') then
1210: dbms_sql.define_column(table_cursor, col_counter, long_var, 32767); /* Kelly: not sure over 2000 is valid */
1211: elsif (col_dtype = 'RAW') then
1212: dbms_sql.define_column_raw(table_cursor, col_counter, raw_var, 32767);
1213: end if;
1214: end loop;
1215:
1216: ignore := dbms_sql.execute(table_cursor);
1212: dbms_sql.define_column_raw(table_cursor, col_counter, raw_var, 32767);
1213: end if;
1214: end loop;
1215:
1216: ignore := dbms_sql.execute(table_cursor);
1217:
1218: row_count := 0;
1219: if (nrow_min is NOT NULL)
1220: then
1219: if (nrow_min is NOT NULL)
1220: then
1221: while (row_count < nrow_min - 1)
1222: loop
1223: if (dbms_sql.fetch_rows(table_cursor) > 0)
1224: then row_count := row_count+1;
1225: else exit;
1226: end if;
1227: end loop;
1229:
1230: while (nrow_max is NULL) or (row_count < nrow_max)
1231: loop
1232:
1233: if dbms_sql.fetch_rows(table_cursor) > 0
1234: then
1235: row_count := row_count+1;
1236:
1237: tableRowOpen(row_string, ntable_type);
1239: for col_counter in 1..nnum_cols
1240: loop
1241: col_dtype := col_dtypes(col_counter);
1242: if (col_dtype = 'VARCHAR2' OR col_dtype = 'CHAR') then
1243: dbms_sql.column_value(table_cursor, col_counter, vc_var);
1244: tableData(vc_var, col_sizes(col_counter), 'left', row_string, ntable_type);
1245: elsif (col_dtype = 'NVARCHAR2' OR col_dtype = 'NCHAR') then
1246: dbms_sql.column_value(table_cursor, col_counter, nc_var);
1247: tableData(nc_var, col_sizes(col_counter), 'left', row_string, ntable_type);
1242: if (col_dtype = 'VARCHAR2' OR col_dtype = 'CHAR') then
1243: dbms_sql.column_value(table_cursor, col_counter, vc_var);
1244: tableData(vc_var, col_sizes(col_counter), 'left', row_string, ntable_type);
1245: elsif (col_dtype = 'NVARCHAR2' OR col_dtype = 'NCHAR') then
1246: dbms_sql.column_value(table_cursor, col_counter, nc_var);
1247: tableData(nc_var, col_sizes(col_counter), 'left', row_string, ntable_type);
1248: elsif (col_dtype = 'NUMBER') then
1249: dbms_sql.column_value(table_cursor, col_counter,number_var);
1250: tableData(number_var, col_sizes(col_counter), 'left', row_string, ntable_type);
1245: elsif (col_dtype = 'NVARCHAR2' OR col_dtype = 'NCHAR') then
1246: dbms_sql.column_value(table_cursor, col_counter, nc_var);
1247: tableData(nc_var, col_sizes(col_counter), 'left', row_string, ntable_type);
1248: elsif (col_dtype = 'NUMBER') then
1249: dbms_sql.column_value(table_cursor, col_counter,number_var);
1250: tableData(number_var, col_sizes(col_counter), 'left', row_string, ntable_type);
1251: elsif (col_dtype = 'DATE') then
1252: dbms_sql.column_value(table_cursor, col_counter, date_var);
1253: tableData(to_nchar(date_var), col_sizes(col_counter), 'left', row_string, ntable_type);
1248: elsif (col_dtype = 'NUMBER') then
1249: dbms_sql.column_value(table_cursor, col_counter,number_var);
1250: tableData(number_var, col_sizes(col_counter), 'left', row_string, ntable_type);
1251: elsif (col_dtype = 'DATE') then
1252: dbms_sql.column_value(table_cursor, col_counter, date_var);
1253: tableData(to_nchar(date_var), col_sizes(col_counter), 'left', row_string, ntable_type);
1254: elsif (col_dtype = 'LONG') then
1255: dbms_sql.column_value(table_cursor, col_counter,long_var);
1256: tableData(long_var, col_sizes(col_counter),'left', row_string, ntable_type);
1251: elsif (col_dtype = 'DATE') then
1252: dbms_sql.column_value(table_cursor, col_counter, date_var);
1253: tableData(to_nchar(date_var), col_sizes(col_counter), 'left', row_string, ntable_type);
1254: elsif (col_dtype = 'LONG') then
1255: dbms_sql.column_value(table_cursor, col_counter,long_var);
1256: tableData(long_var, col_sizes(col_counter),'left', row_string, ntable_type);
1257: elsif (col_dtype = 'RAW') then
1258: dbms_sql.column_value_raw(table_cursor, col_counter, raw_var);
1259: tableData(raw_var, col_sizes(col_counter), 'left', row_string, ntable_type);
1254: elsif (col_dtype = 'LONG') then
1255: dbms_sql.column_value(table_cursor, col_counter,long_var);
1256: tableData(long_var, col_sizes(col_counter),'left', row_string, ntable_type);
1257: elsif (col_dtype = 'RAW') then
1258: dbms_sql.column_value_raw(table_cursor, col_counter, raw_var);
1259: tableData(raw_var, col_sizes(col_counter), 'left', row_string, ntable_type);
1260: else
1261: tableData('Not Printable', col_sizes(col_counter),'left', row_string, ntable_type);
1262: end if;
1276: tableNoData('left', row_string, nnum_cols, ntable_width, ntable_type);
1277: tableRowClose(row_string, ntable_type);
1278: else
1279: if (more_rows)
1280: then more_rows := dbms_sql.fetch_rows(table_cursor) > 0;
1281: end if;
1282: end if;
1283:
1284: dbms_sql.close_cursor(table_cursor);
1280: then more_rows := dbms_sql.fetch_rows(table_cursor) > 0;
1281: end if;
1282: end if;
1283:
1284: dbms_sql.close_cursor(table_cursor);
1285:
1286: return more_rows;
1287: end;
1288:
1301:
1302: htp.formHidden('ctable', ctable);
1303: htp.formHidden('COLS', 'DUMMY');
1304:
1305: cols_cursor := dbms_sql.open_cursor;
1306: sys.dbms_sys_sql.parse_as_user(cols_cursor,
1307: 'select column_name from all_tab_columns where table_name = upper(:t)
1308: and owner = upper(:o)',
1309: dbms_sql.v7);
1305: cols_cursor := dbms_sql.open_cursor;
1306: sys.dbms_sys_sql.parse_as_user(cols_cursor,
1307: 'select column_name from all_tab_columns where table_name = upper(:t)
1308: and owner = upper(:o)',
1309: dbms_sql.v7);
1310: dbms_sql.bind_variable(cols_cursor, ':t', table_resolved);
1311: dbms_sql.bind_variable(cols_cursor, ':o', owner_resolved);
1312: dbms_sql.define_column(cols_cursor, 1, col_name, 2000);
1313: ignore := dbms_sql.execute(cols_cursor);
1306: sys.dbms_sys_sql.parse_as_user(cols_cursor,
1307: 'select column_name from all_tab_columns where table_name = upper(:t)
1308: and owner = upper(:o)',
1309: dbms_sql.v7);
1310: dbms_sql.bind_variable(cols_cursor, ':t', table_resolved);
1311: dbms_sql.bind_variable(cols_cursor, ':o', owner_resolved);
1312: dbms_sql.define_column(cols_cursor, 1, col_name, 2000);
1313: ignore := dbms_sql.execute(cols_cursor);
1314: loop
1307: 'select column_name from all_tab_columns where table_name = upper(:t)
1308: and owner = upper(:o)',
1309: dbms_sql.v7);
1310: dbms_sql.bind_variable(cols_cursor, ':t', table_resolved);
1311: dbms_sql.bind_variable(cols_cursor, ':o', owner_resolved);
1312: dbms_sql.define_column(cols_cursor, 1, col_name, 2000);
1313: ignore := dbms_sql.execute(cols_cursor);
1314: loop
1315: if (dbms_sql.fetch_rows(cols_cursor) > 0)
1308: and owner = upper(:o)',
1309: dbms_sql.v7);
1310: dbms_sql.bind_variable(cols_cursor, ':t', table_resolved);
1311: dbms_sql.bind_variable(cols_cursor, ':o', owner_resolved);
1312: dbms_sql.define_column(cols_cursor, 1, col_name, 2000);
1313: ignore := dbms_sql.execute(cols_cursor);
1314: loop
1315: if (dbms_sql.fetch_rows(cols_cursor) > 0)
1316: then
1309: dbms_sql.v7);
1310: dbms_sql.bind_variable(cols_cursor, ':t', table_resolved);
1311: dbms_sql.bind_variable(cols_cursor, ':o', owner_resolved);
1312: dbms_sql.define_column(cols_cursor, 1, col_name, 2000);
1313: ignore := dbms_sql.execute(cols_cursor);
1314: loop
1315: if (dbms_sql.fetch_rows(cols_cursor) > 0)
1316: then
1317: dbms_sql.column_value(cols_cursor, 1, col_name);
1311: dbms_sql.bind_variable(cols_cursor, ':o', owner_resolved);
1312: dbms_sql.define_column(cols_cursor, 1, col_name, 2000);
1313: ignore := dbms_sql.execute(cols_cursor);
1314: loop
1315: if (dbms_sql.fetch_rows(cols_cursor) > 0)
1316: then
1317: dbms_sql.column_value(cols_cursor, 1, col_name);
1318: htp.formCheckbox('COLS', col_name);
1319: htp.print(col_name);
1313: ignore := dbms_sql.execute(cols_cursor);
1314: loop
1315: if (dbms_sql.fetch_rows(cols_cursor) > 0)
1316: then
1317: dbms_sql.column_value(cols_cursor, 1, col_name);
1318: htp.formCheckbox('COLS', col_name);
1319: htp.print(col_name);
1320: htp.nl;
1321: else
1321: else
1322: exit;
1323: end if;
1324: end loop;
1325: dbms_sql.close_cursor(cols_cursor);
1326: htp.formSubmit(NULL,'Execute Query');
1327: end;
1328:
1329: function tablePrint(ctable in varchar2,
1654: v in varchar2 character set any_cs )
1655: is
1656: begin
1657: if ( n is NOT NULL ) then
1658: dbms_sql.bind_variable( c, n, v );
1659: end if;
1660: end bv;
1661:
1662:
1677: end format_cell;
1678:
1679: function bind_outputs( p_theCursor in integer,
1680: colCnt in number,
1681: rec_tab in dbms_sql.desc_tab2 ) return number
1682: is
1683: columnValue varchar2(1);
1684: ncolumnValue nvarchar2(1);
1685: status integer;
1687: for i in 1 .. colCnt loop
1688: begin
1689: if (rec_tab(i).col_charsetform = 2) or (rec_tab(i).col_type = 12)
1690: then
1691: dbms_sql.define_column( p_theCursor, i, ncolumnValue,2000);
1692: else
1693: dbms_sql.define_column( p_theCursor, i, columnValue, 2000 );
1694: end if;
1695: exception
1689: if (rec_tab(i).col_charsetform = 2) or (rec_tab(i).col_type = 12)
1690: then
1691: dbms_sql.define_column( p_theCursor, i, ncolumnValue,2000);
1692: else
1693: dbms_sql.define_column( p_theCursor, i, columnValue, 2000 );
1694: end if;
1695: exception
1696: when others then
1697: if ( sqlcode = -1007 ) then
1700: raise;
1701: end if;
1702: end;
1703: end loop;
1704: status := dbms_sql.execute(p_theCursor);
1705: return colCnt;
1706: end bind_outputs;
1707:
1708: function path_to_me return varchar2
1862: if ( upper( substr( ltrim( theQuery ), 1, 6 ) ) <> 'SELECT' ) then
1863: raise INVALID_QUERY;
1864: end if;
1865: --
1866: theCursor := dbms_sql.open_cursor;
1867: sys.dbms_sys_sql.parse_as_user( theCursor, theQuery, dbms_sql.native );
1868: --
1869: bv( theCursor, bv1Name, bv1Value );
1870: bv( theCursor, bv2Name, bv2Value );
1863: raise INVALID_QUERY;
1864: end if;
1865: --
1866: theCursor := dbms_sql.open_cursor;
1867: sys.dbms_sys_sql.parse_as_user( theCursor, theQuery, dbms_sql.native );
1868: --
1869: bv( theCursor, bv1Name, bv1Value );
1870: bv( theCursor, bv2Name, bv2Value );
1871: bv( theCursor, bv3Name, bv3Value );
1911: colCnt number default 0;
1912: tmpcursor number default p_theCursor;
1913: recIx number default 0;
1914: recCnt number default 0;
1915: rec_tab dbms_sql.desc_tab2;
1916: begin
1917: --
1918: dbms_sql.describe_columns2(p_theCursor, colCnt, rec_tab);
1919: colCnt := bind_outputs(p_theCursor, colcnt, rec_tab);
1914: recCnt number default 0;
1915: rec_tab dbms_sql.desc_tab2;
1916: begin
1917: --
1918: dbms_sql.describe_columns2(p_theCursor, colCnt, rec_tab);
1919: colCnt := bind_outputs(p_theCursor, colcnt, rec_tab);
1920: --
1921: while (recCnt < p_max_rows)
1922: loop
1919: colCnt := bind_outputs(p_theCursor, colcnt, rec_tab);
1920: --
1921: while (recCnt < p_max_rows)
1922: loop
1923: exit when (dbms_sql.fetch_rows(p_theCursor) <= 0);
1924: recIx := recIx + 1;
1925: if (recIx > p_skip_rec)
1926: then
1927: recCnt := recCnt + 1;
1930: loop
1931: if (rec_tab(i).col_charsetform= 2) or
1932: (rec_tab(i).col_type = 12)
1933: then
1934: dbms_sql.column_value(p_theCursor, i, ncolumnValue);
1935: format_cell(ncolumnValue, p_format_numbers);
1936: else
1937: dbms_sql.column_value(p_theCursor, i, columnValue);
1938: format_cell(columnValue, p_format_numbers);
1933: then
1934: dbms_sql.column_value(p_theCursor, i, ncolumnValue);
1935: format_cell(ncolumnValue, p_format_numbers);
1936: else
1937: dbms_sql.column_value(p_theCursor, i, columnValue);
1938: format_cell(columnValue, p_format_numbers);
1939: end if;
1940: end loop;
1941: htp.tableRowClose;
1940: end loop;
1941: htp.tableRowClose;
1942: end if;
1943: end loop;
1944: dbms_sql.close_cursor(tmpCursor);
1945: p_reccnt := recCnt;
1946: return(recCnt >= p_max_rows);
1947: exception
1948: when others then
1945: p_reccnt := recCnt;
1946: return(recCnt >= p_max_rows);
1947: exception
1948: when others then
1949: if dbms_sql.is_open(p_theCursor) then
1950: dbms_sql.close_cursor(tmpCursor);
1951: end if;
1952: raise;
1953: end cellsprint_fn;
1946: return(recCnt >= p_max_rows);
1947: exception
1948: when others then
1949: if dbms_sql.is_open(p_theCursor) then
1950: dbms_sql.close_cursor(tmpCursor);
1951: end if;
1952: raise;
1953: end cellsprint_fn;
1954:
2120: visible varchar2(2000);
2121: nc_visible nvarchar2(2000);
2122: nc_value nvarchar2(2000);
2123: selected varchar2(2000);
2124: rec_tab dbms_sql.desc_tab2;
2125: status integer;
2126: csform number;
2127: begin
2128: dbms_sql.describe_columns2(p_theCursor, colCnt, rec_tab);
2124: rec_tab dbms_sql.desc_tab2;
2125: status integer;
2126: csform number;
2127: begin
2128: dbms_sql.describe_columns2(p_theCursor, colCnt, rec_tab);
2129: if (rec_tab(1).col_charsetform = 2 or
2130: rec_tab(2).col_charsetform = 2)
2131: then
2132: dbms_sql.define_column(p_theCursor, 1, nc_value, 2000);
2128: dbms_sql.describe_columns2(p_theCursor, colCnt, rec_tab);
2129: if (rec_tab(1).col_charsetform = 2 or
2130: rec_tab(2).col_charsetform = 2)
2131: then
2132: dbms_sql.define_column(p_theCursor, 1, nc_value, 2000);
2133: dbms_sql.define_column(p_theCursor, 2, nc_visible, 2000);
2134: csform := 2;
2135: else
2136: dbms_sql.define_column(p_theCursor, 1, value, 2000);
2129: if (rec_tab(1).col_charsetform = 2 or
2130: rec_tab(2).col_charsetform = 2)
2131: then
2132: dbms_sql.define_column(p_theCursor, 1, nc_value, 2000);
2133: dbms_sql.define_column(p_theCursor, 2, nc_visible, 2000);
2134: csform := 2;
2135: else
2136: dbms_sql.define_column(p_theCursor, 1, value, 2000);
2137: dbms_sql.define_column(p_theCursor, 2, visible, 2000);
2132: dbms_sql.define_column(p_theCursor, 1, nc_value, 2000);
2133: dbms_sql.define_column(p_theCursor, 2, nc_visible, 2000);
2134: csform := 2;
2135: else
2136: dbms_sql.define_column(p_theCursor, 1, value, 2000);
2137: dbms_sql.define_column(p_theCursor, 2, visible, 2000);
2138: csform := 1;
2139: end if;
2140: dbms_sql.define_column(p_theCursor, 3, selected, 2000);
2133: dbms_sql.define_column(p_theCursor, 2, nc_visible, 2000);
2134: csform := 2;
2135: else
2136: dbms_sql.define_column(p_theCursor, 1, value, 2000);
2137: dbms_sql.define_column(p_theCursor, 2, visible, 2000);
2138: csform := 1;
2139: end if;
2140: dbms_sql.define_column(p_theCursor, 3, selected, 2000);
2141:
2136: dbms_sql.define_column(p_theCursor, 1, value, 2000);
2137: dbms_sql.define_column(p_theCursor, 2, visible, 2000);
2138: csform := 1;
2139: end if;
2140: dbms_sql.define_column(p_theCursor, 3, selected, 2000);
2141:
2142: status := dbms_sql.execute(p_theCursor);
2143:
2144: htp.formSelectOpen( cname => p_cname,
2138: csform := 1;
2139: end if;
2140: dbms_sql.define_column(p_theCursor, 3, selected, 2000);
2141:
2142: status := dbms_sql.execute(p_theCursor);
2143:
2144: htp.formSelectOpen( cname => p_cname,
2145: nsize => p_nsize,
2146: cattributes => ite( p_multiple,'multiple',NULL));
2144: htp.formSelectOpen( cname => p_cname,
2145: nsize => p_nsize,
2146: cattributes => ite( p_multiple,'multiple',NULL));
2147: loop
2148: exit when ( dbms_sql.fetch_rows(p_theCursor) <= 0 );
2149: if (csform = 2)
2150: then
2151: dbms_sql.column_value( p_theCursor, 1, nc_value );
2152: dbms_sql.column_value( p_theCursor, 2, nc_visible );
2147: loop
2148: exit when ( dbms_sql.fetch_rows(p_theCursor) <= 0 );
2149: if (csform = 2)
2150: then
2151: dbms_sql.column_value( p_theCursor, 1, nc_value );
2152: dbms_sql.column_value( p_theCursor, 2, nc_visible );
2153: dbms_sql.column_value( p_theCursor, 3, selected );
2154: htp.formSelectOption( cvalue => nc_visible,
2155: cselected => ite( selected IS NULL, NULL, 'select'),
2148: exit when ( dbms_sql.fetch_rows(p_theCursor) <= 0 );
2149: if (csform = 2)
2150: then
2151: dbms_sql.column_value( p_theCursor, 1, nc_value );
2152: dbms_sql.column_value( p_theCursor, 2, nc_visible );
2153: dbms_sql.column_value( p_theCursor, 3, selected );
2154: htp.formSelectOption( cvalue => nc_visible,
2155: cselected => ite( selected IS NULL, NULL, 'select'),
2156: cattributes => 'value="' || nc_value || '"' );
2149: if (csform = 2)
2150: then
2151: dbms_sql.column_value( p_theCursor, 1, nc_value );
2152: dbms_sql.column_value( p_theCursor, 2, nc_visible );
2153: dbms_sql.column_value( p_theCursor, 3, selected );
2154: htp.formSelectOption( cvalue => nc_visible,
2155: cselected => ite( selected IS NULL, NULL, 'select'),
2156: cattributes => 'value="' || nc_value || '"' );
2157: else
2154: htp.formSelectOption( cvalue => nc_visible,
2155: cselected => ite( selected IS NULL, NULL, 'select'),
2156: cattributes => 'value="' || nc_value || '"' );
2157: else
2158: dbms_sql.column_value( p_theCursor, 1, value );
2159: dbms_sql.column_value( p_theCursor, 2, visible );
2160: dbms_sql.column_value( p_theCursor, 3, selected );
2161: htp.formSelectOption( cvalue => visible,
2162: cselected => ite( selected IS NULL, NULL, 'select'),
2155: cselected => ite( selected IS NULL, NULL, 'select'),
2156: cattributes => 'value="' || nc_value || '"' );
2157: else
2158: dbms_sql.column_value( p_theCursor, 1, value );
2159: dbms_sql.column_value( p_theCursor, 2, visible );
2160: dbms_sql.column_value( p_theCursor, 3, selected );
2161: htp.formSelectOption( cvalue => visible,
2162: cselected => ite( selected IS NULL, NULL, 'select'),
2163: cattributes => 'value="' || value || '"' );
2156: cattributes => 'value="' || nc_value || '"' );
2157: else
2158: dbms_sql.column_value( p_theCursor, 1, value );
2159: dbms_sql.column_value( p_theCursor, 2, visible );
2160: dbms_sql.column_value( p_theCursor, 3, selected );
2161: htp.formSelectOption( cvalue => visible,
2162: cselected => ite( selected IS NULL, NULL, 'select'),
2163: cattributes => 'value="' || value || '"' );
2164: end if;
2351: is
2352: l_cursor integer default owa_util.bind_variables( p_query );
2353: begin
2354: calendarprint( l_cursor, p_mf_only );
2355: dbms_sql.close_cursor(l_cursor);
2356: exception
2357: when others then
2358: if dbms_sql.is_open(l_cursor) then
2359: dbms_sql.close_cursor(l_cursor);
2354: calendarprint( l_cursor, p_mf_only );
2355: dbms_sql.close_cursor(l_cursor);
2356: exception
2357: when others then
2358: if dbms_sql.is_open(l_cursor) then
2359: dbms_sql.close_cursor(l_cursor);
2360: end if;
2361: raise;
2362: end calendarprint;
2355: dbms_sql.close_cursor(l_cursor);
2356: exception
2357: when others then
2358: if dbms_sql.is_open(l_cursor) then
2359: dbms_sql.close_cursor(l_cursor);
2360: end if;
2361: raise;
2362: end calendarprint;
2363:
2372: l_cnt number;
2373: l_yyyymon varchar2(7) default NULL;
2374: l_curr_date date;
2375: csform number;
2376: rec_tab dbms_sql.desc_tab2;
2377: l_colcnt number;
2378: begin
2379: l_dates(0) := NULL;
2380: l_text(0) := NULL;
2382: l_ntext(0) := NULL;
2383: l_nlink(0) := NULL;
2384: l_colcnt:= 3;
2385:
2386: dbms_sql.describe_columns2(p_cursor, l_colcnt, rec_tab);
2387: dbms_sql.define_column( p_cursor, 1, l_dates(0) );
2388: if (rec_tab(2).col_charsetform = 2)
2389: then
2390: csform := 2;
2383: l_nlink(0) := NULL;
2384: l_colcnt:= 3;
2385:
2386: dbms_sql.describe_columns2(p_cursor, l_colcnt, rec_tab);
2387: dbms_sql.define_column( p_cursor, 1, l_dates(0) );
2388: if (rec_tab(2).col_charsetform = 2)
2389: then
2390: csform := 2;
2391: dbms_sql.define_column( p_cursor, 2, l_ntext(0), 2000 );
2387: dbms_sql.define_column( p_cursor, 1, l_dates(0) );
2388: if (rec_tab(2).col_charsetform = 2)
2389: then
2390: csform := 2;
2391: dbms_sql.define_column( p_cursor, 2, l_ntext(0), 2000 );
2392: dbms_sql.define_column( p_cursor, 3, l_nlink(0), 2000 );
2393: else
2394: dbms_sql.define_column( p_cursor, 2, l_text(0), 2000 );
2395: dbms_sql.define_column( p_cursor, 3, l_link(0), 2000 );
2388: if (rec_tab(2).col_charsetform = 2)
2389: then
2390: csform := 2;
2391: dbms_sql.define_column( p_cursor, 2, l_ntext(0), 2000 );
2392: dbms_sql.define_column( p_cursor, 3, l_nlink(0), 2000 );
2393: else
2394: dbms_sql.define_column( p_cursor, 2, l_text(0), 2000 );
2395: dbms_sql.define_column( p_cursor, 3, l_link(0), 2000 );
2396: csform := 1;
2390: csform := 2;
2391: dbms_sql.define_column( p_cursor, 2, l_ntext(0), 2000 );
2392: dbms_sql.define_column( p_cursor, 3, l_nlink(0), 2000 );
2393: else
2394: dbms_sql.define_column( p_cursor, 2, l_text(0), 2000 );
2395: dbms_sql.define_column( p_cursor, 3, l_link(0), 2000 );
2396: csform := 1;
2397: end if;
2398: l_cnt := dbms_sql.execute( p_cursor );
2391: dbms_sql.define_column( p_cursor, 2, l_ntext(0), 2000 );
2392: dbms_sql.define_column( p_cursor, 3, l_nlink(0), 2000 );
2393: else
2394: dbms_sql.define_column( p_cursor, 2, l_text(0), 2000 );
2395: dbms_sql.define_column( p_cursor, 3, l_link(0), 2000 );
2396: csform := 1;
2397: end if;
2398: l_cnt := dbms_sql.execute( p_cursor );
2399: l_cnt := 0;
2394: dbms_sql.define_column( p_cursor, 2, l_text(0), 2000 );
2395: dbms_sql.define_column( p_cursor, 3, l_link(0), 2000 );
2396: csform := 1;
2397: end if;
2398: l_cnt := dbms_sql.execute( p_cursor );
2399: l_cnt := 0;
2400:
2401: loop
2402: exit when ( dbms_sql.fetch_rows( p_cursor ) <= 0 );
2398: l_cnt := dbms_sql.execute( p_cursor );
2399: l_cnt := 0;
2400:
2401: loop
2402: exit when ( dbms_sql.fetch_rows( p_cursor ) <= 0 );
2403: dbms_sql.column_value( p_cursor, 1, l_curr_date );
2404: if (l_yyyymon is null)
2405: then
2406: l_yyyymon := to_char(l_curr_date, 'YYYYMON', 'NLS_DATE_LANGUAGE = AMERICAN');
2399: l_cnt := 0;
2400:
2401: loop
2402: exit when ( dbms_sql.fetch_rows( p_cursor ) <= 0 );
2403: dbms_sql.column_value( p_cursor, 1, l_curr_date );
2404: if (l_yyyymon is null)
2405: then
2406: l_yyyymon := to_char(l_curr_date, 'YYYYMON', 'NLS_DATE_LANGUAGE = AMERICAN');
2407: end if;
2415: l_yyyymon := to_char(l_curr_date, 'YYYYMON', 'NLS_DATE_LANGUAGE = AMERICAN');
2416: end if;
2417: l_dates(l_cnt) := l_curr_date;
2418: if (csform = 2) then
2419: dbms_sql.column_value( p_cursor, 2, l_ntext(l_cnt) );
2420: dbms_sql.column_value( p_cursor, 3, l_nlink(l_cnt) );
2421: else
2422: dbms_sql.column_value( p_cursor, 2, l_text(l_cnt) );
2423: dbms_sql.column_value( p_cursor, 3, l_link(l_cnt) );
2416: end if;
2417: l_dates(l_cnt) := l_curr_date;
2418: if (csform = 2) then
2419: dbms_sql.column_value( p_cursor, 2, l_ntext(l_cnt) );
2420: dbms_sql.column_value( p_cursor, 3, l_nlink(l_cnt) );
2421: else
2422: dbms_sql.column_value( p_cursor, 2, l_text(l_cnt) );
2423: dbms_sql.column_value( p_cursor, 3, l_link(l_cnt) );
2424: end if;
2418: if (csform = 2) then
2419: dbms_sql.column_value( p_cursor, 2, l_ntext(l_cnt) );
2420: dbms_sql.column_value( p_cursor, 3, l_nlink(l_cnt) );
2421: else
2422: dbms_sql.column_value( p_cursor, 2, l_text(l_cnt) );
2423: dbms_sql.column_value( p_cursor, 3, l_link(l_cnt) );
2424: end if;
2425: l_cnt := l_cnt+1;
2426: end loop;
2419: dbms_sql.column_value( p_cursor, 2, l_ntext(l_cnt) );
2420: dbms_sql.column_value( p_cursor, 3, l_nlink(l_cnt) );
2421: else
2422: dbms_sql.column_value( p_cursor, 2, l_text(l_cnt) );
2423: dbms_sql.column_value( p_cursor, 3, l_link(l_cnt) );
2424: end if;
2425: l_cnt := l_cnt+1;
2426: end loop;
2427: