I have a PL/SQL statement that uses EXECUTE IMMEDIATE to execute a query. However, I'm having difficulty figuring out how to even get the text of the query that's being executed. I can't use dbms_output as the query is greater than 255 characters. Is there any way to make sqlplus echo the string that's passed in to EXECUTE IMMEDIATE? ...
I have several Oracle queries that perform well when run through SQL*PLUS. However when they are executed as a part of a PL/SQL package, they take MUCH longer. Our DBA has watched these queries take 10 minutes through PLSQL and 10 seconds through SQL*Plus. Does anybody have any pointers on where to look for the misconfiguration? Clie...
We're discussing the performance impact of putting a common function/procedure in a separate package or using a local copy in each package. My thinking is that it would be cleaner to have the common code in a package, but others worry about the performance overhead. Thoughts/experiences? ...
I've got the following small Groovy script that just does a count of rows in the database for a specific date. import groovy.sql.Sql def today= new GregorianCalendar() def dateString = "${today.get(Calendar.MONTH)+1}/${today.get(Calendar.DAY_OF_MONTH)-1}/${today.get(Calendar.YEAR)}" def sql = Sql.newInstance("jdbc:oracle:thin:bc/bc@ne...
hi guys, i have a simple oracle stored procedure proc1 as follows: CREATE OR REPLACE PROCEDURE SYS.proc1 IS total NUMBER := 0; temp INTEGER := 0; BEGIN FOR i IN 1 .. 5 LOOP temp := 2 * i; total := total + temp; END LOOP; DBMS_OUTPUT.put_line (total); END; the owner of proc1 is sys. sys have enough p...
Hi, Given three tables: a car table, an extras table and a link table, something like: table_car --------- int car_id string make string model table_extras ------------ int extra_id string extra table_car_extras_link --------------------- int car_id int extra_id I'd like to write a PL/SQL stored proc that returns data in the follow...
I have a cursor that is used to get some preliminary information for some other processing. It is possible that the query backing the cursor may not return any rows, and in these rare cases, we want to raise a special exception (handled and logged elsewhere so processing is not compeltely halted) so that the user knows about what is most...
I'm new to PL/SQL and I'm trying to use a table of records, but I don't know how to use this feature. What is the problem? DECLARE TYPE TIP IS RECORD ( F1 SMALLINT, F2 SMALLINT); TYPE Ve IS TABLE OF TIP; v ve; IND SMALLINT := 0; BEGIN WHILE(IND<20) ...
I'm new to pl/sql ! I'm trying to sort a table of records, using a simple bubble-sort algorithm. What is the problem? Where could I find more information about using table of records ? DECLARE text VARCHAR2(50); TYPE TIP_VECTOR IS TABLE OF INT INDEX BY BINARY_INTEGER; TYPE contorRecord IS record ( codASCII VARCHAR2...
ODP.Net exposes the ability to pass Associative Arrays as params into an Oracle stored procedure from C#. Its a nice feature unless you are trying to use the data contained within that associative array in a sql query. The reason for this is that it requires a context switch - SQL statements require SQL types and an associative array p...
I have a query where not all conditions are necessary. Here's an example of what it looks like when all conditions are used: select num from (select distinct q.num from cqqv q where q.bcode = '1234567' --this is variable and q.lb = 'AXCT' --this is variable and q.type = 'privt' --this is variable ...
My procedure: CREATE OR REPLACE PROCEDURE akcia_nepozicane_s_kurzorom (denny_poplatok IN NUMBER, kilometrovy_poplatok IN NUMBER) AS my_id_auto NUMBER(5); my_poplatok_denny NUMBER(4); my_poplatok_km NUMBER(2); CURSOR c1 IS SELECT id_auto, poplatok_denny, poplatok_km FROM Auta; BEGIN OPEN c1; LOOP ...
I have to generate PL-SQL code, with some common code(invariable) and a variable code. I don't want to use any external tools. Some ways that I can think: Can I go and maintain the common code in a template and with markers, where my java code will generate code in the markers and generate a new file. Maintain the common code in stati...
I have a number of pl/sql procedures that can take several minutes to run. While developing them, I've added a few print statements to help debug and also provide some feedback and progress indicators. Initially, I ran these on small test sets and output was almost instantaneous. Now that I'm testing with larger test sets that take sever...
This is one of those "there's gotta be a better way" questions. Let me set up the problem, then I'll give you my hacked solution, and perhaps you can suggest a better solution. Thanks! Lets take this little tidbit of PL/SQL DECLARE TYPE foo_record IS RECORD (foo%type, bar%type); TYPE foo_records IS TABLE OF foo_record INDEX BY PLS_IN...
What would be the datatype in java equivalent to the PL/SQL datatype BINARY_INTEGER? ...
hi all. the following text is an excerpt of oracle documentation Oracle® Database PL/SQL Language Reference 11g Release 1 (11.1) : Unhandled exceptions can also affect subprograms. If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. However, if you exit with an unhandled exception, PL/SQL does n...
hi all. I use TOAD to do my PL/SQL development. In TOAD when i type a procedure name and press f4, I can see this procedure's source code. I think TOAD get the source code from v$sqltext view. To confirm my thought, I wrote a query: select * from v$sqltext but when I execute the upper query, Oracle give me an error: ORA-00942: tab...
Using Oracle 10g, I need to rename a bunch of FK constraints which all end in LITE to include an FK prefix. My thinking was (I've ensured all names are short enough to accommodate the prefix): DECLARE v_name VARCHAR2(30 BYTE); v_new_name VARCHAR2(30 BYTE); CURSOR c1 is select CONSTRAINT name from user_constraints where constraint...
I have an Oracle database where I'm trying to select a user field from the earliest row (based on a time field) where certain conditions are met, and I don't know how to do it. Here's the gist of my query: SELECT id, CASE WHEN value = 'xyz' THEN 'Pending' ELSE 'Not Pending' END AS status, ti...