Home » SQL & PL/SQL » SQL & PL/SQL » Function to insert (Oracle database Enterprise 11.2.0.3 Linux 5.8)
icon5.gif  Function to insert [message #580311] Fri, 22 March 2013 12:19 Go to next message
progkcp
Messages: 10
Registered: March 2013
Location: Washington DC USA
Junior Member
I'm trying to figure out a way to write a function to parse through a clob and extract certain values to insert into a table. I've written the following and it compiles but it doesn't work and I'm hoping that someone could tell me if I'm going in the correct direction.


create or replace function all_fields
(type_field VARchar2,
domain_field VARchar2)
return VARchar2 as
typefield VARchar2(100) :=type_field;
domainfield VARchar2(100) :=domain_field;
rtn VARchar2(100);
data varchar2(500) := '{"type":"Feature","properties":{"_type":"mu.acs_topline","_domain_id":"010550102002","name":';
begin
dbms_output.put_line(substr(data, instr(data, '"',instr(data, domain_field), 2) +1, instr(data, '"',instr (data, domain_field), 3)-instr(data, '"',instr(data, domain_field), 2) -1 ));
rtn := substr(data, instr(data, '"',instr(data, domain_field), 2) +1, instr(data, '"',instr (data, domain_field), 3)-instr(data, '"',instr(data, domain_field), 2) -1 );
return rtn;
end;



DECLARE
c varchar2(30);
BEGIN
c := f_field('_domain');
dbms_output.put_line('The function says: ' || c);
insert into target ( domain_id) values(f_field('_domain'));
-- insert into target ( type_id) values(f_field('_type'));
--insert into target ( type_id) values('test'));
commit;
END;
Re: Function to insert [message #580313 is a reply to message #580311] Fri, 22 March 2013 12:21 Go to previous messageGo to next message
joy_division
Messages: 4515
Registered: February 2005
Location: East Coast USA
Senior Member
progkcp wrote on Fri, 22 March 2013 13:19
I've written the following and it compiles but it doesn't work


How does this help someone find the problem?
Re: Function to insert [message #580314 is a reply to message #580311] Fri, 22 March 2013 12:29 Go to previous messageGo to next message
progkcp
Messages: 10
Registered: March 2013
Location: Washington DC USA
Junior Member
I guess I figured that something about my approach was completely misguided and it would be obvious when looked at. Is this the right idea to do inserts from a function?
Re: Function to insert [message #580315 is a reply to message #580313] Fri, 22 March 2013 12:29 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well your anonymous block doesn't reference your function at any point
Re: Function to insert [message #580317 is a reply to message #580315] Fri, 22 March 2013 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
>I've written the following and it compiles but it doesn't work
while above is most likely 100% true, it is also 100% devoid of any actionable detail.

my car won't go.
tell me how to make my car go.

How are we to know what you actually expect to occur or see when code "works"?


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

please post Test Case
Re: Function to insert [message #580318 is a reply to message #580311] Fri, 22 March 2013 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

BlackSwan wrote on Sun, 10 March 2013 15:53
...
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel

Re: Function to insert [message #580321 is a reply to message #580311] Fri, 22 March 2013 12:57 Go to previous messageGo to next message
progkcp
Messages: 10
Registered: March 2013
Location: Washington DC USA
Junior Member
Sorry for the formatting mess. I think my problem has something to do with not sending enough values back to the function from the anonymous block.



create or replace function all_fields
  (type_field VARchar2, 
  domain_field VARchar2) 
  return VARchar2 as
  typefield VARchar2(100) :=type_field;
  domainfield VARchar2(100) :=domain_field;
  rtn VARchar2(100);
  data varchar2(500) := '{"type":"Feature","properties":{"_type":"mu.acs_topline","_domain_id":"010550102002","name":';
begin
  dbms_output.put_line(substr(data, instr(data, '"',instr(data, domain_field), 2) +1, instr(data, '"',instr (data,  domain_field), 3)-instr(data, '"',instr(data, domain_field), 2) -1 ));
rtn := substr(data, instr(data, '"',instr(data, domain_field), 2) +1, instr(data, '"',instr (data, domain_field), 3)-instr(data, '"',instr(data, domain_field), 2) -1 );
return rtn;
  end;



DECLARE
   c varchar2(30);
BEGIN
   c := all_fields('_domain');
   dbms_output.put_line('The function says: ' || c);
   insert into target ( domain_id, type_id) values(all_fields('_domain', '_type'));
   commit;
END;

Re: Function to insert [message #580322 is a reply to message #580321] Fri, 22 March 2013 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is still a mess.
If you don't know how to format code, learn it using SQL Formatter:
SQL> CREATE OR replace FUNCTION All_fields (type_field   VARCHAR2,
  2                                         domain_field VARCHAR2)
  3  RETURN VARCHAR2
  4  AS
  5    typefield   VARCHAR2(100) := type_field;
  6    domainfield VARCHAR2(100) := domain_field;
  7    rtn         VARCHAR2(100);
  8    data        VARCHAR2(500) :=
  9  '{"type":"Feature","properties":{"_type":"mu.acs_topline","_domain_id":"010550102002","name":'
 10  ;
 11  BEGIN
 12    dbms_output.Put_line(Substr(data, Instr(data, '"', Instr(data, domain_field)
 13                                      , 2)
 14                                      + 1, Instr(data, '"',
 15                                           Instr (data, domain_field), 3) -
 16                                           Instr(data, '"',
 17                                           Instr(data, domain_field), 2) - 1));
 18  
 19    rtn := Substr(data, Instr(data, '"', Instr(data, domain_field), 2)
 20                        + 1, Instr(data, '"', Instr (data, domain_field), 3) -
 21                             Instr(data, '"',
 22                                               Instr(data, domain_field), 2) - 1
 23           );
 24  
 25    RETURN rtn;
 26  END; 
 27  /

Function created.

SQL> DECLARE
  2      c VARCHAR2(30);
  3  BEGIN
  4      c := All_fields('_domain');
  5  
  6      dbms_output.Put_line('The function says: '
  7                           || c);
  8  
  9      INSERT INTO target
 10                  (domain_id,
 11                   type_id)
 12      VALUES     (All_fields('_domain', '_type'));
 13  
 14      COMMIT;
 15  END; 
 16  /
    VALUES     (All_fields('_domain', '_type'));
    *
ERROR at line 12:
ORA-06550: line 12, column 5:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 9, column 5:
PL/SQL: SQL Statement ignored

INSERT contains 2 fields, VALUES contains one value.

Regards
Michel

Re: Function to insert [message #580365 is a reply to message #580311] Sat, 23 March 2013 09:18 Go to previous messageGo to next message
progkcp
Messages: 10
Registered: March 2013
Location: Washington DC USA
Junior Member
It looks like I'm trying to return 2 values from a function, which won't work.

I'm thinking that I need to rewrite this into a procedure.

Thank you for the suggestions.

Re: Function to insert [message #580366 is a reply to message #580365] Sat, 23 March 2013 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
Even though twice requested, you have NEVER provided adequate Test Case for whatever you are attempting to do.

http://www.orafaq.com/wiki/Test_case
Re: Function to insert [message #580367 is a reply to message #580365] Sat, 23 March 2013 09:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
progkcp wrote on Sat, 23 March 2013 15:18
It looks like I'm trying to return 2 values from a function, which won't work.


This is not what it does look like.
Maybe this is what you are trying to do but this is not what it looks like.

Regards
Michel

Re: Function to insert [message #580370 is a reply to message #580322] Sat, 23 March 2013 10:33 Go to previous messageGo to next message
progkcp
Messages: 10
Registered: March 2013
Location: Washington DC USA
Junior Member
Michel, I'm trying to get 2 values into my insert. Could you suggest a way that I might do that?


VALUES (All_fields('_domain', '_type'));
*
ERROR at line 12:
ORA-06550: line 12, column 5:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 9, column 5:
PL/SQL: SQL Statement ignored

INSERT contains 2 fields, VALUES contains one value.

Regards
Michel
Re: Function to insert [message #580372 is a reply to message #580370] Sat, 23 March 2013 10:36 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

please post Test Case
Re: Function to insert [message #580375 is a reply to message #580370] Sat, 23 March 2013 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
progkcp wrote on Sat, 23 March 2013 16:33
Michel, I'm trying to get 2 values into my insert. Could you suggest a way that I might do that?


In an other way: change the table definition or change the code.

Regards
Michel

Re: Function to insert [message #580387 is a reply to message #580375] Sat, 23 March 2013 14:58 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Instead of having a function that calculates bits of what you need, I'd write a procedure that works out all the values and does the insert.
Re: Function to insert [message #580388 is a reply to message #580372] Sat, 23 March 2013 16:01 Go to previous messageGo to next message
progkcp
Messages: 10
Registered: March 2013
Location: Washington DC USA
Junior Member
I'm intending for this to be a test case.

Target table -
CREATE TABLE  "TARGET" 
   (	"DOMAIN_ID" VARCHAR2(100), 
	"TYPE_ID" VARCHAR2(100)
   )
/


Test data:
'{"type":"Feature","properties":{"_type":"mu.acs_topline","_domain_id":"010550102002","name":'

I'm intending to parse through the record until I get to _domain, then I use instr to select the 010550102002 value and insert into a table.

CREATE OR replace FUNCTION D_field (domain_field VARCHAR2) 
RETURN VARCHAR2 
AS 
  dom_field VARCHAR2(100) := domain_field; 
  rtn       VARCHAR2(100); 
  data      VARCHAR2(500) := 
'{"type":"Feature","properties":{"_type":"mu.acs_topline","_domain_id":"010550102002","name":' 
; 
BEGIN 
  rtn := Substr(data, Instr(data, '"', Instr(data, dom_field), 2) + 1, 
                      Instr(data, '"', Instr (data, dom_field), 3) - 
                      Instr(data, '"', 
         Instr(data, dom_field 
         ), 2) - 1); 

  RETURN rtn; 
END; 


I run my block:
DECLARE 
    dom VARCHAR2(30); 
BEGIN 
    INSERT INTO target 
                (domain_id) 
    VALUES      (D_field('_domain')); 

    COMMIT; 
END; 

1 row(s) inserted.

Then I select * from my table:

select * from target;

010550102002


Now I'm wondering how I might get an additional value from the record and insert it into the table at same the time that I'm inserting the first value.


Is this a test case?
Re: Function to insert [message #580391 is a reply to message #580388] Sat, 23 March 2013 18:22 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Functions aren't really designed to return multiple values (there are ways, but you can't use them directly in an insert).
So you need to either:
a) have a function you call multiple times, once for each column in the insert.
b) Have a procedure that returns all the values you need and call that before the insert.
c) some other approach (there are loads of ways of doing this)

What would be best depends on the exact requirements and where data really comes from (it's not really going to be hard-coded in the function is it?)
Re: Function to insert [message #580452 is a reply to message #580391] Sun, 24 March 2013 14:59 Go to previous messageGo to next message
progkcp
Messages: 10
Registered: March 2013
Location: Washington DC USA
Junior Member
CREATE OR replace FUNCTION D_field (infield VARCHAR2) 
RETURN VARCHAR2 
AS 
  my_field VARCHAR2(100) := infield; 
  rtn      VARCHAR2(100); 
  data     VARCHAR2(500) := 
'{"type":"Feature","properties":{"_type":"mu.acs_topline","_domain_id":"010550102002","name":' 
; 
BEGIN 
  rtn := Substr(data, Instr(data, '"', Instr(data, my_field), 2) + 1, 
                      Instr(data, '"', Instr (data, my_field), 3) - 
                      Instr(data, '"', 
         Instr(data, my_field 
         ), 2) - 1); 
  RETURN rtn; 
END; 




DECLARE 
    dom VARCHAR2(30); 
BEGIN 
    INSERT INTO target 
                (domain_id, 
                 type_id) 
    VALUES      (D_field('_type'), 
                 D_field('_domain')); 
    COMMIT; 
END; 




Thank you everybody. I've been able to do a simple insert of two values into my target table.

The actual data is in a CLOB, but I used a subset in the function of a very long column to try to figure out the details of the function and insert.

My goal here is to work towards moving large amounts of data using SQL Loader and PL/SQL from disk through staging tables and ultimately into a data warehouse.

I'm nowhere near the level of a PL/SQL developer, so I want to thank everybody for answering my basic questions.
Re: Function to insert [message #580457 is a reply to message #580452] Sun, 24 March 2013 22:32 Go to previous message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
>My goal here is to work towards moving large amounts of data using SQL Loader and PL/SQL from disk through staging tables and ultimately into a data warehouse.
data from EXTERNAL TABLE & directly into data warehouse!
There is no need for SQL Loader, PL/SQL or staging tables; inclusive!
KISS Principle!
Previous Topic: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval
Next Topic: cummulative running balance by condition
Goto Forum:
  


Current Time: Sun Sep 21 22:05:06 CDT 2014

Total time taken to generate the page: 0.09313 seconds