This has been driving me crazy for a while: DECLARE TYPE AttrValueRec IS RECORD ( attr VARCHAR2(40), val VARCHAR2(2000), inst NUMBER(4) ); FUNCTION create_attrval(attr AttrValueRec.attr%TYPE, val AttrValueRec.val%TYPE, ...
Here the Package.. CREATE OR REPLACE PACKAGE G_PKG_REFCUR AS TYPE rcDataCursor IS REF CURSOR; END; Let's consider the PROC.. Create procedure gokul_proc( pId in number, pName in varchar2, OutCur OUT G_PKG_REFCUR.rcDataCursor ) is BEGIN Open OutCur For select * from gokul_table ob where ob.active_s...
Hi, is there any tool, library that can analyze plsql code, like PMD/CheckStyle do for Java ? ...
Suppose I have some data types defined in PL/SQL: TYPE foo_t IS RECORD (...); TYPE foo_table_t IS TABLE OF foo_t INDEX BY BINARY_INTEGER; TYPE bar_t IS RECORD (...); TYPE bar_table_t IS TABLE OF bar_t INDEX BY BINARY_INTEGER; Is it possible for me to write a procedure capable of accepting any data type derived from TABLE (for example...
How would you write a SQL query to get the size of blob in a more human readable form? The example would be something like a large word document is being stored in a blob on a table. I would want to execute something like: select fnc_getReadableSize(documents.doc) from documents where id = ? output: 23.4 MB ...
so simple, if I create my fuction as CREATE OR REPLACE FUNCTION MD5_ENCODE it will run smoothly, but if it satys anonymously within the SQL-Plus block as PL/SQL --> "may not be a function" error. what is this Oracle "feature" again??? many thx. in advance... DECLARE FUNCTION MD5_ENCODE(CLEARTEXT IN VARCHAR2) RETURN VARCHAR2 IS CHK ...
I'm trying to use a procedure (no parameters) to drop all of the user-created database objects located within the schema from where the procedure is launched, but I'm really not sure on how to go about this. Here's what I have so far, but I think I'm going about this the wrong way. create or replace procedure CLEAN_SCHEMA is cursor sc...
I need to return a rowset from an Oracle procedure, and then delete them in that same procedure. Is there a neat way of doing this without temp tables? Something like an in-memory cursor maybe? Basically I'm popping the records off a queue, and I want to avoid two round trips because it's a very frequent process. ...
I'm an accomplished user of SQL; I'm confident creating schema and optimizing queries in ANSI-SQL, for Sybase, MS SQL Server, MySQL, or postgresql. I understand joins, subqueries, indexes, etc., so I don't need to recapitulate any of that that isn't different under Oracle. I'll be taking a job that'll require using Oracle. Point me to o...
Hi, I was wondering how to go about exporting a query from PL/SQL to an text file or csv file. The query I have in mind exports a huge amount of data (about 1 gig). So I'd also like the data split across multiple files; out1.csv out2.csv out3.csv I'd like to be able to decide how many files to split it across. Anyone have any idea ho...
I need to run a couple of relatively simple SQL update statements to update a single column in an Oracle table with 14.4 million rows. One statement runs a function written in Java and the JVM runs out of memory as I’m doing the update on all 14.4 million rows. Have you written a kind of batch PL/SQL routine that can break this simple u...
I have a PL/SQL function (running on Oracle 10g) in which I update some rows. Is there a way to find out how many rows were affected by the UPDATE? When executing the query manually it tells me how many rows were affected, I want to get that number in PL/SQL. ...
I have a string. In this string i need replace all special characters (0-31 codes) with chosen representation. Representations may be of different formats. May be \x??, or \0???, or 10,13 -> \n, 9 -> \t and all others characters are converting to null. Summary - i need find all symbols with 0-31 codes and replace all of them for appropri...
Using PL/SQL, what are good options for sending large amounts of data to client side code? To elaborate, server side PL/SQL operates on a request and generates a response with a large amount of data that must be sent to the client side code. Are there "good options" for sending down large amounts of data? What types of Oracle pros/cons ...
I am currently working on developing some SQL scripts in PL/SQL Developer and I am getting an "SQL Command not properly ended" error that I am unsure how to solve. The code I am using looks something like this CREATE TABLE temp_table as SELECT * FROM table_x INSERT INTO temp_table SELECT * FROM table_y If I execute the two ...
Is there any difference in performance when you break down a stored procedure into multiple procedures instead of having a big procedure?! wich one is faster?! for example: mainSP callSP1 callSP2 callSP3 end; rather than SP .... ..... .... Thanks guys. ...
Suppose I have a PL/SQL stored procedure as follows: PROCEDURE do_something(foo VARCHAR2 DEFAULT NULL) IS BEGIN /* Do something */ END; Now, suppose do_something is invoked two different ways: /* Scenario 1: The 'foo' parameter defaults to NULL */ do_something(); /* Scenario 2: The 'foo' parameter is explicitly set to NULL */ do...
I am writing a PL/SQL procedure that needs to to dynamically generate some queries, one of which involves creating a temporary table using results from a query taken as a parameter. CREATE OR REPLACE PROCEDURE sqlout(query IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ');'; END; It c...
We have a table which is of the form: ID,Value1,Value2,Value3 1,2,3,4 We need to transform this into. ID,Name,Value 1,'Value1',2 1,'Value2',3 1,'Value3',4 Is there a clever way of doing this in one SELECT statement (i.e without UNIONs)? The column names Value1,Value2 and Value3 are fixed and constant. The database is oracle 9i. ...
How do I get a PL/SQL program to end halfway through? I haven't been able to find any way to gracefully end the program if an exception occurs - if I handle it, it loops back into the code. Basically what I want to do is force the app not to run in certain conditions. So, I want to add something like this to the top of the program: B...