Sometimes, in PL SQL you want to add a parameter to a Package, Funtion or Procedure in order to prepare future functionallity. For example: create or replace function doGetMyAccountMoney( Type_Of_Currency IN char := 'EUR') return number is Result number(12,2); begin Result := 10000; IF char <> 'EUR' THEN -- ERROR NOT IMPLEME...
I am writting JAVA programme using JDBC for database conntectivity , I am calling one stored procedure in that which is returning ORACLE REF CURSOR , IS there any way I can handle that without importing ORACLE PACKAGES ? ...
I have loaded image into a new, initialized Oracle ORDImage object and am processing it by PL/SQL. I can read its properties, but cannot process it with the process() method. vLocalImage ORDImage := ORDImage.init(); ... vLocalImage.source.localdata := PORTAL.wwdoc_admin.get_document_blob_content(pFile); vLocalImage.setProperties(); ......
I have two tables I would like to complare. One of the columns is type glob. I would like to do something like this: select key, glob_value source_table minus select key, glob_value target_table Unfortunately, Oracle can't perform minus operations on globs. How can I do this? ...
Is there a better way to forcefully disconnect all users from an Oracle 10g database schema than restarting the Oracle database services? We have several developers using SQL Developer connecting to the same schema on a single Oracle 10g server. The problem is that when we want to drop the schema to rebuild it, inevitably someone is st...
For a given table 'foo', I need a query to generate a set of tables that have foreign keys that point to foo. I'm using Oracle 10G. ...
I have a script that takes a table name and generates a control file by querying all the columns/rows the table. This works fine for numeric and character data but fails on timestamp data so I need to adjust the script to output the timestamp data into the control in such a way that it can be read in properly. So essentially, my questi...
I'm trying this with Oracle SQL Developer and am on an Intel MacBook Pro. But I believe this same error happens with other clients. I can ping the server hosting the database fine so it appears not to be an actual network problem. Also, I believe I'm filling in the connection info correctly. It's something like this: host = foo1.co...
default is 49 how to edit to higher? ...
In Oracle I can declare a reference cursor... TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE; ...and use it to pass a cursor as the return value... FUNCTION end_spool RETURN t_spool AS v_spool t_spool; BEGIN COMMIT; OPEN v_spool FOR SELECT * FROM ...
how to connect oracle by php on MAC OS X? oci? ...
I am trying to build a dependency graph of tables based on the foreign keys between them. This graph needs to start with an arbitrary table name as its root. I could, given a table name look up the tables that reference it using the all_constraints view, then look up the tables that reference them, and so on, but this would be horrible...
When you create a Microsoft Access 2003 link to an Oracle table using Oracle's ODBC driver, you are sometimes asked to state which columns are the primary key(s). I would like to know how to change that initial assignment, or even how to get Access/ODBC to forget the assignment. In my limited testing I wonder if the assignment isn't cac...
The scenario is this We have two applications A and B, both which are running in separate database (Oracle 9i ) transactions Application A - inserts some data into the database, then calls Application B Application B - inserts some data into the database, related (via foreign keys) to A's data. Returns an "ID" to Application A Applicat...
I have Oracle SQL Developer already installed and am able to connect to and query Oracle databases. Using Help -> Check for Updates I was able to install the Oracle MySQL Browser extension but there are no connection options for MySQL databases. ...
What is the difference between Views and Materialized Views in Oracle? ...
I have a Oracle database backup file (.dmp) that was created with expdp. The .dmp file was an export of an entire database. I need to restore 1 of the schemas from within this dump file. I dont know the names of the schemas inside this dump file. To use impdp to import the data i need the name of the schema to load. So, I need to insp...
On my desktop I have written a small Pylons app that connects to Oracle. I'm now trying to deploy it to my server which is running Win2k3 x64. (My desktop is 32-bit XP) The Oracle installation on the server is also 64-bit. I was getting errors about loading the OCI dll, so I installed the 32 bit client into C:\oracle32. If I add this...
I am using Oracle 9 and JDBC and would like to encyrpt a clob as it is inserted into the DB. Ideally I'd like to be able to just insert the plaintext and have it encrypted by a stored procedure: String SQL = "INSERT INTO table (ID, VALUE) values (?, encrypt(?))"; PreparedStatement ps = connection.prepareStatement(SQL); ps.setInt(id); p...
I want to create a stored procedure with one argument which will return different sets of records depending on the argument. What is the way to do this? Can I call it from plain SQL? ...