Trim the ' (apostrophe) character in an sql statement
I'm using the DBMS_SQL package that returns the value '12345' as a value for a column name. How do you trim the apostrophes out from the value so that it could be converted into a number? ...
plsql
Pro*C passing a dynamic array to a PL/SQL procedure
I want to call a PL/SQL stored procedure from Pro*C which takes an array parameter (table of integer). All the documentation on the Oracle site assumed you are using a static array, or at least one defined in the same procedure as the query. I want to pass an array to a C function, that can then be sent up to the database. The problem he...
ora-00979 not a GROUP BY expression
PL/SQL is not my friend recently. I am getting the above error with the following query: SELECT cr.review_sk, cr.cs_sk, cr.full_name, tolist(to_char(cf.fact_date, 'mm/dd/yyyy')) "appt", cs.cs_id, cr.tracking_number from review cr, cs, fact cf where cr.cs_sk = cs.cs_sk and UPPER(cs.cs_id) like '%' || UPPER(i_cs_id) || '%' and row_delete...
Does PL/SQL have an equivalent StringTokenizer to Java's?
I use java.util.StringTokenizer for simple parsing of delimited strings in java. I have a need for the same type of mechanism in pl/sql. I could write it, but if it already exists, I would prefer to use that. Anyone know of a pl/sql implementation? Some useful alternative? ...
How to select in select where the inner select is a substring of return result
This is beset illustrate by an example that I think should work but doesn't: select * from TABLE_A where KEY in ( select substr(DYNAMIC_KEY,3) from TABLE_B where DYNAMIC_KEY like '$$%' and RECORD_ID='104251893783388824'); Basically, the inner select statement return a set of result which had a '$$' prefix. This is use as a lookup ke...
Oracle DB (PL/SQL) Refactoring Tools
Hi, I was wondering if there are any good refactoring tools for Oracle databases and especially for PL/SQL. I'm working on a project where half the developers are working with c# and the other half on the db where there is a large and complex schema and a large code base in pl/sql. We also use sql server but the database team work exclus...
Have PL/SQL Outputs in Real Time.
Is it possible to have Outputs from PL/SQL in real time? I have a pretty huge package that runs for more than an hour and I'd like to see where the package is at a particular time. Anyways, I currently do this with a log table, which gets filled up with hundreds of log descriptions per run, I'm just curious if this is possible. Thanks!...
Oracle material views vs analytic workspaces
In Oracle, what are the pros and cons of using materialized views, and of analytic workspaces? What are the best practices surrounding the use of these features? We have an OLTP system, but would also like to access summary information in reports and interactive decision support tools. ...
How to run REPLACE function in Oracle9i with strings larger than 4000 characters
I have the following block of PLSQL that succeeds when ln_length is 4000 characters or less but fails with "ORA-01460: unimplemented or unreasonable conversion requested" when ln_length is > 4000 characters. The block is: DECLARE ls_string VARCHAR2(32767); ls_temp VARCHAR2(32767); ln_length NUMBER := 4000; BEGIN ls_stri...
Comparing numbers as string in oracle
Hi all. I have a comparison in Oracle for two numbers. 010 and 10. They are equal as long as numeric equality is concerned; however, I need to compare them as String. I tried to_char but it doesn't work. Are there any other functions which would let me do exact comparison of numeric value as string? ------------To clarify everyone's ...
Special characters in email from Oracle pl/sql
I try to send an email using utl_smtp with Oracle including norwegian characters (å æ ø). The characters are stored and displayed correctly in the database otherwise, but shows up as question marks in the email. My database character set is WE8MSWIN1252 I have tried different Content-Type mime headers in the email including 'text/plain...
Oracle Database 10g Express Edition AND Date Formats
I'm new to Oracle (I've been using MySQL mainly until now) so this might be a dumb question. But I have created this table (names are not english but ignore that, that is not important): CREATE TABLE Auta ( id_auto NUMBER(5) UNIQUE NOT NULL, typ CHAR(10), specifikacia_typu CHAR(15), SPZ CHAR(8), farba CHAR(20), datum_vyroby DATE, pocet_...
Conditional Lists of Values in Oracle APEX?
Hey all, I need to use the value of one Select List to populate the value of a second select list, but the items in Select List number two are going to be from a completely different tables depending on what's selected in list number one. Is there a way I can conditionally populate the second list based on the values from the first? S...
Throwing specific error messages in PLSQL Oracle...catching in hibernate?
Is it possible to throw a specific error message in a PL/SQL oracle stored procedure and catch it in Hibernate when it gets invoked? ...
What does Oracle error "ORA-17432: invalid options in all7" mean?
I got the following SQLException: "invalid options in all7" Upon googling the error message, the ONLY hits I saw were Oracle error lists which pinpointed the error at "ORA-17432: invalid options in all7". Sadly, googling for the error # brought up only combined lists with no explanation for the error, aside from this page that said "A T...
In Oracle PL/SQL how do I create a directory on the file system?
Hi how do you create a physical directory on the OS from within PL/SQL? I looked at the "CREATE OR REPLACE DIRECTORY" command but that doesn't do it. Neither does UTL_FILE appear to do it. This would be in Oracle 10g and upwards. thanks ...
passing associative array of type Timestamp to oracle stored procedure
We're running into a strange error using Oracle Odp.Net (connecting to Oracle 9). The problem is illustrated by the code snippet below. This is the error we're experiencing: ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], [] ORA-06544: PL/SQL: internal error, arguments: [...
SQL join on many queries
Hi, I have a table with a structure such as table(PK, a, b, c, d, e, f, g). And I have many queries that I want to join: select PK, sum(c) where... JOIN select PK, sum(e) where... JOIN select PK, sum(g) where ... JOIN select PK,a,b,d,f Every sum(c|e|g) is actually a select sum(c|e|g) from .... where... because there are m...
<<SQL_ERROR>> block
In our Stored procedures we have the following code towards the very end. <<SQL_ERROR>> V_SYS_ERROR_MSG := SUBSTR(SQLERRM, 1, 252); DBMS_OUTPUT.PUT_LINE('ERROR IN EXECUTION IN PROCEDURE'); DBMS_OUTPUT.PUT_LINE('THE ERROR CODE IS ' || V_SYS_ERROR || '- ' || V_SYS_ERROR_MSG); we have statements like fol...
PLSQL Collections - to create a collection of records or not?
Hi, I am new to Oracle PL/SQL and am having some difficulty conceptualizing collections with regard to records. I have the following problem: I want to compare various fields of a record from the client table to various fields of a record from the person table. e.g., LName, FName, Soc. (unfortunately, there is no identifier to easily l...