Hi, i m using Oracle 9i. I m fetching data from a cursor into an array : FETCH contract_cur BULK COLLECT INTO l_contract ; But now i want to "convert" this *l_contract* into a CLOB variable *l_clob* Is there an easy way to do that? Or otherwise, how do i convertthe rows from a SELECT statement into one single CLOB Variab...
Say a table has several subclass types. How can I see all columns from different types in sqldeveloper? In table view, only can see common columns. Thanks. ...
I have the following: create type customer as object ( id number, name varchar2(10), points number, member procedure add_points(num_points number) ) not final; / create type body customer as member procedure add_points(num_points number) is begin points := points + num_points; commit; end add_points; end; / create table custome...
What is the difference between these two pieces of code? TYPE t_my_cursor IS REF CURSOR; v_my_cursor t_my_cursor; OPEN v_my_cursor FOR SELECT SomeTableID FROM MYSCHEMA.SOMETABLE WHERE SomeTableField = p_parameter; And... CURSOR v_my_cur(p_parameter VARCHAR2) IS SELECT SomeTableID FROM MYSCHEMA.SOMETABLE WHERE SomeT...
I have a type myType declared with a member procedure insert_obj. When i try this code, i get the following error: declare v_obj myType; begin v_obj.insert_obj(1,2,3); end; ORA-30625: method dispatch on NULL SELF argument is disallowed I am assuming this is because i have no object on which to call the method... but i cannot ...
Hello Lets assume I have this sample data: | Name | ID | PARENT_ID | ----------------------------- | a1 | 1 | null | | b2 | 2 | null | | c3 | 3 | null | | a1.d4 | 4 | 1 | | a1.e5 | 5 | 1 | | a1.d4.f6 | 6 | 4 | | a1.d4.g7 | 7 | 4 | | a1.e5.h8 | 8 | 5 ...
I'm trying to create a standard UPDATE query for a table. However, if certain criteria are met, some columns should be included/excluded from the UPDATE statement. For example: UPDATE TBL_PROJECT SET REVISION_COUNT = V_REVISION_COUNT ,PRIMARY_BRANCH = IN_PRIMARY_BRANCH ,PROJECT_STATUS = IN_PROJECT_STATUS ... WHERE ...
I have a procedure that accepts an array of folder IDs and needs to return a list of document IDs. Folders are associated to documents in a one-to-many relationship--there are many documents for each folder. Specifically, there is a documents table which has a parent_folderid fk to the folders table. This is what I have: PROCEDURE get_...
I am going on a job interview and have zero experience with MS SQL Server. However I have 1 year with Oracle. Is there such a huge difference between the two? What programming questions can I expect? ...
Hi. I'm stuck with the following: I have a SQL query in a ksh and for the values like 0.23, 0.55 it displays only .23 .55. Anyone have a idea ? There is some parameters to set ? Thanks alot. C.C. ...
I have two very large tables and I need to process a small resultset from these tables. However, processing is done in several functions each and function must do some joining in order to format the data in proper way. I would definitely need to cache the initial resultset somehow so it can be reused by the functions. What I would like ...
I have tables Building and Address, where each Building is associated with 0..n Addresses. I'd like to list Buildings with an associated Address. If a Building has several entrances, and thus several Addresses, I don't care which one is displayed. If a Building has no known addresses, the address fields should be null. This is, I want ...
I've been somewhat haphazardly grouping subsections of code in BEGIN...END blocks when it seems right. Mostly when I'm working on a longer stored procedure and there's a need for a temporary variable in one spot I'll declare it just for that portion of the code. I also do this when I want to identify and handle exceptions thrown for a ...
I am trying to identify table types based on the columns they contain in a stored procedure. The query I initialy came up with is as follows: SELECT CASE WHEN col_one. IS NOT NULL THEN 'COL1' WHEN col_two IS NOT NULL THEN 'COL2' ELSE 'NEITHER' END INTO ls_table_type FRO...
I know it seems like a basic thing, but I've never done this before. I'd like to return a single record from an existing table as the result of an Oracle PL/SQL function. I've found a few different ways of doing this already, but I'm interested in the best way to do it (read: I'm not all that happy with what I've found). The jist of w...
I have an AFTER INSERT OR UPDATE OR DELETE trigger that I'm writing to store every record revision that occurs in a certain table, by copying the INSERT and UPDATE :NEW values into a mirror table, and for DELETE the :OLD values. I could un-clutter my code considerably by conditionally passing either the :NEW or :OLD record into a proced...
I have been trying to find out what the difference is between the IS and AS keywords in PL/SQL when creating an Oracle function or procedure. I have searched and have been unable to find any information on this. Does anyone know the difference? ...
I have read only access to a few tables in an Oracle database. I need to get schema information on some of the columns but i'm having trouble doing so. I'd like to use something analogous to MS SQL's sp_help. I see the table i'm interested in listed in this query. SELECT * FROM ALL_TABLES When I run this query, Oracle "tells me table ...
In Oracle, if the current user tries to revoke all his permissions, what'll happen? For example, if I'm a user(John) created with WITH GRANT OPTION - can I revoke my permissions? ...
We have a table which has a DATE column d. I'd like to get all the rows where the d column is greater / lower than some value, regardless of the date. For example | d | ------------------- |2009/11/1 15:55:23| -------------------- |2009/11/2 15:55:23| -------------------- |2009/11/3 15:55:23| -------------------- |2009/...