I have a java stored procedure that takes in a clob representing a chunk of javascript and mins it. The structure of the function calling the JSP is as follows: function MIN_JS(pcl_js in clob) return clob as language java name 'JSMin.min(oracle.sql.CLOB) return oracle.sql.CLOB'; In the actual JSP, I have the following: import orac...
I am trying to drop all tables in schema with no rows,but when i am executing this code i am getting an error THis is the code: create or replace procedure tester IS v_count NUMBER; CURSOR emp_cur IS select table_name from user_tables; BEGIN FOR emp_rec_cur IN emp_cur LOOP ...
I'm attempting to develop an audit trail/tracking solution for an existing database written in PLSQL/PHP - however I'm still unsure as of yet on an easy (to implement and maintain) solution for tracking changes to fields/values. For instance, the project tracking portion of the DB APP tracks over 200 fields and ideally I'd like a nice wa...
I'd like to create an Oracle Package and two functions in it: A public function ( function_public ) and a private one ( function_private ). The public function uses the private one in an sql statement. Without pragma the code does not compile (PLS-00231: function 'FUNCTION_PRIVATE' may not be used in SQL) CREATE OR REPLACE PACKAGE PRA...
I am using a D2k 6i form and getting the error on form from stored database(oracle9i) procedure ORA-00001:Unique constraint(.) violated but i m not able to trace out from which procedure it is coming. can anybody help me regarding this ...
We are using utl_file in Oracle 10g to copy a blob from a table row to a file on the file system and when we call utl_file.fclose() it takes a long time. It's a 10mb file, not very big, and it takes just over a minute to complete. Anyone know why this would be so slow? Thanks EDIT Looks like this is related to our file system. When we...
Is there be a setting in Oracle 10g to consider data as case insensitive? I saw a solution here. However, that is done on a session. What I am looking for is a setting either on a schema or on a table to consider its data as case insensitive. If it is on a session then I will have to make the change on all the stored procedures. ...
I have an oracle query in which and i want the result to be in custom order 'SENIOR DIRECTOR', 'DIRECTOR', 'MANAGER', 'EMPLOYEE' which is from the field GRADE_DESCRIPTON. I am using the below query. However I am not getting the desired result The order of the result im getting is 'SENIOR DIRECTOR','MANAGER', DIRECTOR,'EMPLOYEE' SELEC...
Given a url pointing to a file hosted on the webserver, is it possible to read the contents of that url into a clob? And if so, how? ...
I've written a PL/SQL procedure that would benefit if indexes were first disabled, then rebuilt upon completion. An existing thread suggests this approach: alter session set skip_unusable_indexes = true; alter index your_index unusable; [do import] alter index your_index rebuild; However, I get the following error on the first alt...
Hello, everybody. Here is the thing. I have an array of 0 and 1. It is supposed to be a binary string. What i need is to format it to string that contains base36 conversion from that binary. In other words, i need to do this: array of 1 and 0 -> some kind of binary number -> convert to base36 number -> put it into string. How to do that?...
I'm trying to clean up an accidental installation of LOG4PLSQL into the wrong (i.e., SYS) schema. There is a queue table called QTAB_LOG that needs to go away. I have successfully stopped and dropped the associated queue: call DBMS_AQADM.STOP_QUEUE('LOG_QUEUE'); call DBMS_AQADM.DROP_QUEUE('LOG_QUEUE'); But dropping the queue table its...
I have a function that takes as one of it's arguments a VARRAY of pl/sql Objects. How do I execute this stored procedure and bind the resultset that it returns to a data grid in TOAD for Oracle? ...
I'm trying to create a function in package that returns a table. I hope to call the function once in the package, but be able to re-use its data mulitple times. While I know I create temp tables in Oracle, I was hoping to keep things DRY. So far, this is what I have: Header: CREATE OR REPLACE PACKAGE TEST AS TYPE MEASURE_RECOR...
We have two tables: Vehicle: Id RegistrationNumber LastAllocationUserName LastAllocationDate LastAllocationId Allocations: Id VehicleId UserName Date What is the most efficient (easiest) way to update every row in Vehicle table with newest allocation? In SQL Server I would use UPDATE FROM and join every Vehicle with newest All...
Here is where the error is occuring in the stack: public static IKSList<DataParameter> Search(int categoryID, int departmentID, string title) { Database db = new Database(DatabaseConfig.CommonConnString, DatabaseConfig.CommonSchemaOwner, "pkg_data_params_new", "spdata_params_search"); db.AddParameter("cat...
Hi folks, In Oracle 10g, is there a way to do the following in PL/SQL? for each table in database for each row in table for each column in row if column is of type 'varchar2' column = trim(column) Thanks! ...
My question is short. I create a cursor to get some values from my table. I want to get the current record of cursor (the fetched record) and the next record from the cursor without fetching it, because I want to make a calculation over the current record and the next record. In traditional programming it's a simple operation; you can d...
How can I count only NULL values in Oracle/PLSQL? I want to count only the null values. Is there a function that does that? ...
Hi all Sorry my English is bad.I hope u can get what I want. I have lots of *.sql files that i want to write a program to compile them and if there is any issue(problem or mistake) report me. One of my friend write an IDE for java,as I remember he use javac to generate the codes error,in other hand maybe u see when u try to write code ...