I am writing a data conversion in PL/SQL that processes data and loads it into a table. According to the PL/SQL Profiler, one of the slowest parts of the conversion is the actual insert into the target table. The table has a single index. To prepare the data for load, I populate a variable using the rowtype of the table, then insert it ...
Does anyone have tools or experience with code coverage for PL/SQL. I believe this is possible using DBMS_PROFILER? ...
Anyone have any experience or tools for unit testing PL/SQL. The best looking tool I've seen for this seems to be Quests Code Tester, but i'm not sure how well that would integration with continuous integration tools or command line testing? ...
It there a tool (preferably free) which will translate Oracle's PL/SQL stored procedure language into Postgresql's PL/pgSQL stored procedure language? ...
I have a web application where users enter arbitrary sql queries for later batch processing. We want to validate the syntax of the query without actually executing it. Some of the queries will take a long time, which is why we don't want to execute them. I'm using Oracle's dbms_sql.parse to do this. However, I now have a situation where...
I've evaluated some plugins from official IntelliJ repository, but none of them seems to provide more then basic syntax highlighting. Even highlighting is limited. For example, Database Navigator doesn't highlight IF. It would be great to have proper highlighting. And if it would have auto-complete feature and provide file structure view...
I have a stored procedure with the following header: FUNCTION SaveShipment (p_user_id IN INTEGER, p_transaction_id IN INTEGER, p_vehicle_code IN VARCHAR2 DEFAULT NULL, p_seals IN VARCHAR2 DEFAULT NULL) RETURN INTEGER; And I am having trouble running it from TOAD's Editor. I cannot run it as part of a select from dual statement becaus...
We get sometimes the following error from our partner's database: <i>ORA-01438: value larger than specified precision allows for this column</i> The full response looks like the following: <?xml version="1.0" encoding="windows-1251"?> <response> <status_code></status_code> <error_text>ORA-01438: value larger than specified precis...
In an Oracle cluster (more than one machine co-operating to serve one database) will the "sysdate" function always return a consistent answer? Even if the servers' Operating System clock reports inconsistent values? ...
When you create a procedure in Oracle PL/SQL, you cannot specify the maximum length of the varchar2 arguments, only the datatype. For example create or replace procedure testproc( arg1 varchar2 ) is begin ... ... end; Do you know the maximum length of a string that you can pass as the arg1 argument to this procedure in Oracle 10g ...
I'm trying to speed up a data load which is controlled via a PL/SQL stored procedure. I've programmatically altered the indexes for the table I want to refresh to be unusable. I want Oracle to ignore these unusable indexes. I can issue the statement... ALTER SESSION SET skip_unusable_indexes = TRUE ...but I subsequently get the error...
Howdy. Consider the following: SQL> DECLARE 2 b1 BOOLEAN; 3 b2 BOOLEAN; 4 FUNCTION checkit RETURN BOOLEAN IS 5 BEGIN 6 dbms_output.put_line('inside checkit'); 7 RETURN TRUE; 8 END checkit; 9 10 PROCEDURE outp(n VARCHAR2, p BOOLEAN) IS 11 BEGIN 12 IF p THEN 13 ...
How do you fix a names mismatch problem, if the client-side names are keywords or reserved words in the server-side language you are using? The DOJO JavaScript toolkit has a QueryReadStore class that you can subclass to submit REST patterned queries to the server. I'm using this in conjunction w/ the FilteringSelect Dijit. I can subcla...
I'm sorting out a series of SQL scripts for my company written in Oracle PL/SQL. I came across an essential script with a strangely placed slash near the bottom. It is checked into CVS this way. Is this a pure syntax error or does it have some function I'm not aware of. The slightly obfuscated script: set serveroutput on size 2000; ...
This is what I currently have: CREATE OR REPLACE TRIGGER MYTRIGGER AFTER INSERT ON SOMETABLE FOR EACH ROW DECLARE v_emplid varchar2(10); BEGIN SELECT personnum into v_emplid FROM PERSON WHERE PERSONID = :new.EMPLOYEEID; dbms_output.put(v_emplid); /* INSERT INTO SOMEOTHERTABLE USING v_emplid and some of the other values...
I've seen the question (and answer) when posed for MS SQL Server, though I don't yet know of one for Oracle and PL/SQL. Are there xUnit style testing frameworks for Oracle's PL/SQL? What are they? ...
I'm writing a stored procedure that needs to have a lot of conditioning in it. With the general knowledge from C#.NET coding that exceptions can hurt performance, I've always avoided using them in PL/SQL as well. My conditioning in this stored proc mostly revolves around whether or not a record exists, which I could do one of two ways:...
I have some EDI messages (X12, HL7, etc ...) stored in an Oracle database. I sometimes want to pull out individual fields (e.g. ISA-03). Currently, I have some really ugly sql. I'd like to create a PL/SQL package to make it easier and was wondering if anybody had already done this. I imagine something like: select edi.x12.extract...
I've got a lot of similar oracle jobs I need to create, and I'd like to do it programatically. Where does the Oracle store the job library (schema/table)? (yes, I know I might be running with scissors) ...
How can I create an Oracle stored procedure which accepts a variable number of parameter values used to feed a IN clause? This is what I am trying to achieve. I do not know how to declare in PLSQL for passing a variable list of primary keys of the rows I want to update. FUNCTION EXECUTE_UPDATE ( <parameter_list> value IN int) R...