Trying to make a generic PL/SQL procedure to export data in specific XML format, e.g. Excel XML. Let's say the procedure accepts a string with the SELECT query to EXECUTE IMMEDIATE. This requires access to data types of each column of the resulting rowset, which -- seeing as the procedure is to be generic -- is only known after the que...
I have plsql procedure which accepts certain parameters e.g. v_name, v_country, v_type. I wish to have a cursor with a select statement like this: select column from table1 t1, table2 t2 where t1.name = v_name and t1.country = v_country and t1.id = t2.id and t2.type = v_type If certain parameters are empty can I only add the relevant...
Hi, I want to create a lst file from a sql script Here is my code: BEGIN DECLARE loc_bia_foracid GAM.foracid%TYPE; loc_fba_foracid GAM.foracid%TYPE; out_spool VARCHAR2(32000); BEGIN loc_bia_foracid:= '&loc_bia_foracid'; loc_fba_foracid:= '&loc_fba_foracid'; out_spool := out_spool || '|' || loc_bia_foracid; out_spool := out_spool || '...
hi i have a field in sql named as address which is of 80 char. i want to put this field into 2 fields addr1 and addr2 of 40 char each. how do i do it. ...
Hi, how do we call a C function from an SQL script? int get_next_fbill_b2kId_seq_num(b2kIdType seq_val,bankIdPtrType bank_id) { validate_dc_alias(dcAlias); tbaDateType sysDate; tbaGetSystemDateTime(sysDate,NULL,NULL); /* returns in TBA date format */ sysDate[10] = EOS; get_seq_value(next_num_char, 0, FBILL_B2KID_SR...
I have a set of tables in Oracle and I would like to identify the table that contains the maximum number of rows. So if, A has 200 rows, B has 345 rows and C has 120 rows I want to be able to identify table B. Is there a simple query I can run to achieve this? Edit: There are 100 + tables so I am looking for something generic. ...
I have a date variable as 24-dec-08 I want only the 08 component from it. How do I do it in a select statement? e.g.: select db||sysdate (this is the component where I want only 08 from the date) from gct; How do i do it? ...
It seems vsize() and length() return the same results. Does anyone know of a practical example of when to use vsize instead of length? select vsize(object_name), length(object_name) from user_objects Result: /468ba408_LDAPHelper 20 20 /de807749_LDAPHelper 20 20 A4201_A4201_UK 14 14 A4201_PGM_FK_I 14 14 A4201_PHC_FK_I 14 14 ...
When I try to create a trigger in schema A for a table located in schema B, I get an ora error : insufficient privileges. What privileges do I need? ...
We've a table with a varchar2(100) column, that occasionally contains carriage-return & line-feeds. We should like to remove those characters in the SQL query. We're using .. REPLACE( col_name, CHR(10) ) .. which has no effect, however replacing 'CHR(10)' for a more conventional 'letter' character proves that the REPLACE function work...
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 ...
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...
So I've got a for loop that processes a list of IDs and has some fairly complex things to do. Without going into all the ugly details, basically this: DECLARE l_selected APEX_APPLICATION_GLOBAL.VC_ARR2; ...snip... BEGIN -- get the list ids l_selected := APEX_UTIL.STRING_TO_TABLE(:P4_SELECT_LIST); ...
I work in a group of about 25 developers. I'm responsible for coming up with the database design (tables, views, etc) and am called apon for performance tuning when necessary. There are a couple of different applications that connect. Database access is via JDBC, hibernate, and iBatis SQL maps. Developers with various levels of exper...
Say I have an Oracle PL/SQL block that inserts a record into a table and need to recover from a unique constraint error, like this: begin insert into some_table ('some', 'values'); exception when ... update some_table set value = 'values' where key = 'some'; end; Is it possible to replace the ellipsis for something in ...
The following PL/SQL will not execute and simply returns 'Invalid Argument' as an error. I've gone as far as commenting out eavery line one by one and I can vary the error I receive but I cannot get the proc to run as I cannot identify where the error lies and it may well lie in permissions or the executing user but any help will be re...
The stored procedures being written here currently concats the parameters to the queries: 'Select * From Names Where Name = ' || prmName || ' Order By ' || prmSortField Is it possible to parameterize this query inside the stored procedure? Possibly like: query = 'select * From Names Where Name = @name Order By ' || prmSortField call(...
I'm trying to implement the "blog this" function from Flickr using the BloggerAPI to my pl/sql based CMS. When Flickr sends me the posting transaction, the HTTP transaction looks like this: POST /pls/website/!pkg.procAPI HTTP/1.1 Host: www.mydomain.com Accept: */* User-Agent: Flickr Content-Type: text/xml; charset=utf-8 Content-Length:...
Background: I have a collegue who needs to convert text from a PL/SQL method into XML compliant text, as he is constructing a excel spreadsheet by updating a text template. ...
Just wondering if there is a better way to write the following PL/SQL piece of code in ORACLE ? IF(p_c_courtesies_cd is not null OR p_c_language_cd is not null OR v_c_name is not null OR v_c_firstname is not null OR v_c_function is not null OR p_c_phone is not null OR p_c_mobile is not null OR p_c_fax is not null OR v_c_email is not n...