A Great Tech Message Board

A Great Tech Message Board

Post by Kath » Sun, 01 Dec 2002 08:37:08



Anyone that would like to join a group to learn more about The Zone
,Computers , Windows 95 ,98 ,2000 , ME and Xp and general Tech infomation.
Please click on the link below ,We try to work together on the Tech Tips
message board,and help others. all you need to do is click on this link :
http://groups.msn.com/InstructorsTechTips
And join Group.

                   Thank you for your time

 
 
 

1. dynamic SQL error messages

Hi,
currently I am writing an PL/SQL (runs on Oracle)script for replacing
the name of rows in a Table. It also contains dynamic SQL.

The following errors occur:
ORA-00936: missing expression
ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "MPETER.GAUSS_REPLACE", line 46
ORA-06512: at line 1

I suppose it has something to do with the update of the dynamic
cursor, because currently I can not write on the server.

Who can help?
Thanks in advance,
Marcus

CREATE OR REPLACE PROCEDURE gauss_replace (table_name IN varchar2,
input_text IN varchar2, output_text IN varchar2)
as

path_var varchar2(500);
http_var varchar2(500);

find varchar2(50) := input_text;
repl varchar2(50) := output_text;
sql_string varchar2(200);
count number;

my_cursor INTEGER;
NUM_ROWS_PROCESSED INTEGER;

BEGIN

sql_string := 'SELECT path, http FROM '||table_name;
dbms_output.put_line(sql_string);
my_cursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE (my_cursor, sql_string, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN (my_cursor, 1, path_var, 500);
DBMS_SQL.DEFINE_COLUMN (my_cursor, 2, http_var, 500);
NUM_ROWS_PROCESSED := DBMS_SQL.EXECUTE (my_cursor);

LOOP
IF DBMS_SQL.FETCH_ROWS (my_cursor) > 0 THEN

/* Read out the value of the cursor */
DBMS_SQL.COLUMN_VALUE (my_cursor, 1, path_var);
DBMS_SQL.COLUMN_VALUE (my_cursor, 2, http_var);
dbms_output.put_line('vorher:');
dbms_output.put_line(path_var);
dbms_output.put_line(http_var);

/* Replacing the text in the column */
path_var := REPLACE(path_var, find, repl);
http_var := REPLACE(http_var, find, repl);
dbms_output.put_line('nachher:');
dbms_output.put_line(path_var);
dbms_output.put_line(http_var);

sql_string := 'UPDATE '||table_name||' SET path='''||path_var||''',
http='''||http_var||''' WHERE CURRENT OF '||my_cursor;
dbms_output.put_line(sql_string);

dbms_sql.parse(my_cursor, sql_string, DBMS_SQL.V7);
dbms_output.put_line('parsing complete');
count := dbms_sql.execute(my_cursor);

ELSE
EXIT;
END IF;
END LOOP;

2. EBF's for ASE12 on Solaris 2.7 and Solaris 2.8

3. Write Conflict - Error Message

4. INGRES Trainer/Consultant Position Miami

5. Strange SQL Server message in application event log

6. using DB-Lib and trusted connection

7. Are there any messages from SQL Server ?

8. Database not starting automatically (Win NT)

9. 'Incorrect Syntax Near' message is worthless

10. error message T-SQL using ADO

11. Sending E-Mail messages to a SQL Server

12. Read only message on a shared ADP

13. SP Message