Hi, I’m writing pl/sql procedure that exports data from Oracle to Excel. I need data formatting so I can’t use CSV. I’ve already tried with XML but it generates too large files when I want to export e.g. 70000 rows with 50 columns (almost 300 MB!!!). That’s why I decided to use HTML tags to generate XLS file – it is smaller than XML a...
Is there anybody who has successfully accessed a Web service from an Oracle stored procedure? If so, was it a Java stored procedure? A PL/SQL stored procedure? Is there any reason why I should not be trying to access a WS from a stored proc? Here are a couple refs that I found so far Database Web Services Calling external Web Servi...
I've been running into some problems duplicating some old ASP files into some newer .NET 2.0 code. One of the tasks is to merge the 4-5 SQL statements into one. While I have done this and had some success in performance boosts, Oracle is a new bag for me. This problem however surpasses my own SQL skills as I haven't done this before. Ba...
How do I declare a session variable in PL/SQL - one that will persist for the duration of the session only, without me having to store it in the database itself? ...
SQL Server procedure can return result sets. I have a table emp(emp__id, emp__name, ...). The procedure below will return a list of employees that matched with the name provided. CREATE OR REPLACE PROCEDURE get_employee_by_name ( @name VARCHAR(100) ) AS SELECT emp_id, emp_name FROM emp WHERE emp_name = @name; So in the client code, to...
Is there any free IDE for Pl/SQL development ...
I need to transform an Oracle SQL statement into a Stored Procedure therefore users with less privileges can access certain data field: SELECT info_field, data_field FROM table_one WHERE some_id = '<id>' -- I need this <id> to be the procedure's parameter UNION ALL SELECT info_field, data_field FROM table_two WHERE ...
Hi, I'm using Oracle 10g and I'm trying to "stack" the conditions in a CASE statement, like I would do in C++ : case 1: case 2: // instructions break; i.e. having the same code block executed for two different successful conditions. I've tried : WHEN 1, 2 THEN WHEN 1 OR 2 THEN ... without luck. Is it even possible ? Than...
In T-SQL, SPACE() function is used to add spaces to a string. For e.g. @s = 'He' + space(5) + 'llo' Output He llo So is there any function in PL/SQL that is equivalent to SPACE()? Thank you. ...
The task is to take a list of tables which is changeable. Write a piece of PL/SQL that when executed outputs every tables rows into individual csv files. So if 5 tables. You will get 5 CSV files with the relevant table data in it. The CSV should be | delimited and have " around each value (for easy import to excel) All I know is the ...
The loop below is callign a proc that does various 'things' If it should throw an exception it also 'raises' it. I want to catch it and ignore it and allow the loop to continue processing the next value in the array. Thanks WHILE indx IS NOT NULL LOOP table_dump_csv(tableList(indx), tableList(indx) || '.csv'); indx := table...
Hi everyone, I have a quick question about fetching results from a weakly typed cursor and was wondering if anyone had come across this problem before? My set up is as follows; Inner function; create or replace FUNCTION A_CURSOR_TEST_INNER ( varCursor OUT SYS_REFCURSOR ) RETURN NUMBER AS varStatus NUMBER; BEGIN OPEN varCursor ...
A number of stored procedures I support query remote databases over a WAN. The network occasionally goes down, but the worst that ever happened was the procedures failed and would have to be restarted. The last couple weeks it's taken a sinister turn. Instead of failing the procedures hang in a wierd locked state. They can't be kille...
I have a table where I'm recording if a user has viewed an object at least once, hence: HasViewed ObjectID number (FK to Object table) UserId number (FK to Users table) Both fields are NOT NULL and together form the Primary Key. My question is, since I don't care how many times someone has viewed an object (after the f...
How can I get a PL/SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT? For example how do a SELECT like: SELECT foo, bar FROM foobar; Hint : BEGIN SELECT foo, bar FROM foobar; END; doesn't work. ...
Hi, i need a report and i should use pivot table for it.Report will be group by categories .It is not good to use case when statement because there are many categories.u can think Northwind Database as sample and All Categories will be shown as Columns and Report will show customers preference among Categories.I dont know another soluti...
How do I make a backup of a PLSQL db? ...
How can I do Exp and Imp by using PL/SQL? ...
I currently have an INSERT TRIGGER which in Oracle 10g runs a custom defined function that generates a funky alpha-numeric code that is used as part of the insert. I really need to make sure that the function (or even trigger) is thread safe so that if two users activate the trigger at once, the function used within the trigger does NOT...
Is there an existing PL/SQL method which takes a string and returns the same string but with backslashes preceding any regexp chars? ...