Recent content by hazelsisson

I managed to solve the problem using lexical parameters, replacing the statement and e.org_id in (:p_orgs) with &org and passing the parameter &org as a string constructed using a function in my application. So in the end I didn't need to use a ref cursor query.

Hello, I have a ref cursor report which works fine apart from one line of the query: and e.org_id in (:p_orgs) After asking on the Oracle 8i forum I understand that this is not possible, and that I need to concatenate the bind variable with the rest of the query...

PS - I will give you a star from home; my work firewall forbids it!

Hi Dagon, thanks for the advice - I will re-think my approach; the ref cursor looks like the best idea. Thanks again, Hazel

Hello, I have a stored sql query used in a report. Parameters are passed to it and all work apart from this line: and e.org_id IN (:p_orgs) e.org_id is a numeric column in the table. :p_orgs is a string that can be blank, or can contain any number of integers separated by commas (e.g. "18...

Hi Santa, I totally agree and usually do give someone a star. However when I tried to yesterday I found that our network security guys had placed a restriction on it - maybe because it was classed as "voting" for something, I'm not sure. So instead of the usual popup I got a content violation...

Brilliant, it worked! So simple too, why didn't I think of that? [wink] Thanks very much Dagon.

Hi guys, just a quick question (hopefully). This is my desired query (simplified): select p.dm_id, x.title from post p, post_desc x where p.post_num = trim(x.post_no)(+) But I get the error "ORA-00933: SQL command not properly ended" because of the use of the trim function before the...

Hi Santa, the table "zcmintranet" is part of our Human Resources system - the "cmi" stands for "car mileage intranet". I'm not too sure why it's prefixed with a "z" - possibly because it's an extra table not included in the standard build of the application. All fields in the table are...

Wow, I never would have thought of that! It works perfectly, thanks a million. Hazel

Hi all, I've devised a CASE statement for use within my stored procedure SQL insert query but then found out it doesn't work in our version of Oracle. (The error message is PLS-00103: Encountered the symbol "CASE" when expecting one of the following: ( - + mod not null others <an...

I seem to have a talent for compicating things! [blush] Thanks very much for your help dbtoo2001, it's just what I needed. Cheers Hazel

Hi guys, I have two tables: cm_std_locations loc_code char(10) loc_desc varchar2(50) cm_std_distance loc1 char(10) loc2 char(10) distance number I have a query as follows: select l.loc_code, l.LOC_DESC, d.distance from cm_std_locations l, (select s.distance from...

I see - I'll experiment. I think I'll be able to use another table rather than all_objects. Thanks again, Hazel

Perfect, that gives me exactly what I wanted. It seems to be quite slow to run, but that's OK as it's just for small amounts of data. Thanks very much Stefan. Hazel