how can I make oracle procedure with oracle xe, how can I check if input is valid or not? ex: if my input is number and I type in char that procedure prints out something in that case, I've dealt with SQL but not with these kind of procedures? any help is appreciated UPDATE This was a dummy example .. what I meant is to start from the ...
Hello, I'm doing an Asp.Net application which will, eventually, generate 'on the fly' report with parameters entered by the user. I'm trying to understand how to dynamically generate Crystal Report reports. Actually, I've got a stored proc being called and filling a DataTable with the results. But there is one part missing to my problem...
Hi!!! I have one table : Questionmaster. it stores DisciplineId,QuestionId,QuestionText etc... Now My Question is: I need 10 records of particular DisciplineId, 20 records for another DisciplineId and 30 records for Someother DisciplineId.... What should I do for that? How can I club all statement and get just 60(10+20+30) rows select...
Hi... I have a table named testtransaction which stores pervQuestionId and NextQuestionId... How to insert records in this table through cursor? there is something cursoe.getnext()...how do i implement it? My code is shown below: create or replace function store_data(disciplineid in char, NoO...
Hi, using a Oracle 10g db I have a table something like this: create table x( ID NUMBER(10) primary key, wedding DATE NOT NULL ); how can I select * from x where wedding is in june 2008??? I know it is probably an easy one but I couldn't find any satisfying answer so far. Help is very much appreciated. ...
What are the best references for Oracle PL/SQL? I have an excellent knowlege of SQL-92 and of MS-SQL extensions, but now I'm working with Oracle and I'm struggling to find good references for the PL/SQL language. I am looking for references for the following: Variable Loops Cursor Packages Trigger Stored Procedures Temporary Table...
Simply trying to get a cursor back for the ids that I specify. CREATE OR REPLACE PACKAGE some_package AS TYPE t_cursor IS REF CURSOR; TYPE t_id_table IS TABLE OF NVARCHAR(38) INDEX BY PLS_INTEGER; PROCEDURE someentity_select( p_ids IN t_id_table, p_results OUT t_cursor); END; CREATE OR REPLACE PACKAGE BODY some_...
I'm trying to enable logging on an Oracle Scheduled Job so that when I look at the run details of the job, I can discern what the procedure of the job worked on and what it did. Currently, the procedure is written to log out through dbms_output.put_line() since this is nice for procedures and SQL*Plus by just enabling set serveroutput on...
I've got a T-SQL script, that converts field to IDENTITY (in a weird way). How do I convert it to PL/SQL? (and, probably, figure out, if there is a simpler way to do this - without creating a temporary table). The T-SQL script: -- alter table ts_changes add TS_THREADID VARCHAR(100) NULL; -- Change Field TS_ID TS_NOTIFICATIONEVENTS t...
I have a table "defects" in the following format: id status stat_date line div area 1 Open 09/21/09 F A cube 1 closed 01/01/10 F A cube 2 Open 10/23/09 B C Back 3 Open 11/08/09 S B Front 3 closed 12/12/09 S B Front My problem is that I want to ...
Just wondering if anyone has ever thought of/implemented something like this. I'm considering implementing a DB versioning scheme. Once per day, a job would run, which outputs all DDL and source code into a single file. A hash would then be run against that file. The hash would be stored in the DB with a version number. Anytime the h...
I am validating an XML document against a schema. So far, all is going well, but there are some significant differences between the generated document and the schema (hence the validation). I've noticed that Oracle will stop validating after the first error is encountered. I'd like to be able to compile a list of errors and take them to ...
Hi, I have a Pl/Sql procedure signature that look like this foo(param1 IN type1, param2 IN type1, c OUT REF CURSOR). This stored procedure is being called in C#. In the C# code, I fill a DataTable with this cursor. I would like to know when the cursor will be closed. Should I close it in the SP? In the code? Or is the object OracleRef...
We're using a PL/SQL table (named pTable) to collect a number of ids to be updated. However, the statement UPDATE aTable SET aColumn = 1 WHERE id IN (SELECT COLUMN_VALUE FROM TABLE (pTable)); takes a long time to execute. It seems that the optimizer comes up with a very bad execution plan, instead of using the...
I tried to register a schema in Oracle the other day to validate an XML document. The code I used was fairly simple: dbms_xmlschema.registerSchema(schemaURL => 'http://www.myCompany.com/schema', schemaDoc => :schemaCLOB); At first, everything seemed to work well, as far as validation goes. I noticed thoug...
Hello experts, I've been working on this for the past few days and I can't find my way out of it. I have a C# web application that needs to print reports on screen. This same application calls a stored procedure on the database (Oracle) and it returns a cursor. Here is the procedure PROCEDURE report_total(beginDate IN DATE, endDate IN D...
I have created this type: create or replace type PRODTABLE as table of PROD_OBJ; and I use that PRODTABLE in the follow PLSQL code: FUNCTION INSERT_PRODUCTS ( a_supplier_id IN FORNECEDOR.ID_FORNECEDOR%TYPE, a_prodArray IN PRODTABLE ) RETURN NUMBER IS v_error_code NUMBER; v_error_mess...
I have an interview coming up for an entry level pl/sql developer job. I took a class in pl/sql but have not done any projects in pl/sql (other languages yes). I do know basic sql (joins subqueries etc), so i am wondering more about specific pl/sql information I should know. ...
Hi I have an oracle db and I need a table containing all the dates spanning 2 years; for example from 01/01/2011 to 01/01/2013. First I thought of a sequence but apparently the only supported type is number, so now I am looking for an efficient way to do this cheers hoax ...
Hi Gurus, I am working on a project where I am importing in orders from an external vendor. I will need to validate the information prior to loading it into our ERP system and then send a response with shipping information once we have processed and shipped the order. I wanted to see how others would track the steps necessary to make ...