Search results for query: *

Di, I need certain clarification from you a. Are you opening a cursor defined by the select stament and returning that cursor? b. Are you planning to use the value returned by the select stement inside the same procedure? Reagrds, Gunjan

Hi, If this code needs to go inside stored procedure then you can use dynamic sql and then execute it. Here is the code snippet lstr := 'select a.eid, (nvl(a.dname, ''Unassigned'')), b.flagtype '; lstr := lstr || 'from table a, table b '; lstr := lstr || 'where a.eid = b.eid '; if...

Hi, I do not have Oracle 8i version with me so cannot test this code for oracle 8i. Hence if any error comes during implementation of custom made aggregate function then let me know. Also I cannot tell anything about the performance of this query. Probably you are better off with PL/SQL :-)...

Hi, You can achieve by executing this insert into table1 select * from table2; Regards, Gunjan

Taupirho, This does not work in every case. Let us see an example. testhier.sql create table manuf_structure ( component_part varchar2(20) not null, part_no varchar2(20) null, qty_per_assembly number not null ) ; insert into manuf_structure (component_part...

oops .. sorry the formatting mistake. Can lead to confusion. The code should be. select level, part_no, component_part, eval_val(sys_connect_by_path(qty_per_assembly, '*')) from manuf_structure CONNECT BY PRIOR a.component_part = a.part_no START WITH a.part_no =...

Hi, I have not tested this but you can use this query. But you need to create a function to work. create or replace function eval_val (expr in varchar2) return number as new_expr varchar2(200); expr_val varchar2(30); begin new_expr := trim(expr); if instr(new_expr, '*', 1) = 1...

Hi, The windows equivalent for unix date is DATE itself. You can use like this H:\>echo %DATE% Tue 08/01/2006 so if you want to pass the date from windows, you can refer to date inside the sql block as %DATE%. Let us know if this resolves your issue. Regards, Gunjan

Hi, If you are using oracle 9i then you can use owa_patter.change for the formatting. Steps: 1. Create a function to return the changed value 2. Call that function from select command Here is the example SQL> create or replace function replace_owa 2 ( 3 p_str in varchar2, 4...

Hi, If you are using Oracle 9i or above then you can use owa_pattern also. Although it is documented in 10g but still works in 9i. Here is the code SQL> create or replace function test_string 2 ( 3 strtochk in varchar2, 4 expr in varchar2 5 ) 6 return number 7 as...

Oops, no need to write inline view. you can use like this select (length(meds)-length(replace(meds,'qd')))/2 from table Regards, Gunjan

Hello Kokiri, How about this select (a.col1 - b.col1)/2 from (select length(meds) col1 from table_name ) a, select length(replace(meds,'qd')) col1 from table_name ) b ; I am assuming your column_name is meds let us know if this suffixes your need...

Hello Alex, Your Code select role_id from tbl_user_access 'error occurs here into i_role_id where cnumber = user_id; Whereas this code should be select role_id into i_role_id from tbl_user_access 'error occurs here where cnumber = user_id; Regards, Gunjan

Hi Barbara, I guess the object still resides in schema "DEVELOPER". To create a view in FUHRMANN user that is accessing the tables in DEVELOPER schema, you need to grant explicit select privilege on the tables in DEVELOPER schema to FUHRMANN (means it has to be given as the code below shows...

Hi George, There are plenty of good books and on-line materials for Oracle PL/SQL. Here is one http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm or you can go to http://tahiti.oracle.com and get all the documentation of Oracle. I do not know about other books but I...

Hi George, Here is the code that will do what you need to do. I have made bold to denote where and how the code is different. Also given some comments as to how it is different. I recommend that you read the Oracle PL/SQL guide for the complete understanding and syntax of PL/SQL create or...

Hi, In the above case (clien windows and server unix), our suggestion is not going to work. And I presumed a thing that (s)he has access to server and so based on that posted it. Well A LESSON LEARNED. Anyway I am also learning and its part of learning... :-) Regrads, Gunjan

How do I get to the file from SQLPLUS .I mean how can i view the file from SQLPLUS .I tried GET but it says unable to open. If the file is on Unix (same as server) then you can view the cntent of file by sql> host vi <file name> or sql> host more <file name> Regards, Gunjan

Hi Outis, You can get the ref material from http://tahiti.oracle.com You can check the documentation from there. For example Oracle 8i SQLPLUS user guide SQL*Plus User's Guide and Reference (book) you can refer. Regards, Gunjan

Hi Outis, I believe you are asking something like this Pseudocode ******** Get some value from user If value is 'X' then Condition 1 Elseif value is 'Y' Condition 2 endif Now the select query is select <something> from <some table> where <condition 1 or codition 2> End...