In Oracle I've created a data type: TABLE of VARCHAR2(200) I want to have a variable of this type within a Stored Procedure (defined locally, not as an actual table in the DB) and fill it with data. Some online samples show how I'd use my type if it was filled and passed as a parameter to the stored procedure: SELECT column_value cu...
Let's say we have a simple XML document (doc.xml) like this: <?xml version="1.0" encoding="UTF-8"?> <Envelope xmlns="http://www.someexample.com/examples"> <Salutation Id="test"> Welcome! </Salutation> </Envelope> And a certificate file:test.p12 How to make a solution using Oracle 9i PL/SQL that digitally signs XML document...
In the JDBC code, I have the following that is working with SQL Server: CallableStatement stmt = connection.prepareCall("{ call getName() }"); ResultSet rs = stmt.executeQuery(); if(rs != null) { while(rs.next()) { //do something with rs.getString("name") } } Multiple rows are returned for the above situation. I under...
I have two variable TO_DATE and FROM_DATE. My table has two fields DOJ and DOL. I want to select all the records whose DOJ < TO_DATE and DOL > FROM_DATE. How can i write an SQL query in for this?? ...
I have been experiencing some frustrations trying to make a simple Oracle cursor retrieval procedure work with JDBC. I keep on getting an error of "[Oracle][ODBC][Ora]ORA-06553: PLS-306: wrong number or types of arguments in call to 'GETNAME'", but I cannot figure out what I am doing wrong. Here is my code in Java: CallableStatement s...
Using a PLSQL script, is it possible to iterate over binary files in a folder and insert them as a BLOB in Oracle 10g? Any examples would be appreciated. ...
We just converted our sql server stored procedures to oracle procedures. Sql Server SP's were highly dependent on session tables (INSERT INTO #table1...) these tables got converted as global temporary tables in oracle. We ended up with aroun 500 GTT's for our 400 SP's Now we are finding out that working with GTT's in oracle is conside...
Hello, I writing an application using Oracle 10g. I am currently facing this problem. I take in "filename" as parameter of type varchar2. A sample value that filename may contain is: 'TEST || to_char(sysdate, 'DDD')'. In the procedure, I want to get the value of this file name as in TEST147. When i write: select filename into ffilena...
I am new to DBMS_PROFILER. All the examples I have seen use a simple top-level procedure to demonstrate the use of the profiler, and from there get all the line numbers etc. I deploy all code in packages, and I am having great difficulty getting my profile session to populate the plsql_profiler_units with useful data. Most of my runs loo...
I'm using table with a counter to ensure unique id's on a child element. I know it is usually better to use a sequence, but I can't use it because I have a lot of counters (a customer can create a couple of buckets and each of them needs to have their own counter, they have to start with 1 (it's a requirement, my customer needs "human r...
Hello, I have a column with pipe separated values such as: '23|12.1| 450|30|9|78|82.5|92.1|120|185|52|11' I want to parse this column to fill a table with 12 corresponding columns: month1, month2, month3...month12. So month1 will have the value 23, month2 the value 12.1 etc... Is there a way to parse it by a loop or delimeter instea...
I have a file to execute in Ksh written by someone. It has a set of commands to execute in sqlplus. It starts with, sqlplus -s $UP <<- END followed by a set of ddl commands such as create,drop,etc., When I execute the file in the shell, I get the error in the starting line quoted above. I understand "-s" starts the sqlplus in silent...
I am making a test. I have all tests in rows, so my rows looks like this; ID | TEST ---------------------------------- 1 | 'select sysdate from dual' 2 | 'select sysdatesss from dual' Now I read it row by row and I need to test it with EXPLAIN PLAN FOR so the for the first row it would be EXPLAIN PLAN FOR select sysdate fro...
Hi, I'm having scenario to which a sql query need to be built. I tried to come up with a efficient query, but could not find a clear way of doing this. My scenario is as follows: I'm having TABLE_A and TABLE_B ,where FIELD_AB will definitely be a filed of TABLE_A, however, there can be exist FIELD_AB in TABLE_B. I need to retrieve val...
Generally, when you specify a function the scale/precision/size of the return datatype is undefined. For example, you say FUNCTION show_price RETURN NUMBER or FUNCTION show_name RETURN VARCHAR2. You are not allowed to have FUNCTION show_price RETURN NUMBER(10,2) or FUNCTION show_name RETURN VARCHAR2(20), and the function return value ...
I'm using PHP and OCI8 to execute anonymous Oracle PL/SQL blocks of code. Is there any way for me to bind a variable and get its output upon completion of the block, just as I can when I call stored procedures in a similar way? $SQL = "declare something varchar2 := 'I want this returned'; begin --How can I return the value of 'someth...
How could this SQL... CREATE TABLE NewTable AS SELECT A,B,C FROM Table1 minus SELECT A, B, C From Table2 ...create a new table with NULL values in column A when neither Table1 or Table2 had NULL values for in column A? But on the other hand, this SQL... SELECT * FROM ( SELECT A,B,C FROM Table1 minus SELECT A, ...
I'm trying to bind a php variable to pl/sql array. The pl/sql procedure works fine when I execute it manually and set the bind, so I know that's not the problem. It's the oci_bind_array_by_name that is causing problems. I get the following error message for the line in the PHP code below where I call the oci_bind_array_by_name functio...
I have a table with columns named with the number of hour of day like this: col00 NUMBER(5) col01 NUMBER(5) col02 NUMBER(5) ... col23 NUMBER(5) ...and I have another query that returns a count by hour. I want to recover the colXX value by hour.... then I can recover with "decode" or "case when..." but I want know if exists any way to...
Given an Oracle Merge statement with a rejection limit, is there a shorthand way to identify how many rows were rejected, without being forced to query the rejection destination? The rejection destination has records pre-merge, so would currently have to be counted twice and the difference taken, but it seems like there should be a cou...