I've written PL/SQL code to denormalize a table into a much-easer-to-query form. The code uses a temporary table to do some of its work, merging some rows from the original table together. The logic is written as a pipelined table function, following the pattern from the linked article. The table function uses a PRAGMA AUTONOMOUS_TRANSA...
Are there any good PL/SQL libraries for JSON that you've worked with and found useful? In PL/SQL, I'm having to tediously hand code the return of JSON values to JavaScript functions. I found one PL/SQL library for auto-generating JSON, but it doesn't do exactly everything I need it too. For example, I couldn't extend the base functions ...
Our application is interfacing with a lot of web services these days. We have our own package that someone wrote a few years back using UTL_HTTP and it generally works, but needs some hard-coding of the SOAP envelope to work with certain systems. I would like to make it more generic, but lack experience to know how many scenarios I would...
I've worked with T-SQL for years but i've just moved to an organisation that is going to require writing some Oracle stuff, probably just simple CRUD operations at least until I find my feet. I'm not going to be migrating databases from one to the other simply interacting with existing Oracle databases from an Application Development per...
I'm fairly new to Oracle triggers and PL/SQL still, but I think I might be missing something here. Here is the relevant part of the trigger, CURSOR columnNames (inTableName IN VARCHAR2) IS SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = inTableName; /* Removed for brevity */ OPEN columnNames('TEMP'); And here is the er...
How do I get started? ...
PL/SQL is not my native tongue. Oracle supports writing stored procedures in Java. What are the advantages of doing this over writing the stored procedures in PL/SQL ...
I am a bit rusty on my cursor lingo in PL/SQL. Anyone know this? ...
I'm starting work on a program which is perhaps most naturally described as a batch of calculations on database tables, and will be executed once a month. All input is in Oracle database tables, and all output will be to Oracle database tables. The program should stay maintainable for many years to come. It seems straight-forward to imp...
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 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(); ......
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 ...
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? ...
I'm using sql*plus 9.2 on Oracle 10g enterprise. I have created some scripts that do basic inserts using parameters that I pass through the command prompt. It seemed logical that I should be able to run a bunch of inserts in a loop. So I tried the following: --begin DECLARE TYPE va_orgs IS TABLE OF nbr.lien_item.lien_item_name%type; ...
Hi I have a dilemma, I'm using java and oracle and trying to keep querys on plsql side. Everything is OK, until I have these complex querys which may and may not have conditions. Its not hard in Java to put together WHERE clause with conditions, but its not nice. And in pl sql side I also found out that the only possibility for 'dynamic ...
I have a file. I want to get its contents into a blob column in my oracle database or into a blob variable in my PL/SQL program. What is the best way to do that? ...
I am trying to do the following in SQL* PLUS in ORACLE. -Create a variable -Pass it as output variable to my method invocation -Print the value from output variable I get the error "undeclared variable" error. I am trying to create a variable that persists in the session till i close the SQL*PLUS window. variable subhandle number; exe...
I have worked with SQL on Oracle 9, and a bit on SQL Server 2000 for about a year. I need to get a jumpstart on working with PL/SQL in Oracle 9i. I work with a handful of ASP.NET 2.0 sites and some VB.NET (VS2005) apps in terms of code experience. What books or websites would you recommend to help me bridge the gap? ...
For a governmental agency, we build a release management system developped in PHP and Oracle. The data for this application is stored in database tables and is processed with PL/SQL packages and procedures. The release management process is extensively based on metadata coming from Subversion repositories. We access the repositories fr...