Executing a Function in Oracle




I have created a Package Body and Package Spec for a function to select a username and a password from a table and return the username.

The code i have created is this:-
CREATE OR REPLACE PACKAGE BODY USER_LOGIN
AS

FUNCTION user_select (USERNAME_IN VARCHAR2,
PASSWORD_IN VARCHAR2)
RETURN VARCHAR2 IS
USERNAME_OUT VARCHAR2(12);
BEGIN
SELECT USERNAME
INTO USERNAME_OUT
FROM USERS
WHERE USERNAME = USERNAME_IN
AND PASSWORD = PASSWORD_IN;
RETURN USERNAME_OUT;
END user_select;

END USER_LOGIN;
/

The package body and spec compiles successfully but i am having trouble when i execute this function. I am trying to do this :-

VARIABLE RETVAL VARCHAR2(12)

EXEC User_login.user_select('HELLO','HELLO',:RETVAL );

but i am getting the following error

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'USER_SELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Can anyone please help me
Posted On: Monday 31st of December 2012 01:12:21 AM Total Views:  352
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




Switching off AUTOEXTEND for a datafile

I've got a datafile originally sized at 100M which has now autoextended to 130M. I have created a 2nd file in the tablespace (also of 100M and with AUTOEXTEND set to OFF). how do I switch off the AUTOEXTEND on the 1st file I must be using the wrong syntax as I can't seem to do it.
VIEWS ON THIS POST

132

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle Universal Installer

How to check the version of Oracle universal installer installed
VIEWS ON THIS POST

54

Posted on:

Wednesday 7th November 2012
View Replies!

regarding rollback segment

, regarding perfomance tuning in ROLLBACK SEGMENT. which is faster DEFAULT ROLLBACK SEGMENT or user defined ROLLBACK SEGMENTS. how many user defined ROLLBACK SEGMENT can we create rgds, ramesh
VIEWS ON THIS POST

323

Posted on:

Wednesday 7th November 2012
View Replies!

Cannot find Oracle.DataAccess.dll

I have installed Oracle9i on my server and the Orcal9i client on my machine and I cannot find the Oracle.DataAccess.dll file. Where should it be
VIEWS ON THIS POST

218

Posted on:

Wednesday 7th November 2012
View Replies!

Alias Name for Table columns in oracle

I need tofetchthe data from a table withAlias name in oracle. example: select f1 'Field Name1',f2 'Field Name2' fromsometablename; Please help me out.
VIEWS ON THIS POST

315

Posted on:

Wednesday 7th November 2012
View Replies!

Insert current date in excel.

I am making windows application in C#.net where i have to generate one formatted excel sheet. So that iwant some formula to insert current date which i can use in my source code I have five columns in excel: Name\t\t Date\t\t Status\t\t\t StartTime\t\t\t EndTime ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- If user insert name in cell of name column then current date should be automatically inserted in cell of date column same as with starttime. I do not want to use =TODAY() as this would change the date, daily,
VIEWS ON THIS POST

165

Posted on:

Wednesday 7th November 2012
View Replies!

Grouping issues !!!!

i haven't had this issue before... i am grouping on invoice_no and trying to calculate a value for a commission based on the gm for an invoice, and sql returns the calculation per line number in the invoice; here is the code: select b.invoice_no as invoice, c.salespersoncode as salesid, c.name as salesperson, br.shortname as branch, sum(b.gm) as gm, (c.base*40) as wkly_base, sum(case when sum(b.gm) between 0 and 50 then sum(b.gm)*0 when sum(b.gm) between 51 and 60 then sum(b.gm)*.06 when sum(b.gm) between 61 and 70 then sum(b.gm)*.07 when sum(b.gm) between 71 and 80 then sum(b.gm)*.08 when sum(b.gm) between 81 and 100 then sum(b.gm)*.09 when sum(b.gm) between 101 and 125 then sum(b.gm)*.1 when sum(b.gm) between 126 and 160 then sum(b.gm)*.12 when sum(b.gm) between 161 and 200 then sum(b.gm)*.13 when sum(b.gm) between 201 and 250 then sum(b.gm)*.14 when sum(b.gm) between 251 and 1000 then sum(b.gm)*.15 when sum(b.gm) > 1000 then sum(b.gm)*.15 end) as commission from bfiles b inner join commissions c on c.salespersoncode = b.salesman inner join branch br on br.branchid = b.branch where b.year = @year and b.week between @startweek and @endweek group by br.shortname,c.salespersoncode,c.name,c.base,b.invoice_no,b.gm order by b.invoice_no any thoughts on how to fix this\t i really need it to add up the gm values and group it by invoice_no, then calculate the commission...
VIEWS ON THIS POST

214

Posted on:

Wednesday 7th November 2012
View Replies!

why truncate so slow?

Hi Peeps, I tried to truncate a table that has about 300 records. fter 5 minutes it was not complete. So I stopped it and used the delete command instead. The delete took less than 2 seconds. If truncate is supposed to be faster than the delete, why was the truncate slow and delet fast. Any ideas why
VIEWS ON THIS POST

172

Posted on:

Monday 12th November 2012
View Replies!

Whats the best 10g DB management tool

We have 10g on RHL AS4.0 and are shopping around for a db management tool to manage the databases . Any suggestions on whats the best one out there. It will be run on the desktops which are windows.
VIEWS ON THIS POST

138

Posted on:

Monday 12th November 2012
View Replies!

what does this event do - 22825 ?

I have found that one user is issuing command "ALTER SESSION SET EVENTS '22825 TRACE NAME CONTEXT FOREVER, LEVEL 1'". Can anyone tell what is purpose of using this event what does it do on level 1 Is it good for issuing this staement every day Deba
VIEWS ON THIS POST

273

Posted on:

Monday 12th November 2012
View Replies!

URGENT!!! upgrade from 8.0.5 to 8.1.7 script hanging

Hi all, I am running u0800050.sql to upgrade our 8.0.5 database to 8.1.7.1.5 on NT platform. This script ran for some time and seems like hanging for more than 2 hours on foll. statement. svrmgr> upgrade obj$ set spare1=5 / What should be the cause of this and what should I do next
VIEWS ON THIS POST

100

Posted on:

Thursday 15th November 2012
View Replies!

Urgent!!! Not an Oracle but NT related thread.

Hi all, I am sorry to put this problem in this forum but I need urgent help and could not find relevant NT fourm. I am looking for advice. We were upgrading the service pack from 5 to 6a on NT 4 box. After the upgrade, we are unable to get our NT box to boot up. We get blue screen. Question 1: When the upgrade was going on, some files on the machine were open. Could this create the problem Question 2: What is the option to boot the machine and get all our files stored on RAID set D: and E: back. We have imporatant files on it and unfortunately we do not have the current backup also for the file system. We do not have the emergency boot disk as well. What should we do next If we re-install the OS from a CD, we should still have our data on E:
VIEWS ON THIS POST

195

Posted on:

Thursday 15th November 2012
View Replies!

Urgent help for EXP/IMP!!

Is it possible to set an option in EXP script to only export the structure of table If not is there any other way to do this I am in a lot of hurry for this - help please!!
VIEWS ON THIS POST

272

Posted on:

Thursday 15th November 2012
View Replies!

Upgrading AIX operating system

Do we have to do any with the existing Oracle software and databases if we upgrade our AIX operating system Any reference and suggestion will be appreciated.
VIEWS ON THIS POST

84

Posted on:

Thursday 15th November 2012
View Replies!

Update using CASE statement - can't see it.

Havin' a bad day......... What's wrong with this UPDATE PHILTEST SET TYPE = CASE DATE1 WHEN '05-NOV-03' THEN 'CR' WHEN '01-DEC-03' THEN 'TN' WHEN '01-JAN-04' THEN 'RV' END CASE; END CASE * ERROR at line 6: ORA-00933: SQL command not properly ended
VIEWS ON THIS POST

278

Posted on:

Thursday 15th November 2012
View Replies!

triggers on sys.aud$

My requirement is to notify (by email) whenever a row is inserted into sys.aud$(table used for recording audit information) .. My idea is to create a trigger on sys.aud$ table and that would email me whenever a row is inserted into this table.. trigger fires... My version of oracle is 8.0.5.2.1 Can I use this email feature.. if yes then how.. If no then can i create a trigger in such a way that inserts row into another table in 8.1.7 database and then a trigger on this table to email.. Hope I did not confuse.. Please help
VIEWS ON THIS POST

239

Posted on:

Thursday 15th November 2012
View Replies!

The 'invisible' LISTENER ???

Got a call from a colleague who can't find the listener.ora file on a 9.2 Windows box. He's developing a call to a C prog and needs to set some External Call settings. He can't find any file called listener.ora on the box. I've run LSNRCTL and the current listener is called LISTENER and I can see the services it's listening for. We know it works as we can connect. Just where is the listener.ora text file Using Net Assistant or Manager, there doesn't appear to be a listener configured. Which is strange. Any ideas
VIEWS ON THIS POST

65

Posted on:

Thursday 15th November 2012
View Replies!

Simple question on DB LINKS

.. I created a db link like this. create public database link my_link connect to home_office identified by home using 'j2ee'; Link is created successfully. and i was able to connect to the database. Now when i remove my tnsnames entry for that DB on my work station, the Link doesn't work. So my question is 1) do we need to have tnsnames entry to the database for which i created the link . is so, then what is the point in creating a link, i can as well type home_office/home@j2ee. i just dont understand this thing. can anyone shed some light on where iam getting it wrong. really appreciate it.
VIEWS ON THIS POST

223

Posted on:

Thursday 15th November 2012
View Replies!

Script problem !

I have a script which is supposed to create a new user based on an exisitng user's privileges but after running it it does not create a new user! Run it on Per. Orcl 9i for win 98 as system/manager! Can anyone try it out and let me know what's the problem ---------> rem --------------------------------------------------------------- rem Filename: cr8like.sql rem Purpose: Script to create a new user (with privs) like an existing rem database user. rem --------------------------------------------------------------- set pages 0 feed off veri off lines 500 accept oldname prompt "Enter user to model new user to: " accept newname prompt "Enter new user name: " accept psw prompt "Enter new user's password: " -- Create user...(1) select 'create user &&newname identified by &&psw'|| ' default tablespace '||default_tablespace|| ' temporary tablespace '||temporary_tablespace|| ' profile '||profile||';' from sys.dba_users where username = upper('&&oldname'); -- Grant Roles...(2) select 'grant '||granted_role||' to &&newname'|| decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';' from sys.dba_role_privs where grantee = upper('&&oldname'); -- Grant System Privs...(3) select 'grant '||privilege||' to &&newname'|| decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';' from sys.dba_sys_privs where grantee = upper('&&oldname'); -- Grant Table Privs...(4) select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname' from sys.dba_tab_privs where grantee = upper('&&oldname'); -- Grant Column Privs...(5) select 'grant '||privilege||' on '||owner||'.'||table_name|| '('||column_name||') to &&newname' from sys.dba_col_privs where grantee = upper('&&oldname'); -- Set Default Role...(6) select 'alter user &&newname default role '|| granted_role ||';' from sys.dba_role_privs where grantee = upper('&&oldname') and default_role = 'YES'; ----------------
VIEWS ON THIS POST

323

Posted on:

Thursday 15th November 2012
View Replies!

scn and time change

hi, what i SCN AND WHAT IS THE SIGNIFICANCE. how do i know that SCN VALUES correspond to the WHICH time.
VIEWS ON THIS POST

139

Posted on:

Thursday 15th November 2012
View Replies!

Root cause for node eviction needed

Could you please help us to find out the root cause analysis on why orinoco1 server rebooted (CRS node eviction). below is the error message Jun 27 17:51:59 orinoco1 logger: Oracle CSSD failure 134. Jun 27 17:51:59 orinoco1 logger: Oracle CRS failure. Rebooting for cluster integrity. Jun 27 17:52:00 orinoco1 logger: Oracle clsomon failed with fatal status 12. Jun 27 17:52:00 orinoco1 logger: Oracle CRS failure. Rebooting for cluster integrity. ====== OCCSD LOG ==================================================================================================== ======== [ CSSD]2011-06-27 17:43:21.978 [1199618400] >TRACE: clssgmClientConnectMsg: Connect from con(0x7ac510) proc(0x777f60) pid() proto(10:2:1:1) [ CSSD]2011-06-27 17:43:45.328 [1199618400] >TRACE: clssgmClientConnectMsg: Connect from con(0x7af170) proc(0x773c10) pid() proto(10:2:1:1) [ CSSD]2011-06-27 17:44:45.678 [1199618400] >TRACE: clssgmClientConnectMsg: Connect from con(0x7af170) proc(0x773c10) pid() proto(10:2:1:1) [ CSSD]2011-06-27 17:45:02.940 [1199618400] >TRACE: clssgmClientConnectMsg: Connect from con(0x7af170) proc(0x773c10) pid(11998) proto(10:2:1:1) [ CSSD]2011-06-27 17:45:16.233 [1199618400] >TRACE: clssgmClientConnectMsg: Connect from con(0x7a2d80) proc(0x77a900) pid(12822) proto(10:2:1:1) [ CSSD]2011-06-27 17:45:45.970 [1199618400] >TRACE: clssgmClientConnectMsg: Connect from con(0x77abf0) proc(0x777e60) pid() proto(10:2:1:1) [ CSSD]2011-06-27 17:46:46.330 [1199618400] >TRACE: clssgmClientConnectMsg: Connect from con(0x77abf0) proc(0x777e60) pid() proto(10:2:1:1) [ CSSD]2011-06-27 17:50:21.821 [1241577824] >WARNING: clssnmPollingThread: node orinoco2 (2) at 50% heartbeat fatal, eviction in 29.560 seconds [ CSSD]2011-06-27 17:50:22.823 [1241577824] >WARNING: clssnmPollingThread: node orinoco2 (2) at 50% heartbeat fatal, eviction in 28.560 seconds [ CSSD]2011-06-27 17:50:36.831 [1241577824] >WARNING: clssnmPollingThread: node orinoco2 (2) at 75% heartbeat fatal, eviction in 14.550 seconds [ CSSD]2011-06-27 17:50:37.823 [1241577824] >WARNING: clssnmPollingThread: node orinoco2 (2) at 75% heartbeat fatal, eviction in 13.560 seconds [ CSSD]2011-06-27 17:50:45.829 [1241577824] >WARNING: clssnmPollingThread: node orinoco2 (2) at 90% heartbeat fatal, eviction in 5.560 seconds [ CSSD]2011-06-27 17:50:46.831 [1241577824] >WARNING: clssnmPollingThread: node orinoco2 (2) at 90% heartbeat fatal, eviction in 4.560 seconds [ CSSD]2011-06-27 17:50:47.833 [1241577824] >TRACE: clssnmPollingThread: node orinoco2 (2) is impending reconfig [ CSSD]2011-06-27 17:50:47.833 [1241577824] >WARNING: clssnmPollingThread: node orinoco2 (2) at 90% heartbeat fatal, eviction in 3.550 seconds [ CSSD]2011-06-27 17:50:48.825 [1241577824] >TRACE: clssnmPollingThread: node orinoco2 (2) is impending reconfig [ CSSD]2011-06-27 17:50:48.825 [1241577824] >WARNING: clssnmPollingThread: node orinoco2 (2) at 90% heartbeat fatal, eviction in 2.560 seconds [ CSSD]2011-06-27 17:50:49.827 [1241577824] >TRACE: clssnmPollingThread: node orinoco2 (2) is impending reconfig [ CSSD]2011-06-27 17:50:49.827 [1241577824] >WARNING: clssnmPollingThread: node orinoco2 (2) at 90% heartbeat fatal, eviction in 1.560 seconds [ CSSD]2011-06-27 17:50:50.829 [1241577824] >TRACE: clssnmPollingThread: node orinoco2 (2) is impending reconfig [ CSSD]2011-06-27 17:50:50.829 [1241577824] >WARNING: clssnmPollingThread: node orinoco2 (2) at 90% heartbeat fatal, eviction in 0.560 seconds ==================================================================================================== ========================== ====== /var/log/messages ==================================================================================================== ======== Jun 27 17:45:01 orinoco1 su(pam_unix)[11911]: session opened for user oracle by (uid=0) Jun 27 17:45:01 orinoco1 su(pam_unix)[11911]: session closed for user oracle Jun 27 17:47:40 orinoco1 kernel: bnx2: eth0 NIC Link is Down Jun 27 17:47:41 orinoco1 kernel: LLT INFO V-14-1-10205 link 2 (eth0) node 0 in trouble Jun 27 17:47:41 orinoco1 kernel: LLT INFO V-14-1-10205 link 2 (eth0) node 2 in trouble Jun 27 17:47:41 orinoco1 kernel: LLT INFO V-14-1-10205 link 2 (eth0) node 5 in trouble Jun 27 17:47:41 orinoco1 kernel: LLT INFO V-14-1-10205 link 2 (eth0) node 1 in trouble Jun 27 17:47:41 orinoco1 kernel: LLT INFO V-14-1-10205 link 2 (eth0) node 3 in trouble Jun 27 17:47:43 orinoco1 kernel: bnx2: eth0 NIC Link is Up, 1000 Mbps full duplex Jun 27 17:47:44 orinoco1 kernel: LLT INFO V-14-1-10024 link 2 (eth0) node 0 active Jun 27 17:47:44 orinoco1 kernel: LLT INFO V-14-1-10024 link 2 (eth0) node 2 active Jun 27 17:47:44 orinoco1 kernel: LLT INFO V-14-1-10024 link 2 (eth0) node 5 active Jun 27 17:47:44 orinoco1 kernel: LLT INFO V-14-1-10024 link 2 (eth0) node 1 active Jun 27 17:47:44 orinoco1 kernel: LLT INFO V-14-1-10024 link 2 (eth0) node 3 active Jun 27 17:47:45 orinoco1 kernel: o2net: connection to node orinoco2 (num 1) at 199.40.40.234:7777 has been idle for 10.0 seconds, shutting it down. Jun 27 17:47:45 orinoco1 kernel: (0,0)2net_idle_timer:1426 here are some times that might help debug the situation: (tmr 1309168055.597322 now 1309168065.596662 dr 1309168055.597308 adv 1309168055.597329:1309168055.597330 func (d5542a8e:504) 1309168035.598570:1309168035.598693) Jun 27 17:47:45 orinoco1 kernel: o2net: no longer connected to node orinoco2 (num 1) at 199.40.40.234:7777 ==================================================================================================== ========================== From the above messages we confirmed that, server has been rebooted to keep cluster integrity due to network interface failure logged in /var/log/mesages... But can somebody confirm if this is due to :- i) private interconnect network failure or ii) vote disk issue Please also confirm that this is not due to glibc bug which causes random eviction. Note that O/S is running on Red Hat Enterprise Linux AS release 4 (Nahant Update 4) with 2.6.9-42.ELsmp. Glibc : glibc-2.3.4-2.25
VIEWS ON THIS POST

299

Posted on:

Thursday 15th November 2012
View Replies!

RMAN backup differential

Hi all, I have a quick question about RMAN differential, I have full backup incremental level=0, and incremental level=1 for every day. Any way, the question is about the differential: after the first incremental the size was about 13M (8M for control and the rest for DF) total, nothing changed in the database and I run another incremental and go down to 11M and I run another incremental and go down to 9.5M. the question is how come the file size keep reducing\t I thought level 1 would cover all of the previous incremental backup.
VIEWS ON THIS POST

91

Posted on:

Friday 16th November 2012
View Replies!

Restoring datafiles with RMAN

HI Can we restore RMAN's hotbackup from one db into different database(different catalog) TX
VIEWS ON THIS POST

98

Posted on:

Friday 16th November 2012
View Replies!

Reorganising Existing tablespace ...

I am Raju C padaria from INDIA. I have been using ORACLE 8.0.4 on AIX 4.3 with Windows NT 4.0 Client. Following is my oracle table space layout allocated to different users initially. Now I need to reorganise one of the tablespaces (prdtst). The size of 'prdtst' tablespace is 100 MB. I need to reduce 100 MB from 'prod' tablespace & add it to 'prdtst' tablespace. How can I make it Kindly guide me in this regard as I am new DBA. Tablespace Size (Mb) Free (Mb) % Free -------------------- --------------- ----------- ------ FIN 200 107 54 HRD 300 56 19 MRKT 100 45 45 MTRL 100 25 25 PRDTST 50 25 51 PROD 500 285 57 QA 150 119 79 ------------------------------------------------------------------------ Thank U well in advance. Raju C Padaria (Padariar@fag.co.in) FAG Bearings INDIA Limited
VIEWS ON THIS POST

157

Posted on:

Friday 16th November 2012
View Replies!

remote connectivity

Hi Guys, I am not able to connect remote oracle server from my client.The remote system is connected through a satellite link.What are the steps I have to follow in configuring Oracle server to access from client.Reply immediately.
VIEWS ON THIS POST

240

Posted on:

Friday 16th November 2012
View Replies!

Regarding Archive log files .....

Hi Team, I need some real time stuff how does at Enterprise level one backup and restore archived log files What kind strategies do they follow. I want know more on this topic. Can anyone please help me out. Please dont say that we take the backup's of archives on tapes and CD's. What i want to know is that how exactly the Database Adminstrator's restore the backup of the archives generated and how many sets of Archived log file Backups are generally maintained in the Production Environment. I just want to know various strategies that would be implemented especially on Archive log files are concerned. Best
VIEWS ON THIS POST

61

Posted on:

Friday 16th November 2012
View Replies!

Problem with 8.1.7 installation

Hi We have Oracle 8.1.6(64 bit) running on HP UX 11.0 server. The LISTENER is running on default port 1521. Now i installed Oracle 8.1.7 server in a different $ORACLE_HOME. The installation was clean without any errors. The problem is when i am trying to run SVRMGRL, i am getting the following error. ------------------------------------------------------------------- $ svrmgrl Oracle Server Manager Release 3.1.7.0.0 - Production Copyright (c) 1997, 1999, Oracle Corporation. Rights Reserved. sh: /u06/oracle/product/8.1.7/bin/oracle: Execute permission denied. sh: /u06/oracle/product/8.1.7/bin/oracle: Execute permission denied. ORA-12547: TNS:lost contact SVRMGR> ----------------------------------------------------------------- I am also not able to create a new LISTENER for 8.1.7. Could anyone please explain why the prblem is occuring.
VIEWS ON THIS POST

157

Posted on:

Friday 16th November 2012
View Replies!

Problem in DBWR

Hi all, I have found the following lines in dbwr trace file. Dump file /....../bdump/dbwr_745.trc Oracle7 Server Release 7.3.4.0.0 - Production With the distributed option PL/SQL Release 2.3.4.0.0 - Production ORACLE_HOME = /a/app/oracle/product/7.3.4 System name: SCO_SV Node name: rane Release: 3.2 Version: 2 Machine: i386 Instance name: rane Redo thread mounted by this instance: 1 Oracle process number: 3 Unix process pid: 745, image: ora_dbwr_imms *** 2004.03.12.10.07.21.000 *** SESSION ID:(2.1) 2004.03.12.10.07.21.000 ORA-01157: cannot identify data file 5 - file not found ORA-01110: data file 5: '......./dbs/C:SPANTEMP.DBF' ORA-07360: sfifi: stat error, unable to obtain information about file. SCO System V/386 Error: 2: No such file or directory after querrying the v$datafile view, the following details are there in view for file # 5 5 RECOVER READ WRITE 10782490 0 102400 /..../dbs/C:SPANTEMP.DBF Database is running perfectly ok. please suggest the solution / meaning of this problem.
VIEWS ON THIS POST

54

Posted on:

Friday 16th November 2012
View Replies!

PL/SQL table

I need to call stored procedure which accepts number as input parameter and out parameter is PL/SQL table from java program. Does anyone has the sample code for the same. I appriciate if you could send me on sk801@hotmail.com
VIEWS ON THIS POST

110

Posted on:

Friday 16th November 2012
View Replies!

Peoplesoft DBA

Hi I was just wundering if somecould point out their exeperinces as a Peoplesoft DBA ..and how different it is from being a normal oracle DBA .Any forums on the net that would help me jump start into people soft DBA regards Hrishy
VIEWS ON THIS POST

82

Posted on:

Friday 16th November 2012
View Replies!

parameter file

How can we know that which parameter file the instance is using. Is there any dictionary view..
VIEWS ON THIS POST

196

Posted on:

Friday 16th November 2012
View Replies!

RE: format 2 decimal in Oracle.

i would like to ask how can i convert to 2 decimal from varchar2(100) text table column. i just use the following sql but can't ouput my expected output. select to_char(nvl(col1,0),'9999.99') from tableA; it return .00 if the column is empty. how can i output 0.00 also, i save as 1,2,,3 in another column. how can i convert to month by using 1,2,3... covert 1 to JAN convert 2 to FEB, etc.
VIEWS ON THIS POST

136

Posted on:

Tuesday 20th November 2012
View Replies!

Connect to a oracle timesTen database using .net

, how do I connect to a Oracle times ten database and run queries using .net , I an coding in c# . I need to connect to database ten timesand get details from tables. any idea
VIEWS ON THIS POST

392

Posted on:

Tuesday 20th November 2012
View Replies!

csv not being read properly

Dear Friends, I have a small csv. When it contains all text it is being read by oledb. But if it contains numbers it is not being read properly. So if my csv is like this: OLD_CUST, NEW_CUST G,\t\t\t\t H I,\t J M,\t N 65464653, 644646636 98765465, 988924212 G,\t 6546546 H.J,N are not being read. 1)How to tackle the above problem 2)Is there a better provider for CSV.Excel.. somethingcalled ACEIf yes pls provide details 3) How to restrict the number of rows to be read eg 5000 when reading using OledbAdapter
VIEWS ON THIS POST

233

Posted on:

Tuesday 20th November 2012
View Replies!

OLAP Datebase Project - SQL Reports - Large Sample Database

I am trying to get some ideas on a project I'm doing for school.Essentially what wewant to do is: get a good large sample database, import it into a OLAP database, run tests on the data, anddevelop sql reports to display our results maybedevelopinga cool way to display it all. Some of the things that weve already looked at are the great plains accounting software andTelerik Sales Dashboard I've never done this an wouldappreciateany ideas on good / interesting ideas for our project.
VIEWS ON THIS POST

78

Posted on:

Tuesday 20th November 2012
View Replies!

How to learn mysql.

How can i learn mysql. i learn something something from ebooks but how i can run our mysqlcommand in asp.net mysql apps
VIEWS ON THIS POST

143

Posted on:

Tuesday 20th November 2012
View Replies!

Oracle ORA-12704: character set mismatch after upgrade from Beta 2

I am starting to get the following error when I try to insert or update into an Oracle 8i database after I upgraded to production version of ASP.Net 2.0! Something with the character set but I am not sure. Same application server works fine with Oracle 10G. How do you force a character set when sending queries to database servers in ASP.Net ORA-12704: character set mismatch Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OracleClient.OracleException: ORA-12704: character set mismatch Source Error: Anunhandledexceptionwasgeneratedduringtheexecutionofthecurrentwebrequest.Informationregardingtheoriginandlocationoftheexceptioncanbeidentifiedusingtheexceptionstacktracebelow. Stack Trace: [OracleException (0x80131938): ORA-12704: character set mismatch ] System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) +204 System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) +1024 System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) +447 System.Data.OracleClient.OracleCommand.ExecuteNonQuery() +108 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +401 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +721 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1215 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +858 System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +199 System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102 Version Information:Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
VIEWS ON THIS POST

145

Posted on:

Tuesday 20th November 2012
View Replies!

Problems with caling StoredProcedure from webform to ms sql db

Hi ! I have done this before but this time i don't know what;s wrong. I made a fonnection with connevtion to db and calling few stopredprocedures this is the code stringStoredSproc="THIRD_SELECTION_NEW"; stringSecondSproc="SelectFromTemp"; stringThirdSproc="TruncateTemp";///checkifmailsent SqlConnectionconn=ConnectionHelper.GetConnection();///connectionclassi'musing SqlCommandSecondcommand=newSqlCommand(SecondSproc,conn); SqlCommandThirdCommand=newSqlCommand(ThirdSproc,conn); SqlCommandcommand=newSqlCommand(StoredSproc,conn); now, i'm trying to write this: command.Commandtype= Commandtype.StoredProcedure and CommandType Property is not availble. why please help!
VIEWS ON THIS POST

260

Posted on:

Tuesday 20th November 2012
View Replies!

Excel - Format of the initialization string does not conform to specification starting at index...

... I'm trying to retreive info from a Excel file (Office XP) but I keep getting the same ERROR over and over again.I cant seem to get the connectionString to GET the Excel file. The ERROR is: Format of the initialization string does not conform to specification starting at index 48... Here's my code: public static List GetInfo() { \t\t\t\t\t string filename = "@C:\File.xls"; \t\t\t\t\t string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filename+";Extended Properties=Excel 9.0;"; \t\t\t\t\t \t\t\t\t\t DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb"); \t\t\t\t\t List returnList = new List(); \t \t\t\t\t\t using (DbConnection connection = factory.CreateConnection()) \t\t\t\t\t { \t\t\t\t\t\t\t connection.ConnectionString = connectionString;\t\t //Here's where the ERROR occurs \t\t\t\t\t\t\t \t\t\t\t\t\t\t using (DbCommand command = connection.CreateCommand()) \t\t\t\t\t\t\t { \t\t\t\t\t\t\t \t .......... \t \t \t \t \t \t .......... \t\t\t\t\t\t\t\t\t .......... I've searched the net, i've been at this for hours, i'm loosing my pacience [:P] I've seen that this error has occured to a lot of people but their solutions haven't helped at all [:^)] Can someone please help me out
VIEWS ON THIS POST

523

Posted on:

Tuesday 20th November 2012
View Replies!

Adding a DBNull value as a parameter to a Int32 database field.

I am using the AddInParameter to add a Int32 value as a SQL paramter. The problem I am having is that it is possible for this value to be null. When I try using logic to return either the int value or a DBNull value as the parameter, because the parameter type is DbType.Int32 I get a build error. How can I, based on the value of the insert integer, either add the actual int value or a DBNull value in the AddInParameter method
VIEWS ON THIS POST

86

Posted on:

Tuesday 20th November 2012
View Replies!

MySQL connection and performance

I have aquestion on MySQLconnection performance, please help. I'm wondering if it does effect ther performance to close connection every time after every call to the database let say I have ten requests to the database for every .aspx request the code would looks like: using(MySQLConnection conn = new MySQLConnection()){ \t\t conn.Open(); \t\t request 1 } --- --- --- using(MySQLConnection conn = new MySQLConnection()){ \t\t conn.Open(); \t\t request 10 } another approach would be trying to reuse one connection by passing opened connection to every method call for those 10 request. I was told these two approaches are not different in MSSQL connection, but I'm not sure if MySql would work the same way. my last concert is disable connection pool on the connection string "connection pool=false;", does it that mean every connection is created freshly does it effect performance when disabling connection pool Thank you for help. Steven
VIEWS ON THIS POST

210

Posted on:

Wednesday 21st November 2012
View Replies!

when max(date) is not working: getting the most recent date

Hi Guys, I have the following simple query: SELECT\t\t MAX(T_SHIPMENT.ROUTERECORDTIME) AS MAXDATE, T_JOB.VEHICLEID FROM\t\t\t\t T_JOB, T_SHIPMENT WHERE\t\t T_JOB.SHIPMENTID = T_SHIPMENT.SHIPPKID GROUP BY T_JOB.VEHICLEID HAVING\t\t (MAX(T_SHIPMENT.ROUTERECORDTIME) IS NOT NULL) Which is working fine, I get two results - both are the most recent dates for the vehicles. When I now add LONGITUDE the query looks like this: SELECT\t\t MAX(T_SHIPMENT.ROUTERECORDTIME) AS MAXDATE, T_JOB.VEHICLEID, T_SHIPMENT.LONGITUDE FROM\t\t\t\t T_JOB, T_SHIPMENT WHERE\t\t T_JOB.SHIPMENTID = T_SHIPMENT.SHIPPKID GROUP BY T_JOB.VEHICLEID, T_SHIPMENT.LONGITUDE HAVING\t\t (MAX(T_SHIPMENT.ROUTERECORDTIME) IS NOT NULL) But the result.. well now I get 13 rows ! How can that be, and how can I get back to my two ones (which is the right result)
VIEWS ON THIS POST

169

Posted on:

Wednesday 21st November 2012
View Replies!

how to create the dsn for a database in mysql

hi, how to create the dsn for a database in my sql and where wil be the account manger and my products list in my sql Ramesh
VIEWS ON THIS POST

189

Posted on:

Wednesday 21st November 2012
View Replies!

SQL question

I have this query: select * from... where \t firstName = "A" or firstName = "B" I need it to be like: where \t firstName = ("A" or "B") Does someone know how can I do that
VIEWS ON THIS POST

99

Posted on:

Wednesday 21st November 2012
View Replies!

Read Excel with unknow table name

\t I need to read an excel file using Oledb. Is there any way to retrieve the data from the 1st table without knowing the table name (not fix) I have read a few articles saying to use an select query. If not, when exporting an datagrid to excel, is there any way to fix the 1st sheet name (table name) Let me know. Thks. Ryan
VIEWS ON THIS POST

240

Posted on:

Wednesday 21st November 2012
View Replies!

Select specified row of record?

Hi all, I wish to know if there any way I can select a specified rows of record from a SELECT statement e.g. I have a 1000 record, but all I want is the 101th to 150th
VIEWS ON THIS POST

176

Posted on:

Wednesday 21st November 2012
View Replies!

Oracle Error ORA-01461: can bind a LONG value only for insert into a LONG column

Hi My C# code: try \t\t{ \t\ttestConn.Open(); \t\t \t\tOracleCommand cmd1 = new OracleCommand("insert into clobtest(:clob,id) values(:clob,'5')",testConn); \t\tOracleParameter op = cmd1.Parameters.Add("clob",OracleType.Clob); \t\top.Value = clob;// clob is more than 4000 characters text. \t\tcmd1.ExecuteNonQuery(); \t\t} \t\tcatch(Exception ex) \t\t{ \t\tMessageBox.Show(ex.ToString()); \t\t} Oracle Error ORA-01461: can bind a LONG value only for insert into a LONG column
VIEWS ON THIS POST

201

Posted on:

Wednesday 21st November 2012
View Replies!

MySQL Tables

Hi , Does anyone has any idea to share with me how to retrieve all the tables in particular MySQL database Please kindly include the code(VB)
VIEWS ON THIS POST

143

Posted on:

Wednesday 21st November 2012
View Replies!

storing search results in a table

I am trying to insert the criteria the user and search info into another table. I am using the einfodesigns mysql driver. I get this message when I try to do this: Compiler Error Message: CS0117: 'eInfoDesigns.dbProvider.MySqlClient.MySqlDataAdapter' does not contain a definition for 'ExecuteNonQuery' Here is my code: MySqlConnectionoMySqlConn=newMySqlConnection(); oMySqlConn.ConnectionString="DataSource=mysql.server.co.uk;"+ "Database=server;"+ "UserID=server;"+ "Password=chemical;"+ "CommandLogging=false"; //oMySqlConn.Open(); //OdbcConnectionmyConnection=newOdbcConnection //(mySQLConnStr); stringSQL="selectDate,payroll,First,Last,Rank,Number,Division,Reason,PODatefromall_by_payrollwhere"+\tDropDownList1.SelectedItem.Value+"='"+TextBox1.Text+"'unionselectDate,payroll,First,Last,Rank,Number,Division,Reason,PODatefrompayincwhere"+\tDropDownList1.SelectedItem.Value+"='"+TextBox1.Text+"'ORDERBYDateASC"; stringSQL2="INSERTINTOresults"+ "(Username,Search)"+ "VALUES('"+User.Identity.Name+"','" +TextBox1.Text.ToString()+"')"; MySqlDataAdaptermyCommand=newMySqlDataAdapter(); MySqlDataAdaptermyCommand2=newMySqlDataAdapter(); myCommand.SelectCommand=newMySqlCommand(SQL,oMySqlConn); myCommand2.SelectCommand=newMySqlCommand(SQL2,oMySqlConn); myCommand2.ExecuteNonQuery(); DataSetds=newDataSet(); myCommand.Fill(ds,"payinc"); MyDataGrid.DataSource=ds.Tables["payinc"].DefaultView; MyDataGrid.DataBind();
VIEWS ON THIS POST

228

Posted on:

Wednesday 21st November 2012
View Replies!

Save Password disabled in Login screen

I have Toad 9.6.1 already installed on my machine and have now installed Toad 10.6 . unfortunately the Save Passwords option on the Connection screen is disabled. Could you tell me how do I enable that option.
VIEWS ON THIS POST

96

Posted on:

Saturday 29th December 2012
View Replies!