In Oracle's PL/SQL I can create a session based global variable with the package definition. With Postgresql's PLpg/SQL, it doesn't seem possible since there are no packages, only independent procedures and functions. Here is the syntax for PL/SQL to declare g_spool_key as a global... CREATE OR REPLACE PACKAGE tox IS g_spool_key ...
What is the equivalent of varchar(max) in Oracle? CLOB? ...
I tried this code and the value of comments from final table having a clob datatype is null or sometimes displays few words. What should I add so that I retrieve the whole data from comments and displays to the textarea? Thanks:-) <%@ page import="java.sql.*" %> <% Class.forName("oracle.jdbc.driver.OracleDriver"); %> <HTML> <TIT...
Hi all, I want to copy an Oracle Database to a DB2 one using DTS in SQL Server 2000. When I set up both connections using their respective driver, I can read both databases, and link the copy operations between them. But when I want to run the DTS package, an error shows up saying "Driver not capable." Here's the log, thanks in advan...
I have this problem I'm hoping someone knows the answer to. I have an oracle stored procedure that takes a customer id and returns all the customer's orders in a ref_cursor. Oversimplifying it, this is what I have: Orders - orderId - siteID Customers - siteID - Name GetOrder(siteID, outCursor) /* returns all orders for a customer */ ...
I have a number from an Oracle database of 47306832975095894070.85314746810624532. When I bring it into SQL Server, it certainly doesn't show that many digits. It shows as 4.73068329750959E+19, and the field is defined as FLOAT. I think that probably includes all the significant digits, but I'm being asked if the number can be store...
Is there a way to use positional parameters when calling Oracle stored procedure using System.Data.OracleClient? IDataParameter parameter = dbCommand.CreateParameter( ); parameter.Value = "Blah Blah"; parameter.Type = OracleType.Varchar; dbCommand.Parameters.Add(parameter); The code above creates a parameter and assigns "Parameter1"...
I have heard these two terms "temporary table" and "global temporary table" used pretty much in similar context. What is the difference between the two? ...
I have a sql query that takes a date parameter (if I were to throw it into a function) and I need to run it on every day of the last year. Does anyone know of an easy way to generate a list of the last 365 days so I can use straight-up sql to do this? Obviously generating a list 0..364 would work too since I could always select SYSDA...
I'm trying to optimize query performance and have had to resort to using optimizer hints. But I've never learned if the optimizer will use more than one hint at a time. e.g. SELECT /*+ INDEX(i dcf_vol_prospect_ids_idx)*/ /*+ LEADING(i vol) */ /*+ ALL_ROWS */ i.id_number, ... FROM i_table i JOIN vol_ta...
As the code shown below, I want to get value from the OracleParameter object. Its datatype is datetime. ... Dim cmd As New OracleCommand("stored_proc_name", cnObject) cmd.Parameters.Add("tran_date_out", OracleDbType.Date, ParameterDirection.Output) ... cmd.ExecuteNonQuery() ... Dim tranDate As Date tranDate = cmd.Parameters("tran_d...
if i have data as follows A | 01/01/2008 00:00:00 B | 01/01/2008 01:00:00 A | 01/01/2008 11:59:00 C | 02/01/2008 00:00:00 D | 02/01/2008 01:00:00 D | 02/01/2008 20:00:00 I want to only select the records whose identifiers (A, B, C or D) have occured twice within a 12 hour period. In this example above this would only be 'A' Can a...
Hi, does anyone have any experience using both of these products and willing to share some pros and cons of each? I love TOAD but I'm moving to Mac, so I'll have to give it away. I can always run TOAD in a VM, but at the same time I'd like to try something new and native to Mac OS. Toad is a memory hog and crashes constantly, so if I co...
Hello, I'm trying to connect to an oracle database with sqldeveloper. I've installed the .Net oracle drivers and placed the tnsnames.ora file at C:\Oracle\product\11.1.0\client_1\Network\Admin. I'm using the following format in tnsnames.ora. dev = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XXX.XXX)(PORT = XXXX)...
I have an Oracle table that contains a field of LONG RAW type that contains ASCII character data. How can I write a query or view that will convert this to a more easily consumed character string? These are always going to be single-byte characters, FWIW. ...
So I've never worked with stored procedures and have not a whole lot of DB experience in general and I've been assigned a task that requires I create a package and I'm stuck. Using SQL Developer, I'm trying to create a package called JUMPTO with this code... create or replace package JUMPTO is type t_locations is ref cursor; proc...
Hi everybody, i've setup a demo to test datasnap 2009, and i've got a problem with dbexpress. The dbexpress error message, (same at design time or runtime) : "Cannot load oci.dll library (Error code 127). The OCI.dll library may be missing from the system path." I'm using Oracle 8.0.5. The path to oci.dll (c:\orant\BIN) is in my syste...
Let say there is a table: TableA:Field1, Field2, Field3 and associated JPA entity class @Entity @Table(name="TableA") public class TableA{ @Id @Column(name="Field1") private Long id; @Column(name="Field2") private Long field2; @Column(name="Field3") private Long field3; //... more associated getter and setter... } ...
Hi I have following data in the table: ID-----startDate----endDate 5549 2008-05-01 4712-12-31 5567 2008-04-17 2008-04-30 1 5567 2008-05-01 2008-07-31 1 5567 2008-09-01 4712-12-31 2 5569 2008-05-01 2008-08-31 5569 2008-09-01 4712-12-31 5589 2008-04-18 2008-04-30 5589 2008-05-01 4712-12-31 5667 2008-...
I'm trying to make the following code smaller. Is this possible? select a.* from table1 a WHERE a."cola1" = 'valuea1' UNION ALL select a.* from tablea1 a inner join tablea2 b on a."cola2" = b."colb2" WHERE a."cola1" = 'valuea2' and b."colb3" = 'valueb3' In effect I'm looking for records from table1 for value1 or value2, but for rec...