PL/SQL ( sorry about previous )
Date: 1995/12/08
Message-ID: <4a9fi3$8ls_at_vivaldi.belnet.be>#1/1
Hi,
I want to do the following within PRO*C.
Select a value of a column from a table. The column name is not known until
runtime. Also the names and the number of the other columns are not known
until runtime. I have learned i could use a dynamic cursor for this purpose.
I've tried the following code (PL/SQL).
The hostvariable holding the name of the column is :typename.
The type of the column is known at runtime in :typedata.
The following types are allowed : 0 = integer
1 = long 2 = string 3 = char
When I use this code i get the following message:
ORA-01405: fetched column value is NULL
Does anybody know why i get this message and what to do about it.
I also want to know when a NULL was returned because this asks for a special
treatement.
I suspect it has something to do with indicators but i don't have a clue
of how i can use them in dynamic SQL.
Does someone know of a better way to solve the problem or know why my code
isn't working please contact me via this news-group or E-MAIL
Please Help!
EXEC SQL EXECUTE
DECLARE
test integer; errornumber integer; BEGIN :source_cursor :=dbms_SQL.open_cursor; dbms_sql.parse ( :source_cursor, 'select ' || :typename || ' from scott.customerdata WHERE USERID = :USERID', 1 ); DBMS_SQL.BIND_VARIABLE ( :source_cursor, ':USERID', :UID ); if ( :typeofdata = '0' ) then DBMS_SQL.DEFINE_COLUMN ( :source_cursor, 1, :datainteger2 ); end if; if ( :typeofdata = '1' ) then DBMS_SQL.DEFINE_COLUMN ( :source_cursor, 1, :datainteger4 ); end if; if ( :typeofdata = '2' ) then DBMS_SQL.DEFINE_COLUMN ( :source_cursor, 1, :datastring, 255 ); end if; if ( :typeofdata = '3' ) then DBMS_SQL.DEFINE_COLUMN ( :source_cursor, 1, :datachar, 1 ); end if; test := DBMS_SQL.EXECUTE ( :source_cursor ); if dbms_sql.fetch_rows( :source_cursor) > 0 then if ( :typeofdata = '0' ) then DBMS_SQL.column_value ( :source_cursor,1, :datainteger2, errornumber, test ); end if; if ( :typeofdata = '1' ) then DBMS_SQL.column_value ( :source_cursor,1, :datainteger4, errornumber, test ); end if; if ( :typeofdata = '2' ) then DBMS_SQL.column_value ( :source_cursor,1, :datastring, errornumber, test ); end if; if ( :typeofdata = '3' ) then DBMS_SQL.column_value ( :source_cursor,1, :datachar, errornumber, test ); end if; end if; dbms_sql.close_cursor ( :source_cursor );END;
END-EXEC;
}
Thanks fo reading this.
-- +---------------------------------+---------------------------------------+Received on Fri Dec 08 1995 - 00:00:00 CET
| Jan Timmermans | e-mail: jtimmerm_at_alpha.luc.ac.be |
| Labo Toegepaste Informatica | tel : +032 011/268425 |
| Limburgse Universitaire Campus | |
| Belgium | |
+---------------------------------+---------------------------------------+