Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL (Oracle 9.2)
Dynamic SQL [message #282059] Tue, 20 November 2007 13:30 Go to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
I am almost at the end of this process and don't what to monopolize other folks time on other boards (I asked a ton of questions) so I am posting here.

This procedure has to build an unknown at runtime numberr of decodes. I am getting an error message:

ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "PLANNING_PWR.RUNNING_TIMES_EXP", line 77
ORA-06512: at line 1

What is causing this?

<code>
CREATE OR REPLACE
procedure running_times_exp(p_table_name IN VARCHAR2,
p_pattern in varchar2,
p_f_date IN DATE,
p_t_date IN DATE)

IS

dynsql_str VARCHAR2(32767);
dynsql_str1 VARCHAR2(1000);
cursor namesnum_cur is --cursor definition stop_id and stop_name;
SELECT B.STOP_NAME, b.STOP_ID
FROM SCHD_BUS_PATTERN A, SCHD_BUS_STOPS B, SCHD_BUS_PATTERN_PATH C
WHERE A.PATTERNNAME = p_pattern
AND A.PATTERNID = C.PATTERNID
AND C.STOP_ID = B.STOP_ID
AND A.VERSION = 229
AND A.VERSION = B.VERSION
AND B.VERSION = C.VERSION
ORDER BY C.SEQ ASC;

cursor nums_cur is --cursor definition for schema level collection stop_id_type;
SELECT B.STOP_ID
FROM SCHD_BUS_PATTERN A, SCHD_BUS_STOPS B, SCHD_BUS_PATTERN_PATH C
WHERE A.PATTERNNAME = p_pattern
AND A.PATTERNID = C.PATTERNID
AND C.STOP_ID = B.STOP_ID
AND A.VERSION = 229
AND A.VERSION = B.VERSION
AND B.VERSION = C.VERSION
ORDER BY C.SEQ ASC;

stop_id_var stop_id_type; --variable for schema level collection

TYPE namesnums_list IS TABLE OF namesnum_cur%rowtype; --declare collection for stop_id and stop_name.
namesnum_var namesnums_list; --collection variable and constructor



begin

dynsql_str := 'CREATE TABLE ' || p_table_name || ' as select bus_id, MAX(';

open namesnum_cur;
open nums_cur;

fetch namesnum_Cur bulk collect into namesnum_var limit 1000;
fetch nums_Cur bulk collect into STOP_ID_var limit 1000;

for i in nvl(namesnum_var.FIRST, 0) .. NVL(namesnum_var.LAST, -1)


loop

dynsql_str1 := 'DECODE(a.stop_id, ''' || namesnum_var(i).stop_id || ''', to_char(a.event_time, ''hh24:mi:ss''), NULL))
from cta_history.bus_state_hist a, schd_bus_stops B
where a.stop_id = ' || stop_id_var(i) ||'
and a.stop_id=b.stop_id
and event_time between
to_date(''' || to_char(p_f_date, 'yyyymmdd hh24miss') ||
''', ''yyyymmdd hh24miss'')
AND to_date(''' || to_char(p_t_date, 'yyyymmdd hh24miss') ||
''',''yyyymmdd hh24miss''))';

exit when namesnum_cur%notfound;

end loop;


close namesnum_cur;
close nums_cur;


dynsql_str:= dynsql_str || dynsql_str1;

my_dbms_output.put_line(dynsql_str);

execute immediate dynsql_str;

end running_times_exp;</code>

That is the blank line above execute immediate. The first where clause item was going to be a dynamic in list but I couldn't make it work but kept the schema level collection.

One last thing. How do I attached a variable as a column name at the end?









Re: Dynamic SQL [message #282061 is a reply to message #282059] Tue, 20 November 2007 13:48 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in above URL.

>One last thing. How do I attached a variable as a column name at the end?
You do NOT do so.
It must be built into the SQL statement.
You should use CUT to take the results from DBMS_OUTPUT & paste into SQL*Plus to see what the syntax error really is.
Re: Dynamic SQL [message #282063 is a reply to message #282061] Tue, 20 November 2007 13:58 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
Could you be a little more specific on what policy I violated?
Re: Dynamic SQL [message #282066 is a reply to message #282063] Tue, 20 November 2007 14:04 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
Oracle9i Enterprise Edition Release 9.2.0.8.0
The OS is Sun Solaris. Do not know version.

dbms_output


LINENO
----------
TEXT
--------------------------------------------------------------------------------
1
CREATE TABLE SkokieCourtIrvingPkRail54 as select bus_id, MAX(DECODE(a.stop_id, '
10450', to_char(a.event_time, 'hh24:mi:ss'), NULL))
from cta_history.bus_state_hist a, schd_bus_stops B

where a.stop_id = 10450
and a.stop_id=b.stop_id
and event_time between
to_date('20070910 080000', 'yyyymmdd hh24miss')

LINENO
----------
TEXT
--------------------------------------------------------------------------------
AND to_date('20070910 084500','yyyymmdd hh24miss'))

2
Re: Dynamic SQL [message #282071 is a reply to message #282066] Tue, 20 November 2007 14:54 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
There was an additional peren at the end of the second string I removed. The new error message with output is below.

*
ERROR at line 1:
ORA-00998: must name this expression with a column alias
ORA-06512: at "PLANNING_PWR.RUNNING_TIMES_EXP", line 76
ORA-06512: at line 1

Line 76 in between

my_dbms_output.put_line(dynsql_str);

execute immediate dynsql_str;


SQL> select * from my_dbms_output_view;

LINENO
----------
TEXT
--------------------------------------------------------------------------------
1
CREATE TABLE SkokieCourtIrvingPkRail54 as select bus_id, MAX(DECODE(a.stop_id, '
10450', to_char(a.event_time, 'hh24:mi:ss'), NULL))
from cta_history.bus_state_hist a, schd_bus_stops B

where a.stop_id = 10450
and a.stop_id=b.stop_id
and event_time between
to_date('20070910 080000', 'yyyymmdd hh24miss')

LINENO
----------
TEXT
--------------------------------------------------------------------------------
AND to_date('20070910 084500','yyyymmdd hh24miss')

2



SQL>
Re: Dynamic SQL [message #282090 is a reply to message #282059] Tue, 20 November 2007 19:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
http://ora-00998.ora-code.com/
Quote:

ORA-00998: must name this expression with a column alias
Cause: An expression or function was used in a CREATE VIEW statement, but no corresponding column name was specified. When expressions or functions are used in a view, all column names for the view must be explicitly specified in the CREATE VIEW statement.
Action: Enter a column name for each column in the view in parentheses after the view name.

You cannot name a view column with "MAX(DECODE(a.stop_id,'10450',to_char(a.event_time,'hh24:mi:ss'),NULL))". Would be very inconvenient to access too. Use alias name.
Re: Dynamic SQL [message #282142 is a reply to message #282071] Wed, 21 November 2007 00:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The following is a copy and paste of my latest response from the other forum.


In some other forums, there are posting guidelines that suggest posting create table and insert statements for tables and sample data, and the output that you want based on that sample data and why, in addition to posting your procedural code. It makes the problem a whole lot clearer and makes it easier for those trying to help you to test things. Otherwise, we are just guessing at what you are trying to achieve, based on your code, which may not be doing what you want it to, or very frequently doing it in an unnecessarily complicated way. The following is a complete working example of my best guess, based on your most recently posted code, as to the decode version of what you are trying to do.

SCOTT@orcl_11g> -- test tables and data
SCOTT@orcl_11g> CREATE TABLE schd_bus_pattern
  2    (patternname   VARCHAR2 (15),
  3  	patternid     NUMBER,
  4  	version       NUMBER)
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO schd_bus_pattern VALUES ('pattern1', 1, 229)
  3  INTO schd_bus_pattern VALUES ('pattern1', 2, 229)
  4  sELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_11g> CREATE TABLE schd_bus_stops
  2    (stop_name     VARCHAR2 (15),
  3  	stop_id       NUMBER,
  4  	version       NUMBER)
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO schd_bus_stops VALUES ('stop10', 10, 229)
  3  INTO schd_bus_stops VALUES ('stop20', 20, 229)
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_11g> CREATE TABLE schd_bus_pattern_path
  2    (patternid     NUMBER,
  3  	stop_id       NUMBER,
  4  	version       NUMBER,
  5  	seq	      NUMBER)
  6  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO schd_bus_pattern_path VALUES (1, 10, 229, 1)
  3  INTO schd_bus_pattern_path VALUES (2, 20, 229, 2)
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_11g> CREATE TABLE bus_state_hist
  2    (bus_id	      NUMBER,
  3  	stop_id       NUMBER,
  4  	event_time    DATE)
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO bus_state_hist VALUES (100, 10, SYSDATE-1.1)
  3  INTO bus_state_hist VALUES (100, 20, SYSDATE-2.2)
  4  INTO bus_state_hist VALUES (200, 10, SYSDATE-3.3)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_11g> -- DECODE VERSION:
SCOTT@orcl_11g> CREATE OR REPLACE procedure running_times_exp
  2    (p_table_name IN VARCHAR2,
  3  	p_pattern    IN VARCHAR2,
  4  	p_f_date     IN DATE,
  5  	p_t_date     IN DATE)
  6  AS
  7    dynsql_str	VARCHAR2(32767);
  8  BEGIN
  9    -- build first lines of dynsql_str:
 10    dynsql_str :=
 11    '-- first lines:';
 12    dynsql_str := dynsql_str || CHR (10) ||
 13    'CREATE TABLE ' || p_table_name || ' AS' || CHR (10) ||
 14    'SELECT bus_id';
 15  
 16    -- build middle (decode) lines of dynsql_str:
 17    dynsql_str := dynsql_str || CHR (10) ||
 18    '-- middle (decode) lines:';
 19    for stop_ids in
 20  	 (SELECT DISTINCT b.STOP_ID, c.seq
 21  	  FROM	 SCHD_BUS_PATTERN A, SCHD_BUS_STOPS B, SCHD_BUS_PATTERN_PATH C
 22  	  WHERE  A.PATTERNNAME = p_pattern
 23  	  AND	 A.PATTERNID = C.PATTERNID
 24  	  AND	 C.STOP_ID = B.STOP_ID
 25  	  AND	 A.VERSION = 229
 26  	  AND	 A.VERSION = B.VERSION
 27  	  AND	 B.VERSION = C.VERSION
 28  	  ORDER  BY C.SEQ ASC)
 29    loop
 30  	 dynsql_str := dynsql_str || CHR (10) ||
 31  	 ', MAX (DECODE(a.stop_id,' || stop_ids.stop_id || ', to_char(a.event_time, ''hh24:mi:ss''), NULL)) "' || stop_ids.stop_id  || '"';
 32    end loop;
 33  
 34    -- build last lines of dynsql_str:
 35    dynsql_str := dynsql_str || CHR (10) ||
 36    '-- last lines:';
 37    dynsql_str := dynsql_str || CHR (10) ||
 38    'from bus_state_hist a, schd_bus_stops B' || CHR (10) ||
 39    'where a.stop_id = b.stop_id' || CHR (10) ||
 40    'and event_time between to_date(''' || to_char(p_f_date, 'yyyymmdd hh24miss') || ''', ''yyyymmdd hh24miss'')
 41  	AND to_date(''' || to_char(p_t_date, 'yyyymmdd hh24miss') || ''',''yyyymmdd hh24miss'')' || CHR (10) ||
 42    'GROUP BY bus_id';
 43  
 44    -- display dynsql_str:
 45    my_dbms_output.put_line (dynsql_str);
 46  
 47    -- execute dynsql_str:
 48    execute immediate dynsql_str;
 49  END running_times_exp;
 50  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> GRANT CREATE TABLE TO scott -- explicit privilege required for execution
  2  /

Grant succeeded.

SCOTT@orcl_11g> EXECUTE running_times_exp ('test_tab', 'pattern1', SYSDATE-10, SYSDATE)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT text FROM my_dbms_output_view ORDER BY lineno
  2  /

TEXT
--------------------------------------------------------------------------------
-- first lines:
CREATE TABLE test_tab AS
SELECT bus_id
-- middle (decode) lines:
, MAX (DECODE(a.stop_id,10, to_char(a.event_time, 'hh24:mi:ss'), NULL)) "10"
, MAX (DECODE(a.stop_id,20, to_char(a.event_time, 'hh24:mi:ss'), NULL)) "20"
-- last lines:
from bus_state_hist a, schd_bus_stops B
where a.stop_id = b.stop_id
and event_time between to_date('20071110 154233', 'yyyymmdd hh24miss')
   AND to_date('20071120 154233','yyyymmdd hh24miss')
GROUP BY bus_id



SCOTT@orcl_11g> SELECT * FROM test_tab
  2  /

    BUS_ID 10       20
---------- -------- --------
       100 13:18:33 10:54:33
       200 08:30:33

SCOTT@orcl_11g> SPOOL OFF



Note: the my_dbms_output package and view are from Tom Kyte at:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:146412348066
Re: Dynamic SQL [message #282333 is a reply to message #282142] Wed, 21 November 2007 09:15 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
Thank you Barbara. Can I increase the CHR (10) size? I don;t know if its related but I get a ora-06502 cahracter string buffer too small error for lines 15, 22 and 45. I increased the sized but got the same error message.
Re: Dynamic SQL [message #282335 is a reply to message #282059] Wed, 21 November 2007 09:30 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Can I increase the CHR (10) size?
HUH?
It appears you have no idea what the CHR() function is/does.
It might be problematic if you change it to any other value other than CHR(32).
Re: Dynamic SQL [message #282336 is a reply to message #282335] Wed, 21 November 2007 09:55 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
You are right. I have not had to use the CHR() function. I have already modified it and got error messages so I figured it out. Do you have anything more to contribute?
Re: Dynamic SQL [message #282343 is a reply to message #282336] Wed, 21 November 2007 10:52 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
Changed the dynsql_str to varchar2(4000) and solved it. Just getting numeric or value error for line 32.
Re: Dynamic SQL [message #282352 is a reply to message #282343] Wed, 21 November 2007 11:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The CHR (10) is a line feed on most systems. You could use CHR (32), which is a space instead, or concatenate a space or include a space in your string. It has nothing to do with the error messages that you have listed. You need to post a copy and paste of a run of your current complete code, including the line numbers and error message, so that we can see what is causing the error. It would also help if you would post create table and insert statements or at least a describe of your table structure. In general, if you are getting a numeric or value error, you are probably trying to use character data where numeric data is required or something that causes that to happen implicitly.
Re: Dynamic SQL [message #282355 is a reply to message #282352] Wed, 21 November 2007 12:03 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
I have been doning as you suggested and I am not get any lines or text from my_dbms_output_view.
Re: Dynamic SQL [message #282364 is a reply to message #282355] Wed, 21 November 2007 12:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Richard Cook wrote on Wed, 21 November 2007 10:03

I have been doning as you suggested ...


You have not posted a copy and paste of a run of your code or create table and insert statements or describes of table structures.


Richard Cook wrote on Wed, 21 November 2007 10:03

... I am not get any lines or text from my_dbms_output_view.


How are you expecting me to guess what is wrong without seeing the code that you are running?
Re: Dynamic SQL [message #282368 is a reply to message #282364] Wed, 21 November 2007 12:55 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
Barbara

I was just describing what was happening. I thought you wanted to see output of my_dbms_output_view which is where I turn when I get an error message after running something. I was executing the whole procedure. I was trying to see if something was wrong with the my_dbms_output_view since I got no output. No I do not expect you to guess.

Error message

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PLANNING_PWR.RUNNING_TIMES_EXP", line 32
ORA-06512: at line 1


Line 32 is the dynsql_str line

loop
dynsql_str := dynsql_str || CHR (10) ||
', MAX (DECODE(a.stop_id,' || stop_ids.stop_id || ', to_char(a.event_time, ''hh24:mi:ss''), NULL)) "' || stop_ids.stop_id || '"';
end loop;



Re: Dynamic SQL [message #282372 is a reply to message #282368] Wed, 21 November 2007 13:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
What datatype is stop_id? In my example, I made it numeric. If it is varchar2, then that would cause that error. To resolve it, you would need to add doubled single quotes on either side, like so:


loop
dynsql_str := dynsql_str || CHR (10) ||
', MAX (DECODE(a.stop_id,''' || stop_ids.stop_id || ''', to_char(a.event_time, ''hh24:mi:ss''), NULL)) "' || stop_ids.stop_id || '"';
end loop;
Re: Dynamic SQL [message #282375 is a reply to message #282059] Wed, 21 November 2007 13:42 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
Stop_id is number. We are being told to leave work as they are closing down for the holiday. I will be checking e-mail if you decide to post aything else. Happy thanksgiving!!
Re: Dynamic SQL [message #283561 is a reply to message #282375] Tue, 27 November 2007 08:29 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
Ok - this actually does something. Inserts two columns and 14 rows then stops. If anyone could take a look thanks. I am testing from my side. Barbara - I still have and am working on your code but this gave results faster.

create or replace procedure running_times_exp(p_table_name IN VARCHAR2,
p_pattern in varchar2,
p_f_date IN DATE,
p_t_date IN DATE)

IS

dynsql_str VARCHAR2(4000);
dynsql_str1 VARCHAR2(4000);
dynsql_str2 VARCHAR2(1000);
cursor namesnum_cur is --cursor definition stop_id and stop_name;
SELECT b.STOP_ID
FROM SCHD_BUS_PATTERN A, SCHD_BUS_STOPS B, SCHD_BUS_PATTERN_PATH C
WHERE A.PATTERNNAME = p_pattern
AND A.PATTERNID = C.PATTERNID
AND C.STOP_ID = B.STOP_ID
AND A.VERSION = 229
AND A.VERSION = B.VERSION
AND B.VERSION = C.VERSION
ORDER BY C.SEQ ASC;

cursor nums_cur is --cursor definition for schema level collection stop_id_type;
SELECT B.STOP_ID
FROM SCHD_BUS_PATTERN A, SCHD_BUS_STOPS B, SCHD_BUS_PATTERN_PATH C
WHERE A.PATTERNNAME = p_pattern
AND A.PATTERNID = C.PATTERNID
AND C.STOP_ID = B.STOP_ID
AND A.VERSION = 229
AND A.VERSION = B.VERSION
AND B.VERSION = C.VERSION
ORDER BY C.SEQ ASC;

stop_id_var stop_id_type; --variable for schema level collection

TYPE namesnums_list IS TABLE OF namesnum_cur%rowtype; --declare collection for stop_id and stop_name.
namesnum_var namesnums_list; --collection variable and constructor


begin

dynsql_str := 'CREATE TABLE ' || p_table_name || ' as select bus_id, ';

open namesnum_cur;
open nums_cur;

fetch namesnum_Cur bulk collect into namesnum_var limit 1000;
fetch nums_Cur bulk collect into STOP_ID_var limit 1000;

for i in nvl(namesnum_var.FIRST, 0) .. NVL(namesnum_var.LAST, -1)


loop
if namesnum_var IS NOT NULL then
dynsql_str1 := 'MAX(DECODE(a.stop_id, ' || namesnum_var(i).stop_id || ', to_char(a.event_time, ''hh24:mi:ss''), NULL)) as "' || namesnum_var(i).stop_id || '"';
dynsql_str2 := 'a.stop_id = '|| stop_id_var(i);
elsif NAMESNUM_VAR.LAST = 'True' then
dynsql_str1 := 'MAX(DECODE(a.stop_id, ' || namesnum_var(i).stop_id || ', to_char(a.event_time, ''hh24:mi:ss''), NULL)) as "' || namesnum_var(i).stop_id || '"';
dynsql_str2 := ' and a.stop_id = ' || stop_id_var(i);
end if;

-- exit when namesnum_cur%notfound;

end loop;
dynsql_str :=dynsql_str || dynsql_str1 ||
' from cta_history.bus_state_hist a, schd_bus_stops B
where '|| dynsql_str2 ||'
and a.stop_id=b.stop_id
and event_time between
to_date(''' || to_char(p_f_date, 'yyyymmdd hh24miss') ||
''', ''yyyymmdd hh24miss'')
AND to_date(''' || to_char(p_t_date, 'yyyymmdd hh24miss') ||
''',''yyyymmdd hh24miss'')
group by bus_id';

close namesnum_cur;
close nums_cur;


my_dbms_output.put_line(dynsql_str);

execute immediate dynsql_str;

end running_times_exp;



Re: Dynamic SQL [message #283566 is a reply to message #283561] Tue, 27 November 2007 08:32 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
Here is the output:


BUS_ID 15547 <----Stop_id
6033 08:36:55
6038 08:42:53
6050 08:14:51
6063 08:20:30
6142 08:35:15
6153 08:23:05
6156 08:19:06
6762 08:34:45
6763 08:35:54
6818 08:18:13
6826 08:17:34
6827 08:39:04
6871 08:01:29
6881 08:39:04
Re: Dynamic SQL [message #283599 is a reply to message #283566] Tue, 27 November 2007 10:34 Go to previous messageGo to next message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
More progress of a sort. Made some code modifications and now all the columns are pulled but none of the bus_id and event_time data. I think it has to do with the way the query is assembled. It sets up a search condition that cannot return any rows. I don't know how it does this. Attached is the output from my_dbms_output_view.

Here is the code:

create or replace procedure running_times_exp(p_table_name IN VARCHAR2,
p_pattern in varchar2,
p_f_date IN DATE,
p_t_date IN DATE)

IS

dynsql_str VARCHAR2(4000);
dynsql_str1 VARCHAR2(4000);
dynsql_str2 VARCHAR2(4000);
cursor namesnum_cur is --cursor definition stop_id and stop_name;
SELECT b.STOP_ID
FROM SCHD_BUS_PATTERN A, SCHD_BUS_STOPS B, SCHD_BUS_PATTERN_PATH C
WHERE A.PATTERNNAME = p_pattern
AND A.PATTERNID = C.PATTERNID
AND C.STOP_ID = B.STOP_ID
AND A.VERSION = 229
AND A.VERSION = B.VERSION
AND B.VERSION = C.VERSION
ORDER BY C.SEQ ASC;

cursor nums_cur is --cursor definition for schema level collection stop_id_type;
SELECT B.STOP_ID
FROM SCHD_BUS_PATTERN A, SCHD_BUS_STOPS B, SCHD_BUS_PATTERN_PATH C
WHERE A.PATTERNNAME = p_pattern
AND A.PATTERNID = C.PATTERNID
AND C.STOP_ID = B.STOP_ID
AND A.VERSION = 229
AND A.VERSION = B.VERSION
AND B.VERSION = C.VERSION
ORDER BY C.SEQ ASC;

stop_id_var stop_id_type; --variable for schema level collection

TYPE namesnums_list IS TABLE OF namesnum_cur%rowtype; --declare collection for stop_id and stop_name.
namesnum_var namesnums_list; --collection variable and constructor


begin

dynsql_str := 'CREATE TABLE ' || p_table_name || ' as select bus_id, ';

open namesnum_cur;
open nums_cur;

fetch namesnum_Cur bulk collect into namesnum_var limit 1000;
fetch nums_Cur bulk collect into STOP_ID_var limit 1000;

--my_dbms_output.put_line('number of elements' ||namesnum_var.COUNT);

for i in nvl(namesnum_var.FIRST, 0) .. NVL(namesnum_var.LAST, -1)


loop
if i = 1 then
dynsql_str1 := 'MAX(DECODE(a.stop_id, ' || namesnum_var(i).stop_id || ', to_char(a.event_time, ''hh24:mi:ss''), NULL)) as "' || namesnum_var(i).stop_id || '"';
dynsql_str2 := 'a.stop_id = '||stop_id_var(i);
elsif i > 1 then
-- my_dbms_output.put_line(dynsql_str1);
dynsql_str1 := dynsql_str1 || ', MAX(DECODE(a.stop_id, ' || namesnum_var(i).stop_id || ', to_char(a.event_time, ''hh24:mi:ss''), NULL)) as "' || namesnum_var(i).stop_id || '"';
dynsql_str2 := dynsql_str2 || ' and a.stop_id = ' || stop_id_var(i);
end if;

-- exit when namesnum_cur%notfound;

end loop;
dynsql_str :=dynsql_str || dynsql_str1 ||
' from cta_history.bus_state_hist a, schd_bus_stops B
where '|| dynsql_str2 ||'
and a.stop_id=b.stop_id
and event_time between
to_date(''' || to_char(p_f_date, 'yyyymmdd hh24miss') ||
''', ''yyyymmdd hh24miss'')
AND to_date(''' || to_char(p_t_date, 'yyyymmdd hh24miss') ||
''',''yyyymmdd hh24miss'')
group by bus_id';

close namesnum_cur;
close nums_cur;


my_dbms_output.put_line(dynsql_str);

execute immediate dynsql_str;

end running_times_exp;

Re: Dynamic SQL [message #283602 is a reply to message #283599] Tue, 27 November 2007 10:49 Go to previous message
Richard Cook
Messages: 15
Registered: December 2004
Junior Member
Done. This works. Build the table and inserts the data. Will have to look at using CLOB instead of VARCHAR2 because some of the strings come close to the limit.

create or replace procedure running_times_exp(p_table_name IN VARCHAR2,
p_pattern in varchar2,
p_f_date IN DATE,
p_t_date IN DATE)

IS

dynsql_str VARCHAR2(4000);
dynsql_str1 VARCHAR2(4000);
dynsql_str2 VARCHAR2(4000);
cursor namesnum_cur is --cursor definition stop_id and stop_name;
SELECT b.STOP_ID
FROM SCHD_BUS_PATTERN A, SCHD_BUS_STOPS B, SCHD_BUS_PATTERN_PATH C
WHERE A.PATTERNNAME = p_pattern
AND A.PATTERNID = C.PATTERNID
AND C.STOP_ID = B.STOP_ID
AND A.VERSION = 229
AND A.VERSION = B.VERSION
AND B.VERSION = C.VERSION
ORDER BY C.SEQ ASC;

cursor nums_cur is --cursor definition for schema level collection stop_id_type;
SELECT B.STOP_ID
FROM SCHD_BUS_PATTERN A, SCHD_BUS_STOPS B, SCHD_BUS_PATTERN_PATH C
WHERE A.PATTERNNAME = p_pattern
AND A.PATTERNID = C.PATTERNID
AND C.STOP_ID = B.STOP_ID
AND A.VERSION = 229
AND A.VERSION = B.VERSION
AND B.VERSION = C.VERSION
ORDER BY C.SEQ ASC;

stop_id_var stop_id_type; --variable for schema level collection

TYPE namesnums_list IS TABLE OF namesnum_cur%rowtype; --declare collection for stop_id and stop_name.
namesnum_var namesnums_list; --collection variable and constructor


begin

dynsql_str := 'CREATE TABLE ' || p_table_name || ' as select bus_id, ';

open namesnum_cur;
open nums_cur;

fetch namesnum_Cur bulk collect into namesnum_var limit 1000;
fetch nums_Cur bulk collect into STOP_ID_var limit 1000;

--my_dbms_output.put_line('number of elements' ||namesnum_var.COUNT);

for i in nvl(namesnum_var.FIRST, 0) .. NVL(namesnum_var.LAST, -1)


loop
if i = 1 then
dynsql_str1 := 'MAX(DECODE(a.stop_id, ' || namesnum_var(i).stop_id || ', to_char(a.event_time, ''hh24:mi:ss''), NULL)) as "' || namesnum_var(i).stop_id || '"';
dynsql_str2 := 'a.stop_id = '||stop_id_var(i);
elsif i > 1 then
-- my_dbms_output.put_line(dynsql_str1);
dynsql_str1 := dynsql_str1 || ', MAX(DECODE(a.stop_id, ' || namesnum_var(i).stop_id || ', to_char(a.event_time, ''hh24:mi:ss''), NULL)) as "' || namesnum_var(i).stop_id || '"';
dynsql_str2 := dynsql_str2 || ' or a.stop_id = ' || stop_id_var(i);
end if;

-- exit when namesnum_cur%notfound;

end loop;
dynsql_str :=dynsql_str || dynsql_str1 ||
' from cta_history.bus_state_hist a, schd_bus_stops B
where ('|| dynsql_str2 ||')
and a.stop_id=b.stop_id
and event_time between
to_date(''' || to_char(p_f_date, 'yyyymmdd hh24miss') ||
''', ''yyyymmdd hh24miss'')
AND to_date(''' || to_char(p_t_date, 'yyyymmdd hh24miss') ||
''',''yyyymmdd hh24miss'')
group by bus_id';

close namesnum_cur;
close nums_cur;


my_dbms_output.put_line(dynsql_str);

execute immediate dynsql_str;

end running_times_exp;

Previous Topic: UTL_MAIL Attachments
Next Topic: setting SQL%ROWCOUNT in stored procedure
Goto Forum:
  


Current Time: Thu Dec 08 10:08:35 CST 2016

Total time taken to generate the page: 0.09607 seconds